DBIx::ResultSet - Lightweight SQL query building and execution.
use DBIx::ResultSet::Connector; # Same arguments as DBI and DBIx::Connector. my $connector = DBIx::ResultSet::Connector->new( $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 simpliefies the creation and execution of SQL queries. This is done by providing a thin wrapper around the 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.
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.
$users_rs->insert( { user_name=>'bob2003', email=>'bob@example.com' }, # fields to insert );
Creates and executes an INSERT statement.
$users_rs->update( { phone => '555-1234' }, # fields to update );
Creates and executes an UPDATE statement.
# Delete all users! $users_rs->delete(); # Or just the ones that are disabled. #users_rs->search({status=>0})->delete();
Creates and executes a DELETE statement.
my $user = $users_rs->search({ user_id => 32 })->array_row( ['created', 'email', 'phone'], # 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'], # 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'], # 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'], # 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'], # 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.
my ($sth, @bind) = $rs->select_sth( ['user_name', 'user_id'], # 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().
my ($sql, @bind) = $users_rs->select_sql(['email', 'age']);
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.
$users_rs->insert({ user_name=>'jdoe' }); my $user_id = $users_rs->auto_pk();
The DBIx::ResultSet::Connector object.
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 a fatal 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.