DBIx::ResultSet - Lightweight SQL query building and execution.
use DBIx::ResultSet; # Same arguments as DBI and DBIx::Connector. my $connector = DBIx::ResultSet->connect( $dsn, $user, $pass, $attr, #optional ); my $users = $connector->resultset('users'); my $adult_users = $users->search({ age => {'>=', 18} }); print 'Users: ' . $users->count() . "\n"; print 'Adult users: ' . $adult_users->count() . "\n";
This module provides an API that simplifies the creation and execution of SQL queries. This is done by providing a thin wrapper around SQL::Abstract, DBIx::Connector, DBI, Data::Page, and the DateTime::Format::* modules.
This module is not an ORM. If you want an ORM use DBIx::Class, it is superb.
Some tips and tricks are recorded in the cookbook.
In order to start using this module you must first configure the connection to your database. This is done using the connect() class method:
# Same arguments as DBI and DBIx::Connector. my $connector = DBIx::ResultSet->connect( $dsn, $user, $pass, $attr, #optional );
The connect() class method is a shortcut for creating a DBIx::ResultSet::Connector object. When created this way, the AutoCommit DBI attribute will default to 1. This is done per the strong recommendations by "new" in DBIx::Connector.
By default the underlying DBIx::Connector object will be called with mode('fixup'). While not recommended, you can change the default connection mode by specifying the ConnectionMode attribute, as in:
my $connector = DBIx::ResultSet->connect( $dsn, $user, $pass, { ConnectionMode => 'ping' }, );
Alternatively you could create a DBIx::ResultSet::Connector object directly and pass your own custom-rolled DBIx::Connector object. For example:
my $dbix_connector = DBIx::Connector->new( $dsn, $username, $password, { AutoCommit => 1 }, ); my $connector = DBIx::ResultSet::Connector->new( dbix_connector => $dbix_connector, );
my $old_rs = $connector->resultset('users')->search({ status => 0 }); my $new_rs = $old_rs->search({ age > 18 }); print 'Disabled adults: ' . $new_rs->count() . "\n";
Returns a new result set object that overlays the passed in where clause on top of the old where clause, creating a new result set. The original result set's where clause is left unmodified.
search() never executes SQL queries. You can call search() as many times as you like and iteratively build a resultset as much as you want, but no SQL will be issued until you call one of the manipulation or retrieval methods.
These methods create, change, or remove data.
$users_rs->insert( { user_name=>'bob2003', email=>'bob@example.com' }, # fields to insert ); # Executes: INSERT INTO users (user_name, email) VALUES (?, ?);
Creates and executes an INSERT statement.
$users_rs->update( { phone => '555-1234' }, # fields to update ); # Executes: UPDATE users SET phone = ?; $users_rs->search({ is_admin=>1 })->update({ phone=>'555-1234 }); # Executes: UPDATE users SET phone = ? WHERE is_admin = ?;
Creates and executes an UPDATE statement.
# Delete all users! $users_rs->delete(); # Executes: DELETE FROM users; # Or just the ones that are disabled. users_rs->search({status=>0})->delete(); # Executes: DELETE FROM users WHERE status = 0;
Creates and executes a DELETE statement.
$users_rs->insert({ user_name=>'jdoe' }); my $user_id = $users_rs->auto_pk(); # Executes (MySQL): SELECT LAST_INSERT_ID(); # Executes (SQLite): SELECT LAST_INSERT_ROWID(); # etc...
Currently only MySQL and SQLite are supported. Oracle support will be added soon, and other databases making their way in as needed.
These methods provide common shortcuts for retrieving data.
my $user = $users_rs->search({ user_id => 32 })->array_row( ['created', 'email', 'phone'], # optional, fields to retrieve ); print $user->[1]; # email
Creates and executes a SELECT statement and then returns an array reference. The array will contain only the first row that is retrieved, so you'll normally be doing this on a resultset that has already been limited to a single row by looking up by the table's primary key(s).
my $user = $users_rs->search({ user_id => 32 })->hash_row( ['created', 'email', 'phone'], # optional, fields to retrieve ); print $user->{email}; # email
This works just the same as array_row(), above, but instead it returns a hash ref.
my $disabled_users = $users_rs->array_of_array_rows( ['user_id', 'email', 'phone'], # optional, fields to retrieve ); print $disabled_users->[2]->[1];
Returns an array ref of array refs, one for each row returned.
my $disabled_users = $rs->array_of_hash_rows( ['user_id', 'email', 'phone'], # optional, fields to retrieve ); print $disabled_users->[2]->{email};
Returns an array ref of hash refs, one for each row.
my $disabled_users = $rs->hash_of_hash_rows( 'user_name', # column to key the hash by ['user_id', 'user_name', 'email', 'phone'], # optional, fields to retrieve ); print $disabled_users->{jsmith}->{email};
Returns a hash ref where the key is the value of the column that you specify as the first argument, and the value is a hash ref contains that row's data.
my $total_users = $users_rs->count();
Returns that number of records that match the resultset.
my $user_ids = $users_rs->column( 'user_id', # column to retrieve ); print 'User IDs: ' . join( ', ', @$user_ids );
Returns an array ref containing a single column's value for all matching rows.
Get DBI statement handles when you have more specialized needs.
my ($sth, @bind) = $rs->select_sth( ['user_name', 'user_id'], # optional, fields to retrieve ); $sth->execute( @bind ); $sth->bind_columns( \my( $user_name, $user_id ) ); while ($sth->fetch()) { ... }
If you want a little more power, or want you DB access a little more effecient for your particular situation, then you might want to get at the select sth.
my $insert_sth; foreach my $user_name (qw( jsmith bthompson gfillman )) { my $fields = { user_name => $user_name, email => $user_name . '@mycompany.com', }; $insert_sth ||= $rs->insert_sth( $fields, # fields to insert ); $insert_sth->execute( $rs->bind_values( $fields ), ); }
If you're going to insert a *lot* of records you probably don't want to be re-generating the SQL every time you call insert().
This mehtod calls SQL::Abstract's values() method. Normally this will be used in conjunction with insert_sth().
These methods just produce SQL, allowing you to have complete control of how it is executed.
my ($sql, @bind) = $users_rs->select_sql( ['email', 'age'], # optional, fields to retrieve );
Returns the SQL and bind values for a SELECT statement. This is useful if you want to handle DBI yourself, or for building subselects. See the DBIx::ResultSet::Cookbook for examples of subselects.
my ($sql, @bind) = $users_rs->search({ title => 'Manager' })->where_sql();
This works just like select_sql(), but it only returns the WHERE portion of the SQL query. This can be useful when you are doing complex joins where you need to write raw SQL, but you still want to build up your WHERE clause without writing SQL.
Note, that if order_by, limit, offset, rows, or page clauses or specified then the returned SQL will include those clauses as well.
The DBIx::ResultSet::Connector object that this resultset is bound too.
my $rs = $connector->resultset('users')->search({}, {page=>2, rows=>50}); my $pager = $rs->pager(); # a pre-populated Data::Page object
A Data::Page object pre-populated based on page() and rows(). If page() has not been specified then trying to access page() will throw an error.
The total_entries and last_page methods are proxied from the pager in to this class so that you can call:
print $rs->total_entries();
Instead of:
print $rs->pager->total_entries();
The name of the table that this result set will be using for queries.
The where clause hash ref to be used when executing queries.
Additional clauses, such as order_by, limit, offset, etc.
DBIx::Class::ResultSet::HashRef
Aran Clary Deltac <bluefeet@gmail.com>
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install DBIx::ResultSet, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::ResultSet
CPAN shell
perl -MCPAN -e shell install DBIx::ResultSet
For more information on module installation, please visit the detailed CPAN module installation guide.