1DBIx::Class::ResultSet(U3s)er Contributed Perl DocumentatDiBoInx::Class::ResultSet(3)
2
6 DBIx::Class::ResultSet - Represents a query used for fetching a set of
7 results.
8
10 my $users_rs = $schema->resultset('User');
11 my $registered_users_rs = $schema->resultset('User')->search({ registered => 1 });
12 my @cds_in_2005 = $schema->resultset('CD')->search({ year => 2005 })->all();
13
15 A ResultSet is an object which stores a set of conditions representing
16 a query. It is the backbone of DBIx::Class (i.e. the really
17 important/useful bit).
18 No SQL is executed on the database when a ResultSet is created, it just
20 stores all the conditions needed to create the query.
21 A basic ResultSet representing the data of an entire table is returned
23 by calling "resultset" on a DBIx::Class::Schema and passing in a Source
24 name.
25 my $users_rs = $schema->resultset('User');
27 A new ResultSet is returned from calling "search" on an existing
29 ResultSet. The new one will contain all the conditions of the original,
30 plus any new conditions added in the "search" call.
31 A ResultSet also incorporates an implicit iterator. "next" and "reset"
33 can be used to walk through all the DBIx::Class::Rows the ResultSet
34 represents.
35 The query that the ResultSet represents is only executed against the
37 database when these methods are called: "find" "next" "all" "first"
38 "single" "count"
39
41 Chaining resultsets
42 Let's say you've got a query that needs to be run to return some data
43 to the user. But, you have an authorization system in place that
44 prevents certain users from seeing certain information. So, you want to
45 construct the basic query in one method, but add constraints to it in
46 another.
47 sub get_data {
49 my $self = shift;
50 my $request = $self->get_request; # Get a request object somehow.
51 my $schema = $self->get_schema; # Get the DBIC schema object somehow.
52 my $cd_rs = $schema->resultset('CD')->search({
54 title => $request->param('title'),
55 year => $request->param('year'),
56 });
57 $self->apply_security_policy( $cd_rs );
59 return $cd_rs->all();
61 }
62 sub apply_security_policy {
64 my $self = shift;
65 my ($rs) = @_;
66 return $rs->search({
68 subversive => 0,
69 });
70 }
71 Resolving conditions and attributes
73 When a resultset is chained from another resultset, conditions and
75 attributes with the same keys need resolving.
76 "join", "prefetch", "+select", "+as" attributes are merged into the
78 existing ones from the original resultset.
79 The "where", "having" attribute, and any search conditions are merged
81 with an SQL "AND" to the existing condition from the original
82 resultset.
83 All other attributes are overridden by any new ones supplied in the
85 search attributes.
86 Multiple queries
88 Since a resultset just defines a query, you can do all sorts of things
89 with it with the same object.
90 # Don't hit the DB yet.
92 my $cd_rs = $schema->resultset('CD')->search({
93 title => 'something',
94 year => 2009,
95 });
96 # Each of these hits the DB individually.
98 my $count = $cd_rs->count;
99 my $most_recent = $cd_rs->get_column('date_released')->max();
100 my @records = $cd_rs->all;
101 And it's not just limited to SELECT statements.
103 $cd_rs->delete();
105 This is even cooler:
107 $cd_rs->create({ artist => 'Fred' });
109 Which is the same as:
111 $schema->resultset('CD')->create({
113 title => 'something',
114 year => 2009,
115 artist => 'Fred'
116 });
117 See: "search", "count", "get_column", "all", "create".
119
121 If a resultset is used in a numeric context it returns the "count".
122 However, if it is used in a boolean context it is always true. So if
123 you want to check if a resultset has any results use "if $rs != 0".
124 "if $rs" will always be true.
125
127 new
128 Arguments: $source, \%$attrs
129 Return Value: $rs
130 The resultset constructor. Takes a source object (usually a
132 DBIx::Class::ResultSourceProxy::Table) and an attribute hash (see
133 "ATTRIBUTES" below). Does not perform any queries -- these are
134 executed as needed by the other methods.
135 Generally you won't need to construct a resultset manually. You'll
137 automatically get one from e.g. a "search" called in scalar context:
138 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
140 IMPORTANT: If called on an object, proxies to new_result instead so
142 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
144 will return a CD object, not a ResultSet.
146 search
148 Arguments: $cond, \%attrs?
149 Return Value: $resultset (scalar context), @row_objs (list context)
150 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
152 my $new_rs = $cd_rs->search({ year => 2005 });
153 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
155 # year = 2005 OR year = 2004
156 If you need to pass in additional attributes but no additional
158 condition, call it as "search(undef, \%attrs)".
159 # "SELECT name, artistid FROM $artist_table"
161 my @all_artists = $schema->resultset('Artist')->search(undef, {
162 columns => [qw/name artistid/],
163 });
164 For a list of attributes that can be passed to "search", see
166 "ATTRIBUTES". For more examples of using this function, see Searching.
167 For a complete documentation for the first argument, see SQL::Abstract.
168 For more help on using joins with search, see
170 DBIx::Class::Manual::Joining.
171 search_rs
173 Arguments: $cond, \%attrs?
174 Return Value: $resultset
175 This method does the same exact thing as search() except it will always
177 return a resultset, even in list context.
178 search_literal
180 Arguments: $sql_fragment, @bind_values
181 Return Value: $resultset (scalar context), @row_objs (list context)
182 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
184 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
185 Pass a literal chunk of SQL to be added to the conditional part of the
187 resultset query.
188 CAVEAT: "search_literal" is provided for Class::DBI compatibility and
190 should only be used in that context. "search_literal" is a convenience
191 method. It is equivalent to calling $schema->search(\[]), but if you
192 want to ensure columns are bound correctly, use "search".
193 Example of how to use "search" instead of "search_literal"
195 my @cds = $cd_rs->search_literal('cdid = ? AND (artist = ? OR artist = ?)', (2, 1, 2));
197 my @cds = $cd_rs->search(\[ 'cdid = ? AND (artist = ? OR artist = ?)', [ 'cdid', 2 ], [ 'artist', 1 ], [ 'artist', 2 ] ]);
198 See "Searching" in DBIx::Class::Manual::Cookbook and "Searching" in
200 DBIx::Class::Manual::FAQ for searching techniques that do not require
201 "search_literal".
202 find
204 Arguments: @values | \%cols, \%attrs?
205 Return Value: $row_object | undef
206 Finds a row based on its primary key or unique constraint. For example,
208 to find a row by its primary key:
209 my $cd = $schema->resultset('CD')->find(5);
211 You can also find a row by a specific unique constraint using the "key"
213 attribute. For example:
214 my $cd = $schema->resultset('CD')->find('Massive Attack', 'Mezzanine', {
216 key => 'cd_artist_title'
217 });
218 Additionally, you can specify the columns explicitly by name:
220 my $cd = $schema->resultset('CD')->find(
222 {
223 artist => 'Massive Attack',
224 title => 'Mezzanine',
225 },
226 { key => 'cd_artist_title' }
227 );
228 If the "key" is specified as "primary", it searches only on the primary
230 key.
231 If no "key" is specified, it searches on all unique constraints defined
233 on the source for which column data is provided, including the primary
234 key.
235 If your table does not have a primary key, you must provide a value for
237 the "key" attribute matching one of the unique constraints on the
238 source.
239 In addition to "key", "find" recognizes and applies standard resultset
241 attributes in the same way as "search" does.
242 Note: If your query does not return only one row, a warning is
244 generated:
245 Query returned more than one row
247 See also "find_or_create" and "update_or_create". For information on
249 how to declare unique constraints, see "add_unique_constraint" in
250 DBIx::Class::ResultSource.
251 search_related
253 Arguments: $rel, $cond, \%attrs?
254 Return Value: $new_resultset
255 $new_rs = $cd_rs->search_related('artist', {
257 name => 'Emo-R-Us',
258 });
259 Searches the specified relationship, optionally specifying a condition
261 and attributes for matching records. See "ATTRIBUTES" for more
262 information.
263 search_related_rs
265 This method works exactly the same as search_related, except that it
266 guarantees a resultset, even in list context.
267 cursor
269 Arguments: none
270 Return Value: $cursor
271 Returns a storage-driven cursor to the given resultset. See
273 DBIx::Class::Cursor for more information.
274 single
276 Arguments: $cond?
277 Return Value: $row_object?
278 my $cd = $schema->resultset('CD')->single({ year => 2001 });
280 Inflates the first result without creating a cursor if the resultset
282 has any records in it; if not returns nothing. Used by "find" as a lean
283 version of "search".
284 While this method can take an optional search condition (just like
286 "search") being a fast-code-path it does not recognize search
287 attributes. If you need to add extra joins or similar, call "search"
288 and then chain-call "single" on the DBIx::Class::ResultSet returned.
289 Note
291 As of 0.08100, this method enforces the assumption that the
292 preceding query returns only one row. If more than one row is
293 returned, you will receive a warning:
294 Query returned more than one row
296 In this case, you should be using "next" or "find" instead, or if
298 you really know what you are doing, use the "rows" attribute to
299 explicitly limit the size of the resultset.
300 This method will also throw an exception if it is called on a
302 resultset prefetching has_many, as such a prefetch implies fetching
303 multiple rows from the database in order to assemble the resulting
304 object.
305 get_column
307 Arguments: $cond?
308 Return Value: $resultsetcolumn
309 my $max_length = $rs->get_column('length')->max;
311 Returns a DBIx::Class::ResultSetColumn instance for a column of the
313 ResultSet.
314 search_like
316 Arguments: $cond, \%attrs?
317 Return Value: $resultset (scalar context), @row_objs (list context)
318 # WHERE title LIKE '%blue%'
320 $cd_rs = $rs->search_like({ title => '%blue%'});
321 Performs a search, but uses "LIKE" instead of "=" as the condition.
323 Note that this is simply a convenience method retained for ex
324 Class::DBI users. You most likely want to use "search" with specific
325 operators.
326 For more information, see DBIx::Class::Manual::Cookbook.
328 This method is deprecated and will be removed in 0.09. Use "search()"
330 instead. An example conversion is:
331 ->search_like({ foo => 'bar' });
333 # Becomes
335 ->search({ foo => { like => 'bar' } });
337 slice
339 Arguments: $first, $last
340 Return Value: $resultset (scalar context), @row_objs (list context)
341 Returns a resultset or object list representing a subset of elements
343 from the resultset slice is called on. Indexes are from 0, i.e., to get
344 the first three records, call:
345 my ($one, $two, $three) = $rs->slice(0, 2);
347 next
349 Arguments: none
350 Return Value: $result?
351 Returns the next element in the resultset ("undef" is there is none).
353 Can be used to efficiently iterate over records in the resultset:
355 my $rs = $schema->resultset('CD')->search;
357 while (my $cd = $rs->next) {
358 print $cd->title;
359 }
360 Note that you need to store the resultset object, and call "next" on
362 it. Calling "resultset('Table')->next" repeatedly will always return
363 the first record from the resultset.
364 result_source
366 Arguments: $result_source?
367 Return Value: $result_source
368 An accessor for the primary ResultSource object from which this
370 ResultSet is derived.
371 result_class
373 Arguments: $result_class?
374 Return Value: $result_class
375 An accessor for the class to use when creating row objects. Defaults to
377 "result_source->result_class" - which in most cases is the name of the
378 "table" class.
379 Note that changing the result_class will also remove any components
381 that were originally loaded in the source class via "load_components"
382 in DBIx::Class::ResultSource. Any overloaded methods in the original
383 source class will not run.
384 count
386 Arguments: $cond, \%attrs??
387 Return Value: $count
388 Performs an SQL "COUNT" with the same query as the resultset was built
390 with to find the number of elements. Passing arguments is equivalent to
391 "$rs->search ($cond, \%attrs)->count"
392 count_rs
394 Arguments: $cond, \%attrs??
395 Return Value: $count_rs
396 Same as "count" but returns a DBIx::Class::ResultSetColumn object.
398 This can be very handy for subqueries:
399 ->search( { amount => $some_rs->count_rs->as_query } )
401 As with regular resultsets the SQL query will be executed only after
403 the resultset is accessed via "next" or "all". That would return the
404 same single value obtainable via "count".
405 count_literal
407 Arguments: $sql_fragment, @bind_values
408 Return Value: $count
409 Counts the results in a literal query. Equivalent to calling
411 "search_literal" with the passed arguments, then "count".
412 all
414 Arguments: none
415 Return Value: @objects
416 Returns all elements in the resultset. Called implicitly if the
418 resultset is returned in list context.
419 reset
421 Arguments: none
422 Return Value: $self
423 Resets the resultset's cursor, so you can iterate through the elements
425 again. Implicitly resets the storage cursor, so a subsequent "next"
426 will trigger another query.
427 first
429 Arguments: none
430 Return Value: $object?
431 Resets the resultset and returns an object for the first result (if the
433 resultset returns anything).
434 update
436 Arguments: \%values
437 Return Value: $storage_rv
438 Sets the specified columns in the resultset to the supplied values in a
440 single query. Return value will be true if the update succeeded or
441 false if no records were updated; exact type of success value is
442 storage-dependent.
443 update_all
445 Arguments: \%values
446 Return Value: 1
447 Fetches all objects and updates them one at a time. Note that
449 "update_all" will run DBIC cascade triggers, while "update" will not.
450 delete
452 Arguments: none
453 Return Value: $storage_rv
454 Deletes the contents of the resultset from its result source. Note that
456 this will not run DBIC cascade triggers. See "delete_all" if you need
457 triggers to run. See also "delete" in DBIx::Class::Row.
458 Return value will be the amount of rows deleted; exact type of return
460 value is storage-dependent.
461 delete_all
463 Arguments: none
464 Return Value: 1
465 Fetches all objects and deletes them one at a time. Note that
467 "delete_all" will run DBIC cascade triggers, while "delete" will not.
468 populate
470 Arguments: \@data;
471 Accepts either an arrayref of hashrefs or alternatively an arrayref of
473 arrayrefs. For the arrayref of hashrefs style each hashref should be a
474 structure suitable forsubmitting to a $resultset->create(...) method.
475 In void context, "insert_bulk" in DBIx::Class::Storage::DBI is used to
477 insert the data, as this is a faster method.
478 Otherwise, each set of data is inserted into the database using
480 "create" in DBIx::Class::ResultSet, and the resulting objects are
481 accumulated into an array. The array itself, or an array reference is
482 returned depending on scalar or list context.
483 Example: Assuming an Artist Class that has many CDs Classes relating:
485 my $Artist_rs = $schema->resultset("Artist");
487 ## Void Context Example
489 $Artist_rs->populate([
490 { artistid => 4, name => 'Manufactured Crap', cds => [
491 { title => 'My First CD', year => 2006 },
492 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
493 ],
494 },
495 { artistid => 5, name => 'Angsty-Whiny Girl', cds => [
496 { title => 'My parents sold me to a record company', year => 2005 },
497 { title => 'Why Am I So Ugly?', year => 2006 },
498 { title => 'I Got Surgery and am now Popular', year => 2007 }
499 ],
500 },
501 ]);
502 ## Array Context Example
504 my ($ArtistOne, $ArtistTwo, $ArtistThree) = $Artist_rs->populate([
505 { name => "Artist One"},
506 { name => "Artist Two"},
507 { name => "Artist Three", cds=> [
508 { title => "First CD", year => 2007},
509 { title => "Second CD", year => 2008},
510 ]}
511 ]);
512 print $ArtistOne->name; ## response is 'Artist One'
514 print $ArtistThree->cds->count ## reponse is '2'
515 For the arrayref of arrayrefs style, the first element should be a
517 list of the fieldsnames to which the remaining elements are rows being
518 inserted. For example:
519 $Arstist_rs->populate([
521 [qw/artistid name/],
522 [100, 'A Formally Unknown Singer'],
523 [101, 'A singer that jumped the shark two albums ago'],
524 [102, 'An actually cool singer'],
525 ]);
526 Please note an important effect on your data when choosing between void
528 and wantarray context. Since void context goes straight to
529 "insert_bulk" in DBIx::Class::Storage::DBI this will skip any component
530 that is overriding "insert". So if you are using something like DBIx-
531 Class-UUIDColumns to create primary keys for you, you will find that
532 your PKs are empty. In this case you will have to use the wantarray
533 context in order to create those values.
534 pager
536 Arguments: none
537 Return Value: $pager
538 Return Value a Data::Page object for the current resultset. Only makes
540 sense for queries with a "page" attribute.
541 To get the full count of entries for a paged resultset, call
543 "total_entries" on the Data::Page object.
544 page
546 Arguments: $page_number
547 Return Value: $rs
548 Returns a resultset for the $page_number page of the resultset on which
550 page is called, where each page contains a number of rows equal to the
551 'rows' attribute set on the resultset (10 by default).
552 new_result
554 Arguments: \%vals
555 Return Value: $rowobject
556 Creates a new row object in the resultset's result class and returns
558 it. The row is not inserted into the database at this point, call
559 "insert" in DBIx::Class::Row to do that. Calling "in_storage" in
560 DBIx::Class::Row will tell you whether the row object has been inserted
561 or not.
562 Passes the hashref of input on to "new" in DBIx::Class::Row.
564 as_query
566 Arguments: none
567 Return Value: \[ $sql, @bind ]
568 Returns the SQL query and bind vars associated with the invocant.
570 This is generally used as the RHS for a subquery.
572 find_or_new
574 Arguments: \%vals, \%attrs?
575 Return Value: $rowobject
576 my $artist = $schema->resultset('Artist')->find_or_new(
578 { artist => 'fred' }, { key => 'artists' });
579 $cd->cd_to_producer->find_or_new({ producer => $producer },
581 { key => 'primary });
582 Find an existing record from this resultset, based on its primary key,
584 or a unique constraint. If none exists, instantiate a new result object
585 and return it. The object will not be saved into your storage until you
586 call "insert" in DBIx::Class::Row on it.
587 You most likely want this method when looking for existing rows using a
589 unique constraint that is not the primary key, or looking for related
590 rows.
591 If you want objects to be saved immediately, use "find_or_create"
593 instead.
594 Note: Take care when using "find_or_new" with a table having columns
596 with default values that you intend to be automatically supplied by the
597 database (e.g. an auto_increment primary key column). In normal usage,
598 the value of such columns should NOT be included at all in the call to
599 "find_or_new", even when set to "undef".
600 create
602 Arguments: \%vals
603 Return Value: a DBIx::Class::Row $object
604 Attempt to create a single new row or a row with multiple related rows
606 in the table represented by the resultset (and related tables). This
607 will not check for duplicate rows before inserting, use
608 "find_or_create" to do that.
609 To create one row for this resultset, pass a hashref of key/value pairs
611 representing the columns of the table and the values you wish to store.
612 If the appropriate relationships are set up, foreign key fields can
613 also be passed an object representing the foreign row, and the value
614 will be set to its primary key.
615 To create related objects, pass a hashref of related-object column
617 values keyed on the relationship name. If the relationship is of type
618 "multi" ("has_many" in DBIx::Class::Relationship) - pass an arrayref of
619 hashrefs. The process will correctly identify columns holding foreign
620 keys, and will transparently populate them from the keys of the
621 corresponding relation. This can be applied recursively, and will work
622 correctly for a structure with an arbitrary depth and width, as long as
623 the relationships actually exists and the correct column data has been
624 supplied.
625 Instead of hashrefs of plain related data (key/value pairs), you may
627 also pass new or inserted objects. New objects (not inserted yet, see
628 "new"), will be inserted into their appropriate tables.
629 Effectively a shortcut for "->new_result(\%vals)->insert".
631 Example of creating a new row.
633 $person_rs->create({
635 name=>"Some Person",
636 email=>"somebody@someplace.com"
637 });
638 Example of creating a new row and also creating rows in a related
640 "has_many" or "has_one" resultset. Note Arrayref.
641 $artist_rs->create(
643 { artistid => 4, name => 'Manufactured Crap', cds => [
644 { title => 'My First CD', year => 2006 },
645 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
646 ],
647 },
648 );
649 Example of creating a new row and also creating a row in a related
651 "belongs_to"resultset. Note Hashref.
652 $cd_rs->create({
654 title=>"Music for Silly Walks",
655 year=>2000,
656 artist => {
657 name=>"Silly Musician",
658 }
659 });
660 WARNING
662 When subclassing ResultSet never attempt to override this method.
663 Since it is a simple shortcut for
664 "$self->new_result($attrs)->insert", a lot of the internals simply
665 never call it, so your override will be bypassed more often than
666 not. Override either new or insert depending on how early in the
667 "create" process you need to intervene.
668 find_or_create
670 Arguments: \%vals, \%attrs?
671 Return Value: $rowobject
672 $cd->cd_to_producer->find_or_create({ producer => $producer },
674 { key => 'primary' });
675 Tries to find a record based on its primary key or unique constraints;
677 if none is found, creates one and returns that instead.
678 my $cd = $schema->resultset('CD')->find_or_create({
680 cdid => 5,
681 artist => 'Massive Attack',
682 title => 'Mezzanine',
683 year => 2005,
684 });
685 Also takes an optional "key" attribute, to search by a specific key or
687 unique constraint. For example:
688 my $cd = $schema->resultset('CD')->find_or_create(
690 {
691 artist => 'Massive Attack',
692 title => 'Mezzanine',
693 },
694 { key => 'cd_artist_title' }
695 );
696 Note: Because find_or_create() reads from the database and then
698 possibly inserts based on the result, this method is subject to a race
699 condition. Another process could create a record in the table after the
700 find has completed and before the create has started. To avoid this
701 problem, use find_or_create() inside a transaction.
702 Note: Take care when using "find_or_create" with a table having columns
704 with default values that you intend to be automatically supplied by the
705 database (e.g. an auto_increment primary key column). In normal usage,
706 the value of such columns should NOT be included at all in the call to
707 "find_or_create", even when set to "undef".
708 See also "find" and "update_or_create". For information on how to
710 declare unique constraints, see "add_unique_constraint" in
711 DBIx::Class::ResultSource.
712 update_or_create
714 Arguments: \%col_values, { key => $unique_constraint }?
715 Return Value: $rowobject
716 $resultset->update_or_create({ col => $val, ... });
718 First, searches for an existing row matching one of the unique
720 constraints (including the primary key) on the source of this
721 resultset. If a row is found, updates it with the other given column
722 values. Otherwise, creates a new row.
723 Takes an optional "key" attribute to search on a specific unique
725 constraint. For example:
726 # In your application
728 my $cd = $schema->resultset('CD')->update_or_create(
729 {
730 artist => 'Massive Attack',
731 title => 'Mezzanine',
732 year => 1998,
733 },
734 { key => 'cd_artist_title' }
735 );
736 $cd->cd_to_producer->update_or_create({
738 producer => $producer,
739 name => 'harry',
740 }, {
741 key => 'primary,
742 });
743 If no "key" is specified, it searches on all unique constraints defined
745 on the source, including the primary key.
746 If the "key" is specified as "primary", it searches only on the primary
748 key.
749 See also "find" and "find_or_create". For information on how to declare
751 unique constraints, see "add_unique_constraint" in
752 DBIx::Class::ResultSource.
753 Note: Take care when using "update_or_create" with a table having
755 columns with default values that you intend to be automatically
756 supplied by the database (e.g. an auto_increment primary key column).
757 In normal usage, the value of such columns should NOT be included at
758 all in the call to "update_or_create", even when set to "undef".
759 update_or_new
761 Arguments: \%col_values, { key => $unique_constraint }?
762 Return Value: $rowobject
763 $resultset->update_or_new({ col => $val, ... });
765 First, searches for an existing row matching one of the unique
767 constraints (including the primary key) on the source of this
768 resultset. If a row is found, updates it with the other given column
769 values. Otherwise, instantiate a new result object and return it. The
770 object will not be saved into your storage until you call "insert" in
771 DBIx::Class::Row on it.
772 Takes an optional "key" attribute to search on a specific unique
774 constraint. For example:
775 # In your application
777 my $cd = $schema->resultset('CD')->update_or_new(
778 {
779 artist => 'Massive Attack',
780 title => 'Mezzanine',
781 year => 1998,
782 },
783 { key => 'cd_artist_title' }
784 );
785 if ($cd->in_storage) {
787 # the cd was updated
788 }
789 else {
790 # the cd is not yet in the database, let's insert it
791 $cd->insert;
792 }
793 Note: Take care when using "update_or_new" with a table having columns
795 with default values that you intend to be automatically supplied by the
796 database (e.g. an auto_increment primary key column). In normal usage,
797 the value of such columns should NOT be included at all in the call to
798 "update_or_new", even when set to "undef".
799 See also "find", "find_or_create" and "find_or_new".
801 get_cache
803 Arguments: none
804 Return Value: \@cache_objects?
805 Gets the contents of the cache for the resultset, if the cache is set.
807 The cache is populated either by using the "prefetch" attribute to
809 "search" or by calling "set_cache".
810 set_cache
812 Arguments: \@cache_objects
813 Return Value: \@cache_objects
814 Sets the contents of the cache for the resultset. Expects an arrayref
816 of objects of the same class as those produced by the resultset. Note
817 that if the cache is set the resultset will return the cached objects
818 rather than re-querying the database even if the cache attr is not set.
819 The contents of the cache can also be populated by using the "prefetch"
821 attribute to "search".
822 clear_cache
824 Arguments: none
825 Return Value: []
826 Clears the cache for the resultset.
828 is_paged
830 Arguments: none
831 Return Value: true, if the resultset has been paginated
832 is_ordered
834 Arguments: none
835 Return Value: true, if the resultset has been ordered with "order_by".
836 related_resultset
838 Arguments: $relationship_name
839 Return Value: $resultset
840 Returns a related resultset for the supplied relationship name.
842 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
844 current_source_alias
846 Arguments: none
847 Return Value: $source_alias
848 Returns the current table alias for the result source this resultset is
850 built on, that will be used in the SQL query. Usually it is "me".
851 Currently the source alias that refers to the result set returned by a
853 "search"/"find" family method depends on how you got to the resultset:
854 it's "me" by default, but eg. "search_related" aliases it to the
855 related result source name (and keeps "me" referring to the original
856 result set). The long term goal is to make DBIx::Class always alias the
857 current resultset as "me" (and make this method unnecessary).
858 Thus it's currently necessary to use this method in predefined queries
860 (see "Predefined searches" in DBIx::Class::Manual::Cookbook) when
861 referring to the source alias of the current result set:
862 # in a result set class
864 sub modified_by {
865 my ($self, $user) = @_;
866 my $me = $self->current_source_alias;
868 return $self->search(
870 "$me.modified" => $user->id,
871 );
872 }
873 as_subselect_rs
875 Arguments: none
876 Return Value: $resultset
877 Act as a barrier to SQL symbols. The resultset provided will be made
879 into a "virtual view" by including it as a subquery within the from
880 clause. From this point on, any joined tables are inaccessible to
881 ->search on the resultset (as if it were simply where-filtered without
882 joins). For example:
883 my $rs = $schema->resultset('Bar')->search({'x.name' => 'abc'},{ join => 'x' });
885 # 'x' now pollutes the query namespace
887 # So the following works as expected
889 my $ok_rs = $rs->search({'x.other' => 1});
890 # But this doesn't: instead of finding a 'Bar' related to two x rows (abc and
892 # def) we look for one row with contradictory terms and join in another table
893 # (aliased 'x_2') which we never use
894 my $broken_rs = $rs->search({'x.name' => 'def'});
895 my $rs2 = $rs->as_subselect_rs;
897 # doesn't work - 'x' is no longer accessible in $rs2, having been sealed away
899 my $not_joined_rs = $rs2->search({'x.other' => 1});
900 # works as expected: finds a 'table' row related to two x rows (abc and def)
902 my $correctly_joined_rs = $rs2->search({'x.name' => 'def'});
903 Another example of when one might use this would be to select a subset
905 of columns in a group by clause:
906 my $rs = $schema->resultset('Bar')->search(undef, {
908 group_by => [qw{ id foo_id baz_id }],
909 })->as_subselect_rs->search(undef, {
910 columns => [qw{ id foo_id }]
911 });
912 In the above example normally columns would have to be equal to the
914 group by, but because we isolated the group by into a subselect the
915 above works.
916 throw_exception
918 See "throw_exception" in DBIx::Class::Schema for details.
919
921 Attributes are used to refine a ResultSet in various ways when
922 searching for data. They can be passed to any method which takes an
923 "\%attrs" argument. See "search", "search_rs", "find", "count".
924 These are in no particular order:
926 order_by
928 Value: ( $order_by | \@order_by | \%order_by )
929 Which column(s) to order the results by.
931 [The full list of suitable values is documented in "ORDER BY CLAUSES"
933 in SQL::Abstract; the following is a summary of common options.]
934 If a single column name, or an arrayref of names is supplied, the
936 argument is passed through directly to SQL. The hashref syntax allows
937 for connection-agnostic specification of ordering direction:
938 For descending order:
940 order_by => { -desc => [qw/col1 col2 col3/] }
942 For explicit ascending order:
944 order_by => { -asc => 'col' }
946 The old scalarref syntax (i.e. order_by => \'year DESC') is still
948 supported, although you are strongly encouraged to use the hashref
949 syntax as outlined above.
950 columns
952 Value: \@columns
953 Shortcut to request a particular set of columns to be retrieved. Each
955 column spec may be a string (a table column name), or a hash (in which
956 case the key is the "as" value, and the value is used as the "select"
957 expression). Adds "me." onto the start of any column without a "." in
958 it and sets "select" from that, then auto-populates "as" from "select"
959 as normal. (You may also use the "cols" attribute, as in earlier
960 versions of DBIC.)
961 +columns
963 Value: \@columns
964 Indicates additional columns to be selected from storage. Works the
966 same as "columns" but adds columns to the selection. (You may also use
967 the "include_columns" attribute, as in earlier versions of DBIC). For
968 example:-
969 $schema->resultset('CD')->search(undef, {
971 '+columns' => ['artist.name'],
972 join => ['artist']
973 });
974 would return all CDs and include a 'name' column to the information
976 passed to object inflation. Note that the 'artist' is the name of the
977 column (or relationship) accessor, and 'name' is the name of the column
978 accessor in the related table.
979 include_columns
981 Value: \@columns
982 Deprecated. Acts as a synonym for "+columns" for backward
984 compatibility.
985 select
987 Value: \@select_columns
988 Indicates which columns should be selected from the storage. You can
990 use column names, or in the case of RDBMS back ends, function or stored
991 procedure names:
992 $rs = $schema->resultset('Employee')->search(undef, {
994 select => [
995 'name',
996 { count => 'employeeid' },
997 { sum => 'salary' }
998 ]
999 });
1000 When you use function/stored procedure names and do not supply an "as"
1002 attribute, the column names returned are storage-dependent. E.g. MySQL
1003 would return a column named "count(employeeid)" in the above example.
1004 NOTE: You will almost always need a corresponding 'as' entry when you
1006 use 'select'.
1007 +select
1009 Indicates additional columns to be selected from storage. Works
1010 the same as "select" but adds columns to the selection.
1011 +as
1013 Indicates additional column names for those added via "+select".
1014 See "as".
1015 as
1017 Value: \@inflation_names
1018 Indicates column names for object inflation. That is, "as" indicates
1020 the name that the column can be accessed as via the "get_column" method
1021 (or via the object accessor, if one already exists). It has nothing to
1022 do with the SQL code "SELECT foo AS bar".
1023 The "as" attribute is used in conjunction with "select", usually when
1025 "select" contains one or more function or stored procedure names:
1026 $rs = $schema->resultset('Employee')->search(undef, {
1028 select => [
1029 'name',
1030 { count => 'employeeid' }
1031 ],
1032 as => ['name', 'employee_count'],
1033 });
1034 my $employee = $rs->first(); # get the first Employee
1036 If the object against which the search is performed already has an
1038 accessor matching a column name specified in "as", the value can be
1039 retrieved using the accessor as normal:
1040 my $name = $employee->name();
1042 If on the other hand an accessor does not exist in the object, you need
1044 to use "get_column" instead:
1045 my $employee_count = $employee->get_column('employee_count');
1047 You can create your own accessors if required - see
1049 DBIx::Class::Manual::Cookbook for details.
1050 Please note: This will NOT insert an "AS employee_count" into the SQL
1052 statement produced, it is used for internal access only. Thus
1053 attempting to use the accessor in an "order_by" clause or similar will
1054 fail miserably.
1055 To get around this limitation, you can supply literal SQL to your
1057 "select" attribute that contains the "AS alias" text, e.g.
1058 select => [\'myfield AS alias']
1060 join
1062 Value: ($rel_name | \@rel_names | \%rel_names)
1063 Contains a list of relationships that should be joined for this query.
1065 For example:
1066 # Get CDs by Nine Inch Nails
1068 my $rs = $schema->resultset('CD')->search(
1069 { 'artist.name' => 'Nine Inch Nails' },
1070 { join => 'artist' }
1071 );
1072 Can also contain a hash reference to refer to the other relation's
1074 relations. For example:
1075 package MyApp::Schema::Track;
1077 use base qw/DBIx::Class/;
1078 __PACKAGE__->table('track');
1079 __PACKAGE__->add_columns(qw/trackid cd position title/);
1080 __PACKAGE__->set_primary_key('trackid');
1081 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1082 1;
1083 # In your application
1085 my $rs = $schema->resultset('Artist')->search(
1086 { 'track.title' => 'Teardrop' },
1087 {
1088 join => { cd => 'track' },
1089 order_by => 'artist.name',
1090 }
1091 );
1092 You need to use the relationship (not the table) name in conditions,
1094 because they are aliased as such. The current table is aliased as "me",
1095 so you need to use me.column_name in order to avoid ambiguity. For
1096 example:
1097 # Get CDs from 1984 with a 'Foo' track
1099 my $rs = $schema->resultset('CD')->search(
1100 {
1101 'me.year' => 1984,
1102 'tracks.name' => 'Foo'
1103 },
1104 { join => 'tracks' }
1105 );
1106 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1108 similarly for a third time). For e.g.
1109 my $rs = $schema->resultset('Artist')->search({
1111 'cds.title' => 'Down to Earth',
1112 'cds_2.title' => 'Popular',
1113 }, {
1114 join => [ qw/cds cds/ ],
1115 });
1116 will return a set of all artists that have both a cd with title 'Down
1118 to Earth' and a cd with title 'Popular'.
1119 If you want to fetch related objects from other tables as well, see
1121 "prefetch" below.
1122 For more help on using joins with search, see
1124 DBIx::Class::Manual::Joining.
1125 prefetch
1127 Value: ($rel_name | \@rel_names | \%rel_names)
1128 Contains one or more relationships that should be fetched along with
1130 the main query (when they are accessed afterwards the data will already
1131 be available, without extra queries to the database). This is useful
1132 for when you know you will need the related objects, because it saves
1133 at least one query:
1134 my $rs = $schema->resultset('Tag')->search(
1136 undef,
1137 {
1138 prefetch => {
1139 cd => 'artist'
1140 }
1141 }
1142 );
1143 The initial search results in SQL like the following:
1145 SELECT tag.*, cd.*, artist.* FROM tag
1147 JOIN cd ON tag.cd = cd.cdid
1148 JOIN artist ON cd.artist = artist.artistid
1149 DBIx::Class has no need to go back to the database when we access the
1151 "cd" or "artist" relationships, which saves us two SQL statements in
1152 this case.
1153 Simple prefetches will be joined automatically, so there is no need for
1155 a "join" attribute in the above search.
1156 "prefetch" can be used with the following relationship types:
1158 "belongs_to", "has_one" (or if you're using "add_relationship", any
1159 relationship declared with an accessor type of 'single' or 'filter'). A
1160 more complex example that prefetches an artists cds, the tracks on
1161 those cds, and the tags associated with that artist is given below
1162 (assuming many-to-many from artists to tags):
1163 my $rs = $schema->resultset('Artist')->search(
1165 undef,
1166 {
1167 prefetch => [
1168 { cds => 'tracks' },
1169 { artist_tags => 'tags' }
1170 ]
1171 }
1172 );
1173 NOTE: If you specify a "prefetch" attribute, the "join" and "select"
1175 attributes will be ignored.
1176 CAVEATs: Prefetch does a lot of deep magic. As such, it may not behave
1178 exactly as you might expect.
1179 · Prefetch uses the "cache" to populate the prefetched relationships.
1181 This may or may not be what you want.
1182 · If you specify a condition on a prefetched relationship, ONLY those
1184 rows that match the prefetched condition will be fetched into that
1185 relationship. This means that adding prefetch to a search() may
1186 alter what is returned by traversing a relationship. So, if you
1187 have "Artist->has_many(CDs)" and you do
1188 my $artist_rs = $schema->resultset('Artist')->search({
1190 'cds.year' => 2008,
1191 }, {
1192 join => 'cds',
1193 });
1194 my $count = $artist_rs->first->cds->count;
1196 my $artist_rs_prefetch = $artist_rs->search( {}, { prefetch => 'cds' } );
1198 my $prefetch_count = $artist_rs_prefetch->first->cds->count;
1200 cmp_ok( $count, '==', $prefetch_count, "Counts should be the same" );
1202 that cmp_ok() may or may not pass depending on the datasets
1204 involved. This behavior may or may not survive the 0.09 transition.
1205 page
1207 Value: $page
1208 Makes the resultset paged and specifies the page to retrieve.
1210 Effectively identical to creating a non-pages resultset and then
1211 calling ->page($page) on it.
1212 If rows attribute is not specified it defaults to 10 rows per page.
1214 When you have a paged resultset, "count" will only return the number of
1216 rows in the page. To get the total, use the "pager" and call
1217 "total_entries" on it.
1218 rows
1220 Value: $rows
1221 Specifies the maximum number of rows for direct retrieval or the number
1223 of rows per page if the page attribute or method is used.
1224 offset
1226 Value: $offset
1227 Specifies the (zero-based) row number for the first row to be
1229 returned, or the of the first row of the first page if paging is used.
1230 group_by
1232 Value: \@columns
1233 A arrayref of columns to group by. Can include columns of joined
1235 tables.
1236 group_by => [qw/ column1 column2 ... /]
1238 having
1240 Value: $condition
1241 HAVING is a select statement attribute that is applied between GROUP BY
1243 and ORDER BY. It is applied to the after the grouping calculations have
1244 been done.
1245 having => { 'count(employee)' => { '>=', 100 } }
1247 distinct
1249 Value: (0 | 1)
1250 Set to 1 to group by all columns. If the resultset already has a
1252 group_by attribute, this setting is ignored and an appropriate warning
1253 is issued.
1254 where
1256 Adds to the WHERE clause.
1257 # only return rows WHERE deleted IS NULL for all searches
1259 __PACKAGE__->resultset_attributes({ where => { deleted => undef } }); )
1260 Can be overridden by passing "{ where => undef }" as an attribute
1262 to a resultset.
1263 cache
1265 Set to 1 to cache search results. This prevents extra SQL queries if
1266 you revisit rows in your ResultSet:
1267 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1269 while( my $artist = $resultset->next ) {
1271 ... do stuff ...
1272 }
1273 $rs->first; # without cache, this would issue a query
1275 By default, searches are not cached.
1277 For more examples of using these attributes, see
1279 DBIx::Class::Manual::Cookbook.
1280 for
1282 Value: ( 'update' | 'shared' )
1283 Set to 'update' for a SELECT ... FOR UPDATE or 'shared' for a SELECT
1285 ... FOR SHARED.
1286perl v5.12.0 2010-05-12 DBIx::Class::ResultSet(3)