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') {
227 $query = { %{$vals[0]} };
228 } elsif (@cols == @vals) {
230 @{$query}{@cols} = @vals;
234 foreach (keys %$query) {
236 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
238 #warn Dumper($query);
239 return $self->search($query)->next;
242 =head2 search_related
244 $rs->search_related('relname', $cond?, $attrs?);
246 Search the specified relationship. Optionally specify a condition for matching
252 my ($self, $rel, @rest) = @_;
253 my $rel_obj = $self->result_source->relationship_info($rel);
254 $self->throw_exception(
255 "No such relationship ${rel} in search_related")
257 my $rs = $self->search(undef, { join => $rel });
258 return $self->result_source->schema->resultset($rel_obj->{class}
269 Returns a storage-driven cursor to the given resultset.
275 my ($attrs) = $self->{attrs};
276 $attrs = { %$attrs };
277 return $self->{cursor}
278 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
279 $attrs->{where},$attrs);
284 Perform a search, but use C<LIKE> instead of equality as the condition. Note
285 that this is simply a convenience method; you most likely want to use
286 L</search> with specific operators.
288 For more information, see L<DBIx::Class::Manual::Cookbook>.
295 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
298 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
299 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
300 return $class->search($query, { %$attrs });
303 =head2 slice($first, $last)
305 Returns a subset of elements from the resultset.
310 my ($self, $min, $max) = @_;
311 my $attrs = { %{ $self->{attrs} || {} } };
312 $attrs->{offset} ||= 0;
313 $attrs->{offset} += $min;
314 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
315 my $slice = (ref $self)->new($self->result_source, $attrs);
316 return (wantarray ? $slice->all : $slice);
321 Returns the next element in the resultset (C<undef> is there is none).
323 Can be used to efficiently iterate over records in the resultset:
325 my $rs = $schema->resultset('CD')->search({});
326 while (my $cd = $rs->next) {
334 my @row = $self->cursor->next;
335 # warn Dumper(\@row); use Data::Dumper;
336 return unless (@row);
337 return $self->_construct_object(@row);
340 sub _construct_object {
341 my ($self, @row) = @_;
342 my @as = @{ $self->{attrs}{as} };
343 #warn "@cols -> @row";
344 my $info = [ {}, {} ];
345 foreach my $as (@as) {
347 my @parts = split(/\./, $as);
348 my $col = pop(@parts);
349 foreach my $p (@parts) {
350 $target = $target->[1]->{$p} ||= [];
352 $target->[0]->{$col} = shift @row;
354 #use Data::Dumper; warn Dumper(\@as, $info);
355 my $new = $self->result_source->result_class->inflate_result(
356 $self->result_source, @$info);
357 $new = $self->{attrs}{record_filter}->($new)
358 if exists $self->{attrs}{record_filter};
364 Returns a reference to the result source for this recordset.
371 Performs an SQL C<COUNT> with the same query as the resultset was built
372 with to find the number of elements. If passed arguments, does a search
373 on the resultset and counts the results of that.
375 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
376 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
377 not support C<DISTINCT> with multiple columns. If you are using such a
378 database, you should only use columns from the main table in your C<group_by>
385 return $self->search(@_)->count if @_ && defined $_[0];
386 unless (defined $self->{count}) {
388 my $select = { 'count' => '*' };
389 if( $group_by = delete $self->{attrs}{group_by} ) {
390 my @distinct = @$group_by;
391 # todo: try CONCAT for multi-column pk
392 my @pk = $self->result_source->primary_columns;
393 if( scalar(@pk) == 1 ) {
395 my $alias = $self->{attrs}{alias};
396 my $re = qr/^($alias\.)?$pk$/;
397 foreach my $column ( @$group_by ) {
398 if( $column =~ $re ) {
399 @distinct = ( $column );
405 $select = { count => { 'distinct' => \@distinct } };
406 #use Data::Dumper; die Dumper $select;
409 my $attrs = { %{ $self->{attrs} },
412 # offset, order by and page are not needed to count. record_filter is cdbi
413 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
415 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
416 $self->{attrs}{group_by} = $group_by;
418 return 0 unless $self->{count};
419 my $count = $self->{count};
420 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
421 $count = $self->{attrs}{rows} if
422 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
428 Calls L</search_literal> with the passed arguments, then L</count>.
432 sub count_literal { shift->search_literal(@_)->count; }
436 Returns all elements in the resultset. Called implictly if the resultset
437 is returned in list context.
443 return map { $self->_construct_object(@$_); }
449 Resets the resultset's cursor, so you can iterate through the elements again.
455 $self->cursor->reset;
461 Resets the resultset and returns the first element.
466 return $_[0]->reset->next;
469 =head2 update(\%values)
471 Sets the specified columns in the resultset to the supplied values.
476 my ($self, $values) = @_;
477 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
478 return $self->result_source->storage->update(
479 $self->result_source->from, $values, $self->{cond});
482 =head2 update_all(\%values)
484 Fetches all objects and updates them one at a time. Note that C<update_all>
485 will run cascade triggers while L</update> will not.
490 my ($self, $values) = @_;
491 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
492 foreach my $obj ($self->all) {
493 $obj->set_columns($values)->update;
500 Deletes the contents of the resultset from its result source.
506 $self->result_source->storage->delete($self->result_source->from, $self->{cond});
512 Fetches all objects and deletes them one at a time. Note that C<delete_all>
513 will run cascade triggers while L</delete> will not.
519 $_->delete for $self->all;
525 Returns a L<Data::Page> object for the current resultset. Only makes
526 sense for queries with a C<page> attribute.
532 my $attrs = $self->{attrs};
533 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
534 $attrs->{rows} ||= 10;
536 return $self->{pager} ||= Data::Page->new(
537 $self->{count}, $attrs->{rows}, $self->{page});
540 =head2 page($page_num)
542 Returns a new resultset for the specified page.
547 my ($self, $page) = @_;
548 my $attrs = { %{$self->{attrs}} };
549 $attrs->{page} = $page;
550 return (ref $self)->new($self->result_source, $attrs);
553 =head2 new_result(\%vals)
555 Creates a result in the resultset's result class.
560 my ($self, $values) = @_;
561 $self->throw_exception( "new_result needs a hash" )
562 unless (ref $values eq 'HASH');
563 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
564 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
566 my $alias = $self->{attrs}{alias};
567 foreach my $key (keys %{$self->{cond}||{}}) {
568 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
570 my $obj = $self->result_source->result_class->new(\%new);
571 $obj->result_source($self->result_source) if $obj->can('result_source');
575 =head2 create(\%vals)
577 Inserts a record into the resultset and returns the object.
579 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
584 my ($self, $attrs) = @_;
585 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
586 return $self->new_result($attrs)->insert;
589 =head2 find_or_create(\%vals, \%attrs?)
591 $class->find_or_create({ key => $val, ... });
593 Searches for a record matching the search condition; if it doesn't find one,
594 creates one and returns that instead.
596 my $cd = $schema->resultset('CD')->find_or_create({
598 artist => 'Massive Attack',
599 title => 'Mezzanine',
603 Also takes an optional C<key> attribute, to search by a specific key or unique
604 constraint. For example:
606 my $cd = $schema->resultset('CD')->find_or_create(
608 artist => 'Massive Attack',
609 title => 'Mezzanine',
611 { key => 'artist_title' }
614 See also L</find> and L</update_or_create>.
620 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
621 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
622 my $exists = $self->find($hash, $attrs);
623 return defined($exists) ? $exists : $self->create($hash);
626 =head2 update_or_create
628 $class->update_or_create({ key => $val, ... });
630 First, search for an existing row matching one of the unique constraints
631 (including the primary key) on the source of this resultset. If a row is
632 found, update it with the other given column values. Otherwise, create a new
635 Takes an optional C<key> attribute to search on a specific unique constraint.
638 # In your application
639 my $cd = $schema->resultset('CD')->update_or_create(
641 artist => 'Massive Attack',
642 title => 'Mezzanine',
645 { key => 'artist_title' }
648 If no C<key> is specified, it searches on all unique constraints defined on the
649 source, including the primary key.
651 If the C<key> is specified as C<primary>, search only on the primary key.
653 See also L</find> and L</find_or_create>.
657 sub update_or_create {
660 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
661 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
663 my %unique_constraints = $self->result_source->unique_constraints;
664 my @constraint_names = (exists $attrs->{key}
666 : keys %unique_constraints);
669 foreach my $name (@constraint_names) {
670 my @unique_cols = @{ $unique_constraints{$name} };
672 map { $_ => $hash->{$_} }
673 grep { exists $hash->{$_} }
676 push @unique_hashes, \%unique_hash
677 if (scalar keys %unique_hash == scalar @unique_cols);
681 if (@unique_hashes) {
682 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
684 $row->set_columns($hash);
690 $row = $self->create($hash);
696 =head2 throw_exception
698 See Schema's throw_exception
702 sub throw_exception {
704 $self->result_source->schema->throw_exception(@_);
709 The resultset takes various attributes that modify its behavior. Here's an
714 Which column(s) to order the results by. This is currently passed through
715 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
717 =head2 cols (arrayref)
719 Shortcut to request a particular set of columns to be retrieved. Adds
720 C<me.> onto the start of any column without a C<.> in it and sets C<select>
721 from that, then auto-populates C<as> from C<select> as normal.
723 =head2 select (arrayref)
725 Indicates which columns should be selected from the storage. You can use
726 column names, or in the case of RDBMS back ends, function or stored procedure
729 $rs = $schema->resultset('Foo')->search(
734 { count => 'column_to_count' },
735 { sum => 'column_to_sum' }
740 When you use function/stored procedure names and do not supply an C<as>
741 attribute, the column names returned are storage-dependent. E.g. MySQL would
742 return a column named C<count(column_to_count)> in the above example.
746 Indicates column names for object inflation. This is used in conjunction with
747 C<select>, usually when C<select> contains one or more function or stored
750 $rs = $schema->resultset('Foo')->search(
755 { count => 'column2' }
757 as => [qw/ column1 column2_count /]
761 my $foo = $rs->first(); # get the first Foo
763 If the object against which the search is performed already has an accessor
764 matching a column name specified in C<as>, the value can be retrieved using
765 the accessor as normal:
767 my $column1 = $foo->column1();
769 If on the other hand an accessor does not exist in the object, you need to
770 use C<get_column> instead:
772 my $column2_count = $foo->get_column('column2_count');
774 You can create your own accessors if required - see
775 L<DBIx::Class::Manual::Cookbook> for details.
779 Contains a list of relationships that should be joined for this query. For
782 # Get CDs by Nine Inch Nails
783 my $rs = $schema->resultset('CD')->search(
784 { 'artist.name' => 'Nine Inch Nails' },
788 Can also contain a hash reference to refer to the other relation's relations.
791 package MyApp::Schema::Track;
792 use base qw/DBIx::Class/;
793 __PACKAGE__->table('track');
794 __PACKAGE__->add_columns(qw/trackid cd position title/);
795 __PACKAGE__->set_primary_key('trackid');
796 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
799 # In your application
800 my $rs = $schema->resultset('Artist')->search(
801 { 'track.title' => 'Teardrop' },
803 join => { cd => 'track' },
804 order_by => 'artist.name',
808 If you want to fetch columns from related tables as well, see C<prefetch>
811 =head2 prefetch arrayref/hashref
813 Contains one or more relationships that should be fetched along with the main
814 query (when they are accessed afterwards they will have already been
815 "prefetched"). This is useful for when you know you will need the related
816 objects, because it saves at least one query:
818 my $rs = $schema->resultset('Tag')->search(
827 The initial search results in SQL like the following:
829 SELECT tag.*, cd.*, artist.* FROM tag
830 JOIN cd ON tag.cd = cd.cdid
831 JOIN artist ON cd.artist = artist.artistid
833 L<DBIx::Class> has no need to go back to the database when we access the
834 C<cd> or C<artist> relationships, which saves us two SQL statements in this
837 Any prefetched relationship will be joined automatically, so there is no need
838 for a C<join> attribute in the above search.
840 C<prefetch> can be used with the following relationship types: C<belongs_to>,
843 =head2 from (arrayref)
845 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
846 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
849 NOTE: Use this on your own risk. This allows you to shoot off your foot!
850 C<join> will usually do what you need and it is strongly recommended that you
851 avoid using C<from> unless you cannot achieve the desired result using C<join>.
853 In simple terms, C<from> works as follows:
856 { <alias> => <table>, -join-type => 'inner|left|right' }
857 [] # nested JOIN (optional)
858 { <table.column> = <foreign_table.foreign_key> }
864 ON <table.column> = <foreign_table.foreign_key>
866 An easy way to follow the examples below is to remember the following:
868 Anything inside "[]" is a JOIN
869 Anything inside "{}" is a condition for the enclosing JOIN
871 The following examples utilize a "person" table in a family tree application.
872 In order to express parent->child relationships, this table is self-joined:
874 # Person->belongs_to('father' => 'Person');
875 # Person->belongs_to('mother' => 'Person');
877 C<from> can be used to nest joins. Here we return all children with a father,
878 then search against all mothers of those children:
880 $rs = $schema->resultset('Person')->search(
883 alias => 'mother', # alias columns in accordance with "from"
885 { mother => 'person' },
888 { child => 'person' },
890 { father => 'person' },
891 { 'father.person_id' => 'child.father_id' }
894 { 'mother.person_id' => 'child.mother_id' }
901 # SELECT mother.* FROM person mother
905 # ON ( father.person_id = child.father_id )
907 # ON ( mother.person_id = child.mother_id )
909 The type of any join can be controlled manually. To search against only people
910 with a father in the person table, we could explicitly use C<INNER JOIN>:
912 $rs = $schema->resultset('Person')->search(
915 alias => 'child', # alias columns in accordance with "from"
917 { child => 'person' },
919 { father => 'person', -join-type => 'inner' },
920 { 'father.id' => 'child.father_id' }
927 # SELECT child.* FROM person child
928 # INNER JOIN person father ON child.father_id = father.id
932 For a paged resultset, specifies which page to retrieve. Leave unset
933 for an unpaged resultset.
937 For a paged resultset, how many rows per page:
941 Can also be used to simulate an SQL C<LIMIT>.
943 =head2 group_by (arrayref)
945 A arrayref of columns to group by. Can include columns of joined tables.
947 group_by => [qw/ column1 column2 ... /]
951 Set to 1 to group by all columns.
953 For more examples of using these attributes, see
954 L<DBIx::Class::Manual::Cookbook>.