1 package DBIx::Class::ResultSet;
9 use Carp::Clan qw/^DBIx::Class/;
12 use DBIx::Class::ResultSetColumn;
13 use base qw/DBIx::Class/;
15 __PACKAGE__->load_components(qw/AccessorGroup/);
16 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
20 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
24 my $rs = $schema->resultset('User')->search(registered => 1);
25 my @rows = $schema->resultset('CD')->search(year => 2005);
29 The resultset is also known as an iterator. It is responsible for handling
30 queries that may return an arbitrary number of rows, e.g. via L</search>
31 or a C<has_many> relationship.
33 In the examples below, the following table classes are used:
35 package MyApp::Schema::Artist;
36 use base qw/DBIx::Class/;
37 __PACKAGE__->load_components(qw/Core/);
38 __PACKAGE__->table('artist');
39 __PACKAGE__->add_columns(qw/artistid name/);
40 __PACKAGE__->set_primary_key('artistid');
41 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
44 package MyApp::Schema::CD;
45 use base qw/DBIx::Class/;
46 __PACKAGE__->load_components(qw/Core/);
47 __PACKAGE__->table('cd');
48 __PACKAGE__->add_columns(qw/cdid artist title year/);
49 __PACKAGE__->set_primary_key('cdid');
50 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
59 =item Arguments: $source, \%$attrs
61 =item Return Value: $rs
65 The resultset constructor. Takes a source object (usually a
66 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
67 L</ATTRIBUTES> below). Does not perform any queries -- these are
68 executed as needed by the other methods.
70 Generally you won't need to construct a resultset manually. You'll
71 automatically get one from e.g. a L</search> called in scalar context:
73 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
75 IMPORTANT: If called on an object, proxies to new_result instead so
77 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
79 will return a CD object, not a ResultSet.
85 return $class->new_result(@_) if ref $class;
87 my ($source, $attrs) = @_;
91 $attrs->{rows} ||= 10;
92 $attrs->{offset} ||= 0;
93 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
96 $attrs->{alias} ||= 'me';
99 result_source => $source,
100 result_class => $attrs->{result_class} || $source->result_class,
101 cond => $attrs->{where},
112 =item Arguments: $cond, \%attrs?
114 =item Return Value: $resultset (scalar context), @row_objs (list context)
118 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
119 my $new_rs = $cd_rs->search({ year => 2005 });
121 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
122 # year = 2005 OR year = 2004
124 If you need to pass in additional attributes but no additional condition,
125 call it as C<search(undef, \%attrs)>.
127 # "SELECT name, artistid FROM $artist_table"
128 my @all_artists = $schema->resultset('Artist')->search(undef, {
129 columns => [qw/name artistid/],
136 my $rs = $self->search_rs( @_ );
137 return (wantarray ? $rs->all : $rs);
144 =item Arguments: $cond, \%attrs?
146 =item Return Value: $resultset
150 This method does the same exact thing as search() except it will
151 always return a resultset, even in list context.
160 unless (@_) { # no search, effectively just a clone
161 $rows = $self->get_cache;
165 $attrs = pop(@_) if @_ > 1 and ref $_[$#_] eq 'HASH';
166 my $our_attrs = { %{$self->{attrs}} };
167 my $having = delete $our_attrs->{having};
169 # merge new attrs into inherited
170 foreach my $key (qw/join prefetch/) {
171 next unless exists $attrs->{$key};
172 $our_attrs->{$key} = $self->_merge_attr($our_attrs->{$key}, delete $attrs->{$key});
175 my $new_attrs = { %{$our_attrs}, %{$attrs} };
178 (@_ == 1 || ref $_[0] eq "HASH")
182 ? $self->throw_exception("Odd number of arguments to search")
189 if (defined $where) {
190 $new_attrs->{where} = (
191 defined $new_attrs->{where}
194 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
195 } $where, $new_attrs->{where}
201 if (defined $having) {
202 $new_attrs->{having} = (
203 defined $new_attrs->{having}
206 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
207 } $having, $new_attrs->{having}
213 my $rs = (ref $self)->new($self->result_source, $new_attrs);
215 $rs->set_cache($rows);
220 =head2 search_literal
224 =item Arguments: $sql_fragment, @bind_values
226 =item Return Value: $resultset (scalar context), @row_objs (list context)
230 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
231 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
233 Pass a literal chunk of SQL to be added to the conditional part of the
239 my ($self, $cond, @vals) = @_;
240 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
241 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
242 return $self->search(\$cond, $attrs);
249 =item Arguments: @values | \%cols, \%attrs?
251 =item Return Value: $row_object
255 Finds a row based on its primary key or unique constraint. For example, to find
256 a row by its primary key:
258 my $cd = $schema->resultset('CD')->find(5);
260 You can also find a row by a specific unique constraint using the C<key>
261 attribute. For example:
263 my $cd = $schema->resultset('CD')->find('Massive Attack', 'Mezzanine', {
264 key => 'cd_artist_title'
267 Additionally, you can specify the columns explicitly by name:
269 my $cd = $schema->resultset('CD')->find(
271 artist => 'Massive Attack',
272 title => 'Mezzanine',
274 { key => 'cd_artist_title' }
277 If the C<key> is specified as C<primary>, it searches only on the primary key.
279 If no C<key> is specified, it searches on all unique constraints defined on the
280 source, including the primary key.
282 See also L</find_or_create> and L</update_or_create>. For information on how to
283 declare unique constraints, see
284 L<DBIx::Class::ResultSource/add_unique_constraint>.
290 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
292 # Default to the primary key, but allow a specific key
293 my @cols = exists $attrs->{key}
294 ? $self->result_source->unique_constraint_columns($attrs->{key})
295 : $self->result_source->primary_columns;
296 $self->throw_exception(
297 "Can't find unless a primary key or unique constraint is defined"
300 # Parse out a hashref from input
302 if (ref $_[0] eq 'HASH') {
303 $input_query = { %{$_[0]} };
305 elsif (@_ == @cols) {
307 @{$input_query}{@cols} = @_;
310 # Compatibility: Allow e.g. find(id => $value)
311 carp "Find by key => value deprecated; please use a hashref instead";
315 my @unique_queries = $self->_unique_queries($input_query, $attrs);
317 # Handle cases where the ResultSet defines the query, or where the user is
319 my $query = @unique_queries ? \@unique_queries : $input_query;
323 my $rs = $self->search($query, $attrs);
324 return keys %{$rs->_resolved_attrs->{collapse}} ? $rs->next : $rs->single;
327 return keys %{$self->_resolved_attrs->{collapse}}
328 ? $self->search($query)->next
329 : $self->single($query);
335 # Build a list of queries which satisfy unique constraints.
337 sub _unique_queries {
338 my ($self, $query, $attrs) = @_;
340 my $alias = $self->{attrs}{alias};
341 my @constraint_names = exists $attrs->{key}
343 : $self->result_source->unique_constraint_names;
346 foreach my $name (@constraint_names) {
347 my @unique_cols = $self->result_source->unique_constraint_columns($name);
348 my $unique_query = $self->_build_unique_query($query, \@unique_cols);
350 my $num_query = scalar keys %$unique_query;
351 next unless $num_query;
353 # Add the ResultSet's alias
354 foreach my $col (grep { ! m/\./ } keys %$unique_query) {
355 $unique_query->{"$alias.$col"} = delete $unique_query->{$col};
358 # XXX: Assuming quite a bit about $self->{attrs}{where}
359 my $num_cols = scalar @unique_cols;
360 my $num_where = exists $self->{attrs}{where}
361 ? scalar keys %{ $self->{attrs}{where} }
363 push @unique_queries, $unique_query
364 if $num_query + $num_where == $num_cols;
367 return @unique_queries;
370 # _build_unique_query
372 # Constrain the specified query hash based on the specified column names.
374 sub _build_unique_query {
375 my ($self, $query, $unique_cols) = @_;
378 map { $_ => $query->{$_} }
379 grep { exists $query->{$_} }
384 =head2 search_related
388 =item Arguments: $rel, $cond, \%attrs?
390 =item Return Value: $new_resultset
394 $new_rs = $cd_rs->search_related('artist', {
398 Searches the specified relationship, optionally specifying a condition and
399 attributes for matching records. See L</ATTRIBUTES> for more information.
404 return shift->related_resultset(shift)->search(@_);
411 =item Arguments: none
413 =item Return Value: $cursor
417 Returns a storage-driven cursor to the given resultset. See
418 L<DBIx::Class::Cursor> for more information.
425 my $attrs = { %{$self->_resolved_attrs} };
426 return $self->{cursor}
427 ||= $self->result_source->storage->select($attrs->{from}, $attrs->{select},
428 $attrs->{where},$attrs);
435 =item Arguments: $cond?
437 =item Return Value: $row_object?
441 my $cd = $schema->resultset('CD')->single({ year => 2001 });
443 Inflates the first result without creating a cursor if the resultset has
444 any records in it; if not returns nothing. Used by L</find> as an optimisation.
446 Can optionally take an additional condition *only* - this is a fast-code-path
447 method; if you need to add extra joins or similar call ->search and then
448 ->single without a condition on the $rs returned from that.
453 my ($self, $where) = @_;
454 my $attrs = { %{$self->_resolved_attrs} };
456 if (defined $attrs->{where}) {
459 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
460 $where, delete $attrs->{where} ]
463 $attrs->{where} = $where;
467 # XXX: Disabled since it doesn't infer uniqueness in all cases
468 # unless ($self->_is_unique_query($attrs->{where})) {
469 # carp "Query not guaranteed to return a single row"
470 # . "; please declare your unique constraints or use search instead";
473 my @data = $self->result_source->storage->select_single(
474 $attrs->{from}, $attrs->{select},
475 $attrs->{where}, $attrs
478 return (@data ? $self->_construct_object(@data) : ());
483 # Try to determine if the specified query is guaranteed to be unique, based on
484 # the declared unique constraints.
486 sub _is_unique_query {
487 my ($self, $query) = @_;
489 my $collapsed = $self->_collapse_query($query);
490 my $alias = $self->{attrs}{alias};
492 foreach my $name ($self->result_source->unique_constraint_names) {
493 my @unique_cols = map {
495 } $self->result_source->unique_constraint_columns($name);
497 # Count the values for each unique column
498 my %seen = map { $_ => 0 } @unique_cols;
500 foreach my $key (keys %$collapsed) {
501 my $aliased = $key =~ /\./ ? $key : "$alias.$key";
502 next unless exists $seen{$aliased}; # Additional constraints are okay
503 $seen{$aliased} = scalar keys %{ $collapsed->{$key} };
506 # If we get 0 or more than 1 value for a column, it's not necessarily unique
507 return 1 unless grep { $_ != 1 } values %seen;
515 # Recursively collapse the query, accumulating values for each column.
517 sub _collapse_query {
518 my ($self, $query, $collapsed) = @_;
522 if (ref $query eq 'ARRAY') {
523 foreach my $subquery (@$query) {
524 next unless ref $subquery; # -or
525 # warn "ARRAY: " . Dumper $subquery;
526 $collapsed = $self->_collapse_query($subquery, $collapsed);
529 elsif (ref $query eq 'HASH') {
530 if (keys %$query and (keys %$query)[0] eq '-and') {
531 foreach my $subquery (@{$query->{-and}}) {
532 # warn "HASH: " . Dumper $subquery;
533 $collapsed = $self->_collapse_query($subquery, $collapsed);
537 # warn "LEAF: " . Dumper $query;
538 foreach my $col (keys %$query) {
539 my $value = $query->{$col};
540 $collapsed->{$col}{$value}++;
552 =item Arguments: $cond?
554 =item Return Value: $resultsetcolumn
558 my $max_length = $rs->get_column('length')->max;
560 Returns a ResultSetColumn instance for $column based on $self
565 my ($self, $column) = @_;
566 my $new = DBIx::Class::ResultSetColumn->new($self, $column);
574 =item Arguments: $cond, \%attrs?
576 =item Return Value: $resultset (scalar context), @row_objs (list context)
580 # WHERE title LIKE '%blue%'
581 $cd_rs = $rs->search_like({ title => '%blue%'});
583 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
584 that this is simply a convenience method. You most likely want to use
585 L</search> with specific operators.
587 For more information, see L<DBIx::Class::Manual::Cookbook>.
593 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
594 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
595 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
596 return $class->search($query, { %$attrs });
603 =item Arguments: $first, $last
605 =item Return Value: $resultset (scalar context), @row_objs (list context)
609 Returns a resultset or object list representing a subset of elements from the
610 resultset slice is called on. Indexes are from 0, i.e., to get the first
613 my ($one, $two, $three) = $rs->slice(0, 2);
618 my ($self, $min, $max) = @_;
619 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
620 $attrs->{offset} = $self->{attrs}{offset} || 0;
621 $attrs->{offset} += $min;
622 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
623 return $self->search(undef(), $attrs);
624 #my $slice = (ref $self)->new($self->result_source, $attrs);
625 #return (wantarray ? $slice->all : $slice);
632 =item Arguments: none
634 =item Return Value: $result?
638 Returns the next element in the resultset (C<undef> is there is none).
640 Can be used to efficiently iterate over records in the resultset:
642 my $rs = $schema->resultset('CD')->search;
643 while (my $cd = $rs->next) {
647 Note that you need to store the resultset object, and call C<next> on it.
648 Calling C<< resultset('Table')->next >> repeatedly will always return the
649 first record from the resultset.
655 if (my $cache = $self->get_cache) {
656 $self->{all_cache_position} ||= 0;
657 return $cache->[$self->{all_cache_position}++];
659 if ($self->{attrs}{cache}) {
660 $self->{all_cache_position} = 1;
661 return ($self->all)[0];
664 exists $self->{stashed_row}
665 ? @{delete $self->{stashed_row}}
666 : $self->cursor->next
668 return unless (@row);
669 return $self->_construct_object(@row);
672 sub _construct_object {
673 my ($self, @row) = @_;
674 my $info = $self->_collapse_result($self->{_attrs}{as}, \@row);
675 my $new = $self->result_class->inflate_result($self->result_source, @$info);
676 $new = $self->{_attrs}{record_filter}->($new)
677 if exists $self->{_attrs}{record_filter};
681 sub _collapse_result {
682 my ($self, $as, $row, $prefix) = @_;
687 foreach my $this_as (@$as) {
688 my $val = shift @copy;
689 if (defined $prefix) {
690 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
692 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
693 $const{$1||''}{$2} = $val;
696 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
697 $const{$1||''}{$2} = $val;
701 my $alias = $self->{attrs}{alias};
702 my $info = [ {}, {} ];
703 foreach my $key (keys %const) {
704 if (length $key && $key ne $alias) {
706 my @parts = split(/\./, $key);
707 foreach my $p (@parts) {
708 $target = $target->[1]->{$p} ||= [];
710 $target->[0] = $const{$key};
712 $info->[0] = $const{$key};
717 if (defined $prefix) {
719 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
720 } keys %{$self->{_attrs}{collapse}}
722 @collapse = keys %{$self->{_attrs}{collapse}};
726 my ($c) = sort { length $a <=> length $b } @collapse;
728 foreach my $p (split(/\./, $c)) {
729 $target = $target->[1]->{$p} ||= [];
731 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
732 my @co_key = @{$self->{_attrs}{collapse}{$c_prefix}};
733 my $tree = $self->_collapse_result($as, $row, $c_prefix);
734 my %co_check = map { ($_, $tree->[0]->{$_}); } @co_key;
740 !defined($tree->[0]->{$_}) || $co_check{$_} ne $tree->[0]->{$_}
745 last unless (@raw = $self->cursor->next);
746 $row = $self->{stashed_row} = \@raw;
747 $tree = $self->_collapse_result($as, $row, $c_prefix);
749 @$target = (@final ? @final : [ {}, {} ]);
750 # single empty result to indicate an empty prefetched has_many
753 #print "final info: " . Dumper($info);
761 =item Arguments: $result_source?
763 =item Return Value: $result_source
767 An accessor for the primary ResultSource object from which this ResultSet
774 =item Arguments: $result_class?
776 =item Return Value: $result_class
780 An accessor for the class to use when creating row objects. Defaults to
781 C<< result_source->result_class >> - which in most cases is the name of the
782 L<"table"|DBIx::Class::Manual::Glossary/"ResultSource"> class.
791 =item Arguments: $cond, \%attrs??
793 =item Return Value: $count
797 Performs an SQL C<COUNT> with the same query as the resultset was built
798 with to find the number of elements. If passed arguments, does a search
799 on the resultset and counts the results of that.
801 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
802 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
803 not support C<DISTINCT> with multiple columns. If you are using such a
804 database, you should only use columns from the main table in your C<group_by>
811 return $self->search(@_)->count if @_ and defined $_[0];
812 return scalar @{ $self->get_cache } if $self->get_cache;
813 my $count = $self->_count;
814 return 0 unless $count;
816 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
817 $count = $self->{attrs}{rows} if
818 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
822 sub _count { # Separated out so pager can get the full count
824 my $select = { count => '*' };
826 my $attrs = { %{$self->_resolved_attrs} };
827 if (my $group_by = delete $attrs->{group_by}) {
828 delete $attrs->{having};
829 my @distinct = (ref $group_by ? @$group_by : ($group_by));
830 # todo: try CONCAT for multi-column pk
831 my @pk = $self->result_source->primary_columns;
833 my $alias = $attrs->{alias};
834 foreach my $column (@distinct) {
835 if ($column =~ qr/^(?:\Q${alias}.\E)?$pk[0]$/) {
836 @distinct = ($column);
842 $select = { count => { distinct => \@distinct } };
845 $attrs->{select} = $select;
846 $attrs->{as} = [qw/count/];
848 # offset, order by and page are not needed to count. record_filter is cdbi
849 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
851 my $tmp_rs = (ref $self)->new($self->result_source, $attrs);
852 my ($count) = $tmp_rs->cursor->next;
860 =item Arguments: $sql_fragment, @bind_values
862 =item Return Value: $count
866 Counts the results in a literal query. Equivalent to calling L</search_literal>
867 with the passed arguments, then L</count>.
871 sub count_literal { shift->search_literal(@_)->count; }
877 =item Arguments: none
879 =item Return Value: @objects
883 Returns all elements in the resultset. Called implicitly if the resultset
884 is returned in list context.
890 return @{ $self->get_cache } if $self->get_cache;
894 # TODO: don't call resolve here
895 if (keys %{$self->_resolved_attrs->{collapse}}) {
896 # if ($self->{attrs}{prefetch}) {
897 # Using $self->cursor->all is really just an optimisation.
898 # If we're collapsing has_many prefetches it probably makes
899 # very little difference, and this is cleaner than hacking
900 # _construct_object to survive the approach
901 my @row = $self->cursor->next;
903 push(@obj, $self->_construct_object(@row));
904 @row = (exists $self->{stashed_row}
905 ? @{delete $self->{stashed_row}}
906 : $self->cursor->next);
909 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
912 $self->set_cache(\@obj) if $self->{attrs}{cache};
920 =item Arguments: none
922 =item Return Value: $self
926 Resets the resultset's cursor, so you can iterate through the elements again.
932 delete $self->{_attrs} if exists $self->{_attrs};
933 $self->{all_cache_position} = 0;
934 $self->cursor->reset;
942 =item Arguments: none
944 =item Return Value: $object?
948 Resets the resultset and returns an object for the first result (if the
949 resultset returns anything).
954 return $_[0]->reset->next;
957 # _cond_for_update_delete
959 # update/delete require the condition to be modified to handle
960 # the differing SQL syntax available. This transforms the $self->{cond}
961 # appropriately, returning the new condition.
963 sub _cond_for_update_delete {
967 # No-op. No condition, we're updating/deleting everything
968 return $cond unless ref $self->{cond};
970 if (ref $self->{cond} eq 'ARRAY') {
974 foreach my $key (keys %{$_}) {
976 $hash{$1} = $_->{$key};
982 elsif (ref $self->{cond} eq 'HASH') {
983 if ((keys %{$self->{cond}})[0] eq '-and') {
986 my @cond = @{$self->{cond}{-and}};
987 for (my $i = 0; $i < @cond; $i++) {
988 my $entry = $cond[$i];
991 if (ref $entry eq 'HASH') {
992 foreach my $key (keys %{$entry}) {
994 $hash{$1} = $entry->{$key};
998 $entry =~ /([^.]+)$/;
999 $hash{$1} = $cond[++$i];
1002 push @{$cond->{-and}}, \%hash;
1006 foreach my $key (keys %{$self->{cond}}) {
1008 $cond->{$1} = $self->{cond}{$key};
1013 $self->throw_exception(
1014 "Can't update/delete on resultset with condition unless hash or array"
1026 =item Arguments: \%values
1028 =item Return Value: $storage_rv
1032 Sets the specified columns in the resultset to the supplied values in a
1033 single query. Return value will be true if the update succeeded or false
1034 if no records were updated; exact type of success value is storage-dependent.
1039 my ($self, $values) = @_;
1040 $self->throw_exception("Values for update must be a hash")
1041 unless ref $values eq 'HASH';
1043 my $cond = $self->_cond_for_update_delete;
1045 return $self->result_source->storage->update(
1046 $self->result_source->from, $values, $cond
1054 =item Arguments: \%values
1056 =item Return Value: 1
1060 Fetches all objects and updates them one at a time. Note that C<update_all>
1061 will run DBIC cascade triggers, while L</update> will not.
1066 my ($self, $values) = @_;
1067 $self->throw_exception("Values for update must be a hash")
1068 unless ref $values eq 'HASH';
1069 foreach my $obj ($self->all) {
1070 $obj->set_columns($values)->update;
1079 =item Arguments: none
1081 =item Return Value: 1
1085 Deletes the contents of the resultset from its result source. Note that this
1086 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
1094 my $cond = $self->_cond_for_update_delete;
1096 $self->result_source->storage->delete($self->result_source->from, $cond);
1104 =item Arguments: none
1106 =item Return Value: 1
1110 Fetches all objects and deletes them one at a time. Note that C<delete_all>
1111 will run DBIC cascade triggers, while L</delete> will not.
1117 $_->delete for $self->all;
1125 =item Arguments: none
1127 =item Return Value: $pager
1131 Return Value a L<Data::Page> object for the current resultset. Only makes
1132 sense for queries with a C<page> attribute.
1138 my $attrs = $self->{attrs};
1139 $self->throw_exception("Can't create pager for non-paged rs")
1140 unless $self->{attrs}{page};
1141 $attrs->{rows} ||= 10;
1142 return $self->{pager} ||= Data::Page->new(
1143 $self->_count, $attrs->{rows}, $self->{attrs}{page});
1150 =item Arguments: $page_number
1152 =item Return Value: $rs
1156 Returns a resultset for the $page_number page of the resultset on which page
1157 is called, where each page contains a number of rows equal to the 'rows'
1158 attribute set on the resultset (10 by default).
1163 my ($self, $page) = @_;
1164 return (ref $self)->new($self->result_source, { %{$self->{attrs}}, page => $page });
1171 =item Arguments: \%vals
1173 =item Return Value: $object
1177 Creates an object in the resultset's result class and returns it.
1182 my ($self, $values) = @_;
1183 $self->throw_exception( "new_result needs a hash" )
1184 unless (ref $values eq 'HASH');
1185 $self->throw_exception(
1186 "Can't abstract implicit construct, condition not a hash"
1187 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
1189 my $alias = $self->{attrs}{alias};
1190 foreach my $key (keys %{$self->{cond}||{}}) {
1191 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
1193 my $obj = $self->result_class->new(\%new);
1194 $obj->result_source($self->result_source) if $obj->can('result_source');
1202 =item Arguments: \%vals, \%attrs?
1204 =item Return Value: $object
1208 Find an existing record from this resultset. If none exists, instantiate a new
1209 result object and return it. The object will not be saved into your storage
1210 until you call L<DBIx::Class::Row/insert> on it.
1212 If you want objects to be saved immediately, use L</find_or_create> instead.
1218 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1219 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1220 my $exists = $self->find($hash, $attrs);
1221 return defined $exists ? $exists : $self->new_result($hash);
1228 =item Arguments: \%vals
1230 =item Return Value: $object
1234 Inserts a record into the resultset and returns the object representing it.
1236 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1241 my ($self, $attrs) = @_;
1242 $self->throw_exception( "create needs a hashref" )
1243 unless ref $attrs eq 'HASH';
1244 return $self->new_result($attrs)->insert;
1247 =head2 find_or_create
1251 =item Arguments: \%vals, \%attrs?
1253 =item Return Value: $object
1257 $class->find_or_create({ key => $val, ... });
1259 Tries to find a record based on its primary key or unique constraint; if none
1260 is found, creates one and returns that instead.
1262 my $cd = $schema->resultset('CD')->find_or_create({
1264 artist => 'Massive Attack',
1265 title => 'Mezzanine',
1269 Also takes an optional C<key> attribute, to search by a specific key or unique
1270 constraint. For example:
1272 my $cd = $schema->resultset('CD')->find_or_create(
1274 artist => 'Massive Attack',
1275 title => 'Mezzanine',
1277 { key => 'cd_artist_title' }
1280 See also L</find> and L</update_or_create>. For information on how to declare
1281 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1285 sub find_or_create {
1287 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1288 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1289 my $exists = $self->find($hash, $attrs);
1290 return defined $exists ? $exists : $self->create($hash);
1293 =head2 update_or_create
1297 =item Arguments: \%col_values, { key => $unique_constraint }?
1299 =item Return Value: $object
1303 $class->update_or_create({ col => $val, ... });
1305 First, searches for an existing row matching one of the unique constraints
1306 (including the primary key) on the source of this resultset. If a row is
1307 found, updates it with the other given column values. Otherwise, creates a new
1310 Takes an optional C<key> attribute to search on a specific unique constraint.
1313 # In your application
1314 my $cd = $schema->resultset('CD')->update_or_create(
1316 artist => 'Massive Attack',
1317 title => 'Mezzanine',
1320 { key => 'cd_artist_title' }
1323 If no C<key> is specified, it searches on all unique constraints defined on the
1324 source, including the primary key.
1326 If the C<key> is specified as C<primary>, it searches only on the primary key.
1328 See also L</find> and L</find_or_create>. For information on how to declare
1329 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1333 sub update_or_create {
1335 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1336 my $cond = ref $_[0] eq 'HASH' ? shift : {@_};
1338 my $row = $self->find($cond);
1340 $row->update($cond);
1344 return $self->create($cond);
1351 =item Arguments: none
1353 =item Return Value: \@cache_objects?
1357 Gets the contents of the cache for the resultset, if the cache is set.
1369 =item Arguments: \@cache_objects
1371 =item Return Value: \@cache_objects
1375 Sets the contents of the cache for the resultset. Expects an arrayref
1376 of objects of the same class as those produced by the resultset. Note that
1377 if the cache is set the resultset will return the cached objects rather
1378 than re-querying the database even if the cache attr is not set.
1383 my ( $self, $data ) = @_;
1384 $self->throw_exception("set_cache requires an arrayref")
1385 if defined($data) && (ref $data ne 'ARRAY');
1386 $self->{all_cache} = $data;
1393 =item Arguments: none
1395 =item Return Value: []
1399 Clears the cache for the resultset.
1404 shift->set_cache(undef);
1407 =head2 related_resultset
1411 =item Arguments: $relationship_name
1413 =item Return Value: $resultset
1417 Returns a related resultset for the supplied relationship name.
1419 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1423 sub related_resultset {
1424 my ($self, $rel) = @_;
1426 $self->{related_resultsets} ||= {};
1427 return $self->{related_resultsets}{$rel} ||= do {
1428 my $rel_obj = $self->result_source->relationship_info($rel);
1430 $self->throw_exception(
1431 "search_related: result source '" . $self->result_source->name .
1432 "' has no such relationship $rel")
1435 my ($from,$seen) = $self->_resolve_from($rel);
1437 my $join_count = $seen->{$rel};
1438 my $alias = ($join_count > 1 ? join('_', $rel, $join_count) : $rel);
1440 $self->result_source->schema->resultset($rel_obj->{class})->search_rs(
1442 %{$self->{attrs}||{}},
1448 where => $self->{cond},
1456 my ($self, $extra_join) = @_;
1457 my $source = $self->result_source;
1458 my $attrs = $self->{attrs};
1460 my $from = $attrs->{from}
1461 || [ { $attrs->{alias} => $source->from } ];
1463 my $seen = { %{$attrs->{seen_join}||{}} };
1465 my $join = ($attrs->{join}
1466 ? [ $attrs->{join}, $extra_join ]
1470 ($join ? $source->resolve_join($join, $attrs->{alias}, $seen) : ()),
1473 return ($from,$seen);
1476 sub _resolved_attrs {
1478 return $self->{_attrs} if $self->{_attrs};
1480 my $attrs = { %{$self->{attrs}||{}} };
1481 my $source = $self->{result_source};
1482 my $alias = $attrs->{alias};
1484 $attrs->{columns} ||= delete $attrs->{cols} if exists $attrs->{cols};
1485 if ($attrs->{columns}) {
1486 delete $attrs->{as};
1487 } elsif (!$attrs->{select}) {
1488 $attrs->{columns} = [ $source->columns ];
1493 ? (ref $attrs->{select} eq 'ARRAY'
1494 ? [ @{$attrs->{select}} ]
1495 : [ $attrs->{select} ])
1496 : [ map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}} ]
1500 ? (ref $attrs->{as} eq 'ARRAY'
1501 ? [ @{$attrs->{as}} ]
1503 : [ map { m/^\Q${alias}.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}} ]
1507 if ($adds = delete $attrs->{include_columns}) {
1508 $adds = [$adds] unless ref $adds eq 'ARRAY';
1509 push(@{$attrs->{select}}, @$adds);
1510 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1 } @$adds);
1512 if ($adds = delete $attrs->{'+select'}) {
1513 $adds = [$adds] unless ref $adds eq 'ARRAY';
1514 push(@{$attrs->{select}},
1515 map { /\./ || ref $_ ? $_ : "${alias}.$_" } @$adds);
1517 if (my $adds = delete $attrs->{'+as'}) {
1518 $adds = [$adds] unless ref $adds eq 'ARRAY';
1519 push(@{$attrs->{as}}, @$adds);
1522 $attrs->{from} ||= [ { 'me' => $source->from } ];
1524 if (exists $attrs->{join} || exists $attrs->{prefetch}) {
1525 my $join = delete $attrs->{join} || {};
1527 if (defined $attrs->{prefetch}) {
1528 $join = $self->_merge_attr(
1529 $join, $attrs->{prefetch}
1533 $attrs->{from} = # have to copy here to avoid corrupting the original
1536 $source->resolve_join($join, $alias, { %{$attrs->{seen_join}||{}} })
1540 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
1541 if ($attrs->{order_by}) {
1542 $attrs->{order_by} = (ref($attrs->{order_by}) eq 'ARRAY'
1543 ? [ @{$attrs->{order_by}} ]
1544 : [ $attrs->{order_by} ]);
1546 $attrs->{order_by} = [];
1549 my $collapse = $attrs->{collapse} || {};
1550 if (my $prefetch = delete $attrs->{prefetch}) {
1552 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
1553 # bring joins back to level of current class
1554 my @prefetch = $source->resolve_prefetch(
1555 $p, $alias, { %{$attrs->{seen_join}||{}} }, \@pre_order, $collapse
1557 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
1558 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
1560 push(@{$attrs->{order_by}}, @pre_order);
1562 $attrs->{collapse} = $collapse;
1564 return $self->{_attrs} = $attrs;
1568 my ($self, $a, $b) = @_;
1569 return $b unless $a;
1571 if (ref $b eq 'HASH' && ref $a eq 'HASH') {
1572 foreach my $key (keys %{$b}) {
1573 if (exists $a->{$key}) {
1574 $a->{$key} = $self->_merge_attr($a->{$key}, $b->{$key});
1576 $a->{$key} = $b->{$key};
1581 $a = [$a] unless ref $a eq 'ARRAY';
1582 $b = [$b] unless ref $b eq 'ARRAY';
1586 foreach my $x ($a, $b) {
1587 foreach my $element (@{$x}) {
1588 if (ref $element eq 'HASH') {
1589 $hash = $self->_merge_attr($hash, $element);
1590 } elsif (ref $element eq 'ARRAY') {
1591 push(@array, @{$element});
1593 push(@array, $element) unless $b == $x
1594 && grep { $_ eq $element } @array;
1599 @array = grep { !exists $hash->{$_} } @array;
1601 return keys %{$hash}
1610 =head2 throw_exception
1612 See L<DBIx::Class::Schema/throw_exception> for details.
1616 sub throw_exception {
1618 $self->result_source->schema->throw_exception(@_);
1621 # XXX: FIXME: Attributes docs need clearing up
1625 The resultset takes various attributes that modify its behavior. Here's an
1632 =item Value: ($order_by | \@order_by)
1636 Which column(s) to order the results by. This is currently passed
1637 through directly to SQL, so you can give e.g. C<year DESC> for a
1638 descending order on the column `year'.
1640 Please note that if you have quoting enabled (see
1641 L<DBIx::Class::Storage/quote_char>) you will need to do C<\'year DESC' > to
1642 specify an order. (The scalar ref causes it to be passed as raw sql to the DB,
1643 so you will need to manually quote things as appropriate.)
1649 =item Value: \@columns
1653 Shortcut to request a particular set of columns to be retrieved. Adds
1654 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1655 from that, then auto-populates C<as> from C<select> as normal. (You may also
1656 use the C<cols> attribute, as in earlier versions of DBIC.)
1658 =head2 include_columns
1662 =item Value: \@columns
1666 Shortcut to include additional columns in the returned results - for example
1668 $schema->resultset('CD')->search(undef, {
1669 include_columns => ['artist.name'],
1673 would return all CDs and include a 'name' column to the information
1674 passed to object inflation
1680 =item Value: \@select_columns
1684 Indicates which columns should be selected from the storage. You can use
1685 column names, or in the case of RDBMS back ends, function or stored procedure
1688 $rs = $schema->resultset('Employee')->search(undef, {
1691 { count => 'employeeid' },
1696 When you use function/stored procedure names and do not supply an C<as>
1697 attribute, the column names returned are storage-dependent. E.g. MySQL would
1698 return a column named C<count(employeeid)> in the above example.
1704 Indicates additional columns to be selected from storage. Works the same as
1705 L<select> but adds columns to the selection.
1713 Indicates additional column names for those added via L<+select>.
1721 =item Value: \@inflation_names
1725 Indicates column names for object inflation. This is used in conjunction with
1726 C<select>, usually when C<select> contains one or more function or stored
1729 $rs = $schema->resultset('Employee')->search(undef, {
1732 { count => 'employeeid' }
1734 as => ['name', 'employee_count'],
1737 my $employee = $rs->first(); # get the first Employee
1739 If the object against which the search is performed already has an accessor
1740 matching a column name specified in C<as>, the value can be retrieved using
1741 the accessor as normal:
1743 my $name = $employee->name();
1745 If on the other hand an accessor does not exist in the object, you need to
1746 use C<get_column> instead:
1748 my $employee_count = $employee->get_column('employee_count');
1750 You can create your own accessors if required - see
1751 L<DBIx::Class::Manual::Cookbook> for details.
1753 Please note: This will NOT insert an C<AS employee_count> into the SQL statement
1754 produced, it is used for internal access only. Thus attempting to use the accessor
1755 in an C<order_by> clause or similar will fail misrably.
1761 =item Value: ($rel_name | \@rel_names | \%rel_names)
1765 Contains a list of relationships that should be joined for this query. For
1768 # Get CDs by Nine Inch Nails
1769 my $rs = $schema->resultset('CD')->search(
1770 { 'artist.name' => 'Nine Inch Nails' },
1771 { join => 'artist' }
1774 Can also contain a hash reference to refer to the other relation's relations.
1777 package MyApp::Schema::Track;
1778 use base qw/DBIx::Class/;
1779 __PACKAGE__->table('track');
1780 __PACKAGE__->add_columns(qw/trackid cd position title/);
1781 __PACKAGE__->set_primary_key('trackid');
1782 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1785 # In your application
1786 my $rs = $schema->resultset('Artist')->search(
1787 { 'track.title' => 'Teardrop' },
1789 join => { cd => 'track' },
1790 order_by => 'artist.name',
1794 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1795 similarly for a third time). For e.g.
1797 my $rs = $schema->resultset('Artist')->search({
1798 'cds.title' => 'Down to Earth',
1799 'cds_2.title' => 'Popular',
1801 join => [ qw/cds cds/ ],
1804 will return a set of all artists that have both a cd with title 'Down
1805 to Earth' and a cd with title 'Popular'.
1807 If you want to fetch related objects from other tables as well, see C<prefetch>
1814 =item Value: ($rel_name | \@rel_names | \%rel_names)
1818 Contains one or more relationships that should be fetched along with the main
1819 query (when they are accessed afterwards they will have already been
1820 "prefetched"). This is useful for when you know you will need the related
1821 objects, because it saves at least one query:
1823 my $rs = $schema->resultset('Tag')->search(
1832 The initial search results in SQL like the following:
1834 SELECT tag.*, cd.*, artist.* FROM tag
1835 JOIN cd ON tag.cd = cd.cdid
1836 JOIN artist ON cd.artist = artist.artistid
1838 L<DBIx::Class> has no need to go back to the database when we access the
1839 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1842 Simple prefetches will be joined automatically, so there is no need
1843 for a C<join> attribute in the above search. If you're prefetching to
1844 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1845 specify the join as well.
1847 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1848 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1849 with an accessor type of 'single' or 'filter').
1859 Makes the resultset paged and specifies the page to retrieve. Effectively
1860 identical to creating a non-pages resultset and then calling ->page($page)
1863 If L<rows> attribute is not specified it defualts to 10 rows per page.
1873 Specifes the maximum number of rows for direct retrieval or the number of
1874 rows per page if the page attribute or method is used.
1880 =item Value: $offset
1884 Specifies the (zero-based) row number for the first row to be returned, or the
1885 of the first row of the first page if paging is used.
1891 =item Value: \@columns
1895 A arrayref of columns to group by. Can include columns of joined tables.
1897 group_by => [qw/ column1 column2 ... /]
1903 =item Value: $condition
1907 HAVING is a select statement attribute that is applied between GROUP BY and
1908 ORDER BY. It is applied to the after the grouping calculations have been
1911 having => { 'count(employee)' => { '>=', 100 } }
1917 =item Value: (0 | 1)
1921 Set to 1 to group by all columns.
1927 Adds to the WHERE clause.
1929 # only return rows WHERE deleted IS NULL for all searches
1930 __PACKAGE__->resultset_attributes({ where => { deleted => undef } }); )
1932 Can be overridden by passing C<{ where => undef }> as an attribute
1939 Set to 1 to cache search results. This prevents extra SQL queries if you
1940 revisit rows in your ResultSet:
1942 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1944 while( my $artist = $resultset->next ) {
1948 $rs->first; # without cache, this would issue a query
1950 By default, searches are not cached.
1952 For more examples of using these attributes, see
1953 L<DBIx::Class::Manual::Cookbook>.
1959 =item Value: \@from_clause
1963 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1964 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1967 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1969 C<join> will usually do what you need and it is strongly recommended that you
1970 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1971 And we really do mean "cannot", not just tried and failed. Attempting to use
1972 this because you're having problems with C<join> is like trying to use x86
1973 ASM because you've got a syntax error in your C. Trust us on this.
1975 Now, if you're still really, really sure you need to use this (and if you're
1976 not 100% sure, ask the mailing list first), here's an explanation of how this
1979 The syntax is as follows -
1982 { <alias1> => <table1> },
1984 { <alias2> => <table2>, -join_type => 'inner|left|right' },
1985 [], # nested JOIN (optional)
1986 { <table1.column1> => <table2.column2>, ... (more conditions) },
1988 # More of the above [ ] may follow for additional joins
1995 ON <table1.column1> = <table2.column2>
1996 <more joins may follow>
1998 An easy way to follow the examples below is to remember the following:
2000 Anything inside "[]" is a JOIN
2001 Anything inside "{}" is a condition for the enclosing JOIN
2003 The following examples utilize a "person" table in a family tree application.
2004 In order to express parent->child relationships, this table is self-joined:
2006 # Person->belongs_to('father' => 'Person');
2007 # Person->belongs_to('mother' => 'Person');
2009 C<from> can be used to nest joins. Here we return all children with a father,
2010 then search against all mothers of those children:
2012 $rs = $schema->resultset('Person')->search(
2015 alias => 'mother', # alias columns in accordance with "from"
2017 { mother => 'person' },
2020 { child => 'person' },
2022 { father => 'person' },
2023 { 'father.person_id' => 'child.father_id' }
2026 { 'mother.person_id' => 'child.mother_id' }
2033 # SELECT mother.* FROM person mother
2036 # JOIN person father
2037 # ON ( father.person_id = child.father_id )
2039 # ON ( mother.person_id = child.mother_id )
2041 The type of any join can be controlled manually. To search against only people
2042 with a father in the person table, we could explicitly use C<INNER JOIN>:
2044 $rs = $schema->resultset('Person')->search(
2047 alias => 'child', # alias columns in accordance with "from"
2049 { child => 'person' },
2051 { father => 'person', -join_type => 'inner' },
2052 { 'father.id' => 'child.father_id' }
2059 # SELECT child.* FROM person child
2060 # INNER JOIN person father ON child.father_id = father.id