1 package DBIx::Class::ResultSet;
12 use Scalar::Util qw/weaken/;
14 use DBIx::Class::ResultSetColumn;
15 use base qw/DBIx::Class/;
16 __PACKAGE__->load_components(qw/AccessorGroup/);
17 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
21 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
25 my $rs = $schema->resultset('User')->search(registered => 1);
26 my @rows = $schema->resultset('CD')->search(year => 2005);
30 The resultset is also known as an iterator. It is responsible for handling
31 queries that may return an arbitrary number of rows, e.g. via L</search>
32 or a C<has_many> relationship.
34 In the examples below, the following table classes are used:
36 package MyApp::Schema::Artist;
37 use base qw/DBIx::Class/;
38 __PACKAGE__->load_components(qw/Core/);
39 __PACKAGE__->table('artist');
40 __PACKAGE__->add_columns(qw/artistid name/);
41 __PACKAGE__->set_primary_key('artistid');
42 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
45 package MyApp::Schema::CD;
46 use base qw/DBIx::Class/;
47 __PACKAGE__->load_components(qw/Core/);
48 __PACKAGE__->table('cd');
49 __PACKAGE__->add_columns(qw/cdid artist title year/);
50 __PACKAGE__->set_primary_key('cdid');
51 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
60 =item Arguments: $source, \%$attrs
62 =item Return Value: $rs
66 The resultset constructor. Takes a source object (usually a
67 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
68 L</ATTRIBUTES> below). Does not perform any queries -- these are
69 executed as needed by the other methods.
71 Generally you won't need to construct a resultset manually. You'll
72 automatically get one from e.g. a L</search> called in scalar context:
74 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
76 IMPORTANT: If called on an object, proxies to new_result instead so
78 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
80 will return a CD object, not a ResultSet.
86 return $class->new_result(@_) if ref $class;
88 my ($source, $attrs) = @_;
92 $attrs->{rows} ||= 10;
93 $attrs->{offset} ||= 0;
94 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
97 $attrs->{alias} ||= 'me';
100 result_source => $source,
101 result_class => $attrs->{result_class} || $source->result_class,
102 cond => $attrs->{where},
103 # from => $attrs->{from},
104 # collapse => $collapse,
106 page => delete $attrs->{page},
116 =item Arguments: $cond, \%attrs?
118 =item Return Value: $resultset (scalar context), @row_objs (list context)
122 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
123 my $new_rs = $cd_rs->search({ year => 2005 });
125 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
126 # year = 2005 OR year = 2004
128 If you need to pass in additional attributes but no additional condition,
129 call it as C<search(undef, \%attrs)>.
131 # "SELECT name, artistid FROM $artist_table"
132 my @all_artists = $schema->resultset('Artist')->search(undef, {
133 columns => [qw/name artistid/],
140 my $rs = $self->search_rs( @_ );
141 return (wantarray ? $rs->all : $rs);
148 =item Arguments: $cond, \%attrs?
150 =item Return Value: $resultset
154 This method does the same exact thing as search() except it will
155 always return a resultset, even in list context.
162 my $our_attrs = { %{$self->{attrs}} };
163 my $having = delete $our_attrs->{having};
165 $attrs = pop(@_) if @_ > 1 and ref $_[$#_] eq 'HASH';
167 # merge new attrs into old
168 foreach my $key (qw/join prefetch/) {
169 next unless (exists $attrs->{$key});
170 if (exists $our_attrs->{$key}) {
171 $our_attrs->{$key} = $self->_merge_attr($our_attrs->{$key}, $attrs->{$key});
173 $our_attrs->{$key} = $attrs->{$key};
175 delete $attrs->{$key};
178 if (exists $our_attrs->{prefetch}) {
179 $our_attrs->{join} = $self->_merge_attr($our_attrs->{join}, $our_attrs->{prefetch}, 1);
182 my $new_attrs = { %{$our_attrs}, %{$attrs} };
184 # merge new where and having into old
186 ? ((@_ == 1 || ref $_[0] eq "HASH")
189 ? $self->throw_exception(
190 "Odd number of arguments to search")
193 if (defined $where) {
194 $new_attrs->{where} = (defined $new_attrs->{where}
196 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
197 $where, $new_attrs->{where} ] }
201 if (defined $having) {
202 $new_attrs->{having} = (defined $new_attrs->{having}
204 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
205 $having, $new_attrs->{having} ] }
209 my $rs = (ref $self)->new($self->result_source, $new_attrs);
210 $rs->{_parent_rs} = $self->{_parent_rs} if ($self->{_parent_rs}); #XXX - hack to pass through parent of related resultsets
212 unless (@_) { # no search, effectively just a clone
213 my $rows = $self->get_cache;
215 $rs->set_cache($rows);
222 =head2 search_literal
226 =item Arguments: $sql_fragment, @bind_values
228 =item Return Value: $resultset (scalar context), @row_objs (list context)
232 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
233 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
235 Pass a literal chunk of SQL to be added to the conditional part of the
241 my ($self, $cond, @vals) = @_;
242 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
243 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
244 return $self->search(\$cond, $attrs);
251 =item Arguments: @values | \%cols, \%attrs?
253 =item Return Value: $row_object
257 Finds a row based on its primary key or unique constraint. For example, to find
258 a row by its primary key:
260 my $cd = $schema->resultset('CD')->find(5);
262 You can also find a row by a specific unique constraint using the C<key>
263 attribute. For example:
265 my $cd = $schema->resultset('CD')->find('Massive Attack', 'Mezzanine', { key => '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 => 'artist_title' }
277 If no C<key> is specified and you explicitly name columns, it searches on all
278 unique constraints defined on the source, including the primary key.
280 If the C<key> is specified as C<primary>, it searches only on 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 # Parse out a hash from input
293 my @cols = exists $attrs->{key}
294 ? $self->result_source->unique_constraint_columns($attrs->{key})
295 : $self->result_source->primary_columns;
298 if (ref $_[0] eq 'HASH') {
299 $hash = { %{$_[0]} };
301 elsif (@_ == @cols) {
303 @{$hash}{@cols} = @_;
306 # For backwards compatibility
310 $self->throw_exception(
311 "Arguments to find must be a hashref or match the number of columns in the "
312 . (exists $attrs->{key} ? "$attrs->{key} unique constraint" : "primary key")
316 # Check the hash we just parsed against our source's unique constraints
317 my @constraint_names = exists $attrs->{key}
319 : $self->result_source->unique_constraint_names;
320 $self->throw_exception(
321 "Can't find unless a primary key or unique constraint is defined"
322 ) unless @constraint_names;
325 foreach my $name (@constraint_names) {
326 my @unique_cols = $self->result_source->unique_constraint_columns($name);
327 my $unique_query = $self->_build_unique_query($hash, \@unique_cols);
329 # Add the ResultSet's alias
330 foreach my $key (grep { ! m/\./ } keys %$unique_query) {
331 my $alias = $self->{attrs}->{alias};
332 $unique_query->{"$alias.$key"} = delete $unique_query->{$key};
335 push @unique_queries, $unique_query if %$unique_query;
338 # Handle cases where the ResultSet already defines the query
339 my $query = @unique_queries ? \@unique_queries : undef;
343 my $rs = $self->search($query, $attrs);
345 return keys %{$rs->{_attrs}->{collapse}} ? $rs->next : $rs->single;
349 return (keys %{$self->{_attrs}->{collapse}})
350 ? $self->search($query)->next
351 : $self->single($query);
355 # _build_unique_query
357 # Constrain the specified query hash based on the specified column names.
359 sub _build_unique_query {
360 my ($self, $query, $unique_cols) = @_;
363 map { $_ => $query->{$_} }
364 grep { exists $query->{$_} }
367 return \%unique_query;
370 =head2 search_related
374 =item Arguments: $cond, \%attrs?
376 =item Return Value: $new_resultset
380 $new_rs = $cd_rs->search_related('artist', {
384 Searches the specified relationship, optionally specifying a condition and
385 attributes for matching records. See L</ATTRIBUTES> for more information.
390 return shift->related_resultset(shift)->search(@_);
397 =item Arguments: none
399 =item Return Value: $cursor
403 Returns a storage-driven cursor to the given resultset. See
404 L<DBIx::Class::Cursor> for more information.
412 my $attrs = { %{$self->{_attrs}} };
413 return $self->{cursor}
414 ||= $self->result_source->storage->select($attrs->{from}, $attrs->{select},
415 $attrs->{where},$attrs);
422 =item Arguments: $cond?
424 =item Return Value: $row_object?
428 my $cd = $schema->resultset('CD')->single({ year => 2001 });
430 Inflates the first result without creating a cursor if the resultset has
431 any records in it; if not returns nothing. Used by L</find> as an optimisation.
433 Can optionally take an additional condition *only* - this is a fast-code-path
434 method; if you need to add extra joins or similar call ->search and then
435 ->single without a condition on the $rs returned from that.
440 my ($self, $where) = @_;
442 my $attrs = { %{$self->{_attrs}} };
444 if (defined $attrs->{where}) {
447 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
448 $where, delete $attrs->{where} ]
451 $attrs->{where} = $where;
455 my @data = $self->result_source->storage->select_single(
456 $attrs->{from}, $attrs->{select},
457 $attrs->{where},$attrs);
458 return (@data ? $self->_construct_object(@data) : ());
465 =item Arguments: $cond?
467 =item Return Value: $resultsetcolumn
471 my $max_length = $rs->get_column('length')->max;
473 Returns a ResultSetColumn instance for $column based on $self
478 my ($self, $column) = @_;
480 my $new = DBIx::Class::ResultSetColumn->new($self, $column);
488 =item Arguments: $cond, \%attrs?
490 =item Return Value: $resultset (scalar context), @row_objs (list context)
494 # WHERE title LIKE '%blue%'
495 $cd_rs = $rs->search_like({ title => '%blue%'});
497 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
498 that this is simply a convenience method. You most likely want to use
499 L</search> with specific operators.
501 For more information, see L<DBIx::Class::Manual::Cookbook>.
507 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
508 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
509 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
510 return $class->search($query, { %$attrs });
517 =item Arguments: $first, $last
519 =item Return Value: $resultset (scalar context), @row_objs (list context)
523 Returns a resultset or object list representing a subset of elements from the
524 resultset slice is called on. Indexes are from 0, i.e., to get the first
527 my ($one, $two, $three) = $rs->slice(0, 2);
532 my ($self, $min, $max) = @_;
533 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
534 $attrs->{offset} = $self->{attrs}{offset} || 0;
535 $attrs->{offset} += $min;
536 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
537 return $self->search(undef(), $attrs);
538 #my $slice = (ref $self)->new($self->result_source, $attrs);
539 #return (wantarray ? $slice->all : $slice);
546 =item Arguments: none
548 =item Return Value: $result?
552 Returns the next element in the resultset (C<undef> is there is none).
554 Can be used to efficiently iterate over records in the resultset:
556 my $rs = $schema->resultset('CD')->search;
557 while (my $cd = $rs->next) {
561 Note that you need to store the resultset object, and call C<next> on it.
562 Calling C<< resultset('Table')->next >> repeatedly will always return the
563 first record from the resultset.
569 if (my $cache = $self->get_cache) {
570 $self->{all_cache_position} ||= 0;
571 return $cache->[$self->{all_cache_position}++];
573 if ($self->{attrs}{cache}) {
574 $self->{all_cache_position} = 1;
575 return ($self->all)[0];
577 my @row = (exists $self->{stashed_row} ?
578 @{delete $self->{stashed_row}} :
581 return unless (@row);
582 return $self->_construct_object(@row);
588 return if(exists $self->{_attrs}); #return if _resolve has already been called
590 my $attrs = $self->{attrs};
591 my $source = ($self->{_parent_rs}) ? $self->{_parent_rs} : $self->{result_source};
593 # XXX - lose storable dclone
594 my $record_filter = delete $attrs->{record_filter} if (defined $attrs->{record_filter});
595 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
596 $attrs->{record_filter} = $record_filter if ($record_filter);
597 $self->{attrs}->{record_filter} = $record_filter if ($record_filter);
599 my $alias = $attrs->{alias};
601 $attrs->{columns} ||= delete $attrs->{cols} if $attrs->{cols};
602 delete $attrs->{as} if $attrs->{columns};
603 $attrs->{columns} ||= [ $self->{result_source}->columns ] unless $attrs->{select};
604 my $select_alias = ($self->{_parent_rs}) ? $self->{attrs}->{_live_join} : $alias;
606 map { m/\./ ? $_ : "${select_alias}.$_" } @{delete $attrs->{columns}}
607 ] if $attrs->{columns};
609 map { m/^\Q$alias.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}}
611 if (my $include = delete $attrs->{include_columns}) {
612 push(@{$attrs->{select}}, @$include);
613 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1; } @$include);
616 $attrs->{from} ||= [ { $alias => $source->from } ];
617 $attrs->{seen_join} ||= {};
619 if (my $join = delete $attrs->{join}) {
620 foreach my $j (ref $join eq 'ARRAY' ? @$join : ($join)) {
621 if (ref $j eq 'HASH') {
622 $seen{$_} = 1 foreach keys %$j;
628 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}, $attrs->{seen_join}));
630 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
631 $attrs->{order_by} = [ $attrs->{order_by} ] if
632 $attrs->{order_by} and !ref($attrs->{order_by});
633 $attrs->{order_by} ||= [];
635 if(my $seladds = delete($attrs->{'+select'})) {
636 my @seladds = (ref($seladds) eq 'ARRAY' ? @$seladds : ($seladds));
638 @{ $attrs->{select} },
639 map { (m/\./ || ref($_)) ? $_ : "${alias}.$_" } $seladds
642 if(my $asadds = delete($attrs->{'+as'})) {
643 my @asadds = (ref($asadds) eq 'ARRAY' ? @$asadds : ($asadds));
644 $attrs->{as} = [ @{ $attrs->{as} }, @asadds ];
647 my $collapse = $attrs->{collapse} || {};
648 if (my $prefetch = delete $attrs->{prefetch}) {
650 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
651 if ( ref $p eq 'HASH' ) {
652 foreach my $key (keys %$p) {
653 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
657 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
660 my @prefetch = $source->resolve_prefetch(
661 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
662 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
663 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
665 push(@{$attrs->{order_by}}, @pre_order);
667 $attrs->{collapse} = $collapse;
668 $self->{_attrs} = $attrs;
672 my ($self, $a, $b, $is_prefetch) = @_;
675 if (ref $b eq 'HASH' && ref $a eq 'HASH') {
676 foreach my $key (keys %{$b}) {
677 if (exists $a->{$key}) {
678 $a->{$key} = $self->_merge_attr($a->{$key}, $b->{$key}, $is_prefetch);
680 $a->{$key} = delete $b->{$key};
685 $a = [$a] unless (ref $a eq 'ARRAY');
686 $b = [$b] unless (ref $b eq 'ARRAY');
691 foreach my $element (@{$_}) {
692 if (ref $element eq 'HASH') {
693 $hash = $self->_merge_attr($hash, $element, $is_prefetch);
694 } elsif (ref $element eq 'ARRAY') {
695 $array = [@{$array}, @{$element}];
697 if (($b == $_) && $is_prefetch) {
698 $self->_merge_array($array, $element, $is_prefetch);
700 push(@{$array}, $element);
706 if ((keys %{$hash}) && (scalar(@{$array} > 0))) {
707 return [$hash, @{$array}];
709 return (keys %{$hash}) ? $hash : $array;
715 my ($self, $a, $b) = @_;
717 $b = [$b] unless (ref $b eq 'ARRAY');
718 # add elements from @{$b} to @{$a} which aren't already in @{$a}
719 foreach my $b_element (@{$b}) {
720 push(@{$a}, $b_element) unless grep {$b_element eq $_} @{$a};
724 sub _construct_object {
725 my ($self, @row) = @_;
726 my @as = @{ $self->{_attrs}{as} };
728 my $info = $self->_collapse_result(\@as, \@row);
729 my $new = $self->result_class->inflate_result($self->result_source, @$info);
730 $new = $self->{_attrs}{record_filter}->($new)
731 if exists $self->{_attrs}{record_filter};
735 sub _collapse_result {
736 my ($self, $as, $row, $prefix) = @_;
738 my $live_join = $self->{attrs}->{_live_join} ||="";
742 foreach my $this_as (@$as) {
743 my $val = shift @copy;
744 if (defined $prefix) {
745 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
747 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
748 $const{$1||''}{$2} = $val;
751 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
752 $const{$1||''}{$2} = $val;
756 my $info = [ {}, {} ];
757 foreach my $key (keys %const) {
758 if (length $key && $key ne $live_join) {
760 my @parts = split(/\./, $key);
761 foreach my $p (@parts) {
762 $target = $target->[1]->{$p} ||= [];
764 $target->[0] = $const{$key};
766 $info->[0] = $const{$key};
771 if (defined $prefix) {
773 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
774 } keys %{$self->{_attrs}->{collapse}}
776 @collapse = keys %{$self->{_attrs}->{collapse}};
780 my ($c) = sort { length $a <=> length $b } @collapse;
782 foreach my $p (split(/\./, $c)) {
783 $target = $target->[1]->{$p} ||= [];
785 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
786 my @co_key = @{$self->{_attrs}->{collapse}{$c_prefix}};
787 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
788 my $tree = $self->_collapse_result($as, $row, $c_prefix);
791 !defined($tree->[0]->{$_}) ||
792 $co_check{$_} ne $tree->[0]->{$_}
795 last unless (@raw = $self->cursor->next);
796 $row = $self->{stashed_row} = \@raw;
797 $tree = $self->_collapse_result($as, $row, $c_prefix);
799 @$target = (@final ? @final : [ {}, {} ]);
800 # single empty result to indicate an empty prefetched has_many
809 =item Arguments: $result_source?
811 =item Return Value: $result_source
815 An accessor for the primary ResultSource object from which this ResultSet
825 =item Arguments: $cond, \%attrs??
827 =item Return Value: $count
831 Performs an SQL C<COUNT> with the same query as the resultset was built
832 with to find the number of elements. If passed arguments, does a search
833 on the resultset and counts the results of that.
835 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
836 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
837 not support C<DISTINCT> with multiple columns. If you are using such a
838 database, you should only use columns from the main table in your C<group_by>
845 return $self->search(@_)->count if @_ and defined $_[0];
846 return scalar @{ $self->get_cache } if $self->get_cache;
848 my $count = $self->_count;
849 return 0 unless $count;
851 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
852 $count = $self->{attrs}{rows} if
853 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
857 sub _count { # Separated out so pager can get the full count
859 my $select = { count => '*' };
862 my $attrs = { %{ $self->{_attrs} } };
863 if (my $group_by = delete $attrs->{group_by}) {
864 delete $attrs->{having};
865 my @distinct = (ref $group_by ? @$group_by : ($group_by));
866 # todo: try CONCAT for multi-column pk
867 my @pk = $self->result_source->primary_columns;
869 foreach my $column (@distinct) {
870 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
871 @distinct = ($column);
877 $select = { count => { distinct => \@distinct } };
880 $attrs->{select} = $select;
881 $attrs->{as} = [qw/count/];
883 # offset, order by and page are not needed to count. record_filter is cdbi
884 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
885 my ($count) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
893 =item Arguments: $sql_fragment, @bind_values
895 =item Return Value: $count
899 Counts the results in a literal query. Equivalent to calling L</search_literal>
900 with the passed arguments, then L</count>.
904 sub count_literal { shift->search_literal(@_)->count; }
910 =item Arguments: none
912 =item Return Value: @objects
916 Returns all elements in the resultset. Called implicitly if the resultset
917 is returned in list context.
923 return @{ $self->get_cache } if $self->get_cache;
927 # TODO: don't call resolve here
929 if (keys %{$self->{_attrs}->{collapse}}) {
930 # if ($self->{attrs}->{prefetch}) {
931 # Using $self->cursor->all is really just an optimisation.
932 # If we're collapsing has_many prefetches it probably makes
933 # very little difference, and this is cleaner than hacking
934 # _construct_object to survive the approach
935 my @row = $self->cursor->next;
937 push(@obj, $self->_construct_object(@row));
938 @row = (exists $self->{stashed_row}
939 ? @{delete $self->{stashed_row}}
940 : $self->cursor->next);
943 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
946 $self->set_cache(\@obj) if $self->{attrs}{cache};
954 =item Arguments: none
956 =item Return Value: $self
960 Resets the resultset's cursor, so you can iterate through the elements again.
966 delete $self->{_attrs} if (exists $self->{_attrs});
968 $self->{all_cache_position} = 0;
969 $self->cursor->reset;
977 =item Arguments: none
979 =item Return Value: $object?
983 Resets the resultset and returns an object for the first result (if the
984 resultset returns anything).
989 return $_[0]->reset->next;
992 # _cond_for_update_delete
994 # update/delete require the condition to be modified to handle
995 # the differing SQL syntax available. This transforms the $self->{cond}
996 # appropriately, returning the new condition.
998 sub _cond_for_update_delete {
1002 if (!ref($self->{cond})) {
1003 # No-op. No condition, we're updating/deleting everything
1005 elsif (ref $self->{cond} eq 'ARRAY') {
1009 foreach my $key (keys %{$_}) {
1011 $hash{$1} = $_->{$key};
1017 elsif (ref $self->{cond} eq 'HASH') {
1018 if ((keys %{$self->{cond}})[0] eq '-and') {
1021 my @cond = @{$self->{cond}{-and}};
1022 for (my $i = 0; $i <= @cond - 1; $i++) {
1023 my $entry = $cond[$i];
1026 if (ref $entry eq 'HASH') {
1027 foreach my $key (keys %{$entry}) {
1029 $hash{$1} = $entry->{$key};
1033 $entry =~ /([^.]+)$/;
1034 $hash{$1} = $cond[++$i];
1037 push @{$cond->{-and}}, \%hash;
1041 foreach my $key (keys %{$self->{cond}}) {
1043 $cond->{$1} = $self->{cond}{$key};
1048 $self->throw_exception(
1049 "Can't update/delete on resultset with condition unless hash or array"
1061 =item Arguments: \%values
1063 =item Return Value: $storage_rv
1067 Sets the specified columns in the resultset to the supplied values in a
1068 single query. Return value will be true if the update succeeded or false
1069 if no records were updated; exact type of success value is storage-dependent.
1074 my ($self, $values) = @_;
1075 $self->throw_exception("Values for update must be a hash")
1076 unless ref $values eq 'HASH';
1078 my $cond = $self->_cond_for_update_delete;
1080 return $self->result_source->storage->update(
1081 $self->result_source->from, $values, $cond
1089 =item Arguments: \%values
1091 =item Return Value: 1
1095 Fetches all objects and updates them one at a time. Note that C<update_all>
1096 will run DBIC cascade triggers, while L</update> will not.
1101 my ($self, $values) = @_;
1102 $self->throw_exception("Values for update must be a hash")
1103 unless ref $values eq 'HASH';
1104 foreach my $obj ($self->all) {
1105 $obj->set_columns($values)->update;
1114 =item Arguments: none
1116 =item Return Value: 1
1120 Deletes the contents of the resultset from its result source. Note that this
1121 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
1130 my $cond = $self->_cond_for_update_delete;
1132 $self->result_source->storage->delete($self->result_source->from, $cond);
1140 =item Arguments: none
1142 =item Return Value: 1
1146 Fetches all objects and deletes them one at a time. Note that C<delete_all>
1147 will run DBIC cascade triggers, while L</delete> will not.
1153 $_->delete for $self->all;
1161 =item Arguments: none
1163 =item Return Value: $pager
1167 Return Value a L<Data::Page> object for the current resultset. Only makes
1168 sense for queries with a C<page> attribute.
1174 my $attrs = $self->{attrs};
1175 $self->throw_exception("Can't create pager for non-paged rs")
1176 unless $self->{page};
1177 $attrs->{rows} ||= 10;
1178 return $self->{pager} ||= Data::Page->new(
1179 $self->_count, $attrs->{rows}, $self->{page});
1186 =item Arguments: $page_number
1188 =item Return Value: $rs
1192 Returns a resultset for the $page_number page of the resultset on which page
1193 is called, where each page contains a number of rows equal to the 'rows'
1194 attribute set on the resultset (10 by default).
1199 my ($self, $page) = @_;
1200 my $attrs = { %{$self->{attrs}} };
1201 $attrs->{page} = $page;
1202 return (ref $self)->new($self->result_source, $attrs);
1209 =item Arguments: \%vals
1211 =item Return Value: $object
1215 Creates an object in the resultset's result class and returns it.
1220 my ($self, $values) = @_;
1221 $self->throw_exception( "new_result needs a hash" )
1222 unless (ref $values eq 'HASH');
1223 $self->throw_exception(
1224 "Can't abstract implicit construct, condition not a hash"
1225 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
1227 my $alias = $self->{attrs}{alias};
1228 foreach my $key (keys %{$self->{cond}||{}}) {
1229 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
1231 my $obj = $self->result_class->new(\%new);
1232 $obj->result_source($self->result_source) if $obj->can('result_source');
1240 =item Arguments: \%vals, \%attrs?
1242 =item Return Value: $object
1246 Find an existing record from this resultset. If none exists, instantiate a new
1247 result object and return it. The object will not be saved into your storage
1248 until you call L<DBIx::Class::Row/insert> on it.
1250 If you want objects to be saved immediately, use L</find_or_create> instead.
1256 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1257 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1258 my $exists = $self->find($hash, $attrs);
1259 return defined $exists ? $exists : $self->new_result($hash);
1266 =item Arguments: \%vals
1268 =item Return Value: $object
1272 Inserts a record into the resultset and returns the object representing it.
1274 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1279 my ($self, $attrs) = @_;
1280 $self->throw_exception( "create needs a hashref" )
1281 unless ref $attrs eq 'HASH';
1282 return $self->new_result($attrs)->insert;
1285 =head2 find_or_create
1289 =item Arguments: \%vals, \%attrs?
1291 =item Return Value: $object
1295 $class->find_or_create({ key => $val, ... });
1297 Searches for a record matching the search condition; if it doesn't find one,
1298 creates one and returns that instead.
1300 my $cd = $schema->resultset('CD')->find_or_create({
1302 artist => 'Massive Attack',
1303 title => 'Mezzanine',
1307 Also takes an optional C<key> attribute, to search by a specific key or unique
1308 constraint. For example:
1310 my $cd = $schema->resultset('CD')->find_or_create(
1312 artist => 'Massive Attack',
1313 title => 'Mezzanine',
1315 { key => 'artist_title' }
1318 See also L</find> and L</update_or_create>. For information on how to declare
1319 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1323 sub find_or_create {
1325 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1326 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1327 my $exists = $self->find($hash, $attrs);
1328 return defined $exists ? $exists : $self->create($hash);
1331 =head2 update_or_create
1335 =item Arguments: \%col_values, { key => $unique_constraint }?
1337 =item Return Value: $object
1341 $class->update_or_create({ col => $val, ... });
1343 First, searches for an existing row matching one of the unique constraints
1344 (including the primary key) on the source of this resultset. If a row is
1345 found, updates it with the other given column values. Otherwise, creates a new
1348 Takes an optional C<key> attribute to search on a specific unique constraint.
1351 # In your application
1352 my $cd = $schema->resultset('CD')->update_or_create(
1354 artist => 'Massive Attack',
1355 title => 'Mezzanine',
1358 { key => 'artist_title' }
1361 If no C<key> is specified, it searches on all unique constraints defined on the
1362 source, including the primary key.
1364 If the C<key> is specified as C<primary>, it searches only on the primary key.
1366 See also L</find> and L</find_or_create>. For information on how to declare
1367 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1371 sub update_or_create {
1373 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1374 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1376 my $row = $self->find($hash, $attrs);
1378 $row->update($hash);
1382 return $self->create($hash);
1389 =item Arguments: none
1391 =item Return Value: \@cache_objects?
1395 Gets the contents of the cache for the resultset, if the cache is set.
1407 =item Arguments: \@cache_objects
1409 =item Return Value: \@cache_objects
1413 Sets the contents of the cache for the resultset. Expects an arrayref
1414 of objects of the same class as those produced by the resultset. Note that
1415 if the cache is set the resultset will return the cached objects rather
1416 than re-querying the database even if the cache attr is not set.
1421 my ( $self, $data ) = @_;
1422 $self->throw_exception("set_cache requires an arrayref")
1423 if defined($data) && (ref $data ne 'ARRAY');
1424 $self->{all_cache} = $data;
1431 =item Arguments: none
1433 =item Return Value: []
1437 Clears the cache for the resultset.
1442 shift->set_cache(undef);
1445 =head2 related_resultset
1449 =item Arguments: $relationship_name
1451 =item Return Value: $resultset
1455 Returns a related resultset for the supplied relationship name.
1457 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1461 sub related_resultset {
1462 my ( $self, $rel ) = @_;
1464 $self->{related_resultsets} ||= {};
1465 return $self->{related_resultsets}{$rel} ||= do {
1466 #warn "fetching related resultset for rel '$rel' " . $self->result_source->{name};
1467 my $rel_obj = $self->result_source->relationship_info($rel);
1468 $self->throw_exception(
1469 "search_related: result source '" . $self->result_source->name .
1470 "' has no such relationship ${rel}")
1471 unless $rel_obj; #die Dumper $self->{attrs};
1473 my $rs = $self->result_source->schema->resultset($rel_obj->{class}
1475 { %{$self->{attrs}},
1479 _live_join => $rel }
1482 # keep reference of the original resultset
1483 $rs->{_parent_rs} = $self->result_source;
1488 =head2 throw_exception
1490 See L<DBIx::Class::Schema/throw_exception> for details.
1494 sub throw_exception {
1496 $self->result_source->schema->throw_exception(@_);
1499 # XXX: FIXME: Attributes docs need clearing up
1503 The resultset takes various attributes that modify its behavior. Here's an
1510 =item Value: ($order_by | \@order_by)
1514 Which column(s) to order the results by. This is currently passed
1515 through directly to SQL, so you can give e.g. C<year DESC> for a
1516 descending order on the column `year'.
1518 Please note that if you have quoting enabled (see
1519 L<DBIx::Class::Storage/quote_char>) you will need to do C<\'year DESC' > to
1520 specify an order. (The scalar ref causes it to be passed as raw sql to the DB,
1521 so you will need to manually quote things as appropriate.)
1527 =item Value: \@columns
1531 Shortcut to request a particular set of columns to be retrieved. Adds
1532 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1533 from that, then auto-populates C<as> from C<select> as normal. (You may also
1534 use the C<cols> attribute, as in earlier versions of DBIC.)
1536 =head2 include_columns
1540 =item Value: \@columns
1544 Shortcut to include additional columns in the returned results - for example
1546 $schema->resultset('CD')->search(undef, {
1547 include_columns => ['artist.name'],
1551 would return all CDs and include a 'name' column to the information
1552 passed to object inflation
1558 =item Value: \@select_columns
1562 Indicates which columns should be selected from the storage. You can use
1563 column names, or in the case of RDBMS back ends, function or stored procedure
1566 $rs = $schema->resultset('Employee')->search(undef, {
1569 { count => 'employeeid' },
1574 When you use function/stored procedure names and do not supply an C<as>
1575 attribute, the column names returned are storage-dependent. E.g. MySQL would
1576 return a column named C<count(employeeid)> in the above example.
1582 Indicates additional columns to be selected from storage. Works the same as
1583 L<select> but adds columns to the selection.
1591 Indicates additional column names for those added via L<+select>.
1599 =item Value: \@inflation_names
1603 Indicates column names for object inflation. This is used in conjunction with
1604 C<select>, usually when C<select> contains one or more function or stored
1607 $rs = $schema->resultset('Employee')->search(undef, {
1610 { count => 'employeeid' }
1612 as => ['name', 'employee_count'],
1615 my $employee = $rs->first(); # get the first Employee
1617 If the object against which the search is performed already has an accessor
1618 matching a column name specified in C<as>, the value can be retrieved using
1619 the accessor as normal:
1621 my $name = $employee->name();
1623 If on the other hand an accessor does not exist in the object, you need to
1624 use C<get_column> instead:
1626 my $employee_count = $employee->get_column('employee_count');
1628 You can create your own accessors if required - see
1629 L<DBIx::Class::Manual::Cookbook> for details.
1631 Please note: This will NOT insert an C<AS employee_count> into the SQL statement
1632 produced, it is used for internal access only. Thus attempting to use the accessor
1633 in an C<order_by> clause or similar will fail misrably.
1639 =item Value: ($rel_name | \@rel_names | \%rel_names)
1643 Contains a list of relationships that should be joined for this query. For
1646 # Get CDs by Nine Inch Nails
1647 my $rs = $schema->resultset('CD')->search(
1648 { 'artist.name' => 'Nine Inch Nails' },
1649 { join => 'artist' }
1652 Can also contain a hash reference to refer to the other relation's relations.
1655 package MyApp::Schema::Track;
1656 use base qw/DBIx::Class/;
1657 __PACKAGE__->table('track');
1658 __PACKAGE__->add_columns(qw/trackid cd position title/);
1659 __PACKAGE__->set_primary_key('trackid');
1660 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1663 # In your application
1664 my $rs = $schema->resultset('Artist')->search(
1665 { 'track.title' => 'Teardrop' },
1667 join => { cd => 'track' },
1668 order_by => 'artist.name',
1672 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1673 similarly for a third time). For e.g.
1675 my $rs = $schema->resultset('Artist')->search({
1676 'cds.title' => 'Down to Earth',
1677 'cds_2.title' => 'Popular',
1679 join => [ qw/cds cds/ ],
1682 will return a set of all artists that have both a cd with title 'Down
1683 to Earth' and a cd with title 'Popular'.
1685 If you want to fetch related objects from other tables as well, see C<prefetch>
1692 =item Value: ($rel_name | \@rel_names | \%rel_names)
1696 Contains one or more relationships that should be fetched along with the main
1697 query (when they are accessed afterwards they will have already been
1698 "prefetched"). This is useful for when you know you will need the related
1699 objects, because it saves at least one query:
1701 my $rs = $schema->resultset('Tag')->search(
1710 The initial search results in SQL like the following:
1712 SELECT tag.*, cd.*, artist.* FROM tag
1713 JOIN cd ON tag.cd = cd.cdid
1714 JOIN artist ON cd.artist = artist.artistid
1716 L<DBIx::Class> has no need to go back to the database when we access the
1717 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1720 Simple prefetches will be joined automatically, so there is no need
1721 for a C<join> attribute in the above search. If you're prefetching to
1722 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1723 specify the join as well.
1725 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1726 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1727 with an accessor type of 'single' or 'filter').
1737 Makes the resultset paged and specifies the page to retrieve. Effectively
1738 identical to creating a non-pages resultset and then calling ->page($page)
1741 If L<rows> attribute is not specified it defualts to 10 rows per page.
1751 Specifes the maximum number of rows for direct retrieval or the number of
1752 rows per page if the page attribute or method is used.
1758 =item Value: $offset
1762 Specifies the (zero-based) row number for the first row to be returned, or the
1763 of the first row of the first page if paging is used.
1769 =item Value: \@columns
1773 A arrayref of columns to group by. Can include columns of joined tables.
1775 group_by => [qw/ column1 column2 ... /]
1781 =item Value: $condition
1785 HAVING is a select statement attribute that is applied between GROUP BY and
1786 ORDER BY. It is applied to the after the grouping calculations have been
1789 having => { 'count(employee)' => { '>=', 100 } }
1795 =item Value: (0 | 1)
1799 Set to 1 to group by all columns.
1803 Set to 1 to cache search results. This prevents extra SQL queries if you
1804 revisit rows in your ResultSet:
1806 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1808 while( my $artist = $resultset->next ) {
1812 $rs->first; # without cache, this would issue a query
1814 By default, searches are not cached.
1816 For more examples of using these attributes, see
1817 L<DBIx::Class::Manual::Cookbook>.
1823 =item Value: \@from_clause
1827 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1828 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1831 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1833 C<join> will usually do what you need and it is strongly recommended that you
1834 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1835 And we really do mean "cannot", not just tried and failed. Attempting to use
1836 this because you're having problems with C<join> is like trying to use x86
1837 ASM because you've got a syntax error in your C. Trust us on this.
1839 Now, if you're still really, really sure you need to use this (and if you're
1840 not 100% sure, ask the mailing list first), here's an explanation of how this
1843 The syntax is as follows -
1846 { <alias1> => <table1> },
1848 { <alias2> => <table2>, -join_type => 'inner|left|right' },
1849 [], # nested JOIN (optional)
1850 { <table1.column1> => <table2.column2>, ... (more conditions) },
1852 # More of the above [ ] may follow for additional joins
1859 ON <table1.column1> = <table2.column2>
1860 <more joins may follow>
1862 An easy way to follow the examples below is to remember the following:
1864 Anything inside "[]" is a JOIN
1865 Anything inside "{}" is a condition for the enclosing JOIN
1867 The following examples utilize a "person" table in a family tree application.
1868 In order to express parent->child relationships, this table is self-joined:
1870 # Person->belongs_to('father' => 'Person');
1871 # Person->belongs_to('mother' => 'Person');
1873 C<from> can be used to nest joins. Here we return all children with a father,
1874 then search against all mothers of those children:
1876 $rs = $schema->resultset('Person')->search(
1879 alias => 'mother', # alias columns in accordance with "from"
1881 { mother => 'person' },
1884 { child => 'person' },
1886 { father => 'person' },
1887 { 'father.person_id' => 'child.father_id' }
1890 { 'mother.person_id' => 'child.mother_id' }
1897 # SELECT mother.* FROM person mother
1900 # JOIN person father
1901 # ON ( father.person_id = child.father_id )
1903 # ON ( mother.person_id = child.mother_id )
1905 The type of any join can be controlled manually. To search against only people
1906 with a father in the person table, we could explicitly use C<INNER JOIN>:
1908 $rs = $schema->resultset('Person')->search(
1911 alias => 'child', # alias columns in accordance with "from"
1913 { child => 'person' },
1915 { father => 'person', -join_type => 'inner' },
1916 { 'father.id' => 'child.father_id' }
1923 # SELECT child.* FROM person child
1924 # INNER JOIN person father ON child.father_id = father.id