1 package DBIx::Class::ResultSet;
12 use base qw/DBIx::Class/;
13 __PACKAGE__->load_components(qw/AccessorGroup/);
14 __PACKAGE__->mk_group_accessors('simple' => 'result_source');
18 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
22 my $rs = $schema->resultset('User')->search(registered => 1);
23 my @rows = $schema->resultset('Foo')->search(bar => 'baz');
27 The resultset is also known as an iterator. It is responsible for handling
28 queries that may return an arbitrary number of rows, e.g. via L</search>
29 or a C<has_many> relationship.
31 In the examples below, the following table classes are used:
33 package MyApp::Schema::Artist;
34 use base qw/DBIx::Class/;
35 __PACKAGE__->table('artist');
36 __PACKAGE__->add_columns(qw/artistid name/);
37 __PACKAGE__->set_primary_key('artistid');
38 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
41 package MyApp::Schema::CD;
42 use base qw/DBIx::Class/;
43 __PACKAGE__->table('artist');
44 __PACKAGE__->add_columns(qw/cdid artist title year/);
45 __PACKAGE__->set_primary_key('cdid');
46 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
51 =head2 new($source, \%$attrs)
53 The resultset constructor. Takes a source object (usually a
54 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see L</ATRRIBUTES>
55 below). Does not perform any queries -- these are executed as needed by the
58 Generally you won't need to construct a resultset manually. You'll
59 automatically get one from e.g. a L</search> called in scalar context:
61 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
67 return $class->new_result(@_) if ref $class;
68 my ($source, $attrs) = @_;
69 #use Data::Dumper; warn Dumper($attrs);
70 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
72 my $alias = ($attrs->{alias} ||= 'me');
73 if ($attrs->{cols} || !$attrs->{select}) {
74 delete $attrs->{as} if $attrs->{cols};
75 my @cols = ($attrs->{cols}
76 ? @{delete $attrs->{cols}}
78 $attrs->{select} = [ map { m/\./ ? $_ : "${alias}.$_" } @cols ];
80 $attrs->{as} ||= [ map { m/^$alias\.(.*)$/ ? $1 : $_ } @{$attrs->{select}} ];
81 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
82 $attrs->{from} ||= [ { $alias => $source->from } ];
83 if (my $join = delete $attrs->{join}) {
84 foreach my $j (ref $join eq 'ARRAY'
85 ? (@{$join}) : ($join)) {
86 if (ref $j eq 'HASH') {
87 $seen{$_} = 1 foreach keys %$j;
92 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}));
94 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
96 if (my $prefetch = delete $attrs->{prefetch}) {
97 foreach my $p (ref $prefetch eq 'ARRAY'
98 ? (@{$prefetch}) : ($prefetch)) {
99 if( ref $p eq 'HASH' ) {
100 foreach my $key (keys %$p) {
101 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
106 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
110 push @cols, $source->resolve_prefetch($p, $attrs->{alias});
112 push(@{$attrs->{select}}, @cols);
113 push(@{$attrs->{as}}, @cols);
117 if ($attrs->{page}) {
118 $attrs->{rows} ||= 10;
119 $attrs->{offset} ||= 0;
120 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
123 result_source => $source,
124 cond => $attrs->{where},
125 from => $attrs->{from},
127 page => delete $attrs->{page},
130 bless ($new, $class);
136 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
137 my $new_rs = $rs->search({ foo => 3 });
139 If you need to pass in additional attributes but no additional condition,
140 call it as C<search({}, \%attrs);>.
142 # "SELECT foo, bar FROM $class_table"
143 my @all = $class->search({}, { cols => [qw/foo bar/] });
150 #use Data::Dumper;warn Dumper(@_);
152 my $attrs = { %{$self->{attrs}} };
153 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
154 $attrs = { %$attrs, %{ pop(@_) } };
157 my $where = (@_ ? ((@_ == 1 || ref $_[0] eq "HASH") ? shift : {@_}) : undef());
158 if (defined $where) {
159 $where = (defined $attrs->{where}
161 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
162 $where, $attrs->{where} ] }
164 $attrs->{where} = $where;
167 my $rs = (ref $self)->new($self->result_source, $attrs);
169 return (wantarray ? $rs->all : $rs);
172 =head2 search_literal
174 my @obj = $rs->search_literal($literal_where_cond, @bind);
175 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
177 Pass a literal chunk of SQL to be added to the conditional part of the
183 my ($self, $cond, @vals) = @_;
184 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
185 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
186 return $self->search(\$cond, $attrs);
189 =head2 find(@colvalues), find(\%cols, \%attrs?)
191 Finds a row based on its primary key or unique constraint. For example:
193 my $cd = $schema->resultset('CD')->find(5);
195 Also takes an optional C<key> attribute, to search by a specific key or unique
196 constraint. For example:
198 my $cd = $schema->resultset('CD')->find_or_create(
200 artist => 'Massive Attack',
201 title => 'Mezzanine',
203 { key => 'artist_title' }
206 See also L</find_or_create> and L</update_or_create>.
211 my ($self, @vals) = @_;
212 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
214 my @cols = $self->result_source->primary_columns;
215 if (exists $attrs->{key}) {
216 my %uniq = $self->result_source->unique_constraints;
217 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
218 unless exists $uniq{$attrs->{key}};
219 @cols = @{ $uniq{$attrs->{key}} };
221 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
222 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
226 if (ref $vals[0] eq 'HASH') {
228 } elsif (@cols == @vals) {
230 @{$query}{@cols} = @vals;
234 #warn Dumper($query);
235 return $self->search($query)->next;
238 =head2 search_related
240 $rs->search_related('relname', $cond?, $attrs?);
242 Search the specified relationship. Optionally specify a condition for matching
248 my ($self, $rel, @rest) = @_;
249 my $rel_obj = $self->result_source->relationship_info($rel);
250 $self->throw_exception(
251 "No such relationship ${rel} in search_related")
253 my $rs = $self->search(undef, { join => $rel });
254 return $self->result_source->schema->resultset($rel_obj->{class}
265 Returns a storage-driven cursor to the given resultset.
271 my ($attrs) = $self->{attrs};
272 $attrs = { %$attrs };
273 return $self->{cursor}
274 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
275 $attrs->{where},$attrs);
280 Perform a search, but use C<LIKE> instead of equality as the condition. Note
281 that this is simply a convenience method; you most likely want to use
282 L</search> with specific operators.
284 For more information, see L<DBIx::Class::Manual::Cookbook>.
291 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
294 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
295 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
296 return $class->search($query, { %$attrs });
299 =head2 slice($first, $last)
301 Returns a subset of elements from the resultset.
306 my ($self, $min, $max) = @_;
307 my $attrs = { %{ $self->{attrs} || {} } };
308 $attrs->{offset} ||= 0;
309 $attrs->{offset} += $min;
310 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
311 my $slice = (ref $self)->new($self->result_source, $attrs);
312 return (wantarray ? $slice->all : $slice);
317 Returns the next element in the resultset (C<undef> is there is none).
319 Can be used to efficiently iterate over records in the resultset:
321 my $rs = $schema->resultset('CD')->search({});
322 while (my $cd = $rs->next) {
330 my @row = $self->cursor->next;
331 # warn Dumper(\@row); use Data::Dumper;
332 return unless (@row);
333 return $self->_construct_object(@row);
336 sub _construct_object {
337 my ($self, @row) = @_;
338 my @as = @{ $self->{attrs}{as} };
339 #warn "@cols -> @row";
340 my $info = [ {}, {} ];
341 foreach my $as (@as) {
343 my @parts = split(/\./, $as);
344 my $col = pop(@parts);
345 foreach my $p (@parts) {
346 $target = $target->[1]->{$p} ||= [];
348 $target->[0]->{$col} = shift @row;
350 #use Data::Dumper; warn Dumper(\@as, $info);
351 my $new = $self->result_source->result_class->inflate_result(
352 $self->result_source, @$info);
353 $new = $self->{attrs}{record_filter}->($new)
354 if exists $self->{attrs}{record_filter};
360 Returns a reference to the result source for this recordset.
367 Performs an SQL C<COUNT> with the same query as the resultset was built
368 with to find the number of elements. If passed arguments, does a search
369 on the resultset and counts the results of that.
371 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
372 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
373 not support C<DISTINCT> with multiple columns. If you are using such a
374 database, you should only use columns from the main table in your C<group_by>
381 return $self->search(@_)->count if @_ && defined $_[0];
382 unless (defined $self->{count}) {
384 my $select = { 'count' => '*' };
385 if( $group_by = delete $self->{attrs}{group_by} ) {
386 my @distinct = @$group_by;
387 # todo: try CONCAT for multi-column pk
388 my @pk = $self->result_source->primary_columns;
389 if( scalar(@pk) == 1 ) {
391 my $alias = $self->{attrs}{alias};
392 my $re = qr/^($alias\.)?$pk$/;
393 foreach my $column ( @$group_by ) {
394 if( $column =~ $re ) {
395 @distinct = ( $column );
401 $select = { count => { 'distinct' => \@distinct } };
402 #use Data::Dumper; die Dumper $select;
405 my $attrs = { %{ $self->{attrs} },
408 # offset, order by and page are not needed to count. record_filter is cdbi
409 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
411 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
412 $self->{attrs}{group_by} = $group_by;
414 return 0 unless $self->{count};
415 my $count = $self->{count};
416 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
417 $count = $self->{attrs}{rows} if
418 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
424 Calls L</search_literal> with the passed arguments, then L</count>.
428 sub count_literal { shift->search_literal(@_)->count; }
432 Returns all elements in the resultset. Called implictly if the resultset
433 is returned in list context.
439 return map { $self->_construct_object(@$_); }
445 Resets the resultset's cursor, so you can iterate through the elements again.
451 $self->cursor->reset;
457 Resets the resultset and returns the first element.
462 return $_[0]->reset->next;
465 =head2 update(\%values)
467 Sets the specified columns in the resultset to the supplied values.
472 my ($self, $values) = @_;
473 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
474 return $self->result_source->storage->update(
475 $self->result_source->from, $values, $self->{cond});
478 =head2 update_all(\%values)
480 Fetches all objects and updates them one at a time. Note that C<update_all>
481 will run cascade triggers while L</update> will not.
486 my ($self, $values) = @_;
487 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
488 foreach my $obj ($self->all) {
489 $obj->set_columns($values)->update;
496 Deletes the contents of the resultset from its result source.
502 $self->result_source->storage->delete($self->result_source->from, $self->{cond});
508 Fetches all objects and deletes them one at a time. Note that C<delete_all>
509 will run cascade triggers while L</delete> will not.
515 $_->delete for $self->all;
521 Returns a L<Data::Page> object for the current resultset. Only makes
522 sense for queries with a C<page> attribute.
528 my $attrs = $self->{attrs};
529 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
530 $attrs->{rows} ||= 10;
532 return $self->{pager} ||= Data::Page->new(
533 $self->{count}, $attrs->{rows}, $self->{page});
536 =head2 page($page_num)
538 Returns a new resultset for the specified page.
543 my ($self, $page) = @_;
544 my $attrs = { %{$self->{attrs}} };
545 $attrs->{page} = $page;
546 return (ref $self)->new($self->result_source, $attrs);
549 =head2 new_result(\%vals)
551 Creates a result in the resultset's result class.
556 my ($self, $values) = @_;
557 $self->throw_exception( "new_result needs a hash" )
558 unless (ref $values eq 'HASH');
559 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
560 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
562 my $alias = $self->{attrs}{alias};
563 foreach my $key (keys %{$self->{cond}||{}}) {
564 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
566 my $obj = $self->result_source->result_class->new(\%new);
567 $obj->result_source($self->result_source) if $obj->can('result_source');
571 =head2 create(\%vals)
573 Inserts a record into the resultset and returns the object.
575 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
580 my ($self, $attrs) = @_;
581 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
582 return $self->new_result($attrs)->insert;
585 =head2 find_or_create(\%vals, \%attrs?)
587 $class->find_or_create({ key => $val, ... });
589 Searches for a record matching the search condition; if it doesn't find one,
590 creates one and returns that instead.
592 my $cd = $schema->resultset('CD')->find_or_create({
594 artist => 'Massive Attack',
595 title => 'Mezzanine',
599 Also takes an optional C<key> attribute, to search by a specific key or unique
600 constraint. For example:
602 my $cd = $schema->resultset('CD')->find_or_create(
604 artist => 'Massive Attack',
605 title => 'Mezzanine',
607 { key => 'artist_title' }
610 See also L</find> and L</update_or_create>.
616 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
617 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
618 my $exists = $self->find($hash, $attrs);
619 return defined($exists) ? $exists : $self->create($hash);
622 =head2 update_or_create
624 $class->update_or_create({ key => $val, ... });
626 First, search for an existing row matching one of the unique constraints
627 (including the primary key) on the source of this resultset. If a row is
628 found, update it with the other given column values. Otherwise, create a new
631 Takes an optional C<key> attribute to search on a specific unique constraint.
634 # In your application
635 my $cd = $schema->resultset('CD')->update_or_create(
637 artist => 'Massive Attack',
638 title => 'Mezzanine',
641 { key => 'artist_title' }
644 If no C<key> is specified, it searches on all unique constraints defined on the
645 source, including the primary key.
647 If the C<key> is specified as C<primary>, search only on the primary key.
649 See also L</find> and L</find_or_create>.
653 sub update_or_create {
656 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
657 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
659 my %unique_constraints = $self->result_source->unique_constraints;
660 my @constraint_names = (exists $attrs->{key}
662 : keys %unique_constraints);
665 foreach my $name (@constraint_names) {
666 my @unique_cols = @{ $unique_constraints{$name} };
668 map { $_ => $hash->{$_} }
669 grep { exists $hash->{$_} }
672 push @unique_hashes, \%unique_hash
673 if (scalar keys %unique_hash == scalar @unique_cols);
677 if (@unique_hashes) {
678 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
680 $row->set_columns($hash);
686 $row = $self->create($hash);
692 =head2 throw_exception
694 See Schema's throw_exception
698 sub throw_exception {
700 $self->result_source->schema->throw_exception(@_);
705 The resultset takes various attributes that modify its behavior. Here's an
710 Which column(s) to order the results by. This is currently passed through
711 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
713 =head2 cols (arrayref)
715 Shortcut to request a particular set of columns to be retrieved. Adds
716 C<me.> onto the start of any column without a C<.> in it and sets C<select>
717 from that, then auto-populates C<as> from C<select> as normal.
719 =head2 select (arrayref)
721 Indicates which columns should be selected from the storage. You can use
722 column names, or in the case of RDBMS back ends, function or stored procedure
725 $rs = $schema->resultset('Foo')->search(
730 { count => 'column_to_count' },
731 { sum => 'column_to_sum' }
736 When you use function/stored procedure names and do not supply an C<as>
737 attribute, the column names returned are storage-dependent. E.g. MySQL would
738 return a column named C<count(column_to_count)> in the above example.
742 Indicates column names for object inflation. This is used in conjunction with
743 C<select>, usually when C<select> contains one or more function or stored
746 $rs = $schema->resultset('Foo')->search(
751 { count => 'column2' }
753 as => [qw/ column1 column2_count /]
757 my $foo = $rs->first(); # get the first Foo
759 If the object against which the search is performed already has an accessor
760 matching a column name specified in C<as>, the value can be retrieved using
761 the accessor as normal:
763 my $column1 = $foo->column1();
765 If on the other hand an accessor does not exist in the object, you need to
766 use C<get_column> instead:
768 my $column2_count = $foo->get_column('column2_count');
770 You can create your own accessors if required - see
771 L<DBIx::Class::Manual::Cookbook> for details.
775 Contains a list of relationships that should be joined for this query. For
778 # Get CDs by Nine Inch Nails
779 my $rs = $schema->resultset('CD')->search(
780 { 'artist.name' => 'Nine Inch Nails' },
784 Can also contain a hash reference to refer to the other relation's relations.
787 package MyApp::Schema::Track;
788 use base qw/DBIx::Class/;
789 __PACKAGE__->table('track');
790 __PACKAGE__->add_columns(qw/trackid cd position title/);
791 __PACKAGE__->set_primary_key('trackid');
792 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
795 # In your application
796 my $rs = $schema->resultset('Artist')->search(
797 { 'track.title' => 'Teardrop' },
799 join => { cd => 'track' },
800 order_by => 'artist.name',
804 If you want to fetch columns from related tables as well, see C<prefetch>
807 =head2 prefetch arrayref/hashref
809 Contains one or more relationships that should be fetched along with the main
810 query (when they are accessed afterwards they will have already been
811 "prefetched"). This is useful for when you know you will need the related
812 objects, because it saves at least one query:
814 my $rs = $schema->resultset('Tag')->search(
823 The initial search results in SQL like the following:
825 SELECT tag.*, cd.*, artist.* FROM tag
826 JOIN cd ON tag.cd = cd.cdid
827 JOIN artist ON cd.artist = artist.artistid
829 L<DBIx::Class> has no need to go back to the database when we access the
830 C<cd> or C<artist> relationships, which saves us two SQL statements in this
833 Any prefetched relationship will be joined automatically, so there is no need
834 for a C<join> attribute in the above search.
836 C<prefetch> can be used with the following relationship types: C<belongs_to>,
839 =head2 from (arrayref)
841 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
842 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
845 NOTE: Use this on your own risk. This allows you to shoot off your foot!
846 C<join> will usually do what you need and it is strongly recommended that you
847 avoid using C<from> unless you cannot achieve the desired result using C<join>.
849 In simple terms, C<from> works as follows:
852 { <alias> => <table>, -join-type => 'inner|left|right' }
853 [] # nested JOIN (optional)
854 { <table.column> = <foreign_table.foreign_key> }
860 ON <table.column> = <foreign_table.foreign_key>
862 An easy way to follow the examples below is to remember the following:
864 Anything inside "[]" is a JOIN
865 Anything inside "{}" is a condition for the enclosing JOIN
867 The following examples utilize a "person" table in a family tree application.
868 In order to express parent->child relationships, this table is self-joined:
870 # Person->belongs_to('father' => 'Person');
871 # Person->belongs_to('mother' => 'Person');
873 C<from> can be used to nest joins. Here we return all children with a father,
874 then search against all mothers of those children:
876 $rs = $schema->resultset('Person')->search(
879 alias => 'mother', # alias columns in accordance with "from"
881 { mother => 'person' },
884 { child => 'person' },
886 { father => 'person' },
887 { 'father.person_id' => 'child.father_id' }
890 { 'mother.person_id' => 'child.mother_id' }
897 # SELECT mother.* FROM person mother
901 # ON ( father.person_id = child.father_id )
903 # ON ( mother.person_id = child.mother_id )
905 The type of any join can be controlled manually. To search against only people
906 with a father in the person table, we could explicitly use C<INNER JOIN>:
908 $rs = $schema->resultset('Person')->search(
911 alias => 'child', # alias columns in accordance with "from"
913 { child => 'person' },
915 { father => 'person', -join-type => 'inner' },
916 { 'father.id' => 'child.father_id' }
923 # SELECT child.* FROM person child
924 # INNER JOIN person father ON child.father_id = father.id
928 For a paged resultset, specifies which page to retrieve. Leave unset
929 for an unpaged resultset.
933 For a paged resultset, how many rows per page:
937 Can also be used to simulate an SQL C<LIMIT>.
939 =head2 group_by (arrayref)
941 A arrayref of columns to group by. Can include columns of joined tables.
943 group_by => [qw/ column1 column2 ... /]
947 Set to 1 to group by all columns.
949 For more examples of using these attributes, see
950 L<DBIx::Class::Manual::Cookbook>.