1 package DBIx::Class::ResultSet;
11 use Scalar::Util qw/weaken/;
13 use base qw/DBIx::Class/;
14 __PACKAGE__->load_components(qw/AccessorGroup/);
15 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
19 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
23 my $rs = $schema->resultset('User')->search(registered => 1);
24 my @rows = $schema->resultset('CD')->search(year => 2005);
28 The resultset is also known as an iterator. It is responsible for handling
29 queries that may return an arbitrary number of rows, e.g. via L</search>
30 or a C<has_many> relationship.
32 In the examples below, the following table classes are used:
34 package MyApp::Schema::Artist;
35 use base qw/DBIx::Class/;
36 __PACKAGE__->load_components(qw/Core/);
37 __PACKAGE__->table('artist');
38 __PACKAGE__->add_columns(qw/artistid name/);
39 __PACKAGE__->set_primary_key('artistid');
40 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
43 package MyApp::Schema::CD;
44 use base qw/DBIx::Class/;
45 __PACKAGE__->load_components(qw/Core/);
46 __PACKAGE__->table('cd');
47 __PACKAGE__->add_columns(qw/cdid artist title year/);
48 __PACKAGE__->set_primary_key('cdid');
49 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
58 =item Arguments: $source, \%$attrs
60 =item Return Value: $rs
64 The resultset constructor. Takes a source object (usually a
65 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
66 L</ATTRIBUTES> below). Does not perform any queries -- these are
67 executed as needed by the other methods.
69 Generally you won't need to construct a resultset manually. You'll
70 automatically get one from e.g. a L</search> called in scalar context:
72 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
74 IMPORTANT: If called on an object, proxies to new_result instead so
76 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
78 will return a CD object, not a ResultSet.
84 return $class->new_result(@_) if ref $class;
86 my ($source, $attrs) = @_;
88 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
89 #use Data::Dumper; warn Dumper($attrs);
90 my $alias = ($attrs->{alias} ||= 'me');
92 $attrs->{columns} ||= delete $attrs->{cols} if $attrs->{cols};
93 delete $attrs->{as} if $attrs->{columns};
94 $attrs->{columns} ||= [ $source->columns ] unless $attrs->{select};
96 map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}}
97 ] if $attrs->{columns};
99 map { m/^\Q$alias.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}}
101 if (my $include = delete $attrs->{include_columns}) {
102 push(@{$attrs->{select}}, @$include);
103 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1; } @$include);
105 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
107 $attrs->{from} ||= [ { $alias => $source->from } ];
108 $attrs->{seen_join} ||= {};
110 if (my $join = delete $attrs->{join}) {
111 foreach my $j (ref $join eq 'ARRAY' ? @$join : ($join)) {
112 if (ref $j eq 'HASH') {
113 $seen{$_} = 1 foreach keys %$j;
118 push(@{$attrs->{from}}, $source->resolve_join(
119 $join, $attrs->{alias}, $attrs->{seen_join})
123 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
124 $attrs->{order_by} = [ $attrs->{order_by} ] if
125 $attrs->{order_by} and !ref($attrs->{order_by});
126 $attrs->{order_by} ||= [];
128 my $collapse = $attrs->{collapse} || {};
129 if (my $prefetch = delete $attrs->{prefetch}) {
131 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
132 if ( ref $p eq 'HASH' ) {
133 foreach my $key (keys %$p) {
134 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
138 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
141 my @prefetch = $source->resolve_prefetch(
142 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
143 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
144 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
146 push(@{$attrs->{order_by}}, @pre_order);
148 $attrs->{collapse} = $collapse;
149 # use Data::Dumper; warn Dumper($collapse) if keys %{$collapse};
151 if ($attrs->{page}) {
152 $attrs->{rows} ||= 10;
153 $attrs->{offset} ||= 0;
154 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
158 result_source => $source,
159 result_class => $attrs->{result_class} || $source->result_class,
160 cond => $attrs->{where},
161 from => $attrs->{from},
162 collapse => $collapse,
164 page => delete $attrs->{page},
174 =item Arguments: $cond, \%attrs?
176 =item Return Value: $resultset (scalar context), @row_objs (list context)
180 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
181 my $new_rs = $cd_rs->search({ year => 2005 });
183 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
184 # year = 2005 OR year = 2004
186 If you need to pass in additional attributes but no additional condition,
187 call it as C<search(undef, \%attrs)>.
189 # "SELECT name, artistid FROM $artist_table"
190 my @all_artists = $schema->resultset('Artist')->search(undef, {
191 columns => [qw/name artistid/],
202 my $attrs = { %{$self->{attrs}} };
203 my $having = delete $attrs->{having};
204 $attrs = { %$attrs, %{ pop(@_) } } if @_ > 1 and ref $_[$#_] eq 'HASH';
207 ? ((@_ == 1 || ref $_[0] eq "HASH")
210 ? $self->throw_exception(
211 "Odd number of arguments to search")
214 if (defined $where) {
215 $attrs->{where} = (defined $attrs->{where}
217 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
218 $where, $attrs->{where} ] }
222 if (defined $having) {
223 $attrs->{having} = (defined $attrs->{having}
225 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
226 $having, $attrs->{having} ] }
230 $rs = (ref $self)->new($self->result_source, $attrs);
236 return (wantarray ? $rs->all : $rs);
239 =head2 search_literal
243 =item Arguments: $sql_fragment, @bind_values
245 =item Return Value: $resultset (scalar context), @row_objs (list context)
249 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
250 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
252 Pass a literal chunk of SQL to be added to the conditional part of the
258 my ($self, $cond, @vals) = @_;
259 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
260 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
261 return $self->search(\$cond, $attrs);
268 =item Arguments: @values | \%cols, \%attrs?
270 =item Return Value: $row_object
274 Finds a row based on its primary key or unique constraint. For example:
276 my $cd = $schema->resultset('CD')->find(5);
278 Also takes an optional C<key> attribute, to search by a specific key or unique
279 constraint. For example:
281 my $cd = $schema->resultset('CD')->find(
283 artist => 'Massive Attack',
284 title => 'Mezzanine',
286 { key => 'artist_title' }
289 See also L</find_or_create> and L</update_or_create>.
294 my ($self, @vals) = @_;
295 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
297 my @cols = $self->result_source->primary_columns;
298 if (exists $attrs->{key}) {
299 my %uniq = $self->result_source->unique_constraints;
300 $self->throw_exception(
301 "Unknown key $attrs->{key} on '" . $self->result_source->name . "'"
302 ) unless exists $uniq{$attrs->{key}};
303 @cols = @{ $uniq{$attrs->{key}} };
305 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
306 $self->throw_exception(
307 "Can't find unless a primary key or unique constraint is defined"
311 if (ref $vals[0] eq 'HASH') {
312 $query = { %{$vals[0]} };
313 } elsif (@cols == @vals) {
315 @{$query}{@cols} = @vals;
319 foreach my $key (grep { ! m/\./ } keys %$query) {
320 $query->{"$self->{attrs}{alias}.$key"} = delete $query->{$key};
322 #warn Dumper($query);
325 my $rs = $self->search($query,$attrs);
326 return keys %{$rs->{collapse}} ? $rs->next : $rs->single;
328 return keys %{$self->{collapse}} ?
329 $self->search($query)->next :
330 $self->single($query);
334 =head2 search_related
338 =item Arguments: $cond, \%attrs?
340 =item Return Value: $new_resultset
344 $new_rs = $cd_rs->search_related('artist', {
348 Searches the specified relationship, optionally specifying a condition and
349 attributes for matching records. See L</ATTRIBUTES> for more information.
354 return shift->related_resultset(shift)->search(@_);
361 =item Arguments: none
363 =item Return Value: $cursor
367 Returns a storage-driven cursor to the given resultset. See
368 L<DBIx::Class::Cursor> for more information.
374 my $attrs = { %{$self->{attrs}} };
375 return $self->{cursor}
376 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
377 $attrs->{where},$attrs);
384 =item Arguments: $cond?
386 =item Return Value: $row_object?
390 my $cd = $schema->resultset('CD')->single({ year => 2001 });
392 Inflates the first result without creating a cursor if the resultset has
393 any records in it; if not returns nothing. Used by find() as an optimisation.
398 my ($self, $where) = @_;
399 my $attrs = { %{$self->{attrs}} };
401 if (defined $attrs->{where}) {
404 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
405 $where, delete $attrs->{where} ]
408 $attrs->{where} = $where;
411 my @data = $self->result_source->storage->select_single(
412 $self->{from}, $attrs->{select},
413 $attrs->{where},$attrs);
414 return (@data ? $self->_construct_object(@data) : ());
422 =item Arguments: $cond, \%attrs?
424 =item Return Value: $resultset (scalar context), @row_objs (list context)
428 # WHERE title LIKE '%blue%'
429 $cd_rs = $rs->search_like({ title => '%blue%'});
431 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
432 that this is simply a convenience method. You most likely want to use
433 L</search> with specific operators.
435 For more information, see L<DBIx::Class::Manual::Cookbook>.
441 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
442 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
443 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
444 return $class->search($query, { %$attrs });
451 =item Arguments: $first, $last
453 =item Return Value: $resultset (scalar context), @row_objs (list context)
457 Returns a resultset or object list representing a subset of elements from the
458 resultset slice is called on. Indexes are from 0, i.e., to get the first
461 my ($one, $two, $three) = $rs->slice(0, 2);
466 my ($self, $min, $max) = @_;
467 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
468 $attrs->{offset} = $self->{attrs}{offset} || 0;
469 $attrs->{offset} += $min;
470 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
471 return $self->search(undef(), $attrs);
472 #my $slice = (ref $self)->new($self->result_source, $attrs);
473 #return (wantarray ? $slice->all : $slice);
480 =item Arguments: none
482 =item Return Value: $result?
486 Returns the next element in the resultset (C<undef> is there is none).
488 Can be used to efficiently iterate over records in the resultset:
490 my $rs = $schema->resultset('CD')->search;
491 while (my $cd = $rs->next) {
495 Note that you need to store the resultset object, and call C<next> on it.
496 Calling C<< resultset('Table')->next >> repeatedly will always return the
497 first record from the resultset.
503 if (@{$self->{all_cache} || []}) {
504 $self->{all_cache_position} ||= 0;
505 return $self->{all_cache}->[$self->{all_cache_position}++];
507 if ($self->{attrs}{cache}) {
508 $self->{all_cache_position} = 1;
509 return ($self->all)[0];
511 my @row = (exists $self->{stashed_row} ?
512 @{delete $self->{stashed_row}} :
515 # warn Dumper(\@row); use Data::Dumper;
516 return unless (@row);
517 return $self->_construct_object(@row);
520 sub _construct_object {
521 my ($self, @row) = @_;
522 my @as = @{ $self->{attrs}{as} };
524 my $info = $self->_collapse_result(\@as, \@row);
526 my $new = $self->result_class->inflate_result($self->result_source, @$info);
528 $new = $self->{attrs}{record_filter}->($new)
529 if exists $self->{attrs}{record_filter};
533 sub _collapse_result {
534 my ($self, $as, $row, $prefix) = @_;
539 foreach my $this_as (@$as) {
540 my $val = shift @copy;
541 if (defined $prefix) {
542 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
544 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
545 $const{$1||''}{$2} = $val;
548 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
549 $const{$1||''}{$2} = $val;
553 my $info = [ {}, {} ];
554 foreach my $key (keys %const) {
557 my @parts = split(/\./, $key);
558 foreach my $p (@parts) {
559 $target = $target->[1]->{$p} ||= [];
561 $target->[0] = $const{$key};
563 $info->[0] = $const{$key};
568 if (defined $prefix) {
570 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
571 } keys %{$self->{collapse}}
573 @collapse = keys %{$self->{collapse}};
577 my ($c) = sort { length $a <=> length $b } @collapse;
579 foreach my $p (split(/\./, $c)) {
580 $target = $target->[1]->{$p} ||= [];
582 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
583 my @co_key = @{$self->{collapse}{$c_prefix}};
584 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
585 my $tree = $self->_collapse_result($as, $row, $c_prefix);
588 !defined($tree->[0]->{$_}) ||
589 $co_check{$_} ne $tree->[0]->{$_}
592 last unless (@raw = $self->cursor->next);
593 $row = $self->{stashed_row} = \@raw;
594 $tree = $self->_collapse_result($as, $row, $c_prefix);
595 #warn Data::Dumper::Dumper($tree, $row);
607 =item Arguments: $result_source?
609 =item Return Value: $result_source
613 An accessor for the primary ResultSource object from which this ResultSet
623 =item Arguments: $cond, \%attrs??
625 =item Return Value: $count
629 Performs an SQL C<COUNT> with the same query as the resultset was built
630 with to find the number of elements. If passed arguments, does a search
631 on the resultset and counts the results of that.
633 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
634 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
635 not support C<DISTINCT> with multiple columns. If you are using such a
636 database, you should only use columns from the main table in your C<group_by>
643 return $self->search(@_)->count if @_ and defined $_[0];
644 return scalar @{ $self->get_cache } if @{ $self->get_cache };
646 my $count = $self->_count;
647 return 0 unless $count;
649 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
650 $count = $self->{attrs}{rows} if
651 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
655 sub _count { # Separated out so pager can get the full count
657 my $select = { count => '*' };
658 my $attrs = { %{ $self->{attrs} } };
659 if (my $group_by = delete $attrs->{group_by}) {
660 delete $attrs->{having};
661 my @distinct = (ref $group_by ? @$group_by : ($group_by));
662 # todo: try CONCAT for multi-column pk
663 my @pk = $self->result_source->primary_columns;
665 foreach my $column (@distinct) {
666 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
667 @distinct = ($column);
673 $select = { count => { distinct => \@distinct } };
674 #use Data::Dumper; die Dumper $select;
677 $attrs->{select} = $select;
678 $attrs->{as} = [qw/count/];
680 # offset, order by and page are not needed to count. record_filter is cdbi
681 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
683 my ($count) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
691 =item Arguments: $sql_fragment, @bind_values
693 =item Return Value: $count
697 Counts the results in a literal query. Equivalent to calling L</search_literal>
698 with the passed arguments, then L</count>.
702 sub count_literal { shift->search_literal(@_)->count; }
708 =item Arguments: none
710 =item Return Value: @objects
714 Returns all elements in the resultset. Called implicitly if the resultset
715 is returned in list context.
721 return @{ $self->get_cache } if @{ $self->get_cache };
725 if (keys %{$self->{collapse}}) {
726 # Using $self->cursor->all is really just an optimisation.
727 # If we're collapsing has_many prefetches it probably makes
728 # very little difference, and this is cleaner than hacking
729 # _construct_object to survive the approach
730 $self->cursor->reset;
731 my @row = $self->cursor->next;
733 push(@obj, $self->_construct_object(@row));
734 @row = (exists $self->{stashed_row}
735 ? @{delete $self->{stashed_row}}
736 : $self->cursor->next);
739 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
742 $self->set_cache(\@obj) if $self->{attrs}{cache};
750 =item Arguments: none
752 =item Return Value: $self
756 Resets the resultset's cursor, so you can iterate through the elements again.
762 $self->{all_cache_position} = 0;
763 $self->cursor->reset;
771 =item Arguments: none
773 =item Return Value: $object?
777 Resets the resultset and returns an object for the first result (if the
778 resultset returns anything).
783 return $_[0]->reset->next;
790 =item Arguments: \%values
792 =item Return Value: $storage_rv
796 Sets the specified columns in the resultset to the supplied values in a
797 single query. Return value will be true if the update succeeded or false
798 if no records were updated; exact type of success value is storage-dependent.
803 my ($self, $values) = @_;
804 $self->throw_exception("Values for update must be a hash")
805 unless ref $values eq 'HASH';
806 return $self->result_source->storage->update(
807 $self->result_source->from, $values, $self->{cond}
815 =item Arguments: \%values
817 =item Return Value: 1
821 Fetches all objects and updates them one at a time. Note that C<update_all>
822 will run DBIC cascade triggers, while L</update> will not.
827 my ($self, $values) = @_;
828 $self->throw_exception("Values for update must be a hash")
829 unless ref $values eq 'HASH';
830 foreach my $obj ($self->all) {
831 $obj->set_columns($values)->update;
840 =item Arguments: none
842 =item Return Value: 1
846 Deletes the contents of the resultset from its result source. Note that this
847 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
856 if (!ref($self->{cond})) {
858 # No-op. No condition, we're deleting everything
860 } elsif (ref $self->{cond} eq 'ARRAY') {
862 $del = [ map { my %hash;
863 foreach my $key (keys %{$_}) {
865 $hash{$1} = $_->{$key};
866 }; \%hash; } @{$self->{cond}} ];
868 } elsif (ref $self->{cond} eq 'HASH') {
870 if ((keys %{$self->{cond}})[0] eq '-and') {
872 $del->{-and} = [ map { my %hash;
873 foreach my $key (keys %{$_}) {
875 $hash{$1} = $_->{$key};
876 }; \%hash; } @{$self->{cond}{-and}} ];
880 foreach my $key (keys %{$self->{cond}}) {
882 $del->{$1} = $self->{cond}{$key};
887 $self->throw_exception(
888 "Can't delete on resultset with condition unless hash or array"
892 $self->result_source->storage->delete($self->result_source->from, $del);
900 =item Arguments: none
902 =item Return Value: 1
906 Fetches all objects and deletes them one at a time. Note that C<delete_all>
907 will run DBIC cascade triggers, while L</delete> will not.
913 $_->delete for $self->all;
921 =item Arguments: none
923 =item Return Value: $pager
927 Return Value a L<Data::Page> object for the current resultset. Only makes
928 sense for queries with a C<page> attribute.
934 my $attrs = $self->{attrs};
935 $self->throw_exception("Can't create pager for non-paged rs")
936 unless $self->{page};
937 $attrs->{rows} ||= 10;
938 return $self->{pager} ||= Data::Page->new(
939 $self->_count, $attrs->{rows}, $self->{page});
946 =item Arguments: $page_number
948 =item Return Value: $rs
952 Returns a resultset for the $page_number page of the resultset on which page
953 is called, where each page contains a number of rows equal to the 'rows'
954 attribute set on the resultset (10 by default).
959 my ($self, $page) = @_;
960 my $attrs = { %{$self->{attrs}} };
961 $attrs->{page} = $page;
962 return (ref $self)->new($self->result_source, $attrs);
969 =item Arguments: \%vals
971 =item Return Value: $object
975 Creates an object in the resultset's result class and returns it.
980 my ($self, $values) = @_;
981 $self->throw_exception( "new_result needs a hash" )
982 unless (ref $values eq 'HASH');
983 $self->throw_exception(
984 "Can't abstract implicit construct, condition not a hash"
985 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
987 my $alias = $self->{attrs}{alias};
988 foreach my $key (keys %{$self->{cond}||{}}) {
989 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
991 my $obj = $self->result_class->new(\%new);
992 $obj->result_source($self->result_source) if $obj->can('result_source');
1000 =item Arguments: \%vals
1002 =item Return Value: $object
1006 Inserts a record into the resultset and returns the object representing it.
1008 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1013 my ($self, $attrs) = @_;
1014 $self->throw_exception( "create needs a hashref" )
1015 unless ref $attrs eq 'HASH';
1016 return $self->new_result($attrs)->insert;
1019 =head2 find_or_create
1023 =item Arguments: \%vals, \%attrs?
1025 =item Return Value: $object
1029 $class->find_or_create({ key => $val, ... });
1031 Searches for a record matching the search condition; if it doesn't find one,
1032 creates one and returns that instead.
1034 my $cd = $schema->resultset('CD')->find_or_create({
1036 artist => 'Massive Attack',
1037 title => 'Mezzanine',
1041 Also takes an optional C<key> attribute, to search by a specific key or unique
1042 constraint. For example:
1044 my $cd = $schema->resultset('CD')->find_or_create(
1046 artist => 'Massive Attack',
1047 title => 'Mezzanine',
1049 { key => 'artist_title' }
1052 See also L</find> and L</update_or_create>.
1056 sub find_or_create {
1058 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1059 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1060 my $exists = $self->find($hash, $attrs);
1061 return defined $exists ? $exists : $self->create($hash);
1064 =head2 update_or_create
1068 =item Arguments: \%col_values, { key => $unique_constraint }?
1070 =item Return Value: $object
1074 $class->update_or_create({ col => $val, ... });
1076 First, searches for an existing row matching one of the unique constraints
1077 (including the primary key) on the source of this resultset. If a row is
1078 found, updates it with the other given column values. Otherwise, creates a new
1081 Takes an optional C<key> attribute to search on a specific unique constraint.
1084 # In your application
1085 my $cd = $schema->resultset('CD')->update_or_create(
1087 artist => 'Massive Attack',
1088 title => 'Mezzanine',
1091 { key => 'artist_title' }
1094 If no C<key> is specified, it searches on all unique constraints defined on the
1095 source, including the primary key.
1097 If the C<key> is specified as C<primary>, it searches only on the primary key.
1099 See also L</find> and L</find_or_create>.
1103 sub update_or_create {
1105 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1106 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1108 my %unique_constraints = $self->result_source->unique_constraints;
1109 my @constraint_names = (exists $attrs->{key}
1111 : keys %unique_constraints);
1114 foreach my $name (@constraint_names) {
1115 my @unique_cols = @{ $unique_constraints{$name} };
1117 map { $_ => $hash->{$_} }
1118 grep { exists $hash->{$_} }
1121 push @unique_hashes, \%unique_hash
1122 if (scalar keys %unique_hash == scalar @unique_cols);
1125 if (@unique_hashes) {
1126 my $row = $self->single(\@unique_hashes);
1128 $row->set_columns($hash);
1134 return $self->create($hash);
1141 =item Arguments: none
1143 =item Return Value: \@cache_objects?
1147 Gets the contents of the cache for the resultset, if the cache is set.
1152 shift->{all_cache} || [];
1159 =item Arguments: \@cache_objects
1161 =item Return Value: \@cache_objects
1165 Sets the contents of the cache for the resultset. Expects an arrayref
1166 of objects of the same class as those produced by the resultset. Note that
1167 if the cache is set the resultset will return the cached objects rather
1168 than re-querying the database even if the cache attr is not set.
1173 my ( $self, $data ) = @_;
1174 $self->throw_exception("set_cache requires an arrayref")
1175 if ref $data ne 'ARRAY';
1176 my $result_class = $self->result_class;
1178 $self->throw_exception(
1179 "cannot cache object of type '$_', expected '$result_class'"
1180 ) if ref $_ ne $result_class;
1182 $self->{all_cache} = $data;
1189 =item Arguments: none
1191 =item Return Value: []
1195 Clears the cache for the resultset.
1200 shift->set_cache([]);
1203 =head2 related_resultset
1207 =item Arguments: $relationship_name
1209 =item Return Value: $resultset
1213 Returns a related resultset for the supplied relationship name.
1215 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1219 sub related_resultset {
1220 my ( $self, $rel ) = @_;
1221 $self->{related_resultsets} ||= {};
1222 return $self->{related_resultsets}{$rel} ||= do {
1223 #warn "fetching related resultset for rel '$rel'";
1224 my $rel_obj = $self->result_source->relationship_info($rel);
1225 $self->throw_exception(
1226 "search_related: result source '" . $self->result_source->name .
1227 "' has no such relationship ${rel}")
1228 unless $rel_obj; #die Dumper $self->{attrs};
1230 my $rs = $self->search(undef, { join => $rel });
1231 my $alias = defined $rs->{attrs}{seen_join}{$rel}
1232 && $rs->{attrs}{seen_join}{$rel} > 1
1233 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
1236 $self->result_source->schema->resultset($rel_obj->{class}
1246 =head2 throw_exception
1248 See L<DBIx::Class::Schema/throw_exception> for details.
1252 sub throw_exception {
1254 $self->result_source->schema->throw_exception(@_);
1257 # XXX: FIXME: Attributes docs need clearing up
1261 The resultset takes various attributes that modify its behavior. Here's an
1268 =item Value: ($order_by | \@order_by)
1272 Which column(s) to order the results by. This is currently passed
1273 through directly to SQL, so you can give e.g. C<year DESC> for a
1274 descending order on the column `year'.
1280 =item Value: \@columns
1284 Shortcut to request a particular set of columns to be retrieved. Adds
1285 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1286 from that, then auto-populates C<as> from C<select> as normal. (You may also
1287 use the C<cols> attribute, as in earlier versions of DBIC.)
1289 =head2 include_columns
1293 =item Value: \@columns
1297 Shortcut to include additional columns in the returned results - for example
1299 $schema->resultset('CD')->search(undef, {
1300 include_columns => ['artist.name'],
1304 would return all CDs and include a 'name' column to the information
1305 passed to object inflation
1311 =item Value: \@select_columns
1315 Indicates which columns should be selected from the storage. You can use
1316 column names, or in the case of RDBMS back ends, function or stored procedure
1319 $rs = $schema->resultset('Employee')->search(undef, {
1322 { count => 'employeeid' },
1327 When you use function/stored procedure names and do not supply an C<as>
1328 attribute, the column names returned are storage-dependent. E.g. MySQL would
1329 return a column named C<count(employeeid)> in the above example.
1335 =item Value: \@inflation_names
1339 Indicates column names for object inflation. This is used in conjunction with
1340 C<select>, usually when C<select> contains one or more function or stored
1343 $rs = $schema->resultset('Employee')->search(undef, {
1346 { count => 'employeeid' }
1348 as => ['name', 'employee_count'],
1351 my $employee = $rs->first(); # get the first Employee
1353 If the object against which the search is performed already has an accessor
1354 matching a column name specified in C<as>, the value can be retrieved using
1355 the accessor as normal:
1357 my $name = $employee->name();
1359 If on the other hand an accessor does not exist in the object, you need to
1360 use C<get_column> instead:
1362 my $employee_count = $employee->get_column('employee_count');
1364 You can create your own accessors if required - see
1365 L<DBIx::Class::Manual::Cookbook> for details.
1371 =item Value: ($rel_name | \@rel_names | \%rel_names)
1375 Contains a list of relationships that should be joined for this query. For
1378 # Get CDs by Nine Inch Nails
1379 my $rs = $schema->resultset('CD')->search(
1380 { 'artist.name' => 'Nine Inch Nails' },
1381 { join => 'artist' }
1384 Can also contain a hash reference to refer to the other relation's relations.
1387 package MyApp::Schema::Track;
1388 use base qw/DBIx::Class/;
1389 __PACKAGE__->table('track');
1390 __PACKAGE__->add_columns(qw/trackid cd position title/);
1391 __PACKAGE__->set_primary_key('trackid');
1392 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1395 # In your application
1396 my $rs = $schema->resultset('Artist')->search(
1397 { 'track.title' => 'Teardrop' },
1399 join => { cd => 'track' },
1400 order_by => 'artist.name',
1404 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1405 similarly for a third time). For e.g.
1407 my $rs = $schema->resultset('Artist')->search({
1408 'cds.title' => 'Down to Earth',
1409 'cds_2.title' => 'Popular',
1411 join => [ qw/cds cds/ ],
1414 will return a set of all artists that have both a cd with title 'Down
1415 to Earth' and a cd with title 'Popular'.
1417 If you want to fetch related objects from other tables as well, see C<prefetch>
1424 =item Value: ($rel_name | \@rel_names | \%rel_names)
1428 Contains one or more relationships that should be fetched along with the main
1429 query (when they are accessed afterwards they will have already been
1430 "prefetched"). This is useful for when you know you will need the related
1431 objects, because it saves at least one query:
1433 my $rs = $schema->resultset('Tag')->search(
1442 The initial search results in SQL like the following:
1444 SELECT tag.*, cd.*, artist.* FROM tag
1445 JOIN cd ON tag.cd = cd.cdid
1446 JOIN artist ON cd.artist = artist.artistid
1448 L<DBIx::Class> has no need to go back to the database when we access the
1449 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1452 Simple prefetches will be joined automatically, so there is no need
1453 for a C<join> attribute in the above search. If you're prefetching to
1454 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1455 specify the join as well.
1457 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1458 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1459 with an accessor type of 'single' or 'filter').
1465 =item Value: \@from_clause
1469 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1470 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1473 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1474 C<join> will usually do what you need and it is strongly recommended that you
1475 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1477 In simple terms, C<from> works as follows:
1480 { <alias> => <table>, -join_type => 'inner|left|right' }
1481 [] # nested JOIN (optional)
1482 { <table.column> => <foreign_table.foreign_key> }
1488 ON <table.column> = <foreign_table.foreign_key>
1490 An easy way to follow the examples below is to remember the following:
1492 Anything inside "[]" is a JOIN
1493 Anything inside "{}" is a condition for the enclosing JOIN
1495 The following examples utilize a "person" table in a family tree application.
1496 In order to express parent->child relationships, this table is self-joined:
1498 # Person->belongs_to('father' => 'Person');
1499 # Person->belongs_to('mother' => 'Person');
1501 C<from> can be used to nest joins. Here we return all children with a father,
1502 then search against all mothers of those children:
1504 $rs = $schema->resultset('Person')->search(
1507 alias => 'mother', # alias columns in accordance with "from"
1509 { mother => 'person' },
1512 { child => 'person' },
1514 { father => 'person' },
1515 { 'father.person_id' => 'child.father_id' }
1518 { 'mother.person_id' => 'child.mother_id' }
1525 # SELECT mother.* FROM person mother
1528 # JOIN person father
1529 # ON ( father.person_id = child.father_id )
1531 # ON ( mother.person_id = child.mother_id )
1533 The type of any join can be controlled manually. To search against only people
1534 with a father in the person table, we could explicitly use C<INNER JOIN>:
1536 $rs = $schema->resultset('Person')->search(
1539 alias => 'child', # alias columns in accordance with "from"
1541 { child => 'person' },
1543 { father => 'person', -join_type => 'inner' },
1544 { 'father.id' => 'child.father_id' }
1551 # SELECT child.* FROM person child
1552 # INNER JOIN person father ON child.father_id = father.id
1562 Makes the resultset paged and specifies the page to retrieve. Effectively
1563 identical to creating a non-pages resultset and then calling ->page($page)
1574 Specifes the maximum number of rows for direct retrieval or the number of
1575 rows per page if the page attribute or method is used.
1581 =item Value: \@columns
1585 A arrayref of columns to group by. Can include columns of joined tables.
1587 group_by => [qw/ column1 column2 ... /]
1593 =item Value: $condition
1597 HAVING is a select statement attribute that is applied between GROUP BY and
1598 ORDER BY. It is applied to the after the grouping calculations have been
1601 having => { 'count(employee)' => { '>=', 100 } }
1607 =item Value: (0 | 1)
1611 Set to 1 to group by all columns.
1615 Set to 1 to cache search results. This prevents extra SQL queries if you
1616 revisit rows in your ResultSet:
1618 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1620 while( my $artist = $resultset->next ) {
1624 $rs->first; # without cache, this would issue a query
1626 By default, searches are not cached.
1628 For more examples of using these attributes, see
1629 L<DBIx::Class::Manual::Cookbook>.