DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
my $rs = $schema->resultset('User')->search(registered => 1); my @rows = $schema->resultset('Foo')->search(bar => 'baz');
The resultset is also known as an iterator. It is responsible for handling queries that may return an arbitrary number of rows, e.g. via "search" or a has_many relationship.
has_many
In the examples below, the following table classes are used:
package MyApp::Schema::Artist; use base qw/DBIx::Class/; __PACKAGE__->table('artist'); __PACKAGE__->add_columns(qw/artistid name/); __PACKAGE__->set_primary_key('artistid'); __PACKAGE__->has_many(cds => 'MyApp::Schema::CD'); 1; package MyApp::Schema::CD; use base qw/DBIx::Class/; __PACKAGE__->table('artist'); __PACKAGE__->add_columns(qw/cdid artist title year/); __PACKAGE__->set_primary_key('cdid'); __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist'); 1;
The resultset constructor. Takes a source object (usually a DBIx::Class::ResultSourceProxy::Table) and an attribute hash (see "ATRRIBUTES" below). Does not perform any queries -- these are executed as needed by the other methods.
Generally you won't need to construct a resultset manually. You'll automatically get one from e.g. a "search" called in scalar context:
my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3" my $new_rs = $rs->search({ foo => 3 });
If you need to pass in additional attributes but no additional condition, call it as search({}, \%attrs);.
search({}, \%attrs);
# "SELECT foo, bar FROM $class_table" my @all = $class->search({}, { cols => [qw/foo bar/] });
my @obj = $rs->search_literal($literal_where_cond, @bind); my $new_rs = $rs->search_literal($literal_where_cond, @bind);
Pass a literal chunk of SQL to be added to the conditional part of the resultset.
Finds a row based on its primary key or unique constraint. For example:
my $cd = $schema->resultset('CD')->find(5);
Also takes an optional key attribute, to search by a specific key or unique constraint. For example:
key
my $cd = $schema->resultset('CD')->find_or_create( { artist => 'Massive Attack', title => 'Mezzanine', }, { key => 'artist_title' } );
See also "find_or_create" and "update_or_create".
$rs->search_related('relname', $cond?, $attrs?);
Search the specified relationship. Optionally specify a condition for matching records.
Returns a storage-driven cursor to the given resultset.
Perform a search, but use LIKE instead of equality as the condition. Note that this is simply a convenience method; you most likely want to use "search" with specific operators.
LIKE
For more information, see DBIx::Class::Manual::Cookbook.
Returns a subset of elements from the resultset.
Returns the next element in the resultset (undef is there is none).
undef
Can be used to efficiently iterate over records in the resultset:
my $rs = $schema->resultset('CD')->search({}); while (my $cd = $rs->next) { print $cd->title; }
Returns a reference to the result source for this recordset.
Performs an SQL COUNT with the same query as the resultset was built with to find the number of elements. If passed arguments, does a search on the resultset and counts the results of that.
COUNT
Note: When using count with group_by, DBIX::Class emulates GROUP BY using COUNT( DISTINCT( columns ) ). Some databases (notably SQLite) do not support DISTINCT with multiple columns. If you are using such a database, you should only use columns from the main table in your group_by clause.
count
group_by
GROUP BY
COUNT( DISTINCT( columns ) )
DISTINCT
Calls "search_literal" with the passed arguments, then "count".
Returns all elements in the resultset. Called implictly if the resultset is returned in list context.
Resets the resultset's cursor, so you can iterate through the elements again.
Resets the resultset and returns the first element.
Sets the specified columns in the resultset to the supplied values.
Fetches all objects and updates them one at a time. Note that update_all will run cascade triggers while "update" will not.
update_all
Deletes the contents of the resultset from its result source.
Fetches all objects and deletes them one at a time. Note that delete_all will run cascade triggers while "delete" will not.
delete_all
Returns a Data::Page object for the current resultset. Only makes sense for queries with a page attribute.
page
Returns a new resultset for the specified page.
Creates a result in the resultset's result class.
Inserts a record into the resultset and returns the object.
Effectively a shortcut for ->new_result(\%vals)->insert.
->new_result(\%vals)->insert
$class->find_or_create({ key => $val, ... });
Searches for a record matching the search condition; if it doesn't find one, creates one and returns that instead.
my $cd = $schema->resultset('CD')->find_or_create({ cdid => 5, artist => 'Massive Attack', title => 'Mezzanine', year => 2005, });
See also "find" and "update_or_create".
$class->update_or_create({ key => $val, ... });
First, search for an existing row matching one of the unique constraints (including the primary key) on the source of this resultset. If a row is found, update it with the other given column values. Otherwise, create a new row.
Takes an optional key attribute to search on a specific unique constraint. For example:
# In your application my $cd = $schema->resultset('CD')->update_or_create( { artist => 'Massive Attack', title => 'Mezzanine', year => 1998, }, { key => 'artist_title' } );
If no key is specified, it searches on all unique constraints defined on the source, including the primary key.
If the key is specified as primary, search only on the primary key.
primary
See also "find" and "find_or_create".
See Schema's throw_exception
The resultset takes various attributes that modify its behavior. Here's an overview of them:
Which column(s) to order the results by. This is currently passed through directly to SQL, so you can give e.g. foo DESC for a descending order.
foo DESC
Shortcut to request a particular set of columns to be retrieved. Adds me. onto the start of any column without a . in it and sets select from that, then auto-populates as from select as normal.
me.
.
select
as
Shortcut to include additional columns in the returned results - for example
{ include_columns => ['foo.name'], join => ['foo'] }
would add a 'name' column to the information passed to object inflation
Indicates which columns should be selected from the storage. You can use column names, or in the case of RDBMS back ends, function or stored procedure names:
$rs = $schema->resultset('Foo')->search( {}, { select => [ 'column_name', { count => 'column_to_count' }, { sum => 'column_to_sum' } ] } );
When you use function/stored procedure names and do not supply an as attribute, the column names returned are storage-dependent. E.g. MySQL would return a column named count(column_to_count) in the above example.
count(column_to_count)
Indicates column names for object inflation. This is used in conjunction with select, usually when select contains one or more function or stored procedure names:
$rs = $schema->resultset('Foo')->search( {}, { select => [ 'column1', { count => 'column2' } ], as => [qw/ column1 column2_count /] } ); my $foo = $rs->first(); # get the first Foo
If the object against which the search is performed already has an accessor matching a column name specified in as, the value can be retrieved using the accessor as normal:
my $column1 = $foo->column1();
If on the other hand an accessor does not exist in the object, you need to use get_column instead:
get_column
my $column2_count = $foo->get_column('column2_count');
You can create your own accessors if required - see DBIx::Class::Manual::Cookbook for details.
Contains a list of relationships that should be joined for this query. For example:
# Get CDs by Nine Inch Nails my $rs = $schema->resultset('CD')->search( { 'artist.name' => 'Nine Inch Nails' }, { join => 'artist' } );
Can also contain a hash reference to refer to the other relation's relations. For example:
package MyApp::Schema::Track; use base qw/DBIx::Class/; __PACKAGE__->table('track'); __PACKAGE__->add_columns(qw/trackid cd position title/); __PACKAGE__->set_primary_key('trackid'); __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD'); 1; # In your application my $rs = $schema->resultset('Artist')->search( { 'track.title' => 'Teardrop' }, { join => { cd => 'track' }, order_by => 'artist.name', } );
If the same join is supplied twice, it will be aliased to <rel>_2 (and similarly for a third time). For e.g.
my $rs = $schema->resultset('Artist')->search( { 'cds.title' => 'Foo', 'cds_2.title' => 'Bar' }, { join => [ qw/cds cds/ ] });
will return a set of all artists that have both a cd with title Foo and a cd with title Bar.
If you want to fetch related objects from other tables as well, see prefetch below.
prefetch
Contains one or more relationships that should be fetched along with the main query (when they are accessed afterwards they will have already been "prefetched"). This is useful for when you know you will need the related objects, because it saves at least one query:
my $rs = $schema->resultset('Tag')->search( {}, { prefetch => { cd => 'artist' } } );
The initial search results in SQL like the following:
SELECT tag.*, cd.*, artist.* FROM tag JOIN cd ON tag.cd = cd.cdid JOIN artist ON cd.artist = artist.artistid
DBIx::Class has no need to go back to the database when we access the cd or artist relationships, which saves us two SQL statements in this case.
cd
artist
Simple prefetches will be joined automatically, so there is no need for a join attribute in the above search. If you're prefetching to depth (e.g. { cd => { artist => 'label' } or similar), you'll need to specify the join as well.
join
prefetch can be used with the following relationship types: belongs_to, has_one (or if you're using add_relationship, any relationship declared with an accessor type of 'single' or 'filter').
belongs_to
has_one
add_relationship
The from attribute gives you manual control over the FROM clause of SQL statements generated by DBIx::Class, allowing you to express custom JOIN clauses.
from
FROM
JOIN
NOTE: Use this on your own risk. This allows you to shoot off your foot! join will usually do what you need and it is strongly recommended that you avoid using from unless you cannot achieve the desired result using join.
In simple terms, from works as follows:
[ { <alias> => <table>, -join-type => 'inner|left|right' } [] # nested JOIN (optional) { <table.column> = <foreign_table.foreign_key> } ] JOIN <alias> <table> [JOIN ...] ON <table.column> = <foreign_table.foreign_key>
An easy way to follow the examples below is to remember the following:
Anything inside "[]" is a JOIN Anything inside "{}" is a condition for the enclosing JOIN
The following examples utilize a "person" table in a family tree application. In order to express parent->child relationships, this table is self-joined:
# Person->belongs_to('father' => 'Person'); # Person->belongs_to('mother' => 'Person');
from can be used to nest joins. Here we return all children with a father, then search against all mothers of those children:
$rs = $schema->resultset('Person')->search( {}, { alias => 'mother', # alias columns in accordance with "from" from => [ { mother => 'person' }, [ [ { child => 'person' }, [ { father => 'person' }, { 'father.person_id' => 'child.father_id' } ] ], { 'mother.person_id' => 'child.mother_id' } ], ] }, ); # Equivalent SQL: # SELECT mother.* FROM person mother # JOIN ( # person child # JOIN person father # ON ( father.person_id = child.father_id ) # ) # ON ( mother.person_id = child.mother_id )
The type of any join can be controlled manually. To search against only people with a father in the person table, we could explicitly use INNER JOIN:
INNER JOIN
$rs = $schema->resultset('Person')->search( {}, { alias => 'child', # alias columns in accordance with "from" from => [ { child => 'person' }, [ { father => 'person', -join-type => 'inner' }, { 'father.id' => 'child.father_id' } ], ] }, ); # Equivalent SQL: # SELECT child.* FROM person child # INNER JOIN person father ON child.father_id = father.id
For a paged resultset, specifies which page to retrieve. Leave unset for an unpaged resultset.
For a paged resultset, how many rows per page:
rows => 10
Can also be used to simulate an SQL LIMIT.
LIMIT
A arrayref of columns to group by. Can include columns of joined tables.
group_by => [qw/ column1 column2 ... /]
Set to 1 to group by all columns.
For more examples of using these attributes, see DBIx::Class::Manual::Cookbook.
To install DBIx::Class, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class
CPAN shell
perl -MCPAN -e shell install DBIx::Class
For more information on module installation, please visit the detailed CPAN module installation guide.