1 package DBIx::Class::ResultSet;
9 use Carp::Clan qw/^DBIx::Class/;
12 use DBIx::Class::ResultSetColumn;
13 use base qw/DBIx::Class/;
15 __PACKAGE__->load_components(qw/AccessorGroup/);
16 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
20 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
24 my $rs = $schema->resultset('User')->search(registered => 1);
25 my @rows = $schema->resultset('CD')->search(year => 2005);
29 The resultset is also known as an iterator. It is responsible for handling
30 queries that may return an arbitrary number of rows, e.g. via L</search>
31 or a C<has_many> relationship.
33 In the examples below, the following table classes are used:
35 package MyApp::Schema::Artist;
36 use base qw/DBIx::Class/;
37 __PACKAGE__->load_components(qw/Core/);
38 __PACKAGE__->table('artist');
39 __PACKAGE__->add_columns(qw/artistid name/);
40 __PACKAGE__->set_primary_key('artistid');
41 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
44 package MyApp::Schema::CD;
45 use base qw/DBIx::Class/;
46 __PACKAGE__->load_components(qw/Core/);
47 __PACKAGE__->table('cd');
48 __PACKAGE__->add_columns(qw/cdid artist title year/);
49 __PACKAGE__->set_primary_key('cdid');
50 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
59 =item Arguments: $source, \%$attrs
61 =item Return Value: $rs
65 The resultset constructor. Takes a source object (usually a
66 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
67 L</ATTRIBUTES> below). Does not perform any queries -- these are
68 executed as needed by the other methods.
70 Generally you won't need to construct a resultset manually. You'll
71 automatically get one from e.g. a L</search> called in scalar context:
73 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
75 IMPORTANT: If called on an object, proxies to new_result instead so
77 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
79 will return a CD object, not a ResultSet.
85 return $class->new_result(@_) if ref $class;
87 my ($source, $attrs) = @_;
91 $attrs->{rows} ||= 10;
92 $attrs->{offset} ||= 0;
93 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
96 $attrs->{alias} ||= 'me';
99 result_source => $source,
100 result_class => $attrs->{result_class} || $source->result_class,
101 cond => $attrs->{where},
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/],
136 For a list of attributes that can be passed to C<search>, see L</ATTRIBUTES>. For more examples of using this function, see L<Searching|DBIx::Class::Manual::Cookbook/Searching>.
142 my $rs = $self->search_rs( @_ );
143 return (wantarray ? $rs->all : $rs);
150 =item Arguments: $cond, \%attrs?
152 =item Return Value: $resultset
156 This method does the same exact thing as search() except it will
157 always return a resultset, even in list context.
166 unless (@_) { # no search, effectively just a clone
167 $rows = $self->get_cache;
171 $attrs = pop(@_) if @_ > 1 and ref $_[$#_] eq 'HASH';
172 my $our_attrs = { %{$self->{attrs}} };
173 my $having = delete $our_attrs->{having};
175 # merge new attrs into inherited
176 foreach my $key (qw/join prefetch/) {
177 next unless exists $attrs->{$key};
178 $our_attrs->{$key} = $self->_merge_attr($our_attrs->{$key}, delete $attrs->{$key});
181 my $new_attrs = { %{$our_attrs}, %{$attrs} };
184 (@_ == 1 || ref $_[0] eq "HASH")
188 ? $self->throw_exception("Odd number of arguments to search")
195 if (defined $where) {
196 $new_attrs->{where} = (
197 defined $new_attrs->{where}
200 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
201 } $where, $new_attrs->{where}
207 if (defined $having) {
208 $new_attrs->{having} = (
209 defined $new_attrs->{having}
212 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
213 } $having, $new_attrs->{having}
219 my $rs = (ref $self)->new($self->result_source, $new_attrs);
221 $rs->set_cache($rows);
226 =head2 search_literal
230 =item Arguments: $sql_fragment, @bind_values
232 =item Return Value: $resultset (scalar context), @row_objs (list context)
236 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
237 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
239 Pass a literal chunk of SQL to be added to the conditional part of the
245 my ($self, $cond, @vals) = @_;
246 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
247 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
248 return $self->search(\$cond, $attrs);
255 =item Arguments: @values | \%cols, \%attrs?
257 =item Return Value: $row_object
261 Finds a row based on its primary key or unique constraint. For example, to find
262 a row by its primary key:
264 my $cd = $schema->resultset('CD')->find(5);
266 You can also find a row by a specific unique constraint using the C<key>
267 attribute. For example:
269 my $cd = $schema->resultset('CD')->find('Massive Attack', 'Mezzanine', {
270 key => 'cd_artist_title'
273 Additionally, you can specify the columns explicitly by name:
275 my $cd = $schema->resultset('CD')->find(
277 artist => 'Massive Attack',
278 title => 'Mezzanine',
280 { key => 'cd_artist_title' }
283 If the C<key> is specified as C<primary>, it searches only on the primary key.
285 If no C<key> is specified, it searches on all unique constraints defined on the
286 source, including the primary key.
288 If your table does not have a primary key, you B<must> provide a value for the
289 C<key> attribute matching one of the unique constraints on the source.
291 See also L</find_or_create> and L</update_or_create>. For information on how to
292 declare unique constraints, see
293 L<DBIx::Class::ResultSource/add_unique_constraint>.
299 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
301 # Default to the primary key, but allow a specific key
302 my @cols = exists $attrs->{key}
303 ? $self->result_source->unique_constraint_columns($attrs->{key})
304 : $self->result_source->primary_columns;
305 $self->throw_exception(
306 "Can't find unless a primary key is defined or unique constraint is specified"
309 # Parse out a hashref from input
311 if (ref $_[0] eq 'HASH') {
312 $input_query = { %{$_[0]} };
314 elsif (@_ == @cols) {
316 @{$input_query}{@cols} = @_;
319 # Compatibility: Allow e.g. find(id => $value)
320 carp "Find by key => value deprecated; please use a hashref instead";
324 my @unique_queries = $self->_unique_queries($input_query, $attrs);
326 # Build the final query: Default to the disjunction of the unique queries,
327 # but allow the input query in case the ResultSet defines the query or the
328 # user is abusing find
329 my $alias = exists $attrs->{alias} ? $attrs->{alias} : $self->{attrs}{alias};
330 my $query = @unique_queries
331 ? [ map { $self->_add_alias($_, $alias) } @unique_queries ]
332 : $self->_add_alias($input_query, $alias);
336 my $rs = $self->search($query, $attrs);
337 return keys %{$rs->_resolved_attrs->{collapse}} ? $rs->next : $rs->single;
340 return keys %{$self->_resolved_attrs->{collapse}}
341 ? $self->search($query)->next
342 : $self->single($query);
348 # Add the specified alias to the specified query hash. A copy is made so the
349 # original query is not modified.
352 my ($self, $query, $alias) = @_;
354 my %aliased = %$query;
355 foreach my $col (grep { ! m/\./ } keys %aliased) {
356 $aliased{"$alias.$col"} = delete $aliased{$col};
364 # Build a list of queries which satisfy unique constraints.
366 sub _unique_queries {
367 my ($self, $query, $attrs) = @_;
369 my @constraint_names = exists $attrs->{key}
371 : $self->result_source->unique_constraint_names;
374 foreach my $name (@constraint_names) {
375 my @unique_cols = $self->result_source->unique_constraint_columns($name);
376 my $unique_query = $self->_build_unique_query($query, \@unique_cols);
378 my $num_query = scalar keys %$unique_query;
379 next unless $num_query;
381 # XXX: Assuming quite a bit about $self->{attrs}{where}
382 my $num_cols = scalar @unique_cols;
383 my $num_where = exists $self->{attrs}{where}
384 ? scalar keys %{ $self->{attrs}{where} }
386 push @unique_queries, $unique_query
387 if $num_query + $num_where == $num_cols;
390 return @unique_queries;
393 # _build_unique_query
395 # Constrain the specified query hash based on the specified column names.
397 sub _build_unique_query {
398 my ($self, $query, $unique_cols) = @_;
401 map { $_ => $query->{$_} }
402 grep { exists $query->{$_} }
407 =head2 search_related
411 =item Arguments: $rel, $cond, \%attrs?
413 =item Return Value: $new_resultset
417 $new_rs = $cd_rs->search_related('artist', {
421 Searches the specified relationship, optionally specifying a condition and
422 attributes for matching records. See L</ATTRIBUTES> for more information.
427 return shift->related_resultset(shift)->search(@_);
434 =item Arguments: none
436 =item Return Value: $cursor
440 Returns a storage-driven cursor to the given resultset. See
441 L<DBIx::Class::Cursor> for more information.
448 my $attrs = { %{$self->_resolved_attrs} };
449 return $self->{cursor}
450 ||= $self->result_source->storage->select($attrs->{from}, $attrs->{select},
451 $attrs->{where},$attrs);
458 =item Arguments: $cond?
460 =item Return Value: $row_object?
464 my $cd = $schema->resultset('CD')->single({ year => 2001 });
466 Inflates the first result without creating a cursor if the resultset has
467 any records in it; if not returns nothing. Used by L</find> as an optimisation.
469 Can optionally take an additional condition *only* - this is a fast-code-path
470 method; if you need to add extra joins or similar call ->search and then
471 ->single without a condition on the $rs returned from that.
476 my ($self, $where) = @_;
477 my $attrs = { %{$self->_resolved_attrs} };
479 if (defined $attrs->{where}) {
482 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
483 $where, delete $attrs->{where} ]
486 $attrs->{where} = $where;
490 # XXX: Disabled since it doesn't infer uniqueness in all cases
491 # unless ($self->_is_unique_query($attrs->{where})) {
492 # carp "Query not guaranteed to return a single row"
493 # . "; please declare your unique constraints or use search instead";
496 my @data = $self->result_source->storage->select_single(
497 $attrs->{from}, $attrs->{select},
498 $attrs->{where}, $attrs
501 return (@data ? $self->_construct_object(@data) : ());
506 # Try to determine if the specified query is guaranteed to be unique, based on
507 # the declared unique constraints.
509 sub _is_unique_query {
510 my ($self, $query) = @_;
512 my $collapsed = $self->_collapse_query($query);
513 my $alias = $self->{attrs}{alias};
515 foreach my $name ($self->result_source->unique_constraint_names) {
516 my @unique_cols = map {
518 } $self->result_source->unique_constraint_columns($name);
520 # Count the values for each unique column
521 my %seen = map { $_ => 0 } @unique_cols;
523 foreach my $key (keys %$collapsed) {
524 my $aliased = $key =~ /\./ ? $key : "$alias.$key";
525 next unless exists $seen{$aliased}; # Additional constraints are okay
526 $seen{$aliased} = scalar keys %{ $collapsed->{$key} };
529 # If we get 0 or more than 1 value for a column, it's not necessarily unique
530 return 1 unless grep { $_ != 1 } values %seen;
538 # Recursively collapse the query, accumulating values for each column.
540 sub _collapse_query {
541 my ($self, $query, $collapsed) = @_;
545 if (ref $query eq 'ARRAY') {
546 foreach my $subquery (@$query) {
547 next unless ref $subquery; # -or
548 # warn "ARRAY: " . Dumper $subquery;
549 $collapsed = $self->_collapse_query($subquery, $collapsed);
552 elsif (ref $query eq 'HASH') {
553 if (keys %$query and (keys %$query)[0] eq '-and') {
554 foreach my $subquery (@{$query->{-and}}) {
555 # warn "HASH: " . Dumper $subquery;
556 $collapsed = $self->_collapse_query($subquery, $collapsed);
560 # warn "LEAF: " . Dumper $query;
561 foreach my $col (keys %$query) {
562 my $value = $query->{$col};
563 $collapsed->{$col}{$value}++;
575 =item Arguments: $cond?
577 =item Return Value: $resultsetcolumn
581 my $max_length = $rs->get_column('length')->max;
583 Returns a L<DBIx::Class::ResultSetColumn> instance for a column of the ResultSet.
588 my ($self, $column) = @_;
589 my $new = DBIx::Class::ResultSetColumn->new($self, $column);
597 =item Arguments: $cond, \%attrs?
599 =item Return Value: $resultset (scalar context), @row_objs (list context)
603 # WHERE title LIKE '%blue%'
604 $cd_rs = $rs->search_like({ title => '%blue%'});
606 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
607 that this is simply a convenience method. You most likely want to use
608 L</search> with specific operators.
610 For more information, see L<DBIx::Class::Manual::Cookbook>.
616 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
617 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
618 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
619 return $class->search($query, { %$attrs });
626 =item Arguments: $first, $last
628 =item Return Value: $resultset (scalar context), @row_objs (list context)
632 Returns a resultset or object list representing a subset of elements from the
633 resultset slice is called on. Indexes are from 0, i.e., to get the first
636 my ($one, $two, $three) = $rs->slice(0, 2);
641 my ($self, $min, $max) = @_;
642 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
643 $attrs->{offset} = $self->{attrs}{offset} || 0;
644 $attrs->{offset} += $min;
645 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
646 return $self->search(undef(), $attrs);
647 #my $slice = (ref $self)->new($self->result_source, $attrs);
648 #return (wantarray ? $slice->all : $slice);
655 =item Arguments: none
657 =item Return Value: $result?
661 Returns the next element in the resultset (C<undef> is there is none).
663 Can be used to efficiently iterate over records in the resultset:
665 my $rs = $schema->resultset('CD')->search;
666 while (my $cd = $rs->next) {
670 Note that you need to store the resultset object, and call C<next> on it.
671 Calling C<< resultset('Table')->next >> repeatedly will always return the
672 first record from the resultset.
678 if (my $cache = $self->get_cache) {
679 $self->{all_cache_position} ||= 0;
680 return $cache->[$self->{all_cache_position}++];
682 if ($self->{attrs}{cache}) {
683 $self->{all_cache_position} = 1;
684 return ($self->all)[0];
687 exists $self->{stashed_row}
688 ? @{delete $self->{stashed_row}}
689 : $self->cursor->next
691 return unless (@row);
692 return $self->_construct_object(@row);
695 sub _construct_object {
696 my ($self, @row) = @_;
697 my $info = $self->_collapse_result($self->{_attrs}{as}, \@row);
698 my $new = $self->result_class->inflate_result($self->result_source, @$info);
699 $new = $self->{_attrs}{record_filter}->($new)
700 if exists $self->{_attrs}{record_filter};
704 sub _collapse_result {
705 my ($self, $as, $row, $prefix) = @_;
710 foreach my $this_as (@$as) {
711 my $val = shift @copy;
712 if (defined $prefix) {
713 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
715 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
716 $const{$1||''}{$2} = $val;
719 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
720 $const{$1||''}{$2} = $val;
724 my $alias = $self->{attrs}{alias};
725 my $info = [ {}, {} ];
726 foreach my $key (keys %const) {
727 if (length $key && $key ne $alias) {
729 my @parts = split(/\./, $key);
730 foreach my $p (@parts) {
731 $target = $target->[1]->{$p} ||= [];
733 $target->[0] = $const{$key};
735 $info->[0] = $const{$key};
740 if (defined $prefix) {
742 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
743 } keys %{$self->{_attrs}{collapse}}
745 @collapse = keys %{$self->{_attrs}{collapse}};
749 my ($c) = sort { length $a <=> length $b } @collapse;
751 foreach my $p (split(/\./, $c)) {
752 $target = $target->[1]->{$p} ||= [];
754 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
755 my @co_key = @{$self->{_attrs}{collapse}{$c_prefix}};
756 my $tree = $self->_collapse_result($as, $row, $c_prefix);
757 my %co_check = map { ($_, $tree->[0]->{$_}); } @co_key;
763 !defined($tree->[0]->{$_}) || $co_check{$_} ne $tree->[0]->{$_}
768 last unless (@raw = $self->cursor->next);
769 $row = $self->{stashed_row} = \@raw;
770 $tree = $self->_collapse_result($as, $row, $c_prefix);
772 @$target = (@final ? @final : [ {}, {} ]);
773 # single empty result to indicate an empty prefetched has_many
776 #print "final info: " . Dumper($info);
784 =item Arguments: $result_source?
786 =item Return Value: $result_source
790 An accessor for the primary ResultSource object from which this ResultSet
797 =item Arguments: $result_class?
799 =item Return Value: $result_class
803 An accessor for the class to use when creating row objects. Defaults to
804 C<< result_source->result_class >> - which in most cases is the name of the
805 L<"table"|DBIx::Class::Manual::Glossary/"ResultSource"> class.
814 =item Arguments: $cond, \%attrs??
816 =item Return Value: $count
820 Performs an SQL C<COUNT> with the same query as the resultset was built
821 with to find the number of elements. If passed arguments, does a search
822 on the resultset and counts the results of that.
824 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
825 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
826 not support C<DISTINCT> with multiple columns. If you are using such a
827 database, you should only use columns from the main table in your C<group_by>
834 return $self->search(@_)->count if @_ and defined $_[0];
835 return scalar @{ $self->get_cache } if $self->get_cache;
836 my $count = $self->_count;
837 return 0 unless $count;
839 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
840 $count = $self->{attrs}{rows} if
841 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
845 sub _count { # Separated out so pager can get the full count
847 my $select = { count => '*' };
849 my $attrs = { %{$self->_resolved_attrs} };
850 if (my $group_by = delete $attrs->{group_by}) {
851 delete $attrs->{having};
852 my @distinct = (ref $group_by ? @$group_by : ($group_by));
853 # todo: try CONCAT for multi-column pk
854 my @pk = $self->result_source->primary_columns;
856 my $alias = $attrs->{alias};
857 foreach my $column (@distinct) {
858 if ($column =~ qr/^(?:\Q${alias}.\E)?$pk[0]$/) {
859 @distinct = ($column);
865 $select = { count => { distinct => \@distinct } };
868 $attrs->{select} = $select;
869 $attrs->{as} = [qw/count/];
871 # offset, order by and page are not needed to count. record_filter is cdbi
872 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
874 my $tmp_rs = (ref $self)->new($self->result_source, $attrs);
875 my ($count) = $tmp_rs->cursor->next;
883 =item Arguments: $sql_fragment, @bind_values
885 =item Return Value: $count
889 Counts the results in a literal query. Equivalent to calling L</search_literal>
890 with the passed arguments, then L</count>.
894 sub count_literal { shift->search_literal(@_)->count; }
900 =item Arguments: none
902 =item Return Value: @objects
906 Returns all elements in the resultset. Called implicitly if the resultset
907 is returned in list context.
913 return @{ $self->get_cache } if $self->get_cache;
917 # TODO: don't call resolve here
918 if (keys %{$self->_resolved_attrs->{collapse}}) {
919 # if ($self->{attrs}{prefetch}) {
920 # Using $self->cursor->all is really just an optimisation.
921 # If we're collapsing has_many prefetches it probably makes
922 # very little difference, and this is cleaner than hacking
923 # _construct_object to survive the approach
924 my @row = $self->cursor->next;
926 push(@obj, $self->_construct_object(@row));
927 @row = (exists $self->{stashed_row}
928 ? @{delete $self->{stashed_row}}
929 : $self->cursor->next);
932 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
935 $self->set_cache(\@obj) if $self->{attrs}{cache};
943 =item Arguments: none
945 =item Return Value: $self
949 Resets the resultset's cursor, so you can iterate through the elements again.
955 delete $self->{_attrs} if exists $self->{_attrs};
956 $self->{all_cache_position} = 0;
957 $self->cursor->reset;
965 =item Arguments: none
967 =item Return Value: $object?
971 Resets the resultset and returns an object for the first result (if the
972 resultset returns anything).
977 return $_[0]->reset->next;
980 # _cond_for_update_delete
982 # update/delete require the condition to be modified to handle
983 # the differing SQL syntax available. This transforms the $self->{cond}
984 # appropriately, returning the new condition.
986 sub _cond_for_update_delete {
990 # No-op. No condition, we're updating/deleting everything
991 return $cond unless ref $self->{cond};
993 if (ref $self->{cond} eq 'ARRAY') {
997 foreach my $key (keys %{$_}) {
999 $hash{$1} = $_->{$key};
1005 elsif (ref $self->{cond} eq 'HASH') {
1006 if ((keys %{$self->{cond}})[0] eq '-and') {
1009 my @cond = @{$self->{cond}{-and}};
1010 for (my $i = 0; $i < @cond; $i++) {
1011 my $entry = $cond[$i];
1014 if (ref $entry eq 'HASH') {
1015 foreach my $key (keys %{$entry}) {
1017 $hash{$1} = $entry->{$key};
1021 $entry =~ /([^.]+)$/;
1022 $hash{$1} = $cond[++$i];
1025 push @{$cond->{-and}}, \%hash;
1029 foreach my $key (keys %{$self->{cond}}) {
1031 $cond->{$1} = $self->{cond}{$key};
1036 $self->throw_exception(
1037 "Can't update/delete on resultset with condition unless hash or array"
1049 =item Arguments: \%values
1051 =item Return Value: $storage_rv
1055 Sets the specified columns in the resultset to the supplied values in a
1056 single query. Return value will be true if the update succeeded or false
1057 if no records were updated; exact type of success value is storage-dependent.
1062 my ($self, $values) = @_;
1063 $self->throw_exception("Values for update must be a hash")
1064 unless ref $values eq 'HASH';
1066 my $cond = $self->_cond_for_update_delete;
1068 return $self->result_source->storage->update(
1069 $self->result_source->from, $values, $cond
1077 =item Arguments: \%values
1079 =item Return Value: 1
1083 Fetches all objects and updates them one at a time. Note that C<update_all>
1084 will run DBIC cascade triggers, while L</update> will not.
1089 my ($self, $values) = @_;
1090 $self->throw_exception("Values for update must be a hash")
1091 unless ref $values eq 'HASH';
1092 foreach my $obj ($self->all) {
1093 $obj->set_columns($values)->update;
1102 =item Arguments: none
1104 =item Return Value: 1
1108 Deletes the contents of the resultset from its result source. Note that this
1109 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
1117 my $cond = $self->_cond_for_update_delete;
1119 $self->result_source->storage->delete($self->result_source->from, $cond);
1127 =item Arguments: none
1129 =item Return Value: 1
1133 Fetches all objects and deletes them one at a time. Note that C<delete_all>
1134 will run DBIC cascade triggers, while L</delete> will not.
1140 $_->delete for $self->all;
1148 =item Arguments: none
1150 =item Return Value: $pager
1154 Return Value a L<Data::Page> object for the current resultset. Only makes
1155 sense for queries with a C<page> attribute.
1161 my $attrs = $self->{attrs};
1162 $self->throw_exception("Can't create pager for non-paged rs")
1163 unless $self->{attrs}{page};
1164 $attrs->{rows} ||= 10;
1165 return $self->{pager} ||= Data::Page->new(
1166 $self->_count, $attrs->{rows}, $self->{attrs}{page});
1173 =item Arguments: $page_number
1175 =item Return Value: $rs
1179 Returns a resultset for the $page_number page of the resultset on which page
1180 is called, where each page contains a number of rows equal to the 'rows'
1181 attribute set on the resultset (10 by default).
1186 my ($self, $page) = @_;
1187 return (ref $self)->new($self->result_source, { %{$self->{attrs}}, page => $page });
1194 =item Arguments: \%vals
1196 =item Return Value: $object
1200 Creates an object in the resultset's result class and returns it.
1205 my ($self, $values) = @_;
1206 $self->throw_exception( "new_result needs a hash" )
1207 unless (ref $values eq 'HASH');
1208 $self->throw_exception(
1209 "Can't abstract implicit construct, condition not a hash"
1210 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
1212 my $alias = $self->{attrs}{alias};
1214 %{ $self->_remove_alias($values, $alias) },
1215 %{ $self->_remove_alias($self->{cond}, $alias) },
1218 my $obj = $self->result_class->new(\%new);
1219 $obj->result_source($self->result_source) if $obj->can('result_source');
1225 # Remove the specified alias from the specified query hash. A copy is made so
1226 # the original query is not modified.
1229 my ($self, $query, $alias) = @_;
1231 my %unaliased = %{ $query || {} };
1232 foreach my $key (keys %unaliased) {
1233 $unaliased{$1} = delete $unaliased{$key}
1234 if $key =~ m/^(?:\Q$alias\E\.)?([^.]+)$/;
1244 =item Arguments: \%vals, \%attrs?
1246 =item Return Value: $object
1250 Find an existing record from this resultset. If none exists, instantiate a new
1251 result object and return it. The object will not be saved into your storage
1252 until you call L<DBIx::Class::Row/insert> on it.
1254 If you want objects to be saved immediately, use L</find_or_create> instead.
1260 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1261 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1262 my $exists = $self->find($hash, $attrs);
1263 return defined $exists ? $exists : $self->new_result($hash);
1270 =item Arguments: \%vals
1272 =item Return Value: $object
1276 Inserts a record into the resultset and returns the object representing it.
1278 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1283 my ($self, $attrs) = @_;
1284 $self->throw_exception( "create needs a hashref" )
1285 unless ref $attrs eq 'HASH';
1286 return $self->new_result($attrs)->insert;
1289 =head2 find_or_create
1293 =item Arguments: \%vals, \%attrs?
1295 =item Return Value: $object
1299 $class->find_or_create({ key => $val, ... });
1301 Tries to find a record based on its primary key or unique constraint; if none
1302 is found, creates one and returns that instead.
1304 my $cd = $schema->resultset('CD')->find_or_create({
1306 artist => 'Massive Attack',
1307 title => 'Mezzanine',
1311 Also takes an optional C<key> attribute, to search by a specific key or unique
1312 constraint. For example:
1314 my $cd = $schema->resultset('CD')->find_or_create(
1316 artist => 'Massive Attack',
1317 title => 'Mezzanine',
1319 { key => 'cd_artist_title' }
1322 See also L</find> and L</update_or_create>. For information on how to declare
1323 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1327 sub find_or_create {
1329 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1330 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1331 my $exists = $self->find($hash, $attrs);
1332 return defined $exists ? $exists : $self->create($hash);
1335 =head2 update_or_create
1339 =item Arguments: \%col_values, { key => $unique_constraint }?
1341 =item Return Value: $object
1345 $class->update_or_create({ col => $val, ... });
1347 First, searches for an existing row matching one of the unique constraints
1348 (including the primary key) on the source of this resultset. If a row is
1349 found, updates it with the other given column values. Otherwise, creates a new
1352 Takes an optional C<key> attribute to search on a specific unique constraint.
1355 # In your application
1356 my $cd = $schema->resultset('CD')->update_or_create(
1358 artist => 'Massive Attack',
1359 title => 'Mezzanine',
1362 { key => 'cd_artist_title' }
1365 If no C<key> is specified, it searches on all unique constraints defined on the
1366 source, including the primary key.
1368 If the C<key> is specified as C<primary>, it searches only on the primary key.
1370 See also L</find> and L</find_or_create>. For information on how to declare
1371 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
1375 sub update_or_create {
1377 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1378 my $cond = ref $_[0] eq 'HASH' ? shift : {@_};
1380 my $row = $self->find($cond, $attrs);
1382 $row->update($cond);
1386 return $self->create($cond);
1393 =item Arguments: none
1395 =item Return Value: \@cache_objects?
1399 Gets the contents of the cache for the resultset, if the cache is set.
1411 =item Arguments: \@cache_objects
1413 =item Return Value: \@cache_objects
1417 Sets the contents of the cache for the resultset. Expects an arrayref
1418 of objects of the same class as those produced by the resultset. Note that
1419 if the cache is set the resultset will return the cached objects rather
1420 than re-querying the database even if the cache attr is not set.
1425 my ( $self, $data ) = @_;
1426 $self->throw_exception("set_cache requires an arrayref")
1427 if defined($data) && (ref $data ne 'ARRAY');
1428 $self->{all_cache} = $data;
1435 =item Arguments: none
1437 =item Return Value: []
1441 Clears the cache for the resultset.
1446 shift->set_cache(undef);
1449 =head2 related_resultset
1453 =item Arguments: $relationship_name
1455 =item Return Value: $resultset
1459 Returns a related resultset for the supplied relationship name.
1461 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
1465 sub related_resultset {
1466 my ($self, $rel) = @_;
1468 $self->{related_resultsets} ||= {};
1469 return $self->{related_resultsets}{$rel} ||= do {
1470 my $rel_obj = $self->result_source->relationship_info($rel);
1472 $self->throw_exception(
1473 "search_related: result source '" . $self->result_source->name .
1474 "' has no such relationship $rel")
1477 my ($from,$seen) = $self->_resolve_from($rel);
1479 my $join_count = $seen->{$rel};
1480 my $alias = ($join_count > 1 ? join('_', $rel, $join_count) : $rel);
1482 $self->result_source->schema->resultset($rel_obj->{class})->search_rs(
1484 %{$self->{attrs}||{}},
1490 where => $self->{cond},
1498 my ($self, $extra_join) = @_;
1499 my $source = $self->result_source;
1500 my $attrs = $self->{attrs};
1502 my $from = $attrs->{from}
1503 || [ { $attrs->{alias} => $source->from } ];
1505 my $seen = { %{$attrs->{seen_join}||{}} };
1507 my $join = ($attrs->{join}
1508 ? [ $attrs->{join}, $extra_join ]
1512 ($join ? $source->resolve_join($join, $attrs->{alias}, $seen) : ()),
1515 return ($from,$seen);
1518 sub _resolved_attrs {
1520 return $self->{_attrs} if $self->{_attrs};
1522 my $attrs = { %{$self->{attrs}||{}} };
1523 my $source = $self->{result_source};
1524 my $alias = $attrs->{alias};
1526 $attrs->{columns} ||= delete $attrs->{cols} if exists $attrs->{cols};
1527 if ($attrs->{columns}) {
1528 delete $attrs->{as};
1529 } elsif (!$attrs->{select}) {
1530 $attrs->{columns} = [ $source->columns ];
1535 ? (ref $attrs->{select} eq 'ARRAY'
1536 ? [ @{$attrs->{select}} ]
1537 : [ $attrs->{select} ])
1538 : [ map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}} ]
1542 ? (ref $attrs->{as} eq 'ARRAY'
1543 ? [ @{$attrs->{as}} ]
1545 : [ map { m/^\Q${alias}.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}} ]
1549 if ($adds = delete $attrs->{include_columns}) {
1550 $adds = [$adds] unless ref $adds eq 'ARRAY';
1551 push(@{$attrs->{select}}, @$adds);
1552 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1 } @$adds);
1554 if ($adds = delete $attrs->{'+select'}) {
1555 $adds = [$adds] unless ref $adds eq 'ARRAY';
1556 push(@{$attrs->{select}},
1557 map { /\./ || ref $_ ? $_ : "${alias}.$_" } @$adds);
1559 if (my $adds = delete $attrs->{'+as'}) {
1560 $adds = [$adds] unless ref $adds eq 'ARRAY';
1561 push(@{$attrs->{as}}, @$adds);
1564 $attrs->{from} ||= [ { 'me' => $source->from } ];
1566 if (exists $attrs->{join} || exists $attrs->{prefetch}) {
1567 my $join = delete $attrs->{join} || {};
1569 if (defined $attrs->{prefetch}) {
1570 $join = $self->_merge_attr(
1571 $join, $attrs->{prefetch}
1575 $attrs->{from} = # have to copy here to avoid corrupting the original
1578 $source->resolve_join($join, $alias, { %{$attrs->{seen_join}||{}} })
1582 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
1583 if ($attrs->{order_by}) {
1584 $attrs->{order_by} = (ref($attrs->{order_by}) eq 'ARRAY'
1585 ? [ @{$attrs->{order_by}} ]
1586 : [ $attrs->{order_by} ]);
1588 $attrs->{order_by} = [];
1591 my $collapse = $attrs->{collapse} || {};
1592 if (my $prefetch = delete $attrs->{prefetch}) {
1593 $prefetch = $self->_merge_attr({}, $prefetch);
1595 my $seen = $attrs->{seen_join} || {};
1596 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
1597 # bring joins back to level of current class
1598 my @prefetch = $source->resolve_prefetch(
1599 $p, $alias, $seen, \@pre_order, $collapse
1601 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
1602 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
1604 push(@{$attrs->{order_by}}, @pre_order);
1606 $attrs->{collapse} = $collapse;
1608 return $self->{_attrs} = $attrs;
1612 my ($self, $a, $b) = @_;
1613 return $b unless defined($a);
1614 return $a unless defined($b);
1616 if (ref $b eq 'HASH' && ref $a eq 'HASH') {
1617 foreach my $key (keys %{$b}) {
1618 if (exists $a->{$key}) {
1619 $a->{$key} = $self->_merge_attr($a->{$key}, $b->{$key});
1621 $a->{$key} = $b->{$key};
1626 $a = [$a] unless ref $a eq 'ARRAY';
1627 $b = [$b] unless ref $b eq 'ARRAY';
1631 foreach my $x ($a, $b) {
1632 foreach my $element (@{$x}) {
1633 if (ref $element eq 'HASH') {
1634 $hash = $self->_merge_attr($hash, $element);
1635 } elsif (ref $element eq 'ARRAY') {
1636 push(@array, @{$element});
1638 push(@array, $element) unless $b == $x
1639 && grep { $_ eq $element } @array;
1644 @array = grep { !exists $hash->{$_} } @array;
1646 return keys %{$hash}
1655 =head2 throw_exception
1657 See L<DBIx::Class::Schema/throw_exception> for details.
1661 sub throw_exception {
1663 $self->result_source->schema->throw_exception(@_);
1666 # XXX: FIXME: Attributes docs need clearing up
1670 The resultset takes various attributes that modify its behavior. Here's an
1677 =item Value: ($order_by | \@order_by)
1681 Which column(s) to order the results by. This is currently passed
1682 through directly to SQL, so you can give e.g. C<year DESC> for a
1683 descending order on the column `year'.
1685 Please note that if you have C<quote_char> enabled (see
1686 L<DBIx::Class::Storage::DBI/connect_info>) you will need to do C<\'year DESC' > to
1687 specify an order. (The scalar ref causes it to be passed as raw sql to the DB,
1688 so you will need to manually quote things as appropriate.)
1694 =item Value: \@columns
1698 Shortcut to request a particular set of columns to be retrieved. Adds
1699 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1700 from that, then auto-populates C<as> from C<select> as normal. (You may also
1701 use the C<cols> attribute, as in earlier versions of DBIC.)
1703 =head2 include_columns
1707 =item Value: \@columns
1711 Shortcut to include additional columns in the returned results - for example
1713 $schema->resultset('CD')->search(undef, {
1714 include_columns => ['artist.name'],
1718 would return all CDs and include a 'name' column to the information
1719 passed to object inflation
1725 =item Value: \@select_columns
1729 Indicates which columns should be selected from the storage. You can use
1730 column names, or in the case of RDBMS back ends, function or stored procedure
1733 $rs = $schema->resultset('Employee')->search(undef, {
1736 { count => 'employeeid' },
1741 When you use function/stored procedure names and do not supply an C<as>
1742 attribute, the column names returned are storage-dependent. E.g. MySQL would
1743 return a column named C<count(employeeid)> in the above example.
1749 Indicates additional columns to be selected from storage. Works the same as
1750 L<select> but adds columns to the selection.
1758 Indicates additional column names for those added via L<+select>.
1766 =item Value: \@inflation_names
1770 Indicates column names for object inflation. This is used in conjunction with
1771 C<select>, usually when C<select> contains one or more function or stored
1774 $rs = $schema->resultset('Employee')->search(undef, {
1777 { count => 'employeeid' }
1779 as => ['name', 'employee_count'],
1782 my $employee = $rs->first(); # get the first Employee
1784 If the object against which the search is performed already has an accessor
1785 matching a column name specified in C<as>, the value can be retrieved using
1786 the accessor as normal:
1788 my $name = $employee->name();
1790 If on the other hand an accessor does not exist in the object, you need to
1791 use C<get_column> instead:
1793 my $employee_count = $employee->get_column('employee_count');
1795 You can create your own accessors if required - see
1796 L<DBIx::Class::Manual::Cookbook> for details.
1798 Please note: This will NOT insert an C<AS employee_count> into the SQL
1799 statement produced, it is used for internal access only. Thus
1800 attempting to use the accessor in an C<order_by> clause or similar
1801 will fail miserably.
1803 To get around this limitation, you can supply literal SQL to your
1804 C<select> attibute that contains the C<AS alias> text, eg:
1806 select => [\'myfield AS alias']
1812 =item Value: ($rel_name | \@rel_names | \%rel_names)
1816 Contains a list of relationships that should be joined for this query. For
1819 # Get CDs by Nine Inch Nails
1820 my $rs = $schema->resultset('CD')->search(
1821 { 'artist.name' => 'Nine Inch Nails' },
1822 { join => 'artist' }
1825 Can also contain a hash reference to refer to the other relation's relations.
1828 package MyApp::Schema::Track;
1829 use base qw/DBIx::Class/;
1830 __PACKAGE__->table('track');
1831 __PACKAGE__->add_columns(qw/trackid cd position title/);
1832 __PACKAGE__->set_primary_key('trackid');
1833 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1836 # In your application
1837 my $rs = $schema->resultset('Artist')->search(
1838 { 'track.title' => 'Teardrop' },
1840 join => { cd => 'track' },
1841 order_by => 'artist.name',
1845 You need to use the relationship (not the table) name in conditions,
1846 because they are aliased as such. The current table is aliased as "me", so
1847 you need to use me.column_name in order to avoid ambiguity. For example:
1849 # Get CDs from 1984 with a 'Foo' track
1850 my $rs = $schema->resultset('CD')->search(
1853 'tracks.name' => 'Foo'
1855 { join => 'tracks' }
1858 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1859 similarly for a third time). For e.g.
1861 my $rs = $schema->resultset('Artist')->search({
1862 'cds.title' => 'Down to Earth',
1863 'cds_2.title' => 'Popular',
1865 join => [ qw/cds cds/ ],
1868 will return a set of all artists that have both a cd with title 'Down
1869 to Earth' and a cd with title 'Popular'.
1871 If you want to fetch related objects from other tables as well, see C<prefetch>
1878 =item Value: ($rel_name | \@rel_names | \%rel_names)
1882 Contains one or more relationships that should be fetched along with the main
1883 query (when they are accessed afterwards they will have already been
1884 "prefetched"). This is useful for when you know you will need the related
1885 objects, because it saves at least one query:
1887 my $rs = $schema->resultset('Tag')->search(
1896 The initial search results in SQL like the following:
1898 SELECT tag.*, cd.*, artist.* FROM tag
1899 JOIN cd ON tag.cd = cd.cdid
1900 JOIN artist ON cd.artist = artist.artistid
1902 L<DBIx::Class> has no need to go back to the database when we access the
1903 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1906 Simple prefetches will be joined automatically, so there is no need
1907 for a C<join> attribute in the above search. If you're prefetching to
1908 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1909 specify the join as well.
1911 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1912 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1913 with an accessor type of 'single' or 'filter').
1923 Makes the resultset paged and specifies the page to retrieve. Effectively
1924 identical to creating a non-pages resultset and then calling ->page($page)
1927 If L<rows> attribute is not specified it defualts to 10 rows per page.
1937 Specifes the maximum number of rows for direct retrieval or the number of
1938 rows per page if the page attribute or method is used.
1944 =item Value: $offset
1948 Specifies the (zero-based) row number for the first row to be returned, or the
1949 of the first row of the first page if paging is used.
1955 =item Value: \@columns
1959 A arrayref of columns to group by. Can include columns of joined tables.
1961 group_by => [qw/ column1 column2 ... /]
1967 =item Value: $condition
1971 HAVING is a select statement attribute that is applied between GROUP BY and
1972 ORDER BY. It is applied to the after the grouping calculations have been
1975 having => { 'count(employee)' => { '>=', 100 } }
1981 =item Value: (0 | 1)
1985 Set to 1 to group by all columns.
1991 Adds to the WHERE clause.
1993 # only return rows WHERE deleted IS NULL for all searches
1994 __PACKAGE__->resultset_attributes({ where => { deleted => undef } }); )
1996 Can be overridden by passing C<{ where => undef }> as an attribute
2003 Set to 1 to cache search results. This prevents extra SQL queries if you
2004 revisit rows in your ResultSet:
2006 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
2008 while( my $artist = $resultset->next ) {
2012 $rs->first; # without cache, this would issue a query
2014 By default, searches are not cached.
2016 For more examples of using these attributes, see
2017 L<DBIx::Class::Manual::Cookbook>.
2023 =item Value: \@from_clause
2027 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
2028 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
2031 NOTE: Use this on your own risk. This allows you to shoot off your foot!
2033 C<join> will usually do what you need and it is strongly recommended that you
2034 avoid using C<from> unless you cannot achieve the desired result using C<join>.
2035 And we really do mean "cannot", not just tried and failed. Attempting to use
2036 this because you're having problems with C<join> is like trying to use x86
2037 ASM because you've got a syntax error in your C. Trust us on this.
2039 Now, if you're still really, really sure you need to use this (and if you're
2040 not 100% sure, ask the mailing list first), here's an explanation of how this
2043 The syntax is as follows -
2046 { <alias1> => <table1> },
2048 { <alias2> => <table2>, -join_type => 'inner|left|right' },
2049 [], # nested JOIN (optional)
2050 { <table1.column1> => <table2.column2>, ... (more conditions) },
2052 # More of the above [ ] may follow for additional joins
2059 ON <table1.column1> = <table2.column2>
2060 <more joins may follow>
2062 An easy way to follow the examples below is to remember the following:
2064 Anything inside "[]" is a JOIN
2065 Anything inside "{}" is a condition for the enclosing JOIN
2067 The following examples utilize a "person" table in a family tree application.
2068 In order to express parent->child relationships, this table is self-joined:
2070 # Person->belongs_to('father' => 'Person');
2071 # Person->belongs_to('mother' => 'Person');
2073 C<from> can be used to nest joins. Here we return all children with a father,
2074 then search against all mothers of those children:
2076 $rs = $schema->resultset('Person')->search(
2079 alias => 'mother', # alias columns in accordance with "from"
2081 { mother => 'person' },
2084 { child => 'person' },
2086 { father => 'person' },
2087 { 'father.person_id' => 'child.father_id' }
2090 { 'mother.person_id' => 'child.mother_id' }
2097 # SELECT mother.* FROM person mother
2100 # JOIN person father
2101 # ON ( father.person_id = child.father_id )
2103 # ON ( mother.person_id = child.mother_id )
2105 The type of any join can be controlled manually. To search against only people
2106 with a father in the person table, we could explicitly use C<INNER JOIN>:
2108 $rs = $schema->resultset('Person')->search(
2111 alias => 'child', # alias columns in accordance with "from"
2113 { child => 'person' },
2115 { father => 'person', -join_type => 'inner' },
2116 { 'father.id' => 'child.father_id' }
2123 # SELECT child.* FROM person child
2124 # INNER JOIN person father ON child.father_id = father.id