table of contents
DBIx::Class::ResultSet(3pm) | User Contributed Perl Documentation | DBIx::Class::ResultSet(3pm) |
NAME¶
DBIx::Class::ResultSet - Represents a query used for fetching a set of results.SYNOPSIS¶
my $users_rs = $schema->resultset('User'); while( $user = $users_rs->next) { print $user->username; } my $registered_users_rs = $schema->resultset('User')->search({ registered => 1 }); my @cds_in_2005 = $schema->resultset('CD')->search({ year => 2005 })->all();
DESCRIPTION¶
A ResultSet is an object which stores a set of conditions representing a query. It is the backbone of DBIx::Class (i.e. the really important/useful bit). No SQL is executed on the database when a ResultSet is created, it just stores all the conditions needed to create the query. A basic ResultSet representing the data of an entire table is returned by calling "resultset" on a DBIx::Class::Schema and passing in a Source name.my $users_rs = $schema->resultset('User');A new ResultSet is returned from calling "search" on an existing ResultSet. The new one will contain all the conditions of the original, plus any new conditions added in the "search" call. A ResultSet also incorporates an implicit iterator. "next" and "reset" can be used to walk through all the DBIx::Class::Rows the ResultSet represents. The query that the ResultSet represents is only executed against the database when these methods are called: "find", "next", "all", "first", "single", "count". If a resultset is used in a numeric context it returns the "count". However, if it is used in a boolean context it is always true. So if you want to check if a resultset has any results, you must use "if $rs != 0".
EXAMPLES¶
Chaining resultsets¶
Let's say you've got a query that needs to be run to return some data to the user. But, you have an authorization system in place that prevents certain users from seeing certain information. So, you want to construct the basic query in one method, but add constraints to it in another.sub get_data { my $self = shift; my $request = $self->get_request; # Get a request object somehow. my $schema = $self->result_source->schema; my $cd_rs = $schema->resultset('CD')->search({ title => $request->param('title'), year => $request->param('year'), }); $cd_rs = $self->apply_security_policy( $cd_rs ); return $cd_rs->all(); } sub apply_security_policy { my $self = shift; my ($rs) = @_; return $rs->search({ subversive => 0, }); }Resolving conditions and attributes When a resultset is chained from another resultset, conditions and attributes with the same keys need resolving. "join", "prefetch", "+select", "+as" attributes are merged into the existing ones from the original resultset. The "where" and "having" attributes, and any search conditions, are merged with an SQL "AND" to the existing condition from the original resultset. All other attributes are overridden by any new ones supplied in the search attributes.
Multiple queries¶
Since a resultset just defines a query, you can do all sorts of things with it with the same object.# Don't hit the DB yet. my $cd_rs = $schema->resultset('CD')->search({ title => 'something', year => 2009, }); # Each of these hits the DB individually. my $count = $cd_rs->count; my $most_recent = $cd_rs->get_column('date_released')->max(); my @records = $cd_rs->all;And it's not just limited to SELECT statements.
$cd_rs->delete();This is even cooler:
$cd_rs->create({ artist => 'Fred' });Which is the same as:
$schema->resultset('CD')->create({ title => 'something', year => 2009, artist => 'Fred' });See: "search", "count", "get_column", "all", "create".
METHODS¶
new¶
- Arguments: $source, \%$attrs
- Return Value: $rs
my $rs = $schema->resultset('CD')->search({ title => '100th Window' });IMPORTANT: If called on an object, proxies to new_result instead so
my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });will return a CD object, not a ResultSet.
search¶
- Arguments: $cond, \%attrs?
- Return Value: $resultset (scalar context) || @row_objs (list context)
my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001" my $new_rs = $cd_rs->search({ year => 2005 }); my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]); # year = 2005 OR year = 2004In list context, "->all()" is called implicitly on the resultset, thus returning a list of row objects instead. To avoid that, use "search_rs". If you need to pass in additional attributes but no additional condition, call it as "search(undef, \%attrs)".
# "SELECT name, artistid FROM $artist_table" my @all_artists = $schema->resultset('Artist')->search(undef, { columns => [qw/name artistid/], });For a list of attributes that can be passed to "search", see "ATTRIBUTES". For more examples of using this function, see Searching. For a complete documentation for the first argument, see SQL::Abstract and its extension DBIx::Class::SQLMaker. For more help on using joins with search, see DBIx::Class::Manual::Joining. CAVEAT Note that "search" does not process/deflate any of the values passed in the SQL::Abstract-compatible search condition structure. This is unlike other condition-bound methods "new", "create" and "find". The user must ensure manually that any value passed to this method will stringify to something the RDBMS knows how to deal with. A notable example is the handling of DateTime objects, for more info see: "Formatting_DateTime_objects_in_queries" in DBIx::Class::Manual::Cookbook.
search_rs¶
- Arguments: $cond, \%attrs?
- Return Value: $resultset
search_literal¶
- Arguments: $sql_fragment, @bind_values
- Return Value: $resultset (scalar context) || @row_objs (list context)
my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/); my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');Pass a literal chunk of SQL to be added to the conditional part of the resultset query. CAVEAT: "search_literal" is provided for Class::DBI compatibility and should only be used in that context. "search_literal" is a convenience method. It is equivalent to calling $schema->search(\[]), but if you want to ensure columns are bound correctly, use "search". Example of how to use "search" instead of "search_literal"
my @cds = $cd_rs->search_literal('cdid = ? AND (artist = ? OR artist = ?)', (2, 1, 2)); my @cds = $cd_rs->search(\[ 'cdid = ? AND (artist = ? OR artist = ?)', [ 'cdid', 2 ], [ 'artist', 1 ], [ 'artist', 2 ] ]);See "Searching" in DBIx::Class::Manual::Cookbook and "Searching" in DBIx::Class::Manual::FAQ for searching techniques that do not require "search_literal".
find¶
- Arguments: \%columns_values | @pk_values, \%attrs?
- Return Value: $row_object | undef
my $cd = $schema->resultset('CD')->find(5);You can also find a row by a specific unique constraint:
my $cd = $schema->resultset('CD')->find( { artist => 'Massive Attack', title => 'Mezzanine', }, { key => 'cd_artist_title' } );See also "find_or_create" and "update_or_create".
search_related¶
- Arguments: $rel, $cond, \%attrs?
- Return Value: $new_resultset (scalar context) || @row_objs (list context)
$new_rs = $cd_rs->search_related('artist', { name => 'Emo-R-Us', });Searches the specified relationship, optionally specifying a condition and attributes for matching records. See "ATTRIBUTES" for more information. In list context, "->all()" is called implicitly on the resultset, thus returning a list of row objects instead. To avoid that, use "search_related_rs". See also "search_related_rs".
search_related_rs¶
This method works exactly the same as search_related, except that it guarantees a resultset, even in list context.cursor¶
- Arguments: none
- Return Value: $cursor
single¶
- Arguments: $cond?
- Return Value: $row_object | undef
my $cd = $schema->resultset('CD')->single({ year => 2001 });Inflates the first result without creating a cursor if the resultset has any records in it; if not returns "undef". Used by "find" as a lean version of "search". While this method can take an optional search condition (just like "search") being a fast-code-path it does not recognize search attributes. If you need to add extra joins or similar, call "search" and then chain-call "single" on the DBIx::Class::ResultSet returned.
- Note
- As of 0.08100, this method enforces the assumption that the
preceding query returns only one row. If more than one row is returned,
you will receive a warning:
Query returned more than one row
get_column¶
- Arguments: $cond?
- Return Value: $resultsetcolumn
my $max_length = $rs->get_column('length')->max;Returns a DBIx::Class::ResultSetColumn instance for a column of the ResultSet.
search_like¶
- Arguments: $cond, \%attrs?
- Return Value: $resultset (scalar context) || @row_objs (list context)
# WHERE title LIKE '%blue%' $cd_rs = $rs->search_like({ title => '%blue%'});Performs a search, but uses "LIKE" instead of "=" as the condition. Note that this is simply a convenience method retained for ex Class::DBI users. You most likely want to use "search" with specific operators. For more information, see DBIx::Class::Manual::Cookbook. This method is deprecated and will be removed in 0.09. Use " search()" instead. An example conversion is:
->search_like({ foo => 'bar' }); # Becomes ->search({ foo => { like => 'bar' } });
slice¶
- Arguments: $first, $last
- Return Value: $resultset (scalar context) || @row_objs (list context)
my ($one, $two, $three) = $rs->slice(0, 2);
next¶
- Arguments: none
- Return Value: $result | undef
my $rs = $schema->resultset('CD')->search; while (my $cd = $rs->next) { print $cd->title; }Note that you need to store the resultset object, and call "next" on it. Calling "resultset('Table')->next" repeatedly will always return the first record from the resultset.
result_source¶
- Arguments: $result_source?
- Return Value: $result_source
result_class¶
- Arguments: $result_class?
- Return Value: $result_class
count¶
- Arguments: $cond, \%attrs??
- Return Value: $count
count_rs¶
- Arguments: $cond, \%attrs??
- Return Value: $count_rs
->search( { amount => $some_rs->count_rs->as_query } )As with regular resultsets the SQL query will be executed only after the resultset is accessed via "next" or "all". That would return the same single value obtainable via "count".
count_literal¶
- Arguments: $sql_fragment, @bind_values
- Return Value: $count
all¶
- Arguments: none
- Return Value: @objects
reset¶
- Arguments: none
- Return Value: $self
first¶
- Arguments: none
- Return Value: $object | undef
update¶
- Arguments: \%values
- Return Value: $storage_rv
update_all¶
- Arguments: \%values
- Return Value: 1
delete¶
- Arguments: none
- Return Value: $storage_rv
delete_all¶
- Arguments: none
- Return Value: 1
populate¶
- Arguments: \@data;
my $Artist_rs = $schema->resultset("Artist"); ## Void Context Example $Artist_rs->populate([ { artistid => 4, name => 'Manufactured Crap', cds => [ { title => 'My First CD', year => 2006 }, { title => 'Yet More Tweeny-Pop crap', year => 2007 }, ], }, { artistid => 5, name => 'Angsty-Whiny Girl', cds => [ { title => 'My parents sold me to a record company', year => 2005 }, { title => 'Why Am I So Ugly?', year => 2006 }, { title => 'I Got Surgery and am now Popular', year => 2007 } ], }, ]); ## Array Context Example my ($ArtistOne, $ArtistTwo, $ArtistThree) = $Artist_rs->populate([ { name => "Artist One"}, { name => "Artist Two"}, { name => "Artist Three", cds=> [ { title => "First CD", year => 2007}, { title => "Second CD", year => 2008}, ]} ]); print $ArtistOne->name; ## response is 'Artist One' print $ArtistThree->cds->count ## reponse is '2'For the arrayref of arrayrefs style, the first element should be a list of the fieldsnames to which the remaining elements are rows being inserted. For example:
$Arstist_rs->populate([ [qw/artistid name/], [100, 'A Formally Unknown Singer'], [101, 'A singer that jumped the shark two albums ago'], [102, 'An actually cool singer'], ]);Please note an important effect on your data when choosing between void and wantarray context. Since void context goes straight to "insert_bulk" in DBIx::Class::Storage::DBI this will skip any component that is overriding "insert". So if you are using something like DBIx-Class-UUIDColumns to create primary keys for you, you will find that your PKs are empty. In this case you will have to use the wantarray context in order to create those values.
pager¶
- Arguments: none
- Return Value: $pager
page¶
- Arguments: $page_number
- Return Value: $rs
new_result¶
- Arguments: \%vals
- Return Value: $rowobject
as_query¶
- Arguments: none
- Return Value: \[ $sql, @bind ]
find_or_new¶
- Arguments: \%vals, \%attrs?
- Return Value: $rowobject
my $artist = $schema->resultset('Artist')->find_or_new( { artist => 'fred' }, { key => 'artists' }); $cd->cd_to_producer->find_or_new({ producer => $producer }, { key => 'primary });Find an existing record from this resultset using "find". if none exists, instantiate a new result object and return it. The object will not be saved into your storage until you call "insert" in DBIx::Class::Row on it. You most likely want this method when looking for existing rows using a unique constraint that is not the primary key, or looking for related rows. If you want objects to be saved immediately, use "find_or_create" instead. Note: Make sure to read the documentation of "find" and understand the significance of the "key" attribute, as its lack may skew your search, and subsequently result in spurious new objects. Note: Take care when using "find_or_new" with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to "find_or_new", even when set to "undef".
create¶
- Arguments: \%vals
- Return Value: a DBIx::Class::Row $object
$person_rs->create({ name=>"Some Person", email=>"somebody@someplace.com" });Example of creating a new row and also creating rows in a related "has_many" or "has_one" resultset. Note Arrayref.
$artist_rs->create( { artistid => 4, name => 'Manufactured Crap', cds => [ { title => 'My First CD', year => 2006 }, { title => 'Yet More Tweeny-Pop crap', year => 2007 }, ], }, );Example of creating a new row and also creating a row in a related "belongs_to" resultset. Note Hashref.
$cd_rs->create({ title=>"Music for Silly Walks", year=>2000, artist => { name=>"Silly Musician", } });
- WARNING
- When subclassing ResultSet never attempt to override this method. Since it is a simple shortcut for "$self->new_result($attrs)->insert", a lot of the internals simply never call it, so your override will be bypassed more often than not. Override either new or insert depending on how early in the "create" process you need to intervene.
find_or_create¶
- Arguments: \%vals, \%attrs?
- Return Value: $rowobject
$cd->cd_to_producer->find_or_create({ producer => $producer }, { key => 'primary' });Tries to find a record based on its primary key or unique constraints; if none is found, creates one and returns that instead.
my $cd = $schema->resultset('CD')->find_or_create({ cdid => 5, artist => 'Massive Attack', title => 'Mezzanine', year => 2005, });Also takes an optional "key" attribute, to search by a specific key or unique constraint. For example:
my $cd = $schema->resultset('CD')->find_or_create( { artist => 'Massive Attack', title => 'Mezzanine', }, { key => 'cd_artist_title' } );Note: Make sure to read the documentation of "find" and understand the significance of the "key" attribute, as its lack may skew your search, and subsequently result in spurious row creation. Note: Because find_or_create() reads from the database and then possibly inserts based on the result, this method is subject to a race condition. Another process could create a record in the table after the find has completed and before the create has started. To avoid this problem, use find_or_create() inside a transaction. Note: Take care when using "find_or_create" with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to "find_or_create", even when set to "undef". See also "find" and "update_or_create". For information on how to declare unique constraints, see "add_unique_constraint" in DBIx::Class::ResultSource.
update_or_create¶
- Arguments: \%col_values, { key => $unique_constraint }?
- Return Value: $row_object
$resultset->update_or_create({ col => $val, ... });Like "find_or_create", but if a row is found it is immediately updated via "$found_row->update (\%col_values)". 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 => 'cd_artist_title' } ); $cd->cd_to_producer->update_or_create({ producer => $producer, name => 'harry', }, { key => 'primary', });Note: Make sure to read the documentation of "find" and understand the significance of the "key" attribute, as its lack may skew your search, and subsequently result in spurious row creation. Note: Take care when using "update_or_create" with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to "update_or_create", even when set to "undef". See also "find" and "find_or_create". For information on how to declare unique constraints, see "add_unique_constraint" in DBIx::Class::ResultSource.
update_or_new¶
- Arguments: \%col_values, { key => $unique_constraint }?
- Return Value: $rowobject
$resultset->update_or_new({ col => $val, ... });Like "find_or_new" but if a row is found it is immediately updated via "$found_row->update (\%col_values)". For example:
# In your application my $cd = $schema->resultset('CD')->update_or_new( { artist => 'Massive Attack', title => 'Mezzanine', year => 1998, }, { key => 'cd_artist_title' } ); if ($cd->in_storage) { # the cd was updated } else { # the cd is not yet in the database, let's insert it $cd->insert; }Note: Make sure to read the documentation of "find" and understand the significance of the "key" attribute, as its lack may skew your search, and subsequently result in spurious new objects. Note: Take care when using "update_or_new" with a table having columns with default values that you intend to be automatically supplied by the database (e.g. an auto_increment primary key column). In normal usage, the value of such columns should NOT be included at all in the call to "update_or_new", even when set to "undef". See also "find", "find_or_create" and "find_or_new".
get_cache¶
- Arguments: none
- Return Value: \@cache_objects | undef
set_cache¶
- Arguments: \@cache_objects
- Return Value: \@cache_objects
clear_cache¶
- Arguments: none
- Return Value: undef
is_paged¶
- Arguments: none
- Return Value: true, if the resultset has been paginated
is_ordered¶
- Arguments: none
- Return Value: true, if the resultset has been ordered with "order_by".
related_resultset¶
- Arguments: $relationship_name
- Return Value: $resultset
$artist_rs = $schema->resultset('CD')->related_resultset('Artist');
current_source_alias¶
- Arguments: none
- Return Value: $source_alias
# in a result set class sub modified_by { my ($self, $user) = @_; my $me = $self->current_source_alias; return $self->search( "$me.modified" => $user->id, ); }
as_subselect_rs¶
- Arguments: none
- Return Value: $resultset
my $rs = $schema->resultset('Bar')->search({'x.name' => 'abc'},{ join => 'x' }); # 'x' now pollutes the query namespace # So the following works as expected my $ok_rs = $rs->search({'x.other' => 1}); # But this doesn't: instead of finding a 'Bar' related to two x rows (abc and # def) we look for one row with contradictory terms and join in another table # (aliased 'x_2') which we never use my $broken_rs = $rs->search({'x.name' => 'def'}); my $rs2 = $rs->as_subselect_rs; # doesn't work - 'x' is no longer accessible in $rs2, having been sealed away my $not_joined_rs = $rs2->search({'x.other' => 1}); # works as expected: finds a 'table' row related to two x rows (abc and def) my $correctly_joined_rs = $rs2->search({'x.name' => 'def'});Another example of when one might use this would be to select a subset of columns in a group by clause:
my $rs = $schema->resultset('Bar')->search(undef, { group_by => [qw{ id foo_id baz_id }], })->as_subselect_rs->search(undef, { columns => [qw{ id foo_id }] });In the above example normally columns would have to be equal to the group by, but because we isolated the group by into a subselect the above works.
throw_exception¶
See "throw_exception" in DBIx::Class::Schema for details.ATTRIBUTES¶
Attributes are used to refine a ResultSet in various ways when searching for data. They can be passed to any method which takes an "\%attrs" argument. See "search", "search_rs", "find", "count". These are in no particular order:order_by¶
- Value: ( $order_by | \@order_by | \%order_by )
For descending order: order_by => { -desc => [qw/col1 col2 col3/] } For explicit ascending order: order_by => { -asc => 'col' }The old scalarref syntax (i.e. order_by => \'year DESC') is still supported, although you are strongly encouraged to use the hashref syntax as outlined above.
columns¶
- Value: \@columns
columns => [ 'foo', { bar => 'baz' } ]is the same as
select => [qw/foo baz/], as => [qw/foo bar/]
+columns¶
- Value: \@columns
$schema->resultset('CD')->search(undef, { '+columns' => ['artist.name'], join => ['artist'] });would return all CDs and include a 'name' column to the information passed to object inflation. Note that the 'artist' is the name of the column (or relationship) accessor, and 'name' is the name of the column accessor in the related table. NOTE: You need to explicitly quote '+columns' when defining the attribute. Not doing so causes Perl to incorrectly interpret +columns as a bareword with a unary plus operator before it.
include_columns¶
- Value: \@columns
select¶
- Value: \@select_columns
$rs = $schema->resultset('Employee')->search(undef, { select => [ 'name', { count => 'employeeid' }, { max => { length => 'name' }, -as => 'longest_name' } ] }); # Equivalent SQL SELECT name, COUNT( employeeid ), MAX( LENGTH( name ) ) AS longest_name FROM employeeNOTE: You will almost always need a corresponding "as" attribute when you use "select", to instruct DBIx::Class how to store the result of the column. Also note that the "as" attribute has nothing to do with the SQL-side 'AS' identifier aliasing. You can however alias a function, so you can use it in e.g. an "ORDER BY" clause. This is done via the "-as" select function attribute supplied as shown in the example above. NOTE: You need to explicitly quote '+select'/'+as' when defining the attributes. Not doing so causes Perl to incorrectly interpret them as a bareword with a unary plus operator before it.
+select¶
Indicates additional columns to be selected
from storage. Works the same as "select" but adds columns to the
default selection, instead of specifying an explicit list.
+as¶
Indicates additional column names for those
added via "+select". See "as".
as¶
- Value: \@inflation_names
$rs = $schema->resultset('Employee')->search(undef, { select => [ 'name', { count => 'employeeid' }, { max => { length => 'name' }, -as => 'longest_name' } ], as => [qw/ name employee_count max_name_length /], });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 $name = $employee->name();If on the other hand an accessor does not exist in the object, you need to use "get_column" instead:
my $employee_count = $employee->get_column('employee_count');You can create your own accessors if required - see DBIx::Class::Manual::Cookbook for details.
join¶
- Value: ($rel_name | \@rel_names | \%rel_names)
# 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', } );You need to use the relationship (not the table) name in conditions, because they are aliased as such. The current table is aliased as "me", so you need to use me.column_name in order to avoid ambiguity. For example:
# Get CDs from 1984 with a 'Foo' track my $rs = $schema->resultset('CD')->search( { 'me.year' => 1984, 'tracks.name' => 'Foo' }, { join => 'tracks' } );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' => 'Down to Earth', 'cds_2.title' => 'Popular', }, { join => [ qw/cds cds/ ], });will return a set of all artists that have both a cd with title 'Down to Earth' and a cd with title 'Popular'. If you want to fetch related objects from other tables as well, see "prefetch" below. For more help on using joins with search, see DBIx::Class::Manual::Joining.
prefetch¶
- Value: ($rel_name | \@rel_names | \%rel_names)
my $rs = $schema->resultset('Tag')->search( undef, { 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.artistidDBIx::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. Simple prefetches will be joined automatically, so there is no need for a "join" attribute in the above search. "prefetch" can be used with the any of the relationship types and multiple prefetches can be specified together. Below is a more complex example that prefetches a CD's artist, its liner notes (if present), the cover image, the tracks on that cd, and the guests on those tracks.
# Assuming: My::Schema::CD->belongs_to( artist => 'My::Schema::Artist' ); My::Schema::CD->might_have( liner_note => 'My::Schema::LinerNotes' ); My::Schema::CD->has_one( cover_image => 'My::Schema::Artwork' ); My::Schema::CD->has_many( tracks => 'My::Schema::Track' ); My::Schema::Artist->belongs_to( record_label => 'My::Schema::RecordLabel' ); My::Schema::Track->has_many( guests => 'My::Schema::Guest' ); my $rs = $schema->resultset('CD')->search( undef, { prefetch => [ { artist => 'record_label'}, # belongs_to => belongs_to 'liner_note', # might_have 'cover_image', # has_one { tracks => 'guests' }, # has_many => has_many ] } );This will produce SQL like the following:
SELECT cd.*, artist.*, record_label.*, liner_note.*, cover_image.*, tracks.*, guests.* FROM cd me JOIN artist artist ON artist.artistid = me.artistid JOIN record_label record_label ON record_label.labelid = artist.labelid LEFT JOIN track tracks ON tracks.cdid = me.cdid LEFT JOIN guest guests ON guests.trackid = track.trackid LEFT JOIN liner_notes liner_note ON liner_note.cdid = me.cdid JOIN cd_artwork cover_image ON cover_image.cdid = me.cdid ORDER BY tracks.cdNow the "artist", "record_label", "liner_note", "cover_image", "tracks", and "guests" of the CD will all be available through the relationship accessors without the need for additional queries to the database. However, there is one caveat to be observed: it can be dangerous to prefetch more than one has_many relationship on a given level. e.g.:
my $rs = $schema->resultset('CD')->search( undef, { prefetch => [ 'tracks', # has_many { cd_to_producer => 'producer' }, # has_many => belongs_to (i.e. m2m) ] } );In fact, "DBIx::Class" will emit the following warning:
Prefetching multiple has_many rels tracks and cd_to_producer at top level will explode the number of row objects retrievable via ->next or ->all. Use at your own risk.The collapser currently can't identify duplicate tuples for multiple has_many relationships and as a result the second has_many relation could contain redundant objects. Using "prefetch" with "join" "prefetch" implies a "join" with the equivalent argument, and is properly merged with any existing "join" specification. So the following:
my $rs = $schema->resultset('CD')->search( {'record_label.name' => 'Music Product Ltd.'}, { join => {artist => 'record_label'}, prefetch => 'artist', } );... will work, searching on the record label's name, but only prefetching the "artist". Using "prefetch" with "select" / "+select" / "as" / "+as" "prefetch" implies a "+select"/"+as" with the fields of the prefetched relations. So given:
my $rs = $schema->resultset('CD')->search( undef, { select => ['cd.title'], as => ['cd_title'], prefetch => 'artist', } );The "select" becomes: 'cd.title', 'artist.*' and the "as" becomes: 'cd_title', 'artist.*'. CAVEATS Prefetch does a lot of deep magic. As such, it may not behave exactly as you might expect.
- •
- Prefetch uses the "cache" to populate the prefetched relationships. This may or may not be what you want.
- •
- If you specify a condition on a prefetched relationship,
ONLY those rows that match the prefetched condition will be fetched into
that relationship. This means that adding prefetch to a search()
may alter what is returned by traversing a relationship. So, if you
have "Artist->has_many(CDs)" and you do
my $artist_rs = $schema->resultset('Artist')->search({ 'cds.year' => 2008, }, { join => 'cds', }); my $count = $artist_rs->first->cds->count; my $artist_rs_prefetch = $artist_rs->search( {}, { prefetch => 'cds' } ); my $prefetch_count = $artist_rs_prefetch->first->cds->count; cmp_ok( $count, '==', $prefetch_count, "Counts should be the same" );
page¶
- Value: $page
rows¶
- Value: $rows
offset¶
- Value: $offset
group_by¶
- Value: \@columns
group_by => [qw/ column1 column2 ... /]
having¶
- Value: $condition
having => { 'count_employee' => { '>=', 100 } }or with an in-place function in which case literal SQL is required:
having => \[ 'count(employee) >= ?', [ count => 100 ] ]
distinct¶
- Value: (0 | 1)
where¶
Adds to the WHERE clause.
Can be overridden by passing "{ where => undef }" as an attribute
to a resultset.
# only return rows WHERE deleted IS NULL for all searches __PACKAGE__->resultset_attributes({ where => { deleted => undef } }); )
cache¶
Set to 1 to cache search results. This prevents extra SQL queries if you revisit rows in your ResultSet:my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } ); while( my $artist = $resultset->next ) { ... do stuff ... } $rs->first; # without cache, this would issue a queryBy default, searches are not cached. For more examples of using these attributes, see DBIx::Class::Manual::Cookbook.
for¶
- Value: ( 'update' | 'shared' )
2011-11-29 | perl v5.14.2 |