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 while( $user = $users_rs->next) {
12 print $user->username;
13 }
14 my $registered_users_rs = $schema->resultset('User')->search({ registered => 1 });
16 my @cds_in_2005 = $schema->resultset('CD')->search({ year => 2005 })->all();
17
19 A ResultSet is an object which stores a set of conditions representing
20 a query. It is the backbone of DBIx::Class (i.e. the really
21 important/useful bit).
22 No SQL is executed on the database when a ResultSet is created, it just
24 stores all the conditions needed to create the query.
25 A basic ResultSet representing the data of an entire table is returned
27 by calling "resultset" on a DBIx::Class::Schema and passing in a Source
28 name.
29 my $users_rs = $schema->resultset('User');
31 A new ResultSet is returned from calling "search" on an existing
33 ResultSet. The new one will contain all the conditions of the original,
34 plus any new conditions added in the "search" call.
35 A ResultSet also incorporates an implicit iterator. "next" and "reset"
37 can be used to walk through all the DBIx::Class::Rows the ResultSet
38 represents.
39 The query that the ResultSet represents is only executed against the
41 database when these methods are called: "find", "next", "all", "first",
42 "single", "count".
43 If a resultset is used in a numeric context it returns the "count".
45 However, if it is used in a boolean context it is always true. So if
46 you want to check if a resultset has any results, you must use "if $rs
47 != 0".
48
50 Chaining resultsets
51 Let's say you've got a query that needs to be run to return some data
52 to the user. But, you have an authorization system in place that
53 prevents certain users from seeing certain information. So, you want to
54 construct the basic query in one method, but add constraints to it in
55 another.
56 sub get_data {
58 my $self = shift;
59 my $request = $self->get_request; # Get a request object somehow.
60 my $schema = $self->result_source->schema;
61 my $cd_rs = $schema->resultset('CD')->search({
63 title => $request->param('title'),
64 year => $request->param('year'),
65 });
66 $cd_rs = $self->apply_security_policy( $cd_rs );
68 return $cd_rs->all();
70 }
71 sub apply_security_policy {
73 my $self = shift;
74 my ($rs) = @_;
75 return $rs->search({
77 subversive => 0,
78 });
79 }
80 Resolving conditions and attributes
82 When a resultset is chained from another resultset (e.g.: "my $new_rs =
84 $old_rs->search(\%extra_cond, \%attrs)"), conditions and attributes
85 with the same keys need resolving.
86 If any of "columns", "select", "as" are present, they reset the
88 original selection, and start the selection "clean".
89 The "join", "prefetch", "+columns", "+select", "+as" attributes are
91 merged into the existing ones from the original resultset.
92 The "where" and "having" attributes, and any search conditions, are
94 merged with an SQL "AND" to the existing condition from the original
95 resultset.
96 All other attributes are overridden by any new ones supplied in the
98 search attributes.
99 Multiple queries
101 Since a resultset just defines a query, you can do all sorts of things
102 with it with the same object.
103 # Don't hit the DB yet.
105 my $cd_rs = $schema->resultset('CD')->search({
106 title => 'something',
107 year => 2009,
108 });
109 # Each of these hits the DB individually.
111 my $count = $cd_rs->count;
112 my $most_recent = $cd_rs->get_column('date_released')->max();
113 my @records = $cd_rs->all;
114 And it's not just limited to SELECT statements.
116 $cd_rs->delete();
118 This is even cooler:
120 $cd_rs->create({ artist => 'Fred' });
122 Which is the same as:
124 $schema->resultset('CD')->create({
126 title => 'something',
127 year => 2009,
128 artist => 'Fred'
129 });
130 See: "search", "count", "get_column", "all", "create".
132 Custom ResultSet classes
134 To add methods to your resultsets, you can subclass
135 DBIx::Class::ResultSet, similar to:
136 package MyApp::Schema::ResultSet::User;
138 use strict;
140 use warnings;
141 use base 'DBIx::Class::ResultSet';
143 sub active {
145 my $self = shift;
146 $self->search({ $self->current_source_alias . '.active' => 1 });
147 }
148 sub unverified {
150 my $self = shift;
151 $self->search({ $self->current_source_alias . '.verified' => 0 });
152 }
153 sub created_n_days_ago {
155 my ($self, $days_ago) = @_;
156 $self->search({
157 $self->current_source_alias . '.create_date' => {
158 '<=',
159 $self->result_source->schema->storage->datetime_parser->format_datetime(
160 DateTime->now( time_zone => 'UTC' )->subtract( days => $days_ago )
161 )}
162 });
163 }
164 sub users_to_warn { shift->active->unverified->created_n_days_ago(7) }
166 1;
168 See "load_namespaces" in DBIx::Class::Schema on how DBIC can discover
170 and automatically attach Result-specific ResulSet classes.
171 ResultSet subclassing with Moose and similar constructor-providers
173 Using Moose or Moo in your ResultSet classes is usually overkill, but
175 you may find it useful if your ResultSets contain a lot of business
176 logic (e.g. "has xml_parser", "has json", etc) or if you just prefer to
177 organize your code via roles.
178 In order to write custom ResultSet classes with Moo you need to use the
180 following template. The BUILDARGS is necessary due to the unusual
181 signature of the constructor provided by DBIC
182 "->new($source, \%args)".
183 use Moo;
185 extends 'DBIx::Class::ResultSet';
186 sub BUILDARGS { $_[2] || {} } # ::RS::new() expects my ($class, $rsrc, $args) = @_
187 ...your code...
189 1;
191 If you want to build your custom ResultSet classes with Moose, you need
193 a similar, though a little more elaborate template in order to
194 interface the inlining of the Moose-provided object constructor, with
195 the DBIC one.
196 package MyApp::Schema::ResultSet::User;
198 use Moose;
200 use MooseX::NonMoose;
201 extends 'DBIx::Class::ResultSet';
202 sub BUILDARGS { $_[2] || {} } # ::RS::new() expects my ($class, $rsrc, $args) = @_
204 ...your code...
206 __PACKAGE__->meta->make_immutable;
208 1;
210 The MooseX::NonMoose is necessary so that the Moose constructor does
212 not entirely overwrite the DBIC one (in contrast Moo does this
213 automatically). Alternatively, you can skip MooseX::NonMoose and get
214 by with just Moose instead by doing:
215 __PACKAGE__->meta->make_immutable(inline_constructor => 0);
217
219 new
220 Arguments: $source, \%attrs?
221 Return Value: $resultset
222 The resultset constructor. Takes a source object (usually a
224 DBIx::Class::ResultSourceProxy::Table) and an attribute hash (see
225 "ATTRIBUTES" below). Does not perform any queries -- these are
226 executed as needed by the other methods.
227 Generally you never construct a resultset manually. Instead you get one
229 from e.g. a "$schema->resultset('$source_name')" or
230 "$another_resultset->search(...)" (the later called in scalar context):
231 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
233 WARNING
235 If called on an object, proxies to "new_result" instead, so
236 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
238 will return a CD object, not a ResultSet, and is equivalent to:
240 my $cd = $schema->resultset('CD')->new_result({ title => 'Spoon' });
242 Please also keep in mind that many internals call "new_result"
244 directly, so overloading this method with the idea of intercepting
245 new result object creation will not work. See also warning
246 pertaining to "create".
247 search
249 Arguments: $cond | undef, \%attrs?
250 Return Value: $resultset (scalar context) | @result_objs (list context)
251 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
253 my $new_rs = $cd_rs->search({ year => 2005 });
254 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
256 # year = 2005 OR year = 2004
257 In list context, "->all()" is called implicitly on the resultset, thus
259 returning a list of result objects instead. To avoid that, use
260 "search_rs".
261 If you need to pass in additional attributes but no additional
263 condition, call it as "search(undef, \%attrs)".
264 # "SELECT name, artistid FROM $artist_table"
266 my @all_artists = $schema->resultset('Artist')->search(undef, {
267 columns => [qw/name artistid/],
268 });
269 For a list of attributes that can be passed to "search", see
271 "ATTRIBUTES". For more examples of using this function, see Searching.
272 For a complete documentation for the first argument, see "WHERE
273 CLAUSES" in SQL::Abstract::Classic and its extension
274 DBIx::Class::SQLMaker.
275 For more help on using joins with search, see
277 DBIx::Class::Manual::Joining.
278 CAVEAT
280 Note that "search" does not process/deflate any of the values passed in
282 the SQL::Abstract::Classic-compatible search condition structure. This
283 is unlike other condition-bound methods "new_result", "create" and
284 "find". The user must ensure manually that any value passed to this
285 method will stringify to something the RDBMS knows how to deal with. A
286 notable example is the handling of DateTime objects, for more info see:
287 "Formatting DateTime objects in queries" in
288 DBIx::Class::Manual::Cookbook.
289 search_rs
291 Arguments: $cond, \%attrs?
292 Return Value: $resultset
293 This method does the same exact thing as search() except it will always
295 return a resultset, even in list context.
296 search_literal
298 CAVEAT: "search_literal" is provided for Class::DBI compatibility and
299 should only be used in that context. "search_literal" is a convenience
300 method. It is equivalent to calling "$schema->search(\[])", but if you
301 want to ensure columns are bound correctly, use "search".
302 See "SEARCHING" in DBIx::Class::Manual::Cookbook and "Searching" in
304 DBIx::Class::Manual::FAQ for searching techniques that do not require
305 "search_literal".
306 Arguments: $sql_fragment, @standalone_bind_values
308 Return Value: $resultset (scalar context) | @result_objs (list context)
309 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
311 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
312 Pass a literal chunk of SQL to be added to the conditional part of the
314 resultset query.
315 Example of how to use "search" instead of "search_literal"
317 my @cds = $cd_rs->search_literal('cdid = ? AND (artist = ? OR artist = ?)', (2, 1, 2));
319 my @cds = $cd_rs->search(\[ 'cdid = ? AND (artist = ? OR artist = ?)', [ 'cdid', 2 ], [ 'artist', 1 ], [ 'artist', 2 ] ]);
320 find
322 Arguments: \%columns_values | @pk_values, { key => $unique_constraint,
323 %attrs }?
324 Return Value: $result | undef
325 Finds and returns a single row based on supplied criteria. Takes either
327 a hashref with the same format as "create" (including inference of
328 foreign keys from related objects), or a list of primary key values in
329 the same order as the primary columns declaration on the
330 "result_source".
331 In either case an attempt is made to combine conditions already
333 existing on the resultset with the condition passed to this method.
334 To aid with preparing the correct query for the storage you may supply
336 the "key" attribute, which is the name of a unique constraint (the
337 unique constraint corresponding to the primary columns is always named
338 "primary"). If the "key" attribute has been supplied, and DBIC is
339 unable to construct a query that satisfies the named unique constraint
340 fully ( non-NULL values for each column member of the constraint) an
341 exception is thrown.
342 If no "key" is specified, the search is carried over all unique
344 constraints which are fully defined by the available condition.
345 If no such constraint is found, "find" currently defaults to a simple
347 "search->(\%column_values)" which may or may not do what you expect.
348 Note that this fallback behavior may be deprecated in further versions.
349 If you need to search with arbitrary conditions - use "search". If the
350 query resulting from this fallback produces more than one row, a
351 warning to the effect is issued, though only the first row is
352 constructed and returned as $result_object.
353 In addition to "key", "find" recognizes and applies standard resultset
355 attributes in the same way as "search" does.
356 Note that if you have extra concerns about the correctness of the
358 resulting query you need to specify the "key" attribute and supply the
359 entire condition as an argument to find (since it is not always
360 possible to perform the combination of the resultset condition with the
361 supplied one, especially if the resultset condition contains literal
362 sql).
363 For example, to find a row by its primary key:
365 my $cd = $schema->resultset('CD')->find(5);
367 You can also find a row by a specific unique constraint:
369 my $cd = $schema->resultset('CD')->find(
371 {
372 artist => 'Massive Attack',
373 title => 'Mezzanine',
374 },
375 { key => 'cd_artist_title' }
376 );
377 See also "find_or_create" and "update_or_create".
379 search_related
381 Arguments: $rel_name, $cond?, \%attrs?
382 Return Value: $resultset (scalar context) | @result_objs (list context)
383 $new_rs = $cd_rs->search_related('artist', {
385 name => 'Emo-R-Us',
386 });
387 Searches the specified relationship, optionally specifying a condition
389 and attributes for matching records. See "ATTRIBUTES" for more
390 information.
391 In list context, "->all()" is called implicitly on the resultset, thus
393 returning a list of result objects instead. To avoid that, use
394 "search_related_rs".
395 See also "search_related_rs".
397 search_related_rs
399 This method works exactly the same as search_related, except that it
400 guarantees a resultset, even in list context.
401 cursor
403 Arguments: none
404 Return Value: $cursor
405 Returns a storage-driven cursor to the given resultset. See
407 DBIx::Class::Cursor for more information.
408 single
410 Arguments: $cond?
411 Return Value: $result | undef
412 my $cd = $schema->resultset('CD')->single({ year => 2001 });
414 Inflates the first result without creating a cursor if the resultset
416 has any records in it; if not returns "undef". Used by "find" as a lean
417 version of "search".
418 While this method can take an optional search condition (just like
420 "search") being a fast-code-path it does not recognize search
421 attributes. If you need to add extra joins or similar, call "search"
422 and then chain-call "single" on the DBIx::Class::ResultSet returned.
423 Note
425 As of 0.08100, this method enforces the assumption that the
426 preceding query returns only one row. If more than one row is
427 returned, you will receive a warning:
428 Query returned more than one row
430 In this case, you should be using "next" or "find" instead, or if
432 you really know what you are doing, use the "rows" attribute to
433 explicitly limit the size of the resultset.
434 This method will also throw an exception if it is called on a
436 resultset prefetching has_many, as such a prefetch implies fetching
437 multiple rows from the database in order to assemble the resulting
438 object.
439 get_column
441 Arguments: $cond?
442 Return Value: $resultsetcolumn
443 my $max_length = $rs->get_column('length')->max;
445 Returns a DBIx::Class::ResultSetColumn instance for a column of the
447 ResultSet.
448 search_like
450 Arguments: $cond, \%attrs?
451 Return Value: $resultset (scalar context) | @result_objs (list context)
452 # WHERE title LIKE '%blue%'
454 $cd_rs = $rs->search_like({ title => '%blue%'});
455 Performs a search, but uses "LIKE" instead of "=" as the condition.
457 Note that this is simply a convenience method retained for ex
458 Class::DBI users. You most likely want to use "search" with specific
459 operators.
460 For more information, see DBIx::Class::Manual::Cookbook.
462 This method is deprecated and will be removed in 0.09. Use search()
464 instead. An example conversion is:
465 ->search_like({ foo => 'bar' });
467 # Becomes
469 ->search({ foo => { like => 'bar' } });
471 slice
473 Arguments: $first, $last
474 Return Value: $resultset (scalar context) | @result_objs (list context)
475 Returns a resultset or object list representing a subset of elements
477 from the resultset slice is called on. Indexes are from 0, i.e., to get
478 the first three records, call:
479 my ($one, $two, $three) = $rs->slice(0, 2);
481 next
483 Arguments: none
484 Return Value: $result | undef
485 Returns the next element in the resultset ("undef" is there is none).
487 Can be used to efficiently iterate over records in the resultset:
489 my $rs = $schema->resultset('CD')->search;
491 while (my $cd = $rs->next) {
492 print $cd->title;
493 }
494 Note that you need to store the resultset object, and call "next" on
496 it. Calling "resultset('Table')->next" repeatedly will always return
497 the first record from the resultset.
498 result_source
500 Arguments: $result_source?
501 Return Value: $result_source
502 An accessor for the primary ResultSource object from which this
504 ResultSet is derived.
505 result_class
507 Arguments: $result_class?
508 Return Value: $result_class
509 An accessor for the class to use when creating result objects. Defaults
511 to "result_source->result_class" - which in most cases is the name of
512 the "table" class.
513 Note that changing the result_class will also remove any components
515 that were originally loaded in the source class via load_components.
516 Any overloaded methods in the original source class will not run.
517 count
519 Arguments: $cond, \%attrs?
520 Return Value: $count
521 Performs an SQL "COUNT" with the same query as the resultset was built
523 with to find the number of elements. Passing arguments is equivalent to
524 "$rs->search ($cond, \%attrs)->count"
525 count_rs
527 Arguments: $cond, \%attrs?
528 Return Value: $count_rs
529 Same as "count" but returns a DBIx::Class::ResultSetColumn object.
531 This can be very handy for subqueries:
532 ->search( { amount => $some_rs->count_rs->as_query } )
534 As with regular resultsets the SQL query will be executed only after
536 the resultset is accessed via "next" or "all". That would return the
537 same single value obtainable via "count".
538 count_literal
540 CAVEAT: "count_literal" is provided for Class::DBI compatibility and
541 should only be used in that context. See "search_literal" for further
542 info.
543 Arguments: $sql_fragment, @standalone_bind_values
545 Return Value: $count
546 Counts the results in a literal query. Equivalent to calling
548 "search_literal" with the passed arguments, then "count".
549 all
551 Arguments: none
552 Return Value: @result_objs
553 Returns all elements in the resultset.
555 reset
557 Arguments: none
558 Return Value: $self
559 Resets the resultset's cursor, so you can iterate through the elements
561 again. Implicitly resets the storage cursor, so a subsequent "next"
562 will trigger another query.
563 first
565 Arguments: none
566 Return Value: $result | undef
567 Resets the resultset (causing a fresh query to storage) and returns an
569 object for the first result (or "undef" if the resultset is empty).
570 update
572 Arguments: \%values
573 Return Value: $underlying_storage_rv
574 Sets the specified columns in the resultset to the supplied values in a
576 single query. Note that this will not run any
577 accessor/set_column/update triggers, nor will it update any result
578 object instances derived from this resultset (this includes the
579 contents of the resultset cache if any). See "update_all" if you need
580 to execute any on-update triggers or cascades defined either by you or
581 a result component.
582 The return value is a pass through of what the underlying storage
584 backend returned, and may vary. See "execute" in DBI for the most
585 common case.
586 CAVEAT
588 Note that "update" does not process/deflate any of the values passed
590 in. This is unlike the corresponding "update" in DBIx::Class::Row. The
591 user must ensure manually that any value passed to this method will
592 stringify to something the RDBMS knows how to deal with. A notable
593 example is the handling of DateTime objects, for more info see:
594 "Formatting DateTime objects in queries" in
595 DBIx::Class::Manual::Cookbook.
596 update_all
598 Arguments: \%values
599 Return Value: 1
600 Fetches all objects and updates them one at a time via "update" in
602 DBIx::Class::Row. Note that "update_all" will run DBIC defined
603 triggers, while "update" will not.
604 delete
606 Arguments: none
607 Return Value: $underlying_storage_rv
608 Deletes the rows matching this resultset in a single query. Note that
610 this will not run any delete triggers, nor will it alter the in_storage
611 status of any result object instances derived from this resultset (this
612 includes the contents of the resultset cache if any). See "delete_all"
613 if you need to execute any on-delete triggers or cascades defined
614 either by you or a result component.
615 The return value is a pass through of what the underlying storage
617 backend returned, and may vary. See "execute" in DBI for the most
618 common case.
619 delete_all
621 Arguments: none
622 Return Value: 1
623 Fetches all objects and deletes them one at a time via "delete" in
625 DBIx::Class::Row. Note that "delete_all" will run DBIC defined
626 triggers, while "delete" will not.
627 populate
629 Arguments: [ \@column_list, \@row_values+ ] | [ \%col_data+ ]
630 Return Value: \@result_objects (scalar context) | @result_objects (list
631 context)
632 Accepts either an arrayref of hashrefs or alternatively an arrayref of
634 arrayrefs.
635 NOTE
637 The context of this method call has an important effect on what is
638 submitted to storage. In void context data is fed directly to
639 fastpath insertion routines provided by the underlying storage
640 (most often "execute_for_fetch" in DBI), bypassing the new and
641 insert calls on the Result class, including any augmentation of
642 these methods provided by components. For example if you are using
643 something like DBIx::Class::UUIDColumns to create primary keys for
644 you, you will find that your PKs are empty. In this case you will
645 have to explicitly force scalar or list context in order to create
646 those values.
647 In non-void (scalar or list) context, this method is simply a wrapper
649 for "create". Depending on list or scalar context either a list of
650 Result objects or an arrayref containing these objects is returned.
651 When supplying data in "arrayref of arrayrefs" invocation style, the
653 first element should be a list of column names and each subsequent
654 element should be a data value in the earlier specified column order.
655 For example:
656 $schema->resultset("Artist")->populate([
658 [ qw( artistid name ) ],
659 [ 100, 'A Formally Unknown Singer' ],
660 [ 101, 'A singer that jumped the shark two albums ago' ],
661 [ 102, 'An actually cool singer' ],
662 ]);
663 For the arrayref of hashrefs style each hashref should be a structure
665 suitable for passing to "create". Multi-create is also permitted with
666 this syntax.
667 $schema->resultset("Artist")->populate([
669 { artistid => 4, name => 'Manufactured Crap', cds => [
670 { title => 'My First CD', year => 2006 },
671 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
672 ],
673 },
674 { artistid => 5, name => 'Angsty-Whiny Girl', cds => [
675 { title => 'My parents sold me to a record company', year => 2005 },
676 { title => 'Why Am I So Ugly?', year => 2006 },
677 { title => 'I Got Surgery and am now Popular', year => 2007 }
678 ],
679 },
680 ]);
681 If you attempt a void-context multi-create as in the example above
683 (each Artist also has the related list of CDs), and do not supply the
684 necessary autoinc foreign key information, this method will proxy to
685 the less efficient "create", and then throw the Result objects away. In
686 this case there are obviously no benefits to using this method over
687 "create".
688 pager
690 Arguments: none
691 Return Value: $pager
692 Returns a DBIx::Class::ResultSet::Pager object tied to the current
694 resultset. Requires the "page" attribute to have been previously set on
695 the resultset object, usually via a call to "page".
696 To get the full count of entries for a paged resultset, call
698 total_entries on the pager object.
699 page
701 Arguments: $page_number
702 Return Value: $resultset
703 Returns a resultset for the $page_number page of the resultset on which
705 page is called, where each page contains a number of rows equal to the
706 'rows' attribute set on the resultset (10 by default).
707 new_result
709 Arguments: \%col_data
710 Return Value: $result
711 Creates a new result object in the resultset's result class and returns
713 it. The row is not inserted into the database at this point, call
714 "insert" in DBIx::Class::Row to do that. Calling "in_storage" in
715 DBIx::Class::Row will tell you whether the result object has been
716 inserted or not.
717 Passes the hashref of input on to "new" in DBIx::Class::Row.
719 as_query
721 Arguments: none
722 Return Value: \[ $sql, @bind_values ]
723 Returns the SQL query and bind vars associated with the invocant.
725 This is generally used as the RHS for a subquery.
727 find_or_new
729 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
730 Return Value: $result
731 my $artist = $schema->resultset('Artist')->find_or_new(
733 { artist => 'fred' }, { key => 'artists' });
734 $cd->cd_to_producer->find_or_new({ producer => $producer },
736 { key => 'primary' });
737 Find an existing record from this resultset using "find". if none
739 exists, instantiate a new result object and return it. The object will
740 not be saved into your storage until you call "insert" in
741 DBIx::Class::Row on it.
742 You most likely want this method when looking for existing rows using a
744 unique constraint that is not the primary key, or looking for related
745 rows.
746 If you want objects to be saved immediately, use "find_or_create"
748 instead.
749 Note: Make sure to read the documentation of "find" and understand the
751 significance of the "key" attribute, as its lack may skew your search,
752 and subsequently result in spurious new objects.
753 Note: Take care when using "find_or_new" with a table having columns
755 with default values that you intend to be automatically supplied by the
756 database (e.g. an auto_increment primary key column). In normal usage,
757 the value of such columns should NOT be included at all in the call to
758 "find_or_new", even when set to "undef".
759 create
761 Arguments: \%col_data
762 Return Value: $result
763 Attempt to create a single new row or a row with multiple related rows
765 in the table represented by the resultset (and related tables). This
766 will not check for duplicate rows before inserting, use
767 "find_or_create" to do that.
768 To create one row for this resultset, pass a hashref of key/value pairs
770 representing the columns of the table and the values you wish to store.
771 If the appropriate relationships are set up, foreign key fields can
772 also be passed an object representing the foreign row, and the value
773 will be set to its primary key.
774 To create related objects, pass a hashref of related-object column
776 values keyed on the relationship name. If the relationship is of type
777 "multi" ("has_many" in DBIx::Class::Relationship) - pass an arrayref of
778 hashrefs. The process will correctly identify columns holding foreign
779 keys, and will transparently populate them from the keys of the
780 corresponding relation. This can be applied recursively, and will work
781 correctly for a structure with an arbitrary depth and width, as long as
782 the relationships actually exists and the correct column data has been
783 supplied.
784 Instead of hashrefs of plain related data (key/value pairs), you may
786 also pass new or inserted objects. New objects (not inserted yet, see
787 "new_result"), will be inserted into their appropriate tables.
788 Effectively a shortcut for "->new_result(\%col_data)->insert".
790 Example of creating a new row.
792 $person_rs->create({
794 name=>"Some Person",
795 email=>"somebody@someplace.com"
796 });
797 Example of creating a new row and also creating rows in a related
799 "has_many" or "has_one" resultset. Note Arrayref.
800 $artist_rs->create(
802 { artistid => 4, name => 'Manufactured Crap', cds => [
803 { title => 'My First CD', year => 2006 },
804 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
805 ],
806 },
807 );
808 Example of creating a new row and also creating a row in a related
810 "belongs_to" resultset. Note Hashref.
811 $cd_rs->create({
813 title=>"Music for Silly Walks",
814 year=>2000,
815 artist => {
816 name=>"Silly Musician",
817 }
818 });
819 WARNING
821 When subclassing ResultSet never attempt to override this method.
822 Since it is a simple shortcut for
823 "$self->new_result($attrs)->insert", a lot of the internals simply
824 never call it, so your override will be bypassed more often than
825 not. Override either "new" in DBIx::Class::Row or "insert" in
826 DBIx::Class::Row depending on how early in the "create" process you
827 need to intervene. See also warning pertaining to "new".
828 find_or_create
830 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
831 Return Value: $result
832 $cd->cd_to_producer->find_or_create({ producer => $producer },
834 { key => 'primary' });
835 Tries to find a record based on its primary key or unique constraints;
837 if none is found, creates one and returns that instead.
838 my $cd = $schema->resultset('CD')->find_or_create({
840 cdid => 5,
841 artist => 'Massive Attack',
842 title => 'Mezzanine',
843 year => 2005,
844 });
845 Also takes an optional "key" attribute, to search by a specific key or
847 unique constraint. For example:
848 my $cd = $schema->resultset('CD')->find_or_create(
850 {
851 artist => 'Massive Attack',
852 title => 'Mezzanine',
853 },
854 { key => 'cd_artist_title' }
855 );
856 Note: Make sure to read the documentation of "find" and understand the
858 significance of the "key" attribute, as its lack may skew your search,
859 and subsequently result in spurious row creation.
860 Note: Because find_or_create() reads from the database and then
862 possibly inserts based on the result, this method is subject to a race
863 condition. Another process could create a record in the table after the
864 find has completed and before the create has started. To avoid this
865 problem, use find_or_create() inside a transaction.
866 Note: Take care when using "find_or_create" with a table having columns
868 with default values that you intend to be automatically supplied by the
869 database (e.g. an auto_increment primary key column). In normal usage,
870 the value of such columns should NOT be included at all in the call to
871 "find_or_create", even when set to "undef".
872 See also "find" and "update_or_create". For information on how to
874 declare unique constraints, see "add_unique_constraint" in
875 DBIx::Class::ResultSource.
876 If you need to know if an existing row was found or a new one created
878 use "find_or_new" and "in_storage" in DBIx::Class::Row instead. Don't
879 forget to call "insert" in DBIx::Class::Row to save the newly created
880 row to the database!
881 my $cd = $schema->resultset('CD')->find_or_new({
883 cdid => 5,
884 artist => 'Massive Attack',
885 title => 'Mezzanine',
886 year => 2005,
887 });
888 if( !$cd->in_storage ) {
890 # do some stuff
891 $cd->insert;
892 }
893 update_or_create
895 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
896 Return Value: $result
897 $resultset->update_or_create({ col => $val, ... });
899 Like "find_or_create", but if a row is found it is immediately updated
901 via "$found_row->update (\%col_data)".
902 Takes an optional "key" attribute to search on a specific unique
904 constraint. For example:
905 # In your application
907 my $cd = $schema->resultset('CD')->update_or_create(
908 {
909 artist => 'Massive Attack',
910 title => 'Mezzanine',
911 year => 1998,
912 },
913 { key => 'cd_artist_title' }
914 );
915 $cd->cd_to_producer->update_or_create({
917 producer => $producer,
918 name => 'harry',
919 }, {
920 key => 'primary',
921 });
922 Note: Make sure to read the documentation of "find" and understand the
924 significance of the "key" attribute, as its lack may skew your search,
925 and subsequently result in spurious row creation.
926 Note: Take care when using "update_or_create" with a table having
928 columns with default values that you intend to be automatically
929 supplied by the database (e.g. an auto_increment primary key column).
930 In normal usage, the value of such columns should NOT be included at
931 all in the call to "update_or_create", even when set to "undef".
932 See also "find" and "find_or_create". For information on how to declare
934 unique constraints, see "add_unique_constraint" in
935 DBIx::Class::ResultSource.
936 If you need to know if an existing row was updated or a new one created
938 use "update_or_new" and "in_storage" in DBIx::Class::Row instead. Don't
939 forget to call "insert" in DBIx::Class::Row to save the newly created
940 row to the database!
941 update_or_new
943 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
944 Return Value: $result
945 $resultset->update_or_new({ col => $val, ... });
947 Like "find_or_new" but if a row is found it is immediately updated via
949 "$found_row->update (\%col_data)".
950 For example:
952 # In your application
954 my $cd = $schema->resultset('CD')->update_or_new(
955 {
956 artist => 'Massive Attack',
957 title => 'Mezzanine',
958 year => 1998,
959 },
960 { key => 'cd_artist_title' }
961 );
962 if ($cd->in_storage) {
964 # the cd was updated
965 }
966 else {
967 # the cd is not yet in the database, let's insert it
968 $cd->insert;
969 }
970 Note: Make sure to read the documentation of "find" and understand the
972 significance of the "key" attribute, as its lack may skew your search,
973 and subsequently result in spurious new objects.
974 Note: Take care when using "update_or_new" with a table having columns
976 with default values that you intend to be automatically supplied by the
977 database (e.g. an auto_increment primary key column). In normal usage,
978 the value of such columns should NOT be included at all in the call to
979 "update_or_new", even when set to "undef".
980 See also "find", "find_or_create" and "find_or_new".
982 get_cache
984 Arguments: none
985 Return Value: \@result_objs | undef
986 Gets the contents of the cache for the resultset, if the cache is set.
988 The cache is populated either by using the "prefetch" attribute to
990 "search" or by calling "set_cache".
991 set_cache
993 Arguments: \@result_objs
994 Return Value: \@result_objs
995 Sets the contents of the cache for the resultset. Expects an arrayref
997 of objects of the same class as those produced by the resultset. Note
998 that if the cache is set, the resultset will return the cached objects
999 rather than re-querying the database even if the cache attr is not set.
1000 The contents of the cache can also be populated by using the "prefetch"
1002 attribute to "search".
1003 clear_cache
1005 Arguments: none
1006 Return Value: undef
1007 Clears the cache for the resultset.
1009 is_paged
1011 Arguments: none
1012 Return Value: true, if the resultset has been paginated
1013 is_ordered
1015 Arguments: none
1016 Return Value: true, if the resultset has been ordered with "order_by".
1017 related_resultset
1019 Arguments: $rel_name
1020 Return Value: $resultset
1021 Returns a related resultset for the supplied relationship name.
1023 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1025 current_source_alias
1027 Arguments: none
1028 Return Value: $source_alias
1029 Returns the current table alias for the result source this resultset is
1031 built on, that will be used in the SQL query. Usually it is "me".
1032 Currently the source alias that refers to the result set returned by a
1034 "search"/"find" family method depends on how you got to the resultset:
1035 it's "me" by default, but eg. "search_related" aliases it to the
1036 related result source name (and keeps "me" referring to the original
1037 result set). The long term goal is to make DBIx::Class always alias the
1038 current resultset as "me" (and make this method unnecessary).
1039 Thus it's currently necessary to use this method in predefined queries
1041 (see "Predefined searches" in DBIx::Class::Manual::Cookbook) when
1042 referring to the source alias of the current result set:
1043 # in a result set class
1045 sub modified_by {
1046 my ($self, $user) = @_;
1047 my $me = $self->current_source_alias;
1049 return $self->search({
1051 "$me.modified" => $user->id,
1052 });
1053 }
1054 The alias of newly created resultsets can be altered by the alias
1056 attribute.
1057 as_subselect_rs
1059 Arguments: none
1060 Return Value: $resultset
1061 Act as a barrier to SQL symbols. The resultset provided will be made
1063 into a "virtual view" by including it as a subquery within the from
1064 clause. From this point on, any joined tables are inaccessible to
1065 ->search on the resultset (as if it were simply where-filtered without
1066 joins). For example:
1067 my $rs = $schema->resultset('Bar')->search({'x.name' => 'abc'},{ join => 'x' });
1069 # 'x' now pollutes the query namespace
1071 # So the following works as expected
1073 my $ok_rs = $rs->search({'x.other' => 1});
1074 # But this doesn't: instead of finding a 'Bar' related to two x rows (abc and
1076 # def) we look for one row with contradictory terms and join in another table
1077 # (aliased 'x_2') which we never use
1078 my $broken_rs = $rs->search({'x.name' => 'def'});
1079 my $rs2 = $rs->as_subselect_rs;
1081 # doesn't work - 'x' is no longer accessible in $rs2, having been sealed away
1083 my $not_joined_rs = $rs2->search({'x.other' => 1});
1084 # works as expected: finds a 'table' row related to two x rows (abc and def)
1086 my $correctly_joined_rs = $rs2->search({'x.name' => 'def'});
1087 Another example of when one might use this would be to select a subset
1089 of columns in a group by clause:
1090 my $rs = $schema->resultset('Bar')->search(undef, {
1092 group_by => [qw{ id foo_id baz_id }],
1093 })->as_subselect_rs->search(undef, {
1094 columns => [qw{ id foo_id }]
1095 });
1096 In the above example normally columns would have to be equal to the
1098 group by, but because we isolated the group by into a subselect the
1099 above works.
1100 throw_exception
1102 See "throw_exception" in DBIx::Class::Schema for details.
1103
1105 Attributes are used to refine a ResultSet in various ways when
1106 searching for data. They can be passed to any method which takes an
1107 "\%attrs" argument. See "search", "search_rs", "find", "count".
1108 Default attributes can be set on the result class using
1110 "resultset_attributes" in DBIx::Class::ResultSource. (Please read the
1111 CAVEATS on that feature before using it!)
1112 These are in no particular order:
1114 order_by
1116 Value: ( $order_by | \@order_by | \%order_by )
1117 Which column(s) to order the results by.
1119 [The full list of suitable values is documented in "ORDER BY CLAUSES"
1121 in SQL::Abstract::Classic; the following is a summary of common
1122 options.]
1123 If a single column name, or an arrayref of names is supplied, the
1125 argument is passed through directly to SQL. The hashref syntax allows
1126 for connection-agnostic specification of ordering direction:
1127 For descending order:
1129 order_by => { -desc => [qw/col1 col2 col3/] }
1131 For explicit ascending order:
1133 order_by => { -asc => 'col' }
1135 The old scalarref syntax (i.e. order_by => \'year DESC') is still
1137 supported, although you are strongly encouraged to use the hashref
1138 syntax as outlined above.
1139 columns
1141 Value: \@columns | \%columns | $column
1142 Shortcut to request a particular set of columns to be retrieved. Each
1144 column spec may be a string (a table column name), or a hash (in which
1145 case the key is the "as" value, and the value is used as the "select"
1146 expression). Adds the "current_source_alias" onto the start of any
1147 column without a "." in it and sets "select" from that, then auto-
1148 populates "as" from "select" as normal. (You may also use the "cols"
1149 attribute, as in earlier versions of DBIC, but this is deprecated)
1150 Essentially "columns" does the same as "select" and "as".
1152 columns => [ 'some_column', { dbic_slot => 'another_column' } ]
1154 is the same as
1156 select => [qw(some_column another_column)],
1158 as => [qw(some_column dbic_slot)]
1159 If you want to individually retrieve related columns (in essence
1161 perform manual "prefetch") you have to make sure to specify the correct
1162 inflation slot chain such that it matches existing relationships:
1163 my $rs = $schema->resultset('Artist')->search({}, {
1165 # required to tell DBIC to collapse has_many relationships
1166 collapse => 1,
1167 join => { cds => 'tracks' },
1168 '+columns' => {
1169 'cds.cdid' => 'cds.cdid',
1170 'cds.tracks.title' => 'tracks.title',
1171 },
1172 });
1173 Like elsewhere, literal SQL or literal values can be included by using
1175 a scalar reference or a literal bind value, and these values will be
1176 available in the result with "get_column" (see also
1177 SQL::Abstract::Classic/Literal SQL and value type operators>):
1178 # equivalent SQL: SELECT 1, 'a string', IF(my_column,?,?) ...
1180 # bind values: $true_value, $false_value
1181 columns => [
1182 {
1183 foo => \1,
1184 bar => \q{'a string'},
1185 baz => \[ 'IF(my_column,?,?)', $true_value, $false_value ],
1186 }
1187 ]
1188 +columns
1190 NOTE: You MUST explicitly quote '+columns' when using this attribute.
1191 Not doing so causes Perl to incorrectly interpret "+columns" as a
1192 bareword with a unary plus operator before it, which is the same as
1193 simply "columns".
1194 Value: \@extra_columns
1196 Indicates additional columns to be selected from storage. Works the
1198 same as "columns" but adds columns to the current selection. (You may
1199 also use the "include_columns" attribute, as in earlier versions of
1200 DBIC, but this is deprecated)
1201 $schema->resultset('CD')->search(undef, {
1203 '+columns' => ['artist.name'],
1204 join => ['artist']
1205 });
1206 would return all CDs and include a 'name' column to the information
1208 passed to object inflation. Note that the 'artist' is the name of the
1209 column (or relationship) accessor, and 'name' is the name of the column
1210 accessor in the related table.
1211 select
1213 Value: \@select_columns
1214 Indicates which columns should be selected from the storage. You can
1216 use column names, or in the case of RDBMS back ends, function or stored
1217 procedure names:
1218 $rs = $schema->resultset('Employee')->search(undef, {
1220 select => [
1221 'name',
1222 { count => 'employeeid' },
1223 { max => { length => 'name' }, -as => 'longest_name' }
1224 ]
1225 });
1226 # Equivalent SQL
1228 SELECT name, COUNT( employeeid ), MAX( LENGTH( name ) ) AS longest_name FROM employee
1229 NOTE: You will almost always need a corresponding "as" attribute when
1231 you use "select", to instruct DBIx::Class how to store the result of
1232 the column.
1233 Also note that the "as" attribute has nothing to do with the SQL-side
1235 "AS" identifier aliasing. You can alias a function (so you can use it
1236 e.g. in an "ORDER BY" clause), however this is done via the "-as"
1237 select function attribute supplied as shown in the example above.
1238 +select
1240 NOTE: You MUST explicitly quote '+select' when using this attribute.
1241 Not doing so causes Perl to incorrectly interpret "+select" as a
1242 bareword with a unary plus operator before it, which is the same as
1243 simply "select".
1244 Value: \@extra_select_columns
1246 Indicates additional columns to be selected from storage. Works the
1248 same as "select" but adds columns to the current selection, instead of
1249 specifying a new explicit list.
1250 as
1252 Value: \@inflation_names
1253 Indicates DBIC-side names for object inflation. That is "as" indicates
1255 the slot name in which the column value will be stored within the Row
1256 object. The value will then be accessible via this identifier by the
1257 "get_column" method (or via the object accessor if one with the same
1258 name already exists) as shown below.
1259 The "as" attribute has nothing to do with the SQL-side identifier
1261 aliasing "AS". See "select" for details.
1262 $rs = $schema->resultset('Employee')->search(undef, {
1264 select => [
1265 'name',
1266 { count => 'employeeid' },
1267 { max => { length => 'name' }, -as => 'longest_name' }
1268 ],
1269 as => [qw/
1270 name
1271 employee_count
1272 max_name_length
1273 /],
1274 });
1275 If the object against which the search is performed already has an
1277 accessor matching a column name specified in "as", the value can be
1278 retrieved using the accessor as normal:
1279 my $name = $employee->name();
1281 If on the other hand an accessor does not exist in the object, you need
1283 to use "get_column" instead:
1284 my $employee_count = $employee->get_column('employee_count');
1286 You can create your own accessors if required - see
1288 DBIx::Class::Manual::Cookbook for details.
1289 +as
1291 NOTE: You MUST explicitly quote '+as' when using this attribute. Not
1292 doing so causes Perl to incorrectly interpret "+as" as a bareword with
1293 a unary plus operator before it, which is the same as simply "as".
1294 Value: \@extra_inflation_names
1296 Indicates additional inflation names for selectors added via "+select".
1298 See "as".
1299 join
1301 Value: ($rel_name | \@rel_names | \%rel_names)
1302 Contains a list of relationships that should be joined for this query.
1304 For example:
1305 # Get CDs by Nine Inch Nails
1307 my $rs = $schema->resultset('CD')->search(
1308 { 'artist.name' => 'Nine Inch Nails' },
1309 { join => 'artist' }
1310 );
1311 Can also contain a hash reference to refer to the other relation's
1313 relations. For example:
1314 package MyApp::Schema::Track;
1316 use base qw/DBIx::Class/;
1317 __PACKAGE__->table('track');
1318 __PACKAGE__->add_columns(qw/trackid cd position title/);
1319 __PACKAGE__->set_primary_key('trackid');
1320 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1321 1;
1322 # In your application
1324 my $rs = $schema->resultset('Artist')->search(
1325 { 'track.title' => 'Teardrop' },
1326 {
1327 join => { cd => 'track' },
1328 order_by => 'artist.name',
1329 }
1330 );
1331 You need to use the relationship (not the table) name in conditions,
1333 because they are aliased as such. The current table is aliased as "me",
1334 so you need to use me.column_name in order to avoid ambiguity. For
1335 example:
1336 # Get CDs from 1984 with a 'Foo' track
1338 my $rs = $schema->resultset('CD')->search(
1339 {
1340 'me.year' => 1984,
1341 'tracks.name' => 'Foo'
1342 },
1343 { join => 'tracks' }
1344 );
1345 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1347 similarly for a third time). For e.g.
1348 my $rs = $schema->resultset('Artist')->search({
1350 'cds.title' => 'Down to Earth',
1351 'cds_2.title' => 'Popular',
1352 }, {
1353 join => [ qw/cds cds/ ],
1354 });
1355 will return a set of all artists that have both a cd with title 'Down
1357 to Earth' and a cd with title 'Popular'.
1358 If you want to fetch related objects from other tables as well, see
1360 "prefetch" below.
1361 NOTE: An internal join-chain pruner will discard certain joins while
1363 constructing the actual SQL query, as long as the joins in question do not
1364 affect the retrieved result. This for example includes 1:1 left joins
1365 that are not part of the restriction specification (WHERE/HAVING) nor are
1366 a part of the query selection.
1367 For more help on using joins with search, see
1369 DBIx::Class::Manual::Joining.
1370 collapse
1372 Value: (0 | 1)
1373 When set to a true value, indicates that any rows fetched from joined
1375 has_many relationships are to be aggregated into the corresponding
1376 "parent" object. For example, the resultset:
1377 my $rs = $schema->resultset('CD')->search({}, {
1379 '+columns' => [ qw/ tracks.title tracks.position / ],
1380 join => 'tracks',
1381 collapse => 1,
1382 });
1383 While executing the following query:
1385 SELECT me.*, tracks.title, tracks.position
1387 FROM cd me
1388 LEFT JOIN track tracks
1389 ON tracks.cdid = me.cdid
1390 Will return only as many objects as there are rows in the CD source,
1392 even though the result of the query may span many rows. Each of these
1393 CD objects will in turn have multiple "Track" objects hidden behind the
1394 has_many generated accessor "tracks". Without "collapse => 1", the
1395 return values of this resultset would be as many CD objects as there
1396 are tracks (a "Cartesian product"), with each CD object containing
1397 exactly one of all fetched Track data.
1398 When a collapse is requested on a non-ordered resultset, an order by
1400 some unique part of the main source (the left-most table) is inserted
1401 automatically. This is done so that the resultset is allowed to be
1402 "lazy" - calling $rs->next will fetch only as many rows as it needs to
1403 build the next object with all of its related data.
1404 If an "order_by" is already declared, and orders the resultset in a way
1406 that makes collapsing as described above impossible (e.g. "ORDER BY
1407 has_many_rel.column" or "ORDER BY RANDOM()"), DBIC will automatically
1408 switch to "eager" mode and slurp the entire resultset before
1409 constructing the first object returned by "next".
1410 Setting this attribute on a resultset that does not join any has_many
1412 relations is a no-op.
1413 For a more in-depth discussion, see "PREFETCHING".
1415 prefetch
1417 Value: ($rel_name | \@rel_names | \%rel_names)
1418 This attribute is a shorthand for specifying a "join" spec, adding all
1420 columns from the joined related sources as "+columns" and setting
1421 "collapse" to a true value. It can be thought of as a rough superset of
1422 the "join" attribute.
1423 For example, the following two queries are equivalent:
1425 my $rs = $schema->resultset('Artist')->search({}, {
1427 prefetch => { cds => ['genre', 'tracks' ] },
1428 });
1429 and
1431 my $rs = $schema->resultset('Artist')->search({}, {
1433 join => { cds => ['genre', 'tracks' ] },
1434 collapse => 1,
1435 '+columns' => [
1436 (map
1437 { +{ "cds.$_" => "cds.$_" } }
1438 $schema->source('Artist')->related_source('cds')->columns
1439 ),
1440 (map
1441 { +{ "cds.genre.$_" => "genre.$_" } }
1442 $schema->source('Artist')->related_source('cds')->related_source('genre')->columns
1443 ),
1444 (map
1445 { +{ "cds.tracks.$_" => "tracks.$_" } }
1446 $schema->source('Artist')->related_source('cds')->related_source('tracks')->columns
1447 ),
1448 ],
1449 });
1450 Both producing the following SQL:
1452 SELECT me.artistid, me.name, me.rank, me.charfield,
1454 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track,
1455 genre.genreid, genre.name,
1456 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
1457 FROM artist me
1458 LEFT JOIN cd cds
1459 ON cds.artist = me.artistid
1460 LEFT JOIN genre genre
1461 ON genre.genreid = cds.genreid
1462 LEFT JOIN track tracks
1463 ON tracks.cd = cds.cdid
1464 ORDER BY me.artistid
1465 While "prefetch" implies a "join", it is ok to mix the two together, as
1467 the arguments are properly merged and generally do the right thing. For
1468 example, you may want to do the following:
1469 my $artists_and_cds_without_genre = $schema->resultset('Artist')->search(
1471 { 'genre.genreid' => undef },
1472 {
1473 join => { cds => 'genre' },
1474 prefetch => 'cds',
1475 }
1476 );
1477 Which generates the following SQL:
1479 SELECT me.artistid, me.name, me.rank, me.charfield,
1481 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
1482 FROM artist me
1483 LEFT JOIN cd cds
1484 ON cds.artist = me.artistid
1485 LEFT JOIN genre genre
1486 ON genre.genreid = cds.genreid
1487 WHERE genre.genreid IS NULL
1488 ORDER BY me.artistid
1489 For a more in-depth discussion, see "PREFETCHING".
1491 alias
1493 Value: $source_alias
1494 Sets the source alias for the query. Normally, this defaults to "me",
1496 but nested search queries (sub-SELECTs) might need specific aliases set
1497 to reference inner queries. For example:
1498 my $q = $rs
1500 ->related_resultset('CDs')
1501 ->related_resultset('Tracks')
1502 ->search({
1503 'track.id' => { -ident => 'none_search.id' },
1504 })
1505 ->as_query;
1506 my $ids = $self->search({
1508 -not_exists => $q,
1509 }, {
1510 alias => 'none_search',
1511 group_by => 'none_search.id',
1512 })->get_column('id')->as_query;
1513 $self->search({ id => { -in => $ids } })
1515 This attribute is directly tied to "current_source_alias".
1517 page
1519 Value: $page
1520 Makes the resultset paged and specifies the page to retrieve.
1522 Effectively identical to creating a non-pages resultset and then
1523 calling ->page($page) on it.
1524 If "rows" attribute is not specified it defaults to 10 rows per page.
1526 When you have a paged resultset, "count" will only return the number of
1528 rows in the page. To get the total, use the "pager" and call
1529 "total_entries" on it.
1530 rows
1532 Value: $rows
1533 Specifies the maximum number of rows for direct retrieval or the number
1535 of rows per page if the page attribute or method is used.
1536 offset
1538 Value: $offset
1539 Specifies the (zero-based) row number for the first row to be
1541 returned, or the of the first row of the first page if paging is used.
1542 software_limit
1544 Value: (0 | 1)
1545 When combined with "rows" and/or "offset" the generated SQL will not
1547 include any limit dialect stanzas. Instead the entire result will be
1548 selected as if no limits were specified, and DBIC will perform the
1549 limit locally, by artificially advancing and finishing the resulting
1550 "cursor".
1551 This is the recommended way of performing resultset limiting when no
1553 sane RDBMS implementation is available (e.g. Sybase ASE using the
1554 Generic Sub Query hack)
1555 group_by
1557 Value: \@columns
1558 A arrayref of columns to group by. Can include columns of joined
1560 tables.
1561 group_by => [qw/ column1 column2 ... /]
1563 having
1565 Value: $condition
1566 The HAVING operator specifies a secondary condition applied to the set
1568 after the grouping calculations have been done. In other words it is a
1569 constraint just like "where" (and accepting the same
1570 SQL::Abstract::Classic syntax) applied to the data as it exists after
1571 GROUP BY has taken place. Specifying "having" without "group_by" is a
1572 logical mistake, and a fatal error on most RDBMS engines.
1573 E.g.
1575 having => { 'count_employee' => { '>=', 100 } }
1577 or with an in-place function in which case literal SQL is required:
1579 having => \[ 'count(employee) >= ?', 100 ]
1581 distinct
1583 Value: (0 | 1)
1584 Set to 1 to automatically generate a "group_by" clause based on the
1586 selection (including intelligent handling of "order_by" contents). Note
1587 that the group criteria calculation takes place over the final
1588 selection. This includes any "+columns", "+select" or "order_by"
1589 additions in subsequent "search" calls, and standalone columns selected
1590 via DBIx::Class::ResultSetColumn ("get_column"). A notable exception
1591 are the extra selections specified via "prefetch" - such selections are
1592 explicitly excluded from group criteria calculations.
1593 If the final ResultSet also explicitly defines a "group_by" attribute,
1595 this setting is ignored and an appropriate warning is issued.
1596 where
1598 Adds to the WHERE clause.
1599 # only return rows WHERE deleted IS NULL for all searches
1601 __PACKAGE__->resultset_attributes({ where => { deleted => undef } });
1602 Can be overridden by passing "{ where => undef }" as an attribute
1604 to a resultset.
1605 For more complicated where clauses see "WHERE CLAUSES" in
1607 SQL::Abstract::Classic.
1608 cache
1610 Set to 1 to cache search results. This prevents extra SQL queries if
1611 you revisit rows in your ResultSet:
1612 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1614 while( my $artist = $resultset->next ) {
1616 ... do stuff ...
1617 }
1618 $rs->first; # without cache, this would issue a query
1620 By default, searches are not cached.
1622 For more examples of using these attributes, see
1624 DBIx::Class::Manual::Cookbook.
1625 for
1627 Value: ( 'update' | 'shared' | \$scalar )
1628 Set to 'update' for a SELECT ... FOR UPDATE or 'shared' for a SELECT
1630 ... FOR SHARED. If \$scalar is passed, this is taken directly and
1631 embedded in the query.
1632
1634 DBIx::Class supports arbitrary related data prefetching from multiple
1635 related sources. Any combination of relationship types and column sets
1636 are supported. If collapsing is requested, there is an additional
1637 requirement of selecting enough data to make every individual object
1638 uniquely identifiable.
1639 Here are some more involved examples, based on the following
1641 relationship map:
1642 # Assuming:
1644 My::Schema::CD->belongs_to( artist => 'My::Schema::Artist' );
1645 My::Schema::CD->might_have( liner_note => 'My::Schema::LinerNotes' );
1646 My::Schema::CD->has_many( tracks => 'My::Schema::Track' );
1647 My::Schema::Artist->belongs_to( record_label => 'My::Schema::RecordLabel' );
1649 My::Schema::Track->has_many( guests => 'My::Schema::Guest' );
1651 my $rs = $schema->resultset('Tag')->search(
1655 undef,
1656 {
1657 prefetch => {
1658 cd => 'artist'
1659 }
1660 }
1661 );
1662 The initial search results in SQL like the following:
1664 SELECT tag.*, cd.*, artist.* FROM tag
1666 JOIN cd ON tag.cd = cd.cdid
1667 JOIN artist ON cd.artist = artist.artistid
1668 DBIx::Class has no need to go back to the database when we access the
1670 "cd" or "artist" relationships, which saves us two SQL statements in
1671 this case.
1672 Simple prefetches will be joined automatically, so there is no need for
1674 a "join" attribute in the above search.
1675 The "prefetch" attribute can be used with any of the relationship types
1677 and multiple prefetches can be specified together. Below is a more
1678 complex example that prefetches a CD's artist, its liner notes (if
1679 present), the cover image, the tracks on that CD, and the guests on
1680 those tracks.
1681 my $rs = $schema->resultset('CD')->search(
1683 undef,
1684 {
1685 prefetch => [
1686 { artist => 'record_label'}, # belongs_to => belongs_to
1687 'liner_note', # might_have
1688 'cover_image', # has_one
1689 { tracks => 'guests' }, # has_many => has_many
1690 ]
1691 }
1692 );
1693 This will produce SQL like the following:
1695 SELECT cd.*, artist.*, record_label.*, liner_note.*, cover_image.*,
1697 tracks.*, guests.*
1698 FROM cd me
1699 JOIN artist artist
1700 ON artist.artistid = me.artistid
1701 JOIN record_label record_label
1702 ON record_label.labelid = artist.labelid
1703 LEFT JOIN track tracks
1704 ON tracks.cdid = me.cdid
1705 LEFT JOIN guest guests
1706 ON guests.trackid = track.trackid
1707 LEFT JOIN liner_notes liner_note
1708 ON liner_note.cdid = me.cdid
1709 JOIN cd_artwork cover_image
1710 ON cover_image.cdid = me.cdid
1711 ORDER BY tracks.cd
1712 Now the "artist", "record_label", "liner_note", "cover_image",
1714 "tracks", and "guests" of the CD will all be available through the
1715 relationship accessors without the need for additional queries to the
1716 database.
1717 CAVEATS
1719 Prefetch does a lot of deep magic. As such, it may not behave exactly
1721 as you might expect.
1722 • Prefetch uses the "cache" to populate the prefetched relationships.
1724 This may or may not be what you want.
1725 • If you specify a condition on a prefetched relationship, ONLY those
1727 rows that match the prefetched condition will be fetched into that
1728 relationship. This means that adding prefetch to a search() may
1729 alter what is returned by traversing a relationship. So, if you
1730 have "Artist->has_many(CDs)" and you do
1731 my $artist_rs = $schema->resultset('Artist')->search({
1733 'cds.year' => 2008,
1734 }, {
1735 join => 'cds',
1736 });
1737 my $count = $artist_rs->first->cds->count;
1739 my $artist_rs_prefetch = $artist_rs->search( {}, { prefetch => 'cds' } );
1741 my $prefetch_count = $artist_rs_prefetch->first->cds->count;
1743 cmp_ok( $count, '==', $prefetch_count, "Counts should be the same" );
1745 That cmp_ok() may or may not pass depending on the datasets
1747 involved. In other words the "WHERE" condition would apply to the
1748 entire dataset, just like it would in regular SQL. If you want to
1749 add a condition only to the "right side" of a "LEFT JOIN" -
1750 consider declaring and using a relationship with a custom condition
1751
1753 Because DBIC may need more information to bind values than just the
1754 column name and value itself, it uses a special format for both passing
1755 and receiving bind values. Each bind value should be composed of an
1756 arrayref of "[ \%args => $val ]". The format of "\%args" is currently:
1757 dbd_attrs
1759 If present (in any form), this is what is being passed directly to
1760 bind_param. Note that different DBD's expect different bind args.
1761 (e.g. DBD::SQLite takes a single numerical type, while DBD::Pg
1762 takes a hashref if bind options.)
1763 If this is specified, all other bind options described below are
1765 ignored.
1766 sqlt_datatype
1768 If present, this is used to infer the actual bind attribute by
1769 passing to "$resolved_storage->bind_attribute_by_data_type()".
1770 Defaults to the "data_type" from the add_columns column info.
1771 Note that the data type is somewhat freeform (hence the sqlt_
1773 prefix); currently drivers are expected to "Do the Right Thing"
1774 when given a common datatype name. (Not ideal, but that's what we
1775 got at this point.)
1776 sqlt_size
1778 Currently used to correctly allocate buffers for
1779 bind_param_inout(). Defaults to "size" from the add_columns column
1780 info, or to a sensible value based on the "data_type".
1781 dbic_colname
1783 Used to fill in missing sqlt_datatype and sqlt_size attributes (if
1784 they are explicitly specified they are never overridden). Also
1785 used by some weird DBDs, where the column name should be available
1786 at bind_param time (e.g. Oracle).
1787 For backwards compatibility and convenience, the following shortcuts
1789 are supported:
1790 [ $name => $val ] === [ { dbic_colname => $name }, $val ]
1792 [ \$dt => $val ] === [ { sqlt_datatype => $dt }, $val ]
1793 [ undef, $val ] === [ {}, $val ]
1794 $val === [ {}, $val ]
1795
1797 Check the list of additional DBIC resources.
1798
1800 This module is free software copyright by the DBIx::Class (DBIC)
1801 authors. You can redistribute it and/or modify it under the same terms
1802 as the DBIx::Class library.
1803perl v5.32.1 2021-01-27 DBIx::Class::ResultSet(3)