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/],
199 my $attrs = { %{$self->{attrs}} };
200 my $having = delete $attrs->{having};
201 $attrs = { %$attrs, %{ pop(@_) } } if @_ > 1 and ref $_[$#_] eq 'HASH';
204 ? ((@_ == 1 || ref $_[0] eq "HASH")
207 ? $self->throw_exception(
208 "Odd number of arguments to search")
211 if (defined $where) {
212 $attrs->{where} = (defined $attrs->{where}
214 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
215 $where, $attrs->{where} ] }
219 if (defined $having) {
220 $attrs->{having} = (defined $attrs->{having}
222 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
223 $having, $attrs->{having} ] }
227 my $rs = (ref $self)->new($self->result_source, $attrs);
229 my $rows = $self->get_cache;
231 $rs->set_cache($rows);
234 return (wantarray ? $rs->all : $rs);
237 =head2 search_literal
241 =item Arguments: $sql_fragment, @bind_values
243 =item Return Value: $resultset (scalar context), @row_objs (list context)
247 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
248 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
250 Pass a literal chunk of SQL to be added to the conditional part of the
256 my ($self, $cond, @vals) = @_;
257 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
258 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
259 return $self->search(\$cond, $attrs);
266 =item Arguments: @values | \%cols, \%attrs?
268 =item Return Value: $row_object
272 Finds a row based on its primary key or unique constraint. For example:
274 my $cd = $schema->resultset('CD')->find(5);
276 Also takes an optional C<key> attribute, to search by a specific key or unique
277 constraint. For example:
279 my $cd = $schema->resultset('CD')->find(
281 artist => 'Massive Attack',
282 title => 'Mezzanine',
284 { key => 'artist_title' }
287 See also L</find_or_create> and L</update_or_create>.
292 my ($self, @vals) = @_;
293 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
295 my @cols = $self->result_source->primary_columns;
296 if (exists $attrs->{key}) {
297 my %uniq = $self->result_source->unique_constraints;
298 $self->throw_exception(
299 "Unknown key $attrs->{key} on '" . $self->result_source->name . "'"
300 ) unless exists $uniq{$attrs->{key}};
301 @cols = @{ $uniq{$attrs->{key}} };
303 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
304 $self->throw_exception(
305 "Can't find unless a primary key or unique constraint is defined"
309 if (ref $vals[0] eq 'HASH') {
310 $query = { %{$vals[0]} };
311 } elsif (@cols == @vals) {
313 @{$query}{@cols} = @vals;
317 foreach my $key (grep { ! m/\./ } keys %$query) {
318 $query->{"$self->{attrs}{alias}.$key"} = delete $query->{$key};
320 #warn Dumper($query);
323 my $rs = $self->search($query,$attrs);
324 return keys %{$rs->{collapse}} ? $rs->next : $rs->single;
326 return keys %{$self->{collapse}} ?
327 $self->search($query)->next :
328 $self->single($query);
332 =head2 search_related
336 =item Arguments: $cond, \%attrs?
338 =item Return Value: $new_resultset
342 $new_rs = $cd_rs->search_related('artist', {
346 Searches the specified relationship, optionally specifying a condition and
347 attributes for matching records. See L</ATTRIBUTES> for more information.
352 return shift->related_resultset(shift)->search(@_);
359 =item Arguments: none
361 =item Return Value: $cursor
365 Returns a storage-driven cursor to the given resultset. See
366 L<DBIx::Class::Cursor> for more information.
372 my $attrs = { %{$self->{attrs}} };
373 return $self->{cursor}
374 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
375 $attrs->{where},$attrs);
382 =item Arguments: $cond?
384 =item Return Value: $row_object?
388 my $cd = $schema->resultset('CD')->single({ year => 2001 });
390 Inflates the first result without creating a cursor if the resultset has
391 any records in it; if not returns nothing. Used by find() as an optimisation.
396 my ($self, $where) = @_;
397 my $attrs = { %{$self->{attrs}} };
399 if (defined $attrs->{where}) {
402 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
403 $where, delete $attrs->{where} ]
406 $attrs->{where} = $where;
409 my @data = $self->result_source->storage->select_single(
410 $self->{from}, $attrs->{select},
411 $attrs->{where},$attrs);
412 return (@data ? $self->_construct_object(@data) : ());
420 =item Arguments: $cond, \%attrs?
422 =item Return Value: $resultset (scalar context), @row_objs (list context)
426 # WHERE title LIKE '%blue%'
427 $cd_rs = $rs->search_like({ title => '%blue%'});
429 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
430 that this is simply a convenience method. You most likely want to use
431 L</search> with specific operators.
433 For more information, see L<DBIx::Class::Manual::Cookbook>.
439 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
440 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
441 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
442 return $class->search($query, { %$attrs });
449 =item Arguments: $first, $last
451 =item Return Value: $resultset (scalar context), @row_objs (list context)
455 Returns a resultset or object list representing a subset of elements from the
456 resultset slice is called on. Indexes are from 0, i.e., to get the first
459 my ($one, $two, $three) = $rs->slice(0, 2);
464 my ($self, $min, $max) = @_;
465 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
466 $attrs->{offset} = $self->{attrs}{offset} || 0;
467 $attrs->{offset} += $min;
468 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
469 return $self->search(undef(), $attrs);
470 #my $slice = (ref $self)->new($self->result_source, $attrs);
471 #return (wantarray ? $slice->all : $slice);
478 =item Arguments: none
480 =item Return Value: $result?
484 Returns the next element in the resultset (C<undef> is there is none).
486 Can be used to efficiently iterate over records in the resultset:
488 my $rs = $schema->resultset('CD')->search;
489 while (my $cd = $rs->next) {
493 Note that you need to store the resultset object, and call C<next> on it.
494 Calling C<< resultset('Table')->next >> repeatedly will always return the
495 first record from the resultset.
501 if (@{$self->{all_cache} || []}) {
502 $self->{all_cache_position} ||= 0;
503 return $self->{all_cache}->[$self->{all_cache_position}++];
505 if ($self->{attrs}{cache}) {
506 $self->{all_cache_position} = 1;
507 return ($self->all)[0];
509 my @row = (exists $self->{stashed_row} ?
510 @{delete $self->{stashed_row}} :
513 # warn Dumper(\@row); use Data::Dumper;
514 return unless (@row);
515 return $self->_construct_object(@row);
518 sub _construct_object {
519 my ($self, @row) = @_;
520 my @as = @{ $self->{attrs}{as} };
522 my $info = $self->_collapse_result(\@as, \@row);
524 my $new = $self->result_class->inflate_result($self->result_source, @$info);
526 $new = $self->{attrs}{record_filter}->($new)
527 if exists $self->{attrs}{record_filter};
531 sub _collapse_result {
532 my ($self, $as, $row, $prefix) = @_;
537 foreach my $this_as (@$as) {
538 my $val = shift @copy;
539 if (defined $prefix) {
540 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
542 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
543 $const{$1||''}{$2} = $val;
546 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
547 $const{$1||''}{$2} = $val;
551 my $info = [ {}, {} ];
552 foreach my $key (keys %const) {
555 my @parts = split(/\./, $key);
556 foreach my $p (@parts) {
557 $target = $target->[1]->{$p} ||= [];
559 $target->[0] = $const{$key};
561 $info->[0] = $const{$key};
566 if (defined $prefix) {
568 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
569 } keys %{$self->{collapse}}
571 @collapse = keys %{$self->{collapse}};
575 my ($c) = sort { length $a <=> length $b } @collapse;
577 foreach my $p (split(/\./, $c)) {
578 $target = $target->[1]->{$p} ||= [];
580 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
581 my @co_key = @{$self->{collapse}{$c_prefix}};
582 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
583 my $tree = $self->_collapse_result($as, $row, $c_prefix);
586 !defined($tree->[0]->{$_}) ||
587 $co_check{$_} ne $tree->[0]->{$_}
590 last unless (@raw = $self->cursor->next);
591 $row = $self->{stashed_row} = \@raw;
592 $tree = $self->_collapse_result($as, $row, $c_prefix);
593 #warn Data::Dumper::Dumper($tree, $row);
605 =item Arguments: $result_source?
607 =item Return Value: $result_source
611 An accessor for the primary ResultSource object from which this ResultSet
621 =item Arguments: $cond, \%attrs??
623 =item Return Value: $count
627 Performs an SQL C<COUNT> with the same query as the resultset was built
628 with to find the number of elements. If passed arguments, does a search
629 on the resultset and counts the results of that.
631 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
632 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
633 not support C<DISTINCT> with multiple columns. If you are using such a
634 database, you should only use columns from the main table in your C<group_by>
641 return $self->search(@_)->count if @_ and defined $_[0];
642 return scalar @{ $self->get_cache } if @{ $self->get_cache };
644 my $count = $self->_count;
645 return 0 unless $count;
647 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
648 $count = $self->{attrs}{rows} if
649 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
653 sub _count { # Separated out so pager can get the full count
655 my $select = { count => '*' };
656 my $attrs = { %{ $self->{attrs} } };
657 if (my $group_by = delete $attrs->{group_by}) {
658 delete $attrs->{having};
659 my @distinct = (ref $group_by ? @$group_by : ($group_by));
660 # todo: try CONCAT for multi-column pk
661 my @pk = $self->result_source->primary_columns;
663 foreach my $column (@distinct) {
664 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
665 @distinct = ($column);
671 $select = { count => { distinct => \@distinct } };
672 #use Data::Dumper; die Dumper $select;
675 $attrs->{select} = $select;
676 $attrs->{as} = [qw/count/];
678 # offset, order by and page are not needed to count. record_filter is cdbi
679 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
681 my ($count) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
689 =item Arguments: $sql_fragment, @bind_values
691 =item Return Value: $count
695 Counts the results in a literal query. Equivalent to calling L</search_literal>
696 with the passed arguments, then L</count>.
700 sub count_literal { shift->search_literal(@_)->count; }
706 =item Arguments: none
708 =item Return Value: @objects
712 Returns all elements in the resultset. Called implicitly if the resultset
713 is returned in list context.
719 return @{ $self->get_cache } if @{ $self->get_cache };
723 if (keys %{$self->{collapse}}) {
724 # Using $self->cursor->all is really just an optimisation.
725 # If we're collapsing has_many prefetches it probably makes
726 # very little difference, and this is cleaner than hacking
727 # _construct_object to survive the approach
728 $self->cursor->reset;
729 my @row = $self->cursor->next;
731 push(@obj, $self->_construct_object(@row));
732 @row = (exists $self->{stashed_row}
733 ? @{delete $self->{stashed_row}}
734 : $self->cursor->next);
737 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
740 $self->set_cache(\@obj) if $self->{attrs}{cache};
748 =item Arguments: none
750 =item Return Value: $self
754 Resets the resultset's cursor, so you can iterate through the elements again.
760 $self->{all_cache_position} = 0;
761 $self->cursor->reset;
769 =item Arguments: none
771 =item Return Value: $object?
775 Resets the resultset and returns an object for the first result (if the
776 resultset returns anything).
781 return $_[0]->reset->next;
784 # _cond_for_update_delete
786 # update/delete require the condition to be modified to handle
787 # the differing SQL syntax available. This transforms the $self->{cond}
788 # appropriately, returning the new condition.
790 sub _cond_for_update_delete {
794 if (!ref($self->{cond})) {
795 # No-op. No condition, we're updating/deleting everything
797 elsif (ref $self->{cond} eq 'ARRAY') {
801 foreach my $key (keys %{$_}) {
803 $hash{$1} = $_->{$key};
809 elsif (ref $self->{cond} eq 'HASH') {
810 if ((keys %{$self->{cond}})[0] eq '-and') {
813 my @cond = @{$self->{cond}{-and}};
814 for (my $i = 0; $i < @cond - 1; $i++) {
815 my $entry = $cond[$i];
818 if (ref $entry eq 'HASH') {
819 foreach my $key (keys %{$entry}) {
821 $hash{$1} = $entry->{$key};
825 $entry =~ /([^.]+)$/;
826 $hash{$entry} = $cond[++$i];
829 push @{$cond->{-and}}, \%hash;
833 foreach my $key (keys %{$self->{cond}}) {
835 $cond->{$1} = $self->{cond}{$key};
840 $self->throw_exception(
841 "Can't update/delete on resultset with condition unless hash or array"
853 =item Arguments: \%values
855 =item Return Value: $storage_rv
859 Sets the specified columns in the resultset to the supplied values in a
860 single query. Return value will be true if the update succeeded or false
861 if no records were updated; exact type of success value is storage-dependent.
866 my ($self, $values) = @_;
867 $self->throw_exception("Values for update must be a hash")
868 unless ref $values eq 'HASH';
870 my $cond = $self->_cond_for_update_delete;
872 return $self->result_source->storage->update(
873 $self->result_source->from, $values, $cond
881 =item Arguments: \%values
883 =item Return Value: 1
887 Fetches all objects and updates them one at a time. Note that C<update_all>
888 will run DBIC cascade triggers, while L</update> will not.
893 my ($self, $values) = @_;
894 $self->throw_exception("Values for update must be a hash")
895 unless ref $values eq 'HASH';
896 foreach my $obj ($self->all) {
897 $obj->set_columns($values)->update;
906 =item Arguments: none
908 =item Return Value: 1
912 Deletes the contents of the resultset from its result source. Note that this
913 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
922 my $cond = $self->_cond_for_update_delete;
924 $self->result_source->storage->delete($self->result_source->from, $cond);
932 =item Arguments: none
934 =item Return Value: 1
938 Fetches all objects and deletes them one at a time. Note that C<delete_all>
939 will run DBIC cascade triggers, while L</delete> will not.
945 $_->delete for $self->all;
953 =item Arguments: none
955 =item Return Value: $pager
959 Return Value a L<Data::Page> object for the current resultset. Only makes
960 sense for queries with a C<page> attribute.
966 my $attrs = $self->{attrs};
967 $self->throw_exception("Can't create pager for non-paged rs")
968 unless $self->{page};
969 $attrs->{rows} ||= 10;
970 return $self->{pager} ||= Data::Page->new(
971 $self->_count, $attrs->{rows}, $self->{page});
978 =item Arguments: $page_number
980 =item Return Value: $rs
984 Returns a resultset for the $page_number page of the resultset on which page
985 is called, where each page contains a number of rows equal to the 'rows'
986 attribute set on the resultset (10 by default).
991 my ($self, $page) = @_;
992 my $attrs = { %{$self->{attrs}} };
993 $attrs->{page} = $page;
994 return (ref $self)->new($self->result_source, $attrs);
1001 =item Arguments: \%vals
1003 =item Return Value: $object
1007 Creates an object in the resultset's result class and returns it.
1012 my ($self, $values) = @_;
1013 $self->throw_exception( "new_result needs a hash" )
1014 unless (ref $values eq 'HASH');
1015 $self->throw_exception(
1016 "Can't abstract implicit construct, condition not a hash"
1017 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
1019 my $alias = $self->{attrs}{alias};
1020 foreach my $key (keys %{$self->{cond}||{}}) {
1021 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
1023 my $obj = $self->result_class->new(\%new);
1024 $obj->result_source($self->result_source) if $obj->can('result_source');
1032 =item Arguments: \%vals
1034 =item Return Value: $object
1038 Inserts a record into the resultset and returns the object representing it.
1040 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1045 my ($self, $attrs) = @_;
1046 $self->throw_exception( "create needs a hashref" )
1047 unless ref $attrs eq 'HASH';
1048 return $self->new_result($attrs)->insert;
1051 =head2 find_or_create
1055 =item Arguments: \%vals, \%attrs?
1057 =item Return Value: $object
1061 $class->find_or_create({ key => $val, ... });
1063 Searches for a record matching the search condition; if it doesn't find one,
1064 creates one and returns that instead.
1066 my $cd = $schema->resultset('CD')->find_or_create({
1068 artist => 'Massive Attack',
1069 title => 'Mezzanine',
1073 Also takes an optional C<key> attribute, to search by a specific key or unique
1074 constraint. For example:
1076 my $cd = $schema->resultset('CD')->find_or_create(
1078 artist => 'Massive Attack',
1079 title => 'Mezzanine',
1081 { key => 'artist_title' }
1084 See also L</find> and L</update_or_create>.
1088 sub find_or_create {
1090 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1091 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1092 my $exists = $self->find($hash, $attrs);
1093 return defined $exists ? $exists : $self->create($hash);
1096 =head2 update_or_create
1100 =item Arguments: \%col_values, { key => $unique_constraint }?
1102 =item Return Value: $object
1106 $class->update_or_create({ col => $val, ... });
1108 First, searches for an existing row matching one of the unique constraints
1109 (including the primary key) on the source of this resultset. If a row is
1110 found, updates it with the other given column values. Otherwise, creates a new
1113 Takes an optional C<key> attribute to search on a specific unique constraint.
1116 # In your application
1117 my $cd = $schema->resultset('CD')->update_or_create(
1119 artist => 'Massive Attack',
1120 title => 'Mezzanine',
1123 { key => 'artist_title' }
1126 If no C<key> is specified, it searches on all unique constraints defined on the
1127 source, including the primary key.
1129 If the C<key> is specified as C<primary>, it searches only on the primary key.
1131 See also L</find> and L</find_or_create>.
1135 sub update_or_create {
1137 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1138 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1140 my %unique_constraints = $self->result_source->unique_constraints;
1141 my @constraint_names = (exists $attrs->{key}
1143 : keys %unique_constraints);
1146 foreach my $name (@constraint_names) {
1147 my @unique_cols = @{ $unique_constraints{$name} };
1149 map { $_ => $hash->{$_} }
1150 grep { exists $hash->{$_} }
1153 push @unique_hashes, \%unique_hash
1154 if (scalar keys %unique_hash == scalar @unique_cols);
1157 if (@unique_hashes) {
1158 my $row = $self->single(\@unique_hashes);
1160 $row->set_columns($hash);
1166 return $self->create($hash);
1173 =item Arguments: none
1175 =item Return Value: \@cache_objects?
1179 Gets the contents of the cache for the resultset, if the cache is set.
1184 shift->{all_cache} || [];
1191 =item Arguments: \@cache_objects
1193 =item Return Value: \@cache_objects
1197 Sets the contents of the cache for the resultset. Expects an arrayref
1198 of objects of the same class as those produced by the resultset. Note that
1199 if the cache is set the resultset will return the cached objects rather
1200 than re-querying the database even if the cache attr is not set.
1205 my ( $self, $data ) = @_;
1206 $self->throw_exception("set_cache requires an arrayref")
1207 if ref $data ne 'ARRAY';
1208 my $result_class = $self->result_class;
1210 $self->throw_exception(
1211 "cannot cache object of type '$_', expected '$result_class'"
1212 ) if ref $_ ne $result_class;
1214 $self->{all_cache} = $data;
1221 =item Arguments: none
1223 =item Return Value: []
1227 Clears the cache for the resultset.
1232 shift->set_cache([]);
1235 =head2 related_resultset
1239 =item Arguments: $relationship_name
1241 =item Return Value: $resultset
1245 Returns a related resultset for the supplied relationship name.
1247 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1251 sub related_resultset {
1252 my ( $self, $rel ) = @_;
1253 $self->{related_resultsets} ||= {};
1254 return $self->{related_resultsets}{$rel} ||= do {
1255 #warn "fetching related resultset for rel '$rel'";
1256 my $rel_obj = $self->result_source->relationship_info($rel);
1257 $self->throw_exception(
1258 "search_related: result source '" . $self->result_source->name .
1259 "' has no such relationship ${rel}")
1260 unless $rel_obj; #die Dumper $self->{attrs};
1262 my $rs = $self->search(undef, { join => $rel });
1263 my $alias = defined $rs->{attrs}{seen_join}{$rel}
1264 && $rs->{attrs}{seen_join}{$rel} > 1
1265 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
1268 $self->result_source->schema->resultset($rel_obj->{class}
1278 =head2 throw_exception
1280 See L<DBIx::Class::Schema/throw_exception> for details.
1284 sub throw_exception {
1286 $self->result_source->schema->throw_exception(@_);
1289 # XXX: FIXME: Attributes docs need clearing up
1293 The resultset takes various attributes that modify its behavior. Here's an
1300 =item Value: ($order_by | \@order_by)
1304 Which column(s) to order the results by. This is currently passed
1305 through directly to SQL, so you can give e.g. C<year DESC> for a
1306 descending order on the column `year'.
1312 =item Value: \@columns
1316 Shortcut to request a particular set of columns to be retrieved. Adds
1317 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1318 from that, then auto-populates C<as> from C<select> as normal. (You may also
1319 use the C<cols> attribute, as in earlier versions of DBIC.)
1321 =head2 include_columns
1325 =item Value: \@columns
1329 Shortcut to include additional columns in the returned results - for example
1331 $schema->resultset('CD')->search(undef, {
1332 include_columns => ['artist.name'],
1336 would return all CDs and include a 'name' column to the information
1337 passed to object inflation
1343 =item Value: \@select_columns
1347 Indicates which columns should be selected from the storage. You can use
1348 column names, or in the case of RDBMS back ends, function or stored procedure
1351 $rs = $schema->resultset('Employee')->search(undef, {
1354 { count => 'employeeid' },
1359 When you use function/stored procedure names and do not supply an C<as>
1360 attribute, the column names returned are storage-dependent. E.g. MySQL would
1361 return a column named C<count(employeeid)> in the above example.
1367 =item Value: \@inflation_names
1371 Indicates column names for object inflation. This is used in conjunction with
1372 C<select>, usually when C<select> contains one or more function or stored
1375 $rs = $schema->resultset('Employee')->search(undef, {
1378 { count => 'employeeid' }
1380 as => ['name', 'employee_count'],
1383 my $employee = $rs->first(); # get the first Employee
1385 If the object against which the search is performed already has an accessor
1386 matching a column name specified in C<as>, the value can be retrieved using
1387 the accessor as normal:
1389 my $name = $employee->name();
1391 If on the other hand an accessor does not exist in the object, you need to
1392 use C<get_column> instead:
1394 my $employee_count = $employee->get_column('employee_count');
1396 You can create your own accessors if required - see
1397 L<DBIx::Class::Manual::Cookbook> for details.
1403 =item Value: ($rel_name | \@rel_names | \%rel_names)
1407 Contains a list of relationships that should be joined for this query. For
1410 # Get CDs by Nine Inch Nails
1411 my $rs = $schema->resultset('CD')->search(
1412 { 'artist.name' => 'Nine Inch Nails' },
1413 { join => 'artist' }
1416 Can also contain a hash reference to refer to the other relation's relations.
1419 package MyApp::Schema::Track;
1420 use base qw/DBIx::Class/;
1421 __PACKAGE__->table('track');
1422 __PACKAGE__->add_columns(qw/trackid cd position title/);
1423 __PACKAGE__->set_primary_key('trackid');
1424 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1427 # In your application
1428 my $rs = $schema->resultset('Artist')->search(
1429 { 'track.title' => 'Teardrop' },
1431 join => { cd => 'track' },
1432 order_by => 'artist.name',
1436 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1437 similarly for a third time). For e.g.
1439 my $rs = $schema->resultset('Artist')->search({
1440 'cds.title' => 'Down to Earth',
1441 'cds_2.title' => 'Popular',
1443 join => [ qw/cds cds/ ],
1446 will return a set of all artists that have both a cd with title 'Down
1447 to Earth' and a cd with title 'Popular'.
1449 If you want to fetch related objects from other tables as well, see C<prefetch>
1456 =item Value: ($rel_name | \@rel_names | \%rel_names)
1460 Contains one or more relationships that should be fetched along with the main
1461 query (when they are accessed afterwards they will have already been
1462 "prefetched"). This is useful for when you know you will need the related
1463 objects, because it saves at least one query:
1465 my $rs = $schema->resultset('Tag')->search(
1474 The initial search results in SQL like the following:
1476 SELECT tag.*, cd.*, artist.* FROM tag
1477 JOIN cd ON tag.cd = cd.cdid
1478 JOIN artist ON cd.artist = artist.artistid
1480 L<DBIx::Class> has no need to go back to the database when we access the
1481 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1484 Simple prefetches will be joined automatically, so there is no need
1485 for a C<join> attribute in the above search. If you're prefetching to
1486 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1487 specify the join as well.
1489 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1490 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1491 with an accessor type of 'single' or 'filter').
1497 =item Value: \@from_clause
1501 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1502 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1505 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1506 C<join> will usually do what you need and it is strongly recommended that you
1507 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1509 In simple terms, C<from> works as follows:
1512 { <alias> => <table>, -join_type => 'inner|left|right' }
1513 [] # nested JOIN (optional)
1514 { <table.column> => <foreign_table.foreign_key> }
1520 ON <table.column> = <foreign_table.foreign_key>
1522 An easy way to follow the examples below is to remember the following:
1524 Anything inside "[]" is a JOIN
1525 Anything inside "{}" is a condition for the enclosing JOIN
1527 The following examples utilize a "person" table in a family tree application.
1528 In order to express parent->child relationships, this table is self-joined:
1530 # Person->belongs_to('father' => 'Person');
1531 # Person->belongs_to('mother' => 'Person');
1533 C<from> can be used to nest joins. Here we return all children with a father,
1534 then search against all mothers of those children:
1536 $rs = $schema->resultset('Person')->search(
1539 alias => 'mother', # alias columns in accordance with "from"
1541 { mother => 'person' },
1544 { child => 'person' },
1546 { father => 'person' },
1547 { 'father.person_id' => 'child.father_id' }
1550 { 'mother.person_id' => 'child.mother_id' }
1557 # SELECT mother.* FROM person mother
1560 # JOIN person father
1561 # ON ( father.person_id = child.father_id )
1563 # ON ( mother.person_id = child.mother_id )
1565 The type of any join can be controlled manually. To search against only people
1566 with a father in the person table, we could explicitly use C<INNER JOIN>:
1568 $rs = $schema->resultset('Person')->search(
1571 alias => 'child', # alias columns in accordance with "from"
1573 { child => 'person' },
1575 { father => 'person', -join_type => 'inner' },
1576 { 'father.id' => 'child.father_id' }
1583 # SELECT child.* FROM person child
1584 # INNER JOIN person father ON child.father_id = father.id
1594 Makes the resultset paged and specifies the page to retrieve. Effectively
1595 identical to creating a non-pages resultset and then calling ->page($page)
1606 Specifes the maximum number of rows for direct retrieval or the number of
1607 rows per page if the page attribute or method is used.
1613 =item Value: \@columns
1617 A arrayref of columns to group by. Can include columns of joined tables.
1619 group_by => [qw/ column1 column2 ... /]
1625 =item Value: $condition
1629 HAVING is a select statement attribute that is applied between GROUP BY and
1630 ORDER BY. It is applied to the after the grouping calculations have been
1633 having => { 'count(employee)' => { '>=', 100 } }
1639 =item Value: (0 | 1)
1643 Set to 1 to group by all columns.
1647 Set to 1 to cache search results. This prevents extra SQL queries if you
1648 revisit rows in your ResultSet:
1650 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1652 while( my $artist = $resultset->next ) {
1656 $rs->first; # without cache, this would issue a query
1658 By default, searches are not cached.
1660 For more examples of using these attributes, see
1661 L<DBIx::Class::Manual::Cookbook>.