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 and its extension DBIx::Class::SQLMaker.
274 For more help on using joins with search, see
276 DBIx::Class::Manual::Joining.
277 CAVEAT
279 Note that "search" does not process/deflate any of the values passed in
281 the SQL::Abstract-compatible search condition structure. This is unlike
282 other condition-bound methods "new_result", "create" and "find". The
283 user must ensure manually that any value passed to this method will
284 stringify to something the RDBMS knows how to deal with. A notable
285 example is the handling of DateTime objects, for more info see:
286 "Formatting DateTime objects in queries" in
287 DBIx::Class::Manual::Cookbook.
288 search_rs
290 Arguments: $cond, \%attrs?
291 Return Value: $resultset
292 This method does the same exact thing as search() except it will always
294 return a resultset, even in list context.
295 search_literal
297 CAVEAT: "search_literal" is provided for Class::DBI compatibility and
298 should only be used in that context. "search_literal" is a convenience
299 method. It is equivalent to calling "$schema->search(\[])", but if you
300 want to ensure columns are bound correctly, use "search".
301 See "SEARCHING" in DBIx::Class::Manual::Cookbook and "Searching" in
303 DBIx::Class::Manual::FAQ for searching techniques that do not require
304 "search_literal".
305 Arguments: $sql_fragment, @standalone_bind_values
307 Return Value: $resultset (scalar context) | @result_objs (list context)
308 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
310 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
311 Pass a literal chunk of SQL to be added to the conditional part of the
313 resultset query.
314 Example of how to use "search" instead of "search_literal"
316 my @cds = $cd_rs->search_literal('cdid = ? AND (artist = ? OR artist = ?)', (2, 1, 2));
318 my @cds = $cd_rs->search(\[ 'cdid = ? AND (artist = ? OR artist = ?)', [ 'cdid', 2 ], [ 'artist', 1 ], [ 'artist', 2 ] ]);
319 find
321 Arguments: \%columns_values | @pk_values, { key => $unique_constraint,
322 %attrs }?
323 Return Value: $result | undef
324 Finds and returns a single row based on supplied criteria. Takes either
326 a hashref with the same format as "create" (including inference of
327 foreign keys from related objects), or a list of primary key values in
328 the same order as the primary columns declaration on the
329 "result_source".
330 In either case an attempt is made to combine conditions already
332 existing on the resultset with the condition passed to this method.
333 To aid with preparing the correct query for the storage you may supply
335 the "key" attribute, which is the name of a unique constraint (the
336 unique constraint corresponding to the primary columns is always named
337 "primary"). If the "key" attribute has been supplied, and DBIC is
338 unable to construct a query that satisfies the named unique constraint
339 fully ( non-NULL values for each column member of the constraint) an
340 exception is thrown.
341 If no "key" is specified, the search is carried over all unique
343 constraints which are fully defined by the available condition.
344 If no such constraint is found, "find" currently defaults to a simple
346 "search->(\%column_values)" which may or may not do what you expect.
347 Note that this fallback behavior may be deprecated in further versions.
348 If you need to search with arbitrary conditions - use "search". If the
349 query resulting from this fallback produces more than one row, a
350 warning to the effect is issued, though only the first row is
351 constructed and returned as $result_object.
352 In addition to "key", "find" recognizes and applies standard resultset
354 attributes in the same way as "search" does.
355 Note that if you have extra concerns about the correctness of the
357 resulting query you need to specify the "key" attribute and supply the
358 entire condition as an argument to find (since it is not always
359 possible to perform the combination of the resultset condition with the
360 supplied one, especially if the resultset condition contains literal
361 sql).
362 For example, to find a row by its primary key:
364 my $cd = $schema->resultset('CD')->find(5);
366 You can also find a row by a specific unique constraint:
368 my $cd = $schema->resultset('CD')->find(
370 {
371 artist => 'Massive Attack',
372 title => 'Mezzanine',
373 },
374 { key => 'cd_artist_title' }
375 );
376 See also "find_or_create" and "update_or_create".
378 search_related
380 Arguments: $rel_name, $cond?, \%attrs?
381 Return Value: $resultset (scalar context) | @result_objs (list context)
382 $new_rs = $cd_rs->search_related('artist', {
384 name => 'Emo-R-Us',
385 });
386 Searches the specified relationship, optionally specifying a condition
388 and attributes for matching records. See "ATTRIBUTES" for more
389 information.
390 In list context, "->all()" is called implicitly on the resultset, thus
392 returning a list of result objects instead. To avoid that, use
393 "search_related_rs".
394 See also "search_related_rs".
396 search_related_rs
398 This method works exactly the same as search_related, except that it
399 guarantees a resultset, even in list context.
400 cursor
402 Arguments: none
403 Return Value: $cursor
404 Returns a storage-driven cursor to the given resultset. See
406 DBIx::Class::Cursor for more information.
407 single
409 Arguments: $cond?
410 Return Value: $result | undef
411 my $cd = $schema->resultset('CD')->single({ year => 2001 });
413 Inflates the first result without creating a cursor if the resultset
415 has any records in it; if not returns "undef". Used by "find" as a lean
416 version of "search".
417 While this method can take an optional search condition (just like
419 "search") being a fast-code-path it does not recognize search
420 attributes. If you need to add extra joins or similar, call "search"
421 and then chain-call "single" on the DBIx::Class::ResultSet returned.
422 Note
424 As of 0.08100, this method enforces the assumption that the
425 preceding query returns only one row. If more than one row is
426 returned, you will receive a warning:
427 Query returned more than one row
429 In this case, you should be using "next" or "find" instead, or if
431 you really know what you are doing, use the "rows" attribute to
432 explicitly limit the size of the resultset.
433 This method will also throw an exception if it is called on a
435 resultset prefetching has_many, as such a prefetch implies fetching
436 multiple rows from the database in order to assemble the resulting
437 object.
438 get_column
440 Arguments: $cond?
441 Return Value: $resultsetcolumn
442 my $max_length = $rs->get_column('length')->max;
444 Returns a DBIx::Class::ResultSetColumn instance for a column of the
446 ResultSet.
447 search_like
449 Arguments: $cond, \%attrs?
450 Return Value: $resultset (scalar context) | @result_objs (list context)
451 # WHERE title LIKE '%blue%'
453 $cd_rs = $rs->search_like({ title => '%blue%'});
454 Performs a search, but uses "LIKE" instead of "=" as the condition.
456 Note that this is simply a convenience method retained for ex
457 Class::DBI users. You most likely want to use "search" with specific
458 operators.
459 For more information, see DBIx::Class::Manual::Cookbook.
461 This method is deprecated and will be removed in 0.09. Use search()
463 instead. An example conversion is:
464 ->search_like({ foo => 'bar' });
466 # Becomes
468 ->search({ foo => { like => 'bar' } });
470 slice
472 Arguments: $first, $last
473 Return Value: $resultset (scalar context) | @result_objs (list context)
474 Returns a resultset or object list representing a subset of elements
476 from the resultset slice is called on. Indexes are from 0, i.e., to get
477 the first three records, call:
478 my ($one, $two, $three) = $rs->slice(0, 2);
480 next
482 Arguments: none
483 Return Value: $result | undef
484 Returns the next element in the resultset ("undef" is there is none).
486 Can be used to efficiently iterate over records in the resultset:
488 my $rs = $schema->resultset('CD')->search;
490 while (my $cd = $rs->next) {
491 print $cd->title;
492 }
493 Note that you need to store the resultset object, and call "next" on
495 it. Calling "resultset('Table')->next" repeatedly will always return
496 the first record from the resultset.
497 result_source
499 Arguments: $result_source?
500 Return Value: $result_source
501 An accessor for the primary ResultSource object from which this
503 ResultSet is derived.
504 result_class
506 Arguments: $result_class?
507 Return Value: $result_class
508 An accessor for the class to use when creating result objects. Defaults
510 to "result_source->result_class" - which in most cases is the name of
511 the "table" class.
512 Note that changing the result_class will also remove any components
514 that were originally loaded in the source class via load_components.
515 Any overloaded methods in the original source class will not run.
516 count
518 Arguments: $cond, \%attrs?
519 Return Value: $count
520 Performs an SQL "COUNT" with the same query as the resultset was built
522 with to find the number of elements. Passing arguments is equivalent to
523 "$rs->search ($cond, \%attrs)->count"
524 count_rs
526 Arguments: $cond, \%attrs?
527 Return Value: $count_rs
528 Same as "count" but returns a DBIx::Class::ResultSetColumn object.
530 This can be very handy for subqueries:
531 ->search( { amount => $some_rs->count_rs->as_query } )
533 As with regular resultsets the SQL query will be executed only after
535 the resultset is accessed via "next" or "all". That would return the
536 same single value obtainable via "count".
537 count_literal
539 CAVEAT: "count_literal" is provided for Class::DBI compatibility and
540 should only be used in that context. See "search_literal" for further
541 info.
542 Arguments: $sql_fragment, @standalone_bind_values
544 Return Value: $count
545 Counts the results in a literal query. Equivalent to calling
547 "search_literal" with the passed arguments, then "count".
548 all
550 Arguments: none
551 Return Value: @result_objs
552 Returns all elements in the resultset.
554 reset
556 Arguments: none
557 Return Value: $self
558 Resets the resultset's cursor, so you can iterate through the elements
560 again. Implicitly resets the storage cursor, so a subsequent "next"
561 will trigger another query.
562 first
564 Arguments: none
565 Return Value: $result | undef
566 Resets the resultset (causing a fresh query to storage) and returns an
568 object for the first result (or "undef" if the resultset is empty).
569 update
571 Arguments: \%values
572 Return Value: $underlying_storage_rv
573 Sets the specified columns in the resultset to the supplied values in a
575 single query. Note that this will not run any
576 accessor/set_column/update triggers, nor will it update any result
577 object instances derived from this resultset (this includes the
578 contents of the resultset cache if any). See "update_all" if you need
579 to execute any on-update triggers or cascades defined either by you or
580 a result component.
581 The return value is a pass through of what the underlying storage
583 backend returned, and may vary. See "execute" in DBI for the most
584 common case.
585 CAVEAT
587 Note that "update" does not process/deflate any of the values passed
589 in. This is unlike the corresponding "update" in DBIx::Class::Row. The
590 user must ensure manually that any value passed to this method will
591 stringify to something the RDBMS knows how to deal with. A notable
592 example is the handling of DateTime objects, for more info see:
593 "Formatting DateTime objects in queries" in
594 DBIx::Class::Manual::Cookbook.
595 update_all
597 Arguments: \%values
598 Return Value: 1
599 Fetches all objects and updates them one at a time via "update" in
601 DBIx::Class::Row. Note that "update_all" will run DBIC defined
602 triggers, while "update" will not.
603 delete
605 Arguments: none
606 Return Value: $underlying_storage_rv
607 Deletes the rows matching this resultset in a single query. Note that
609 this will not run any delete triggers, nor will it alter the in_storage
610 status of any result object instances derived from this resultset (this
611 includes the contents of the resultset cache if any). See "delete_all"
612 if you need to execute any on-delete triggers or cascades defined
613 either by you or a result component.
614 The return value is a pass through of what the underlying storage
616 backend returned, and may vary. See "execute" in DBI for the most
617 common case.
618 delete_all
620 Arguments: none
621 Return Value: 1
622 Fetches all objects and deletes them one at a time via "delete" in
624 DBIx::Class::Row. Note that "delete_all" will run DBIC defined
625 triggers, while "delete" will not.
626 populate
628 Arguments: [ \@column_list, \@row_values+ ] | [ \%col_data+ ]
629 Return Value: \@result_objects (scalar context) | @result_objects (list
630 context)
631 Accepts either an arrayref of hashrefs or alternatively an arrayref of
633 arrayrefs.
634 NOTE
636 The context of this method call has an important effect on what is
637 submitted to storage. In void context data is fed directly to
638 fastpath insertion routines provided by the underlying storage
639 (most often "execute_for_fetch" in DBI), bypassing the new and
640 insert calls on the Result class, including any augmentation of
641 these methods provided by components. For example if you are using
642 something like DBIx::Class::UUIDColumns to create primary keys for
643 you, you will find that your PKs are empty. In this case you will
644 have to explicitly force scalar or list context in order to create
645 those values.
646 In non-void (scalar or list) context, this method is simply a wrapper
648 for "create". Depending on list or scalar context either a list of
649 Result objects or an arrayref containing these objects is returned.
650 When supplying data in "arrayref of arrayrefs" invocation style, the
652 first element should be a list of column names and each subsequent
653 element should be a data value in the earlier specified column order.
654 For example:
655 $schema->resultset("Artist")->populate([
657 [ qw( artistid name ) ],
658 [ 100, 'A Formally Unknown Singer' ],
659 [ 101, 'A singer that jumped the shark two albums ago' ],
660 [ 102, 'An actually cool singer' ],
661 ]);
662 For the arrayref of hashrefs style each hashref should be a structure
664 suitable for passing to "create". Multi-create is also permitted with
665 this syntax.
666 $schema->resultset("Artist")->populate([
668 { artistid => 4, name => 'Manufactured Crap', cds => [
669 { title => 'My First CD', year => 2006 },
670 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
671 ],
672 },
673 { artistid => 5, name => 'Angsty-Whiny Girl', cds => [
674 { title => 'My parents sold me to a record company', year => 2005 },
675 { title => 'Why Am I So Ugly?', year => 2006 },
676 { title => 'I Got Surgery and am now Popular', year => 2007 }
677 ],
678 },
679 ]);
680 If you attempt a void-context multi-create as in the example above
682 (each Artist also has the related list of CDs), and do not supply the
683 necessary autoinc foreign key information, this method will proxy to
684 the less efficient "create", and then throw the Result objects away. In
685 this case there are obviously no benefits to using this method over
686 "create".
687 pager
689 Arguments: none
690 Return Value: $pager
691 Returns a Data::Page object for the current resultset. Only makes sense
693 for queries with a "page" attribute.
694 To get the full count of entries for a paged resultset, call
696 "total_entries" on the Data::Page object.
697 page
699 Arguments: $page_number
700 Return Value: $resultset
701 Returns a resultset for the $page_number page of the resultset on which
703 page is called, where each page contains a number of rows equal to the
704 'rows' attribute set on the resultset (10 by default).
705 new_result
707 Arguments: \%col_data
708 Return Value: $result
709 Creates a new result object in the resultset's result class and returns
711 it. The row is not inserted into the database at this point, call
712 "insert" in DBIx::Class::Row to do that. Calling "in_storage" in
713 DBIx::Class::Row will tell you whether the result object has been
714 inserted or not.
715 Passes the hashref of input on to "new" in DBIx::Class::Row.
717 as_query
719 Arguments: none
720 Return Value: \[ $sql, @bind_values ]
721 Returns the SQL query and bind vars associated with the invocant.
723 This is generally used as the RHS for a subquery.
725 find_or_new
727 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
728 Return Value: $result
729 my $artist = $schema->resultset('Artist')->find_or_new(
731 { artist => 'fred' }, { key => 'artists' });
732 $cd->cd_to_producer->find_or_new({ producer => $producer },
734 { key => 'primary' });
735 Find an existing record from this resultset using "find". if none
737 exists, instantiate a new result object and return it. The object will
738 not be saved into your storage until you call "insert" in
739 DBIx::Class::Row on it.
740 You most likely want this method when looking for existing rows using a
742 unique constraint that is not the primary key, or looking for related
743 rows.
744 If you want objects to be saved immediately, use "find_or_create"
746 instead.
747 Note: Make sure to read the documentation of "find" and understand the
749 significance of the "key" attribute, as its lack may skew your search,
750 and subsequently result in spurious new objects.
751 Note: Take care when using "find_or_new" with a table having columns
753 with default values that you intend to be automatically supplied by the
754 database (e.g. an auto_increment primary key column). In normal usage,
755 the value of such columns should NOT be included at all in the call to
756 "find_or_new", even when set to "undef".
757 create
759 Arguments: \%col_data
760 Return Value: $result
761 Attempt to create a single new row or a row with multiple related rows
763 in the table represented by the resultset (and related tables). This
764 will not check for duplicate rows before inserting, use
765 "find_or_create" to do that.
766 To create one row for this resultset, pass a hashref of key/value pairs
768 representing the columns of the table and the values you wish to store.
769 If the appropriate relationships are set up, foreign key fields can
770 also be passed an object representing the foreign row, and the value
771 will be set to its primary key.
772 To create related objects, pass a hashref of related-object column
774 values keyed on the relationship name. If the relationship is of type
775 "multi" ("has_many" in DBIx::Class::Relationship) - pass an arrayref of
776 hashrefs. The process will correctly identify columns holding foreign
777 keys, and will transparently populate them from the keys of the
778 corresponding relation. This can be applied recursively, and will work
779 correctly for a structure with an arbitrary depth and width, as long as
780 the relationships actually exists and the correct column data has been
781 supplied.
782 Instead of hashrefs of plain related data (key/value pairs), you may
784 also pass new or inserted objects. New objects (not inserted yet, see
785 "new_result"), will be inserted into their appropriate tables.
786 Effectively a shortcut for "->new_result(\%col_data)->insert".
788 Example of creating a new row.
790 $person_rs->create({
792 name=>"Some Person",
793 email=>"somebody@someplace.com"
794 });
795 Example of creating a new row and also creating rows in a related
797 "has_many" or "has_one" resultset. Note Arrayref.
798 $artist_rs->create(
800 { artistid => 4, name => 'Manufactured Crap', cds => [
801 { title => 'My First CD', year => 2006 },
802 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
803 ],
804 },
805 );
806 Example of creating a new row and also creating a row in a related
808 "belongs_to" resultset. Note Hashref.
809 $cd_rs->create({
811 title=>"Music for Silly Walks",
812 year=>2000,
813 artist => {
814 name=>"Silly Musician",
815 }
816 });
817 WARNING
819 When subclassing ResultSet never attempt to override this method.
820 Since it is a simple shortcut for
821 "$self->new_result($attrs)->insert", a lot of the internals simply
822 never call it, so your override will be bypassed more often than
823 not. Override either "new" in DBIx::Class::Row or "insert" in
824 DBIx::Class::Row depending on how early in the "create" process you
825 need to intervene. See also warning pertaining to "new".
826 find_or_create
828 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
829 Return Value: $result
830 $cd->cd_to_producer->find_or_create({ producer => $producer },
832 { key => 'primary' });
833 Tries to find a record based on its primary key or unique constraints;
835 if none is found, creates one and returns that instead.
836 my $cd = $schema->resultset('CD')->find_or_create({
838 cdid => 5,
839 artist => 'Massive Attack',
840 title => 'Mezzanine',
841 year => 2005,
842 });
843 Also takes an optional "key" attribute, to search by a specific key or
845 unique constraint. For example:
846 my $cd = $schema->resultset('CD')->find_or_create(
848 {
849 artist => 'Massive Attack',
850 title => 'Mezzanine',
851 },
852 { key => 'cd_artist_title' }
853 );
854 Note: Make sure to read the documentation of "find" and understand the
856 significance of the "key" attribute, as its lack may skew your search,
857 and subsequently result in spurious row creation.
858 Note: Because find_or_create() reads from the database and then
860 possibly inserts based on the result, this method is subject to a race
861 condition. Another process could create a record in the table after the
862 find has completed and before the create has started. To avoid this
863 problem, use find_or_create() inside a transaction.
864 Note: Take care when using "find_or_create" with a table having columns
866 with default values that you intend to be automatically supplied by the
867 database (e.g. an auto_increment primary key column). In normal usage,
868 the value of such columns should NOT be included at all in the call to
869 "find_or_create", even when set to "undef".
870 See also "find" and "update_or_create". For information on how to
872 declare unique constraints, see "add_unique_constraint" in
873 DBIx::Class::ResultSource.
874 If you need to know if an existing row was found or a new one created
876 use "find_or_new" and "in_storage" in DBIx::Class::Row instead. Don't
877 forget to call "insert" in DBIx::Class::Row to save the newly created
878 row to the database!
879 my $cd = $schema->resultset('CD')->find_or_new({
881 cdid => 5,
882 artist => 'Massive Attack',
883 title => 'Mezzanine',
884 year => 2005,
885 });
886 if( !$cd->in_storage ) {
888 # do some stuff
889 $cd->insert;
890 }
891 update_or_create
893 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
894 Return Value: $result
895 $resultset->update_or_create({ col => $val, ... });
897 Like "find_or_create", but if a row is found it is immediately updated
899 via "$found_row->update (\%col_data)".
900 Takes an optional "key" attribute to search on a specific unique
902 constraint. For example:
903 # In your application
905 my $cd = $schema->resultset('CD')->update_or_create(
906 {
907 artist => 'Massive Attack',
908 title => 'Mezzanine',
909 year => 1998,
910 },
911 { key => 'cd_artist_title' }
912 );
913 $cd->cd_to_producer->update_or_create({
915 producer => $producer,
916 name => 'harry',
917 }, {
918 key => 'primary',
919 });
920 Note: Make sure to read the documentation of "find" and understand the
922 significance of the "key" attribute, as its lack may skew your search,
923 and subsequently result in spurious row creation.
924 Note: Take care when using "update_or_create" with a table having
926 columns with default values that you intend to be automatically
927 supplied by the database (e.g. an auto_increment primary key column).
928 In normal usage, the value of such columns should NOT be included at
929 all in the call to "update_or_create", even when set to "undef".
930 See also "find" and "find_or_create". For information on how to declare
932 unique constraints, see "add_unique_constraint" in
933 DBIx::Class::ResultSource.
934 If you need to know if an existing row was updated or a new one created
936 use "update_or_new" and "in_storage" in DBIx::Class::Row instead. Don't
937 forget to call "insert" in DBIx::Class::Row to save the newly created
938 row to the database!
939 update_or_new
941 Arguments: \%col_data, { key => $unique_constraint, %attrs }?
942 Return Value: $result
943 $resultset->update_or_new({ col => $val, ... });
945 Like "find_or_new" but if a row is found it is immediately updated via
947 "$found_row->update (\%col_data)".
948 For example:
950 # In your application
952 my $cd = $schema->resultset('CD')->update_or_new(
953 {
954 artist => 'Massive Attack',
955 title => 'Mezzanine',
956 year => 1998,
957 },
958 { key => 'cd_artist_title' }
959 );
960 if ($cd->in_storage) {
962 # the cd was updated
963 }
964 else {
965 # the cd is not yet in the database, let's insert it
966 $cd->insert;
967 }
968 Note: Make sure to read the documentation of "find" and understand the
970 significance of the "key" attribute, as its lack may skew your search,
971 and subsequently result in spurious new objects.
972 Note: Take care when using "update_or_new" with a table having columns
974 with default values that you intend to be automatically supplied by the
975 database (e.g. an auto_increment primary key column). In normal usage,
976 the value of such columns should NOT be included at all in the call to
977 "update_or_new", even when set to "undef".
978 See also "find", "find_or_create" and "find_or_new".
980 get_cache
982 Arguments: none
983 Return Value: \@result_objs | undef
984 Gets the contents of the cache for the resultset, if the cache is set.
986 The cache is populated either by using the "prefetch" attribute to
988 "search" or by calling "set_cache".
989 set_cache
991 Arguments: \@result_objs
992 Return Value: \@result_objs
993 Sets the contents of the cache for the resultset. Expects an arrayref
995 of objects of the same class as those produced by the resultset. Note
996 that if the cache is set, the resultset will return the cached objects
997 rather than re-querying the database even if the cache attr is not set.
998 The contents of the cache can also be populated by using the "prefetch"
1000 attribute to "search".
1001 clear_cache
1003 Arguments: none
1004 Return Value: undef
1005 Clears the cache for the resultset.
1007 is_paged
1009 Arguments: none
1010 Return Value: true, if the resultset has been paginated
1011 is_ordered
1013 Arguments: none
1014 Return Value: true, if the resultset has been ordered with "order_by".
1015 related_resultset
1017 Arguments: $rel_name
1018 Return Value: $resultset
1019 Returns a related resultset for the supplied relationship name.
1021 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1023 current_source_alias
1025 Arguments: none
1026 Return Value: $source_alias
1027 Returns the current table alias for the result source this resultset is
1029 built on, that will be used in the SQL query. Usually it is "me".
1030 Currently the source alias that refers to the result set returned by a
1032 "search"/"find" family method depends on how you got to the resultset:
1033 it's "me" by default, but eg. "search_related" aliases it to the
1034 related result source name (and keeps "me" referring to the original
1035 result set). The long term goal is to make DBIx::Class always alias the
1036 current resultset as "me" (and make this method unnecessary).
1037 Thus it's currently necessary to use this method in predefined queries
1039 (see "Predefined searches" in DBIx::Class::Manual::Cookbook) when
1040 referring to the source alias of the current result set:
1041 # in a result set class
1043 sub modified_by {
1044 my ($self, $user) = @_;
1045 my $me = $self->current_source_alias;
1047 return $self->search({
1049 "$me.modified" => $user->id,
1050 });
1051 }
1052 The alias of newly created resultsets can be altered by the alias
1054 attribute.
1055 as_subselect_rs
1057 Arguments: none
1058 Return Value: $resultset
1059 Act as a barrier to SQL symbols. The resultset provided will be made
1061 into a "virtual view" by including it as a subquery within the from
1062 clause. From this point on, any joined tables are inaccessible to
1063 ->search on the resultset (as if it were simply where-filtered without
1064 joins). For example:
1065 my $rs = $schema->resultset('Bar')->search({'x.name' => 'abc'},{ join => 'x' });
1067 # 'x' now pollutes the query namespace
1069 # So the following works as expected
1071 my $ok_rs = $rs->search({'x.other' => 1});
1072 # But this doesn't: instead of finding a 'Bar' related to two x rows (abc and
1074 # def) we look for one row with contradictory terms and join in another table
1075 # (aliased 'x_2') which we never use
1076 my $broken_rs = $rs->search({'x.name' => 'def'});
1077 my $rs2 = $rs->as_subselect_rs;
1079 # doesn't work - 'x' is no longer accessible in $rs2, having been sealed away
1081 my $not_joined_rs = $rs2->search({'x.other' => 1});
1082 # works as expected: finds a 'table' row related to two x rows (abc and def)
1084 my $correctly_joined_rs = $rs2->search({'x.name' => 'def'});
1085 Another example of when one might use this would be to select a subset
1087 of columns in a group by clause:
1088 my $rs = $schema->resultset('Bar')->search(undef, {
1090 group_by => [qw{ id foo_id baz_id }],
1091 })->as_subselect_rs->search(undef, {
1092 columns => [qw{ id foo_id }]
1093 });
1094 In the above example normally columns would have to be equal to the
1096 group by, but because we isolated the group by into a subselect the
1097 above works.
1098 throw_exception
1100 See "throw_exception" in DBIx::Class::Schema for details.
1101
1103 Attributes are used to refine a ResultSet in various ways when
1104 searching for data. They can be passed to any method which takes an
1105 "\%attrs" argument. See "search", "search_rs", "find", "count".
1106 Default attributes can be set on the result class using
1108 "resultset_attributes" in DBIx::Class::ResultSource. (Please read the
1109 CAVEATS on that feature before using it!)
1110 These are in no particular order:
1112 order_by
1114 Value: ( $order_by | \@order_by | \%order_by )
1115 Which column(s) to order the results by.
1117 [The full list of suitable values is documented in "ORDER BY CLAUSES"
1119 in SQL::Abstract; the following is a summary of common options.]
1120 If a single column name, or an arrayref of names is supplied, the
1122 argument is passed through directly to SQL. The hashref syntax allows
1123 for connection-agnostic specification of ordering direction:
1124 For descending order:
1126 order_by => { -desc => [qw/col1 col2 col3/] }
1128 For explicit ascending order:
1130 order_by => { -asc => 'col' }
1132 The old scalarref syntax (i.e. order_by => \'year DESC') is still
1134 supported, although you are strongly encouraged to use the hashref
1135 syntax as outlined above.
1136 columns
1138 Value: \@columns | \%columns | $column
1139 Shortcut to request a particular set of columns to be retrieved. Each
1141 column spec may be a string (a table column name), or a hash (in which
1142 case the key is the "as" value, and the value is used as the "select"
1143 expression). Adds the "current_source_alias" onto the start of any
1144 column without a "." in it and sets "select" from that, then auto-
1145 populates "as" from "select" as normal. (You may also use the "cols"
1146 attribute, as in earlier versions of DBIC, but this is deprecated)
1147 Essentially "columns" does the same as "select" and "as".
1149 columns => [ 'some_column', { dbic_slot => 'another_column' } ]
1151 is the same as
1153 select => [qw(some_column another_column)],
1155 as => [qw(some_column dbic_slot)]
1156 If you want to individually retrieve related columns (in essence
1158 perform manual "prefetch") you have to make sure to specify the correct
1159 inflation slot chain such that it matches existing relationships:
1160 my $rs = $schema->resultset('Artist')->search({}, {
1162 # required to tell DBIC to collapse has_many relationships
1163 collapse => 1,
1164 join => { cds => 'tracks' },
1165 '+columns' => {
1166 'cds.cdid' => 'cds.cdid',
1167 'cds.tracks.title' => 'tracks.title',
1168 },
1169 });
1170 Like elsewhere, literal SQL or literal values can be included by using
1172 a scalar reference or a literal bind value, and these values will be
1173 available in the result with "get_column" (see also "Literal SQL and
1174 value type operators" in SQL::Abstract):
1175 # equivalent SQL: SELECT 1, 'a string', IF(my_column,?,?) ...
1177 # bind values: $true_value, $false_value
1178 columns => [
1179 {
1180 foo => \1,
1181 bar => \q{'a string'},
1182 baz => \[ 'IF(my_column,?,?)', $true_value, $false_value ],
1183 }
1184 ]
1185 +columns
1187 NOTE: You MUST explicitly quote '+columns' when using this attribute.
1188 Not doing so causes Perl to incorrectly interpret "+columns" as a
1189 bareword with a unary plus operator before it, which is the same as
1190 simply "columns".
1191 Value: \@extra_columns
1193 Indicates additional columns to be selected from storage. Works the
1195 same as "columns" but adds columns to the current selection. (You may
1196 also use the "include_columns" attribute, as in earlier versions of
1197 DBIC, but this is deprecated)
1198 $schema->resultset('CD')->search(undef, {
1200 '+columns' => ['artist.name'],
1201 join => ['artist']
1202 });
1203 would return all CDs and include a 'name' column to the information
1205 passed to object inflation. Note that the 'artist' is the name of the
1206 column (or relationship) accessor, and 'name' is the name of the column
1207 accessor in the related table.
1208 select
1210 Value: \@select_columns
1211 Indicates which columns should be selected from the storage. You can
1213 use column names, or in the case of RDBMS back ends, function or stored
1214 procedure names:
1215 $rs = $schema->resultset('Employee')->search(undef, {
1217 select => [
1218 'name',
1219 { count => 'employeeid' },
1220 { max => { length => 'name' }, -as => 'longest_name' }
1221 ]
1222 });
1223 # Equivalent SQL
1225 SELECT name, COUNT( employeeid ), MAX( LENGTH( name ) ) AS longest_name FROM employee
1226 NOTE: You will almost always need a corresponding "as" attribute when
1228 you use "select", to instruct DBIx::Class how to store the result of
1229 the column.
1230 Also note that the "as" attribute has nothing to do with the SQL-side
1232 "AS" identifier aliasing. You can alias a function (so you can use it
1233 e.g. in an "ORDER BY" clause), however this is done via the "-as"
1234 select function attribute supplied as shown in the example above.
1235 +select
1237 NOTE: You MUST explicitly quote '+select' when using this attribute.
1238 Not doing so causes Perl to incorrectly interpret "+select" as a
1239 bareword with a unary plus operator before it, which is the same as
1240 simply "select".
1241 Value: \@extra_select_columns
1243 Indicates additional columns to be selected from storage. Works the
1245 same as "select" but adds columns to the current selection, instead of
1246 specifying a new explicit list.
1247 as
1249 Value: \@inflation_names
1250 Indicates DBIC-side names for object inflation. That is "as" indicates
1252 the slot name in which the column value will be stored within the Row
1253 object. The value will then be accessible via this identifier by the
1254 "get_column" method (or via the object accessor if one with the same
1255 name already exists) as shown below.
1256 The "as" attribute has nothing to do with the SQL-side identifier
1258 aliasing "AS". See "select" for details.
1259 $rs = $schema->resultset('Employee')->search(undef, {
1261 select => [
1262 'name',
1263 { count => 'employeeid' },
1264 { max => { length => 'name' }, -as => 'longest_name' }
1265 ],
1266 as => [qw/
1267 name
1268 employee_count
1269 max_name_length
1270 /],
1271 });
1272 If the object against which the search is performed already has an
1274 accessor matching a column name specified in "as", the value can be
1275 retrieved using the accessor as normal:
1276 my $name = $employee->name();
1278 If on the other hand an accessor does not exist in the object, you need
1280 to use "get_column" instead:
1281 my $employee_count = $employee->get_column('employee_count');
1283 You can create your own accessors if required - see
1285 DBIx::Class::Manual::Cookbook for details.
1286 +as
1288 NOTE: You MUST explicitly quote '+as' when using this attribute. Not
1289 doing so causes Perl to incorrectly interpret "+as" as a bareword with
1290 a unary plus operator before it, which is the same as simply "as".
1291 Value: \@extra_inflation_names
1293 Indicates additional inflation names for selectors added via "+select".
1295 See "as".
1296 join
1298 Value: ($rel_name | \@rel_names | \%rel_names)
1299 Contains a list of relationships that should be joined for this query.
1301 For example:
1302 # Get CDs by Nine Inch Nails
1304 my $rs = $schema->resultset('CD')->search(
1305 { 'artist.name' => 'Nine Inch Nails' },
1306 { join => 'artist' }
1307 );
1308 Can also contain a hash reference to refer to the other relation's
1310 relations. For example:
1311 package MyApp::Schema::Track;
1313 use base qw/DBIx::Class/;
1314 __PACKAGE__->table('track');
1315 __PACKAGE__->add_columns(qw/trackid cd position title/);
1316 __PACKAGE__->set_primary_key('trackid');
1317 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1318 1;
1319 # In your application
1321 my $rs = $schema->resultset('Artist')->search(
1322 { 'track.title' => 'Teardrop' },
1323 {
1324 join => { cd => 'track' },
1325 order_by => 'artist.name',
1326 }
1327 );
1328 You need to use the relationship (not the table) name in conditions,
1330 because they are aliased as such. The current table is aliased as "me",
1331 so you need to use me.column_name in order to avoid ambiguity. For
1332 example:
1333 # Get CDs from 1984 with a 'Foo' track
1335 my $rs = $schema->resultset('CD')->search(
1336 {
1337 'me.year' => 1984,
1338 'tracks.name' => 'Foo'
1339 },
1340 { join => 'tracks' }
1341 );
1342 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1344 similarly for a third time). For e.g.
1345 my $rs = $schema->resultset('Artist')->search({
1347 'cds.title' => 'Down to Earth',
1348 'cds_2.title' => 'Popular',
1349 }, {
1350 join => [ qw/cds cds/ ],
1351 });
1352 will return a set of all artists that have both a cd with title 'Down
1354 to Earth' and a cd with title 'Popular'.
1355 If you want to fetch related objects from other tables as well, see
1357 "prefetch" below.
1358 NOTE: An internal join-chain pruner will discard certain joins while
1360 constructing the actual SQL query, as long as the joins in question do not
1361 affect the retrieved result. This for example includes 1:1 left joins
1362 that are not part of the restriction specification (WHERE/HAVING) nor are
1363 a part of the query selection.
1364 For more help on using joins with search, see
1366 DBIx::Class::Manual::Joining.
1367 collapse
1369 Value: (0 | 1)
1370 When set to a true value, indicates that any rows fetched from joined
1372 has_many relationships are to be aggregated into the corresponding
1373 "parent" object. For example, the resultset:
1374 my $rs = $schema->resultset('CD')->search({}, {
1376 '+columns' => [ qw/ tracks.title tracks.position / ],
1377 join => 'tracks',
1378 collapse => 1,
1379 });
1380 While executing the following query:
1382 SELECT me.*, tracks.title, tracks.position
1384 FROM cd me
1385 LEFT JOIN track tracks
1386 ON tracks.cdid = me.cdid
1387 Will return only as many objects as there are rows in the CD source,
1389 even though the result of the query may span many rows. Each of these
1390 CD objects will in turn have multiple "Track" objects hidden behind the
1391 has_many generated accessor "tracks". Without "collapse => 1", the
1392 return values of this resultset would be as many CD objects as there
1393 are tracks (a "Cartesian product"), with each CD object containing
1394 exactly one of all fetched Track data.
1395 When a collapse is requested on a non-ordered resultset, an order by
1397 some unique part of the main source (the left-most table) is inserted
1398 automatically. This is done so that the resultset is allowed to be
1399 "lazy" - calling $rs->next will fetch only as many rows as it needs to
1400 build the next object with all of its related data.
1401 If an "order_by" is already declared, and orders the resultset in a way
1403 that makes collapsing as described above impossible (e.g. "ORDER BY
1404 has_many_rel.column" or "ORDER BY RANDOM()"), DBIC will automatically
1405 switch to "eager" mode and slurp the entire resultset before
1406 constructing the first object returned by "next".
1407 Setting this attribute on a resultset that does not join any has_many
1409 relations is a no-op.
1410 For a more in-depth discussion, see "PREFETCHING".
1412 prefetch
1414 Value: ($rel_name | \@rel_names | \%rel_names)
1415 This attribute is a shorthand for specifying a "join" spec, adding all
1417 columns from the joined related sources as "+columns" and setting
1418 "collapse" to a true value. It can be thought of as a rough superset of
1419 the "join" attribute.
1420 For example, the following two queries are equivalent:
1422 my $rs = $schema->resultset('Artist')->search({}, {
1424 prefetch => { cds => ['genre', 'tracks' ] },
1425 });
1426 and
1428 my $rs = $schema->resultset('Artist')->search({}, {
1430 join => { cds => ['genre', 'tracks' ] },
1431 collapse => 1,
1432 '+columns' => [
1433 (map
1434 { +{ "cds.$_" => "cds.$_" } }
1435 $schema->source('Artist')->related_source('cds')->columns
1436 ),
1437 (map
1438 { +{ "cds.genre.$_" => "genre.$_" } }
1439 $schema->source('Artist')->related_source('cds')->related_source('genre')->columns
1440 ),
1441 (map
1442 { +{ "cds.tracks.$_" => "tracks.$_" } }
1443 $schema->source('Artist')->related_source('cds')->related_source('tracks')->columns
1444 ),
1445 ],
1446 });
1447 Both producing the following SQL:
1449 SELECT me.artistid, me.name, me.rank, me.charfield,
1451 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track,
1452 genre.genreid, genre.name,
1453 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
1454 FROM artist me
1455 LEFT JOIN cd cds
1456 ON cds.artist = me.artistid
1457 LEFT JOIN genre genre
1458 ON genre.genreid = cds.genreid
1459 LEFT JOIN track tracks
1460 ON tracks.cd = cds.cdid
1461 ORDER BY me.artistid
1462 While "prefetch" implies a "join", it is ok to mix the two together, as
1464 the arguments are properly merged and generally do the right thing. For
1465 example, you may want to do the following:
1466 my $artists_and_cds_without_genre = $schema->resultset('Artist')->search(
1468 { 'genre.genreid' => undef },
1469 {
1470 join => { cds => 'genre' },
1471 prefetch => 'cds',
1472 }
1473 );
1474 Which generates the following SQL:
1476 SELECT me.artistid, me.name, me.rank, me.charfield,
1478 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
1479 FROM artist me
1480 LEFT JOIN cd cds
1481 ON cds.artist = me.artistid
1482 LEFT JOIN genre genre
1483 ON genre.genreid = cds.genreid
1484 WHERE genre.genreid IS NULL
1485 ORDER BY me.artistid
1486 For a more in-depth discussion, see "PREFETCHING".
1488 alias
1490 Value: $source_alias
1491 Sets the source alias for the query. Normally, this defaults to "me",
1493 but nested search queries (sub-SELECTs) might need specific aliases set
1494 to reference inner queries. For example:
1495 my $q = $rs
1497 ->related_resultset('CDs')
1498 ->related_resultset('Tracks')
1499 ->search({
1500 'track.id' => { -ident => 'none_search.id' },
1501 })
1502 ->as_query;
1503 my $ids = $self->search({
1505 -not_exists => $q,
1506 }, {
1507 alias => 'none_search',
1508 group_by => 'none_search.id',
1509 })->get_column('id')->as_query;
1510 $self->search({ id => { -in => $ids } })
1512 This attribute is directly tied to "current_source_alias".
1514 page
1516 Value: $page
1517 Makes the resultset paged and specifies the page to retrieve.
1519 Effectively identical to creating a non-pages resultset and then
1520 calling ->page($page) on it.
1521 If "rows" attribute is not specified it defaults to 10 rows per page.
1523 When you have a paged resultset, "count" will only return the number of
1525 rows in the page. To get the total, use the "pager" and call
1526 "total_entries" on it.
1527 rows
1529 Value: $rows
1530 Specifies the maximum number of rows for direct retrieval or the number
1532 of rows per page if the page attribute or method is used.
1533 offset
1535 Value: $offset
1536 Specifies the (zero-based) row number for the first row to be
1538 returned, or the of the first row of the first page if paging is used.
1539 software_limit
1541 Value: (0 | 1)
1542 When combined with "rows" and/or "offset" the generated SQL will not
1544 include any limit dialect stanzas. Instead the entire result will be
1545 selected as if no limits were specified, and DBIC will perform the
1546 limit locally, by artificially advancing and finishing the resulting
1547 "cursor".
1548 This is the recommended way of performing resultset limiting when no
1550 sane RDBMS implementation is available (e.g. Sybase ASE using the
1551 Generic Sub Query hack)
1552 group_by
1554 Value: \@columns
1555 A arrayref of columns to group by. Can include columns of joined
1557 tables.
1558 group_by => [qw/ column1 column2 ... /]
1560 having
1562 Value: $condition
1563 The HAVING operator specifies a secondary condition applied to the set
1565 after the grouping calculations have been done. In other words it is a
1566 constraint just like "where" (and accepting the same SQL::Abstract
1567 syntax) applied to the data as it exists after GROUP BY has taken
1568 place. Specifying "having" without "group_by" is a logical mistake, and
1569 a fatal error on most RDBMS engines.
1570 E.g.
1572 having => { 'count_employee' => { '>=', 100 } }
1574 or with an in-place function in which case literal SQL is required:
1576 having => \[ 'count(employee) >= ?', 100 ]
1578 distinct
1580 Value: (0 | 1)
1581 Set to 1 to automatically generate a "group_by" clause based on the
1583 selection (including intelligent handling of "order_by" contents). Note
1584 that the group criteria calculation takes place over the final
1585 selection. This includes any "+columns", "+select" or "order_by"
1586 additions in subsequent "search" calls, and standalone columns selected
1587 via DBIx::Class::ResultSetColumn ("get_column"). A notable exception
1588 are the extra selections specified via "prefetch" - such selections are
1589 explicitly excluded from group criteria calculations.
1590 If the final ResultSet also explicitly defines a "group_by" attribute,
1592 this setting is ignored and an appropriate warning is issued.
1593 where
1595 Adds to the WHERE clause.
1596 # only return rows WHERE deleted IS NULL for all searches
1598 __PACKAGE__->resultset_attributes({ where => { deleted => undef } });
1599 Can be overridden by passing "{ where => undef }" as an attribute
1601 to a resultset.
1602 For more complicated where clauses see "WHERE CLAUSES" in
1604 SQL::Abstract.
1605 cache
1607 Set to 1 to cache search results. This prevents extra SQL queries if
1608 you revisit rows in your ResultSet:
1609 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1611 while( my $artist = $resultset->next ) {
1613 ... do stuff ...
1614 }
1615 $rs->first; # without cache, this would issue a query
1617 By default, searches are not cached.
1619 For more examples of using these attributes, see
1621 DBIx::Class::Manual::Cookbook.
1622 for
1624 Value: ( 'update' | 'shared' | \$scalar )
1625 Set to 'update' for a SELECT ... FOR UPDATE or 'shared' for a SELECT
1627 ... FOR SHARED. If \$scalar is passed, this is taken directly and
1628 embedded in the query.
1629
1631 DBIx::Class supports arbitrary related data prefetching from multiple
1632 related sources. Any combination of relationship types and column sets
1633 are supported. If collapsing is requested, there is an additional
1634 requirement of selecting enough data to make every individual object
1635 uniquely identifiable.
1636 Here are some more involved examples, based on the following
1638 relationship map:
1639 # Assuming:
1641 My::Schema::CD->belongs_to( artist => 'My::Schema::Artist' );
1642 My::Schema::CD->might_have( liner_note => 'My::Schema::LinerNotes' );
1643 My::Schema::CD->has_many( tracks => 'My::Schema::Track' );
1644 My::Schema::Artist->belongs_to( record_label => 'My::Schema::RecordLabel' );
1646 My::Schema::Track->has_many( guests => 'My::Schema::Guest' );
1648 my $rs = $schema->resultset('Tag')->search(
1652 undef,
1653 {
1654 prefetch => {
1655 cd => 'artist'
1656 }
1657 }
1658 );
1659 The initial search results in SQL like the following:
1661 SELECT tag.*, cd.*, artist.* FROM tag
1663 JOIN cd ON tag.cd = cd.cdid
1664 JOIN artist ON cd.artist = artist.artistid
1665 DBIx::Class has no need to go back to the database when we access the
1667 "cd" or "artist" relationships, which saves us two SQL statements in
1668 this case.
1669 Simple prefetches will be joined automatically, so there is no need for
1671 a "join" attribute in the above search.
1672 The "prefetch" attribute can be used with any of the relationship types
1674 and multiple prefetches can be specified together. Below is a more
1675 complex example that prefetches a CD's artist, its liner notes (if
1676 present), the cover image, the tracks on that CD, and the guests on
1677 those tracks.
1678 my $rs = $schema->resultset('CD')->search(
1680 undef,
1681 {
1682 prefetch => [
1683 { artist => 'record_label'}, # belongs_to => belongs_to
1684 'liner_note', # might_have
1685 'cover_image', # has_one
1686 { tracks => 'guests' }, # has_many => has_many
1687 ]
1688 }
1689 );
1690 This will produce SQL like the following:
1692 SELECT cd.*, artist.*, record_label.*, liner_note.*, cover_image.*,
1694 tracks.*, guests.*
1695 FROM cd me
1696 JOIN artist artist
1697 ON artist.artistid = me.artistid
1698 JOIN record_label record_label
1699 ON record_label.labelid = artist.labelid
1700 LEFT JOIN track tracks
1701 ON tracks.cdid = me.cdid
1702 LEFT JOIN guest guests
1703 ON guests.trackid = track.trackid
1704 LEFT JOIN liner_notes liner_note
1705 ON liner_note.cdid = me.cdid
1706 JOIN cd_artwork cover_image
1707 ON cover_image.cdid = me.cdid
1708 ORDER BY tracks.cd
1709 Now the "artist", "record_label", "liner_note", "cover_image",
1711 "tracks", and "guests" of the CD will all be available through the
1712 relationship accessors without the need for additional queries to the
1713 database.
1714 CAVEATS
1716 Prefetch does a lot of deep magic. As such, it may not behave exactly
1718 as you might expect.
1719 · Prefetch uses the "cache" to populate the prefetched relationships.
1721 This may or may not be what you want.
1722 · If you specify a condition on a prefetched relationship, ONLY those
1724 rows that match the prefetched condition will be fetched into that
1725 relationship. This means that adding prefetch to a search() may
1726 alter what is returned by traversing a relationship. So, if you
1727 have "Artist->has_many(CDs)" and you do
1728 my $artist_rs = $schema->resultset('Artist')->search({
1730 'cds.year' => 2008,
1731 }, {
1732 join => 'cds',
1733 });
1734 my $count = $artist_rs->first->cds->count;
1736 my $artist_rs_prefetch = $artist_rs->search( {}, { prefetch => 'cds' } );
1738 my $prefetch_count = $artist_rs_prefetch->first->cds->count;
1740 cmp_ok( $count, '==', $prefetch_count, "Counts should be the same" );
1742 That cmp_ok() may or may not pass depending on the datasets
1744 involved. In other words the "WHERE" condition would apply to the
1745 entire dataset, just like it would in regular SQL. If you want to
1746 add a condition only to the "right side" of a "LEFT JOIN" -
1747 consider declaring and using a relationship with a custom condition
1748
1750 Because DBIC may need more information to bind values than just the
1751 column name and value itself, it uses a special format for both passing
1752 and receiving bind values. Each bind value should be composed of an
1753 arrayref of "[ \%args => $val ]". The format of "\%args" is currently:
1754 dbd_attrs
1756 If present (in any form), this is what is being passed directly to
1757 bind_param. Note that different DBD's expect different bind args.
1758 (e.g. DBD::SQLite takes a single numerical type, while DBD::Pg
1759 takes a hashref if bind options.)
1760 If this is specified, all other bind options described below are
1762 ignored.
1763 sqlt_datatype
1765 If present, this is used to infer the actual bind attribute by
1766 passing to "$resolved_storage->bind_attribute_by_data_type()".
1767 Defaults to the "data_type" from the add_columns column info.
1768 Note that the data type is somewhat freeform (hence the sqlt_
1770 prefix); currently drivers are expected to "Do the Right Thing"
1771 when given a common datatype name. (Not ideal, but that's what we
1772 got at this point.)
1773 sqlt_size
1775 Currently used to correctly allocate buffers for
1776 bind_param_inout(). Defaults to "size" from the add_columns column
1777 info, or to a sensible value based on the "data_type".
1778 dbic_colname
1780 Used to fill in missing sqlt_datatype and sqlt_size attributes (if
1781 they are explicitly specified they are never overridden). Also
1782 used by some weird DBDs, where the column name should be available
1783 at bind_param time (e.g. Oracle).
1784 For backwards compatibility and convenience, the following shortcuts
1786 are supported:
1787 [ $name => $val ] === [ { dbic_colname => $name }, $val ]
1789 [ \$dt => $val ] === [ { sqlt_datatype => $dt }, $val ]
1790 [ undef, $val ] === [ {}, $val ]
1791 $val === [ {}, $val ]
1792
1794 Check the list of additional DBIC resources.
1795
1797 This module is free software copyright by the DBIx::Class (DBIC)
1798 authors. You can redistribute it and/or modify it under the same terms
1799 as the DBIx::Class library.
1800perl v5.28.0 2018-01-29 DBIx::Class::ResultSet(3)