1 package DBIx::Class::ResultSet;
11 use Scalar::Util qw/weaken/;
13 use DBIx::Class::ResultSetColumn;
14 use base qw/DBIx::Class/;
15 __PACKAGE__->load_components(qw/AccessorGroup/);
16 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
20 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
24 my $rs = $schema->resultset('User')->search(registered => 1);
25 my @rows = $schema->resultset('CD')->search(year => 2005);
29 The resultset is also known as an iterator. It is responsible for handling
30 queries that may return an arbitrary number of rows, e.g. via L</search>
31 or a C<has_many> relationship.
33 In the examples below, the following table classes are used:
35 package MyApp::Schema::Artist;
36 use base qw/DBIx::Class/;
37 __PACKAGE__->load_components(qw/Core/);
38 __PACKAGE__->table('artist');
39 __PACKAGE__->add_columns(qw/artistid name/);
40 __PACKAGE__->set_primary_key('artistid');
41 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
44 package MyApp::Schema::CD;
45 use base qw/DBIx::Class/;
46 __PACKAGE__->load_components(qw/Core/);
47 __PACKAGE__->table('cd');
48 __PACKAGE__->add_columns(qw/cdid artist title year/);
49 __PACKAGE__->set_primary_key('cdid');
50 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
59 =item Arguments: $source, \%$attrs
61 =item Return Value: $rs
65 The resultset constructor. Takes a source object (usually a
66 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
67 L</ATTRIBUTES> below). Does not perform any queries -- these are
68 executed as needed by the other methods.
70 Generally you won't need to construct a resultset manually. You'll
71 automatically get one from e.g. a L</search> called in scalar context:
73 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
75 IMPORTANT: If called on an object, proxies to new_result instead so
77 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
79 will return a CD object, not a ResultSet.
85 return $class->new_result(@_) if ref $class;
87 my ($source, $attrs) = @_;
89 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
90 #use Data::Dumper; warn Dumper($attrs);
91 my $alias = ($attrs->{alias} ||= 'me');
93 $attrs->{columns} ||= delete $attrs->{cols} if $attrs->{cols};
94 delete $attrs->{as} if $attrs->{columns};
95 $attrs->{columns} ||= [ $source->columns ] unless $attrs->{select};
97 map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}}
98 ] if $attrs->{columns};
100 map { m/^\Q$alias.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}}
102 if (my $include = delete $attrs->{include_columns}) {
103 push(@{$attrs->{select}}, @$include);
104 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1; } @$include);
106 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
108 $attrs->{from} ||= [ { $alias => $source->from } ];
109 $attrs->{seen_join} ||= {};
111 if (my $join = delete $attrs->{join}) {
112 foreach my $j (ref $join eq 'ARRAY' ? @$join : ($join)) {
113 if (ref $j eq 'HASH') {
114 $seen{$_} = 1 foreach keys %$j;
119 push(@{$attrs->{from}}, $source->resolve_join(
120 $join, $attrs->{alias}, $attrs->{seen_join})
124 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
125 $attrs->{order_by} = [ $attrs->{order_by} ] if
126 $attrs->{order_by} and !ref($attrs->{order_by});
127 $attrs->{order_by} ||= [];
129 my $collapse = $attrs->{collapse} || {};
130 if (my $prefetch = delete $attrs->{prefetch}) {
132 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
133 if ( ref $p eq 'HASH' ) {
134 foreach my $key (keys %$p) {
135 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
139 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
142 my @prefetch = $source->resolve_prefetch(
143 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
144 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
145 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
147 push(@{$attrs->{order_by}}, @pre_order);
149 $attrs->{collapse} = $collapse;
150 # use Data::Dumper; warn Dumper($collapse) if keys %{$collapse};
152 if ($attrs->{page}) {
153 $attrs->{rows} ||= 10;
154 $attrs->{offset} ||= 0;
155 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
159 result_source => $source,
160 result_class => $attrs->{result_class} || $source->result_class,
161 cond => $attrs->{where},
162 from => $attrs->{from},
163 collapse => $collapse,
165 page => delete $attrs->{page},
175 =item Arguments: $cond, \%attrs?
177 =item Return Value: $resultset (scalar context), @row_objs (list context)
181 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
182 my $new_rs = $cd_rs->search({ year => 2005 });
184 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
185 # year = 2005 OR year = 2004
187 If you need to pass in additional attributes but no additional condition,
188 call it as C<search(undef, \%attrs)>.
190 # "SELECT name, artistid FROM $artist_table"
191 my @all_artists = $schema->resultset('Artist')->search(undef, {
192 columns => [qw/name artistid/],
203 my $attrs = { %{$self->{attrs}} };
204 my $having = delete $attrs->{having};
205 $attrs = { %$attrs, %{ pop(@_) } } if @_ > 1 and ref $_[$#_] eq 'HASH';
208 ? ((@_ == 1 || ref $_[0] eq "HASH")
211 ? $self->throw_exception(
212 "Odd number of arguments to search")
215 if (defined $where) {
216 $attrs->{where} = (defined $attrs->{where}
218 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
219 $where, $attrs->{where} ] }
223 if (defined $having) {
224 $attrs->{having} = (defined $attrs->{having}
226 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
227 $having, $attrs->{having} ] }
231 $rs = (ref $self)->new($self->result_source, $attrs);
237 return (wantarray ? $rs->all : $rs);
240 =head2 search_literal
244 =item Arguments: $sql_fragment, @bind_values
246 =item Return Value: $resultset (scalar context), @row_objs (list context)
250 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
251 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
253 Pass a literal chunk of SQL to be added to the conditional part of the
259 my ($self, $cond, @vals) = @_;
260 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
261 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
262 return $self->search(\$cond, $attrs);
269 =item Arguments: @values | \%cols, \%attrs?
271 =item Return Value: $row_object
275 Finds a row based on its primary key or unique constraint. For example, to find
276 a row by its primary key:
278 my $cd = $schema->resultset('CD')->find(5);
280 You can also find a row by a specific key or unique constraint by specifying
281 the C<key> attribute. For example:
283 my $cd = $schema->resultset('CD')->find('Massive Attack', 'Mezzanine', { key => 'artist_title' });
285 Additionally, you can specify the columns explicitly by name:
287 my $cd = $schema->resultset('CD')->find(
289 artist => 'Massive Attack',
290 title => 'Mezzanine',
292 { key => 'artist_title' }
295 If no C<key> is specified and you explicitly name columns, it searches on all
296 unique constraints defined on the source, including the primary key.
298 If the C<key> is specified as C<primary>, it searches only on the primary key.
300 See also L</find_or_create> and L</update_or_create>. For information on how to
301 declare unique constraints, see
302 L<DBIx::Class::ResultSource/add_unique_constraint>.
307 my ($self, @vals) = @_;
308 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
310 # Parse out a hash from input
311 my @unique_cols = exists $attrs->{key}
312 ? $self->result_source->unique_constraint_columns($attrs->{key})
313 : $self->result_source->primary_columns;
316 if (ref $vals[0] eq 'HASH') {
317 %hash = %{ $vals[0] };
319 elsif (@vals == @unique_cols) {
320 @hash{@unique_cols} = @vals;
323 # Hack for CDBI queries
327 # Check the hash we just parsed against our source's unique constraints
328 my @constraint_names = exists $attrs->{key}
330 : $self->result_source->unique_constraint_names;
331 $self->throw_exception(
332 "Can't find unless a primary key or unique constraint is defined"
333 ) unless @constraint_names;
336 foreach my $name (@constraint_names) {
337 my @unique_cols = $self->result_source->unique_constraint_columns($name);
338 my $unique_hash = $self->_unique_hash(\%hash, \@unique_cols);
340 # TODO: Check that the ResultSet defines the rest of the query
341 push @unique_hashes, $unique_hash
342 if scalar keys %$unique_hash;# == scalar @unique_cols;
345 # Add the ResultSet's alias
346 foreach my $unique_hash (@unique_hashes) {
347 foreach my $key (grep { ! m/\./ } keys %$unique_hash) {
348 $unique_hash->{"$self->{attrs}{alias}.$key"} = delete $unique_hash->{$key};
352 # Handle cases where the ResultSet already defines the query
353 my $query = @unique_hashes ? \@unique_hashes : undef;
357 my $rs = $self->search($query, $attrs);
358 return keys %{$rs->{collapse}} ? $rs->next : $rs->single;
361 return keys %{$self->{collapse}}
362 ? $self->search($query)->next
363 : $self->single($query);
369 # Constrain the specified hash based on the specific column names.
372 my ($self, $hash, $unique_cols) = @_;
374 # Ugh, CDBI lowercases column names
375 if (exists $INC{'DBIx/Class/CDBICompat/ColumnCase.pm'}) {
376 foreach my $key (keys %$hash) {
377 $hash->{lc $key} = delete $hash->{$key};
382 map { $_ => $hash->{$_} }
383 grep { exists $hash->{$_} }
386 return \%unique_hash;
389 =head2 search_related
393 =item Arguments: $cond, \%attrs?
395 =item Return Value: $new_resultset
399 $new_rs = $cd_rs->search_related('artist', {
403 Searches the specified relationship, optionally specifying a condition and
404 attributes for matching records. See L</ATTRIBUTES> for more information.
409 return shift->related_resultset(shift)->search(@_);
416 =item Arguments: none
418 =item Return Value: $cursor
422 Returns a storage-driven cursor to the given resultset. See
423 L<DBIx::Class::Cursor> for more information.
429 my $attrs = { %{$self->{attrs}} };
430 return $self->{cursor}
431 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
432 $attrs->{where},$attrs);
439 =item Arguments: $cond?
441 =item Return Value: $row_object?
445 my $cd = $schema->resultset('CD')->single({ year => 2001 });
447 Inflates the first result without creating a cursor if the resultset has
448 any records in it; if not returns nothing. Used by L</find> as an optimisation.
453 my ($self, $where) = @_;
454 my $attrs = { %{$self->{attrs}} };
456 if (defined $attrs->{where}) {
459 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
460 $where, delete $attrs->{where} ]
463 $attrs->{where} = $where;
466 my @data = $self->result_source->storage->select_single(
467 $self->{from}, $attrs->{select},
468 $attrs->{where},$attrs);
469 return (@data ? $self->_construct_object(@data) : ());
476 =item Arguments: $cond?
478 =item Return Value: $resultsetcolumn
482 my $max_length = $rs->get_column('length')->max;
484 Returns a ResultSetColumn instance for $column based on $self
489 my ($self, $column) = @_;
491 my $new = DBIx::Class::ResultSetColumn->new($self, $column);
499 =item Arguments: $cond, \%attrs?
501 =item Return Value: $resultset (scalar context), @row_objs (list context)
505 # WHERE title LIKE '%blue%'
506 $cd_rs = $rs->search_like({ title => '%blue%'});
508 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
509 that this is simply a convenience method. You most likely want to use
510 L</search> with specific operators.
512 For more information, see L<DBIx::Class::Manual::Cookbook>.
518 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
519 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
520 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
521 return $class->search($query, { %$attrs });
528 =item Arguments: $first, $last
530 =item Return Value: $resultset (scalar context), @row_objs (list context)
534 Returns a resultset or object list representing a subset of elements from the
535 resultset slice is called on. Indexes are from 0, i.e., to get the first
538 my ($one, $two, $three) = $rs->slice(0, 2);
543 my ($self, $min, $max) = @_;
544 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
545 $attrs->{offset} = $self->{attrs}{offset} || 0;
546 $attrs->{offset} += $min;
547 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
548 return $self->search(undef(), $attrs);
549 #my $slice = (ref $self)->new($self->result_source, $attrs);
550 #return (wantarray ? $slice->all : $slice);
557 =item Arguments: none
559 =item Return Value: $result?
563 Returns the next element in the resultset (C<undef> is there is none).
565 Can be used to efficiently iterate over records in the resultset:
567 my $rs = $schema->resultset('CD')->search;
568 while (my $cd = $rs->next) {
572 Note that you need to store the resultset object, and call C<next> on it.
573 Calling C<< resultset('Table')->next >> repeatedly will always return the
574 first record from the resultset.
580 if (@{$self->{all_cache} || []}) {
581 $self->{all_cache_position} ||= 0;
582 return $self->{all_cache}->[$self->{all_cache_position}++];
584 if ($self->{attrs}{cache}) {
585 $self->{all_cache_position} = 1;
586 return ($self->all)[0];
588 my @row = (exists $self->{stashed_row} ?
589 @{delete $self->{stashed_row}} :
592 # warn Dumper(\@row); use Data::Dumper;
593 return unless (@row);
594 return $self->_construct_object(@row);
597 sub _construct_object {
598 my ($self, @row) = @_;
599 my @as = @{ $self->{attrs}{as} };
601 my $info = $self->_collapse_result(\@as, \@row);
603 my $new = $self->result_class->inflate_result($self->result_source, @$info);
605 $new = $self->{attrs}{record_filter}->($new)
606 if exists $self->{attrs}{record_filter};
610 sub _collapse_result {
611 my ($self, $as, $row, $prefix) = @_;
616 foreach my $this_as (@$as) {
617 my $val = shift @copy;
618 if (defined $prefix) {
619 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
621 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
622 $const{$1||''}{$2} = $val;
625 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
626 $const{$1||''}{$2} = $val;
630 my $info = [ {}, {} ];
631 foreach my $key (keys %const) {
634 my @parts = split(/\./, $key);
635 foreach my $p (@parts) {
636 $target = $target->[1]->{$p} ||= [];
638 $target->[0] = $const{$key};
640 $info->[0] = $const{$key};
645 if (defined $prefix) {
647 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
648 } keys %{$self->{collapse}}
650 @collapse = keys %{$self->{collapse}};
654 my ($c) = sort { length $a <=> length $b } @collapse;
656 foreach my $p (split(/\./, $c)) {
657 $target = $target->[1]->{$p} ||= [];
659 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
660 my @co_key = @{$self->{collapse}{$c_prefix}};
661 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
662 my $tree = $self->_collapse_result($as, $row, $c_prefix);
665 !defined($tree->[0]->{$_}) ||
666 $co_check{$_} ne $tree->[0]->{$_}
669 last unless (@raw = $self->cursor->next);
670 $row = $self->{stashed_row} = \@raw;
671 $tree = $self->_collapse_result($as, $row, $c_prefix);
672 #warn Data::Dumper::Dumper($tree, $row);
684 =item Arguments: $result_source?
686 =item Return Value: $result_source
690 An accessor for the primary ResultSource object from which this ResultSet
700 =item Arguments: $cond, \%attrs??
702 =item Return Value: $count
706 Performs an SQL C<COUNT> with the same query as the resultset was built
707 with to find the number of elements. If passed arguments, does a search
708 on the resultset and counts the results of that.
710 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
711 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
712 not support C<DISTINCT> with multiple columns. If you are using such a
713 database, you should only use columns from the main table in your C<group_by>
720 return $self->search(@_)->count if @_ and defined $_[0];
721 return scalar @{ $self->get_cache } if @{ $self->get_cache };
723 my $count = $self->_count;
724 return 0 unless $count;
726 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
727 $count = $self->{attrs}{rows} if
728 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
732 sub _count { # Separated out so pager can get the full count
734 my $select = { count => '*' };
735 my $attrs = { %{ $self->{attrs} } };
736 if (my $group_by = delete $attrs->{group_by}) {
737 delete $attrs->{having};
738 my @distinct = (ref $group_by ? @$group_by : ($group_by));
739 # todo: try CONCAT for multi-column pk
740 my @pk = $self->result_source->primary_columns;
742 foreach my $column (@distinct) {
743 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
744 @distinct = ($column);
750 $select = { count => { distinct => \@distinct } };
751 #use Data::Dumper; die Dumper $select;
754 $attrs->{select} = $select;
755 $attrs->{as} = [qw/count/];
757 # offset, order by and page are not needed to count. record_filter is cdbi
758 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
760 my ($count) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
768 =item Arguments: $sql_fragment, @bind_values
770 =item Return Value: $count
774 Counts the results in a literal query. Equivalent to calling L</search_literal>
775 with the passed arguments, then L</count>.
779 sub count_literal { shift->search_literal(@_)->count; }
785 =item Arguments: none
787 =item Return Value: @objects
791 Returns all elements in the resultset. Called implicitly if the resultset
792 is returned in list context.
798 return @{ $self->get_cache } if @{ $self->get_cache };
802 if (keys %{$self->{collapse}}) {
803 # Using $self->cursor->all is really just an optimisation.
804 # If we're collapsing has_many prefetches it probably makes
805 # very little difference, and this is cleaner than hacking
806 # _construct_object to survive the approach
807 $self->cursor->reset;
808 my @row = $self->cursor->next;
810 push(@obj, $self->_construct_object(@row));
811 @row = (exists $self->{stashed_row}
812 ? @{delete $self->{stashed_row}}
813 : $self->cursor->next);
816 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
819 $self->set_cache(\@obj) if $self->{attrs}{cache};
827 =item Arguments: none
829 =item Return Value: $self
833 Resets the resultset's cursor, so you can iterate through the elements again.
839 $self->{all_cache_position} = 0;
840 $self->cursor->reset;
848 =item Arguments: none
850 =item Return Value: $object?
854 Resets the resultset and returns an object for the first result (if the
855 resultset returns anything).
860 return $_[0]->reset->next;
863 # _cond_for_update_delete
865 # update/delete require the condition to be modified to handle
866 # the differing SQL syntax available. This transforms the $self->{cond}
867 # appropriately, returning the new condition.
869 sub _cond_for_update_delete {
873 if (!ref($self->{cond})) {
874 # No-op. No condition, we're updating/deleting everything
876 elsif (ref $self->{cond} eq 'ARRAY') {
880 foreach my $key (keys %{$_}) {
882 $hash{$1} = $_->{$key};
888 elsif (ref $self->{cond} eq 'HASH') {
889 if ((keys %{$self->{cond}})[0] eq '-and') {
892 my @cond = @{$self->{cond}{-and}};
893 for (my $i = 0; $i < @cond - 1; $i++) {
894 my $entry = $cond[$i];
897 if (ref $entry eq 'HASH') {
898 foreach my $key (keys %{$entry}) {
900 $hash{$1} = $entry->{$key};
904 $entry =~ /([^.]+)$/;
905 $hash{$entry} = $cond[++$i];
908 push @{$cond->{-and}}, \%hash;
912 foreach my $key (keys %{$self->{cond}}) {
914 $cond->{$1} = $self->{cond}{$key};
919 $self->throw_exception(
920 "Can't update/delete on resultset with condition unless hash or array"
932 =item Arguments: \%values
934 =item Return Value: $storage_rv
938 Sets the specified columns in the resultset to the supplied values in a
939 single query. Return value will be true if the update succeeded or false
940 if no records were updated; exact type of success value is storage-dependent.
945 my ($self, $values) = @_;
946 $self->throw_exception("Values for update must be a hash")
947 unless ref $values eq 'HASH';
949 my $cond = $self->_cond_for_update_delete;
951 return $self->result_source->storage->update(
952 $self->result_source->from, $values, $cond
960 =item Arguments: \%values
962 =item Return Value: 1
966 Fetches all objects and updates them one at a time. Note that C<update_all>
967 will run DBIC cascade triggers, while L</update> will not.
972 my ($self, $values) = @_;
973 $self->throw_exception("Values for update must be a hash")
974 unless ref $values eq 'HASH';
975 foreach my $obj ($self->all) {
976 $obj->set_columns($values)->update;
985 =item Arguments: none
987 =item Return Value: 1
991 Deletes the contents of the resultset from its result source. Note that this
992 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
1001 my $cond = $self->_cond_for_update_delete;
1003 $self->result_source->storage->delete($self->result_source->from, $cond);
1011 =item Arguments: none
1013 =item Return Value: 1
1017 Fetches all objects and deletes them one at a time. Note that C<delete_all>
1018 will run DBIC cascade triggers, while L</delete> will not.
1024 $_->delete for $self->all;
1032 =item Arguments: none
1034 =item Return Value: $pager
1038 Return Value a L<Data::Page> object for the current resultset. Only makes
1039 sense for queries with a C<page> attribute.
1045 my $attrs = $self->{attrs};
1046 $self->throw_exception("Can't create pager for non-paged rs")
1047 unless $self->{page};
1048 $attrs->{rows} ||= 10;
1049 return $self->{pager} ||= Data::Page->new(
1050 $self->_count, $attrs->{rows}, $self->{page});
1057 =item Arguments: $page_number
1059 =item Return Value: $rs
1063 Returns a resultset for the $page_number page of the resultset on which page
1064 is called, where each page contains a number of rows equal to the 'rows'
1065 attribute set on the resultset (10 by default).
1070 my ($self, $page) = @_;
1071 my $attrs = { %{$self->{attrs}} };
1072 $attrs->{page} = $page;
1073 return (ref $self)->new($self->result_source, $attrs);
1080 =item Arguments: \%vals
1082 =item Return Value: $object
1086 Creates an object in the resultset's result class and returns it.
1091 my ($self, $values) = @_;
1092 $self->throw_exception( "new_result needs a hash" )
1093 unless (ref $values eq 'HASH');
1094 $self->throw_exception(
1095 "Can't abstract implicit construct, condition not a hash"
1096 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
1098 my $alias = $self->{attrs}{alias};
1099 foreach my $key (keys %{$self->{cond}||{}}) {
1100 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
1102 my $obj = $self->result_class->new(\%new);
1103 $obj->result_source($self->result_source) if $obj->can('result_source');
1111 =item Arguments: \%vals, \%attrs?
1113 =item Return Value: $object
1117 Find an existing record from this resultset. If none exists, instantiate a new
1118 result object and return it. The object will not be saved into your storage
1119 until you call L<DBIx::Class::Row/insert> on it.
1121 If you want objects to be saved immediately, use L</find_or_create> instead.
1127 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1128 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1129 my $exists = $self->find($hash, $attrs);
1130 return defined $exists ? $exists : $self->new_result($hash);
1137 =item Arguments: \%vals
1139 =item Return Value: $object
1143 Inserts a record into the resultset and returns the object representing it.
1145 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1150 my ($self, $attrs) = @_;
1151 $self->throw_exception( "create needs a hashref" )
1152 unless ref $attrs eq 'HASH';
1153 return $self->new_result($attrs)->insert;
1156 =head2 find_or_create
1160 =item Arguments: \%vals, \%attrs?
1162 =item Return Value: $object
1166 $class->find_or_create({ key => $val, ... });
1168 Searches for a record matching the search condition; if it doesn't find one,
1169 creates one and returns that instead.
1171 my $cd = $schema->resultset('CD')->find_or_create({
1173 artist => 'Massive Attack',
1174 title => 'Mezzanine',
1178 Also takes an optional C<key> attribute, to search by a specific key or unique
1179 constraint. For example:
1181 my $cd = $schema->resultset('CD')->find_or_create(
1183 artist => 'Massive Attack',
1184 title => 'Mezzanine',
1186 { key => 'artist_title' }
1189 See also L</find> and L</update_or_create>. For information on how to declare
1190 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1194 sub find_or_create {
1196 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1197 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1198 my $exists = $self->find($hash, $attrs);
1199 return defined $exists ? $exists : $self->create($hash);
1202 =head2 update_or_create
1206 =item Arguments: \%col_values, { key => $unique_constraint }?
1208 =item Return Value: $object
1212 $class->update_or_create({ col => $val, ... });
1214 First, searches for an existing row matching one of the unique constraints
1215 (including the primary key) on the source of this resultset. If a row is
1216 found, updates it with the other given column values. Otherwise, creates a new
1219 Takes an optional C<key> attribute to search on a specific unique constraint.
1222 # In your application
1223 my $cd = $schema->resultset('CD')->update_or_create(
1225 artist => 'Massive Attack',
1226 title => 'Mezzanine',
1229 { key => 'artist_title' }
1232 If no C<key> is specified, it searches on all unique constraints defined on the
1233 source, including the primary key.
1235 If the C<key> is specified as C<primary>, it searches only on the primary key.
1237 See also L</find> and L</find_or_create>. For information on how to declare
1238 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1242 sub update_or_create {
1244 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1245 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1247 my $row = $self->find($hash, $attrs);
1249 $row->set_columns($hash);
1254 return $self->create($hash);
1261 =item Arguments: none
1263 =item Return Value: \@cache_objects?
1267 Gets the contents of the cache for the resultset, if the cache is set.
1272 shift->{all_cache} || [];
1279 =item Arguments: \@cache_objects
1281 =item Return Value: \@cache_objects
1285 Sets the contents of the cache for the resultset. Expects an arrayref
1286 of objects of the same class as those produced by the resultset. Note that
1287 if the cache is set the resultset will return the cached objects rather
1288 than re-querying the database even if the cache attr is not set.
1293 my ( $self, $data ) = @_;
1294 $self->throw_exception("set_cache requires an arrayref")
1295 if ref $data ne 'ARRAY';
1296 my $result_class = $self->result_class;
1298 $self->throw_exception(
1299 "cannot cache object of type '$_', expected '$result_class'"
1300 ) if ref $_ ne $result_class;
1302 $self->{all_cache} = $data;
1309 =item Arguments: none
1311 =item Return Value: []
1315 Clears the cache for the resultset.
1320 shift->set_cache([]);
1323 =head2 related_resultset
1327 =item Arguments: $relationship_name
1329 =item Return Value: $resultset
1333 Returns a related resultset for the supplied relationship name.
1335 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1339 sub related_resultset {
1340 my ( $self, $rel ) = @_;
1341 $self->{related_resultsets} ||= {};
1342 return $self->{related_resultsets}{$rel} ||= do {
1343 #warn "fetching related resultset for rel '$rel'";
1344 my $rel_obj = $self->result_source->relationship_info($rel);
1345 $self->throw_exception(
1346 "search_related: result source '" . $self->result_source->name .
1347 "' has no such relationship ${rel}")
1348 unless $rel_obj; #die Dumper $self->{attrs};
1350 my $rs = $self->search(undef, { join => $rel });
1351 my $alias = defined $rs->{attrs}{seen_join}{$rel}
1352 && $rs->{attrs}{seen_join}{$rel} > 1
1353 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
1356 $self->result_source->schema->resultset($rel_obj->{class}
1366 =head2 throw_exception
1368 See L<DBIx::Class::Schema/throw_exception> for details.
1372 sub throw_exception {
1374 $self->result_source->schema->throw_exception(@_);
1377 # XXX: FIXME: Attributes docs need clearing up
1381 The resultset takes various attributes that modify its behavior. Here's an
1388 =item Value: ($order_by | \@order_by)
1392 Which column(s) to order the results by. This is currently passed
1393 through directly to SQL, so you can give e.g. C<year DESC> for a
1394 descending order on the column `year'.
1400 =item Value: \@columns
1404 Shortcut to request a particular set of columns to be retrieved. Adds
1405 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1406 from that, then auto-populates C<as> from C<select> as normal. (You may also
1407 use the C<cols> attribute, as in earlier versions of DBIC.)
1409 =head2 include_columns
1413 =item Value: \@columns
1417 Shortcut to include additional columns in the returned results - for example
1419 $schema->resultset('CD')->search(undef, {
1420 include_columns => ['artist.name'],
1424 would return all CDs and include a 'name' column to the information
1425 passed to object inflation
1431 =item Value: \@select_columns
1435 Indicates which columns should be selected from the storage. You can use
1436 column names, or in the case of RDBMS back ends, function or stored procedure
1439 $rs = $schema->resultset('Employee')->search(undef, {
1442 { count => 'employeeid' },
1447 When you use function/stored procedure names and do not supply an C<as>
1448 attribute, the column names returned are storage-dependent. E.g. MySQL would
1449 return a column named C<count(employeeid)> in the above example.
1455 =item Value: \@inflation_names
1459 Indicates column names for object inflation. This is used in conjunction with
1460 C<select>, usually when C<select> contains one or more function or stored
1463 $rs = $schema->resultset('Employee')->search(undef, {
1466 { count => 'employeeid' }
1468 as => ['name', 'employee_count'],
1471 my $employee = $rs->first(); # get the first Employee
1473 If the object against which the search is performed already has an accessor
1474 matching a column name specified in C<as>, the value can be retrieved using
1475 the accessor as normal:
1477 my $name = $employee->name();
1479 If on the other hand an accessor does not exist in the object, you need to
1480 use C<get_column> instead:
1482 my $employee_count = $employee->get_column('employee_count');
1484 You can create your own accessors if required - see
1485 L<DBIx::Class::Manual::Cookbook> for details.
1491 =item Value: ($rel_name | \@rel_names | \%rel_names)
1495 Contains a list of relationships that should be joined for this query. For
1498 # Get CDs by Nine Inch Nails
1499 my $rs = $schema->resultset('CD')->search(
1500 { 'artist.name' => 'Nine Inch Nails' },
1501 { join => 'artist' }
1504 Can also contain a hash reference to refer to the other relation's relations.
1507 package MyApp::Schema::Track;
1508 use base qw/DBIx::Class/;
1509 __PACKAGE__->table('track');
1510 __PACKAGE__->add_columns(qw/trackid cd position title/);
1511 __PACKAGE__->set_primary_key('trackid');
1512 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1515 # In your application
1516 my $rs = $schema->resultset('Artist')->search(
1517 { 'track.title' => 'Teardrop' },
1519 join => { cd => 'track' },
1520 order_by => 'artist.name',
1524 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1525 similarly for a third time). For e.g.
1527 my $rs = $schema->resultset('Artist')->search({
1528 'cds.title' => 'Down to Earth',
1529 'cds_2.title' => 'Popular',
1531 join => [ qw/cds cds/ ],
1534 will return a set of all artists that have both a cd with title 'Down
1535 to Earth' and a cd with title 'Popular'.
1537 If you want to fetch related objects from other tables as well, see C<prefetch>
1544 =item Value: ($rel_name | \@rel_names | \%rel_names)
1548 Contains one or more relationships that should be fetched along with the main
1549 query (when they are accessed afterwards they will have already been
1550 "prefetched"). This is useful for when you know you will need the related
1551 objects, because it saves at least one query:
1553 my $rs = $schema->resultset('Tag')->search(
1562 The initial search results in SQL like the following:
1564 SELECT tag.*, cd.*, artist.* FROM tag
1565 JOIN cd ON tag.cd = cd.cdid
1566 JOIN artist ON cd.artist = artist.artistid
1568 L<DBIx::Class> has no need to go back to the database when we access the
1569 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1572 Simple prefetches will be joined automatically, so there is no need
1573 for a C<join> attribute in the above search. If you're prefetching to
1574 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1575 specify the join as well.
1577 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1578 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1579 with an accessor type of 'single' or 'filter').
1585 =item Value: \@from_clause
1589 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1590 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1593 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1594 C<join> will usually do what you need and it is strongly recommended that you
1595 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1597 In simple terms, C<from> works as follows:
1600 { <alias> => <table>, -join_type => 'inner|left|right' }
1601 [] # nested JOIN (optional)
1602 { <table.column> => <foreign_table.foreign_key> }
1608 ON <table.column> = <foreign_table.foreign_key>
1610 An easy way to follow the examples below is to remember the following:
1612 Anything inside "[]" is a JOIN
1613 Anything inside "{}" is a condition for the enclosing JOIN
1615 The following examples utilize a "person" table in a family tree application.
1616 In order to express parent->child relationships, this table is self-joined:
1618 # Person->belongs_to('father' => 'Person');
1619 # Person->belongs_to('mother' => 'Person');
1621 C<from> can be used to nest joins. Here we return all children with a father,
1622 then search against all mothers of those children:
1624 $rs = $schema->resultset('Person')->search(
1627 alias => 'mother', # alias columns in accordance with "from"
1629 { mother => 'person' },
1632 { child => 'person' },
1634 { father => 'person' },
1635 { 'father.person_id' => 'child.father_id' }
1638 { 'mother.person_id' => 'child.mother_id' }
1645 # SELECT mother.* FROM person mother
1648 # JOIN person father
1649 # ON ( father.person_id = child.father_id )
1651 # ON ( mother.person_id = child.mother_id )
1653 The type of any join can be controlled manually. To search against only people
1654 with a father in the person table, we could explicitly use C<INNER JOIN>:
1656 $rs = $schema->resultset('Person')->search(
1659 alias => 'child', # alias columns in accordance with "from"
1661 { child => 'person' },
1663 { father => 'person', -join_type => 'inner' },
1664 { 'father.id' => 'child.father_id' }
1671 # SELECT child.* FROM person child
1672 # INNER JOIN person father ON child.father_id = father.id
1682 Makes the resultset paged and specifies the page to retrieve. Effectively
1683 identical to creating a non-pages resultset and then calling ->page($page)
1694 Specifes the maximum number of rows for direct retrieval or the number of
1695 rows per page if the page attribute or method is used.
1701 =item Value: \@columns
1705 A arrayref of columns to group by. Can include columns of joined tables.
1707 group_by => [qw/ column1 column2 ... /]
1713 =item Value: $condition
1717 HAVING is a select statement attribute that is applied between GROUP BY and
1718 ORDER BY. It is applied to the after the grouping calculations have been
1721 having => { 'count(employee)' => { '>=', 100 } }
1727 =item Value: (0 | 1)
1731 Set to 1 to group by all columns.
1735 Set to 1 to cache search results. This prevents extra SQL queries if you
1736 revisit rows in your ResultSet:
1738 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1740 while( my $artist = $resultset->next ) {
1744 $rs->first; # without cache, this would issue a query
1746 By default, searches are not cached.
1748 For more examples of using these attributes, see
1749 L<DBIx::Class::Manual::Cookbook>.