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 if (my $include = delete $attrs->{include_columns}) {
82 push(@{$attrs->{select}}, @$include);
83 push(@{$attrs->{as}}, map { m/([^\.]+)$/; $1; } @$include);
85 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
86 $attrs->{from} ||= [ { $alias => $source->from } ];
87 $attrs->{seen_join} ||= {};
88 if (my $join = delete $attrs->{join}) {
89 foreach my $j (ref $join eq 'ARRAY'
90 ? (@{$join}) : ($join)) {
91 if (ref $j eq 'HASH') {
92 $seen{$_} = 1 foreach keys %$j;
97 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}, $attrs->{seen_join}));
99 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
101 if (my $prefetch = delete $attrs->{prefetch}) {
102 foreach my $p (ref $prefetch eq 'ARRAY'
103 ? (@{$prefetch}) : ($prefetch)) {
104 if( ref $p eq 'HASH' ) {
105 foreach my $key (keys %$p) {
106 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
111 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
114 my @prefetch = $source->resolve_prefetch($p, $attrs->{alias});
116 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
117 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
121 if ($attrs->{page}) {
122 $attrs->{rows} ||= 10;
123 $attrs->{offset} ||= 0;
124 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
127 result_source => $source,
128 cond => $attrs->{where},
129 from => $attrs->{from},
131 page => delete $attrs->{page},
134 bless ($new, $class);
140 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
141 my $new_rs = $rs->search({ foo => 3 });
143 If you need to pass in additional attributes but no additional condition,
144 call it as C<search({}, \%attrs);>.
146 # "SELECT foo, bar FROM $class_table"
147 my @all = $class->search({}, { cols => [qw/foo bar/] });
154 #use Data::Dumper;warn Dumper(@_);
156 my $attrs = { %{$self->{attrs}} };
157 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
158 $attrs = { %$attrs, %{ pop(@_) } };
161 my $where = (@_ ? ((@_ == 1 || ref $_[0] eq "HASH") ? shift : {@_}) : undef());
162 if (defined $where) {
163 $where = (defined $attrs->{where}
165 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
166 $where, $attrs->{where} ] }
168 $attrs->{where} = $where;
171 my $rs = (ref $self)->new($self->result_source, $attrs);
173 return (wantarray ? $rs->all : $rs);
176 =head2 search_literal
178 my @obj = $rs->search_literal($literal_where_cond, @bind);
179 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
181 Pass a literal chunk of SQL to be added to the conditional part of the
187 my ($self, $cond, @vals) = @_;
188 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
189 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
190 return $self->search(\$cond, $attrs);
193 =head2 find(@colvalues), find(\%cols, \%attrs?)
195 Finds a row based on its primary key or unique constraint. For example:
197 my $cd = $schema->resultset('CD')->find(5);
199 Also takes an optional C<key> attribute, to search by a specific key or unique
200 constraint. For example:
202 my $cd = $schema->resultset('CD')->find_or_create(
204 artist => 'Massive Attack',
205 title => 'Mezzanine',
207 { key => 'artist_title' }
210 See also L</find_or_create> and L</update_or_create>.
215 my ($self, @vals) = @_;
216 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
218 my @cols = $self->result_source->primary_columns;
219 if (exists $attrs->{key}) {
220 my %uniq = $self->result_source->unique_constraints;
221 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
222 unless exists $uniq{$attrs->{key}};
223 @cols = @{ $uniq{$attrs->{key}} };
225 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
226 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
230 if (ref $vals[0] eq 'HASH') {
231 $query = { %{$vals[0]} };
232 } elsif (@cols == @vals) {
234 @{$query}{@cols} = @vals;
238 foreach (keys %$query) {
240 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
242 #warn Dumper($query);
243 return $self->search($query,$attrs)->next;
246 =head2 search_related
248 $rs->search_related('relname', $cond?, $attrs?);
250 Search the specified relationship. Optionally specify a condition for matching
256 my ($self, $rel, @rest) = @_;
257 my $rel_obj = $self->result_source->relationship_info($rel);
258 $self->throw_exception(
259 "No such relationship ${rel} in search_related")
261 my $rs = $self->search(undef, { join => $rel });
262 my $alias = ($rs->{attrs}{seen_join}{$rel} > 1
263 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
265 return $self->result_source->schema->resultset($rel_obj->{class}
276 Returns a storage-driven cursor to the given resultset.
282 my ($attrs) = $self->{attrs};
283 $attrs = { %$attrs };
284 return $self->{cursor}
285 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
286 $attrs->{where},$attrs);
291 Perform a search, but use C<LIKE> instead of equality as the condition. Note
292 that this is simply a convenience method; you most likely want to use
293 L</search> with specific operators.
295 For more information, see L<DBIx::Class::Manual::Cookbook>.
302 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
305 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
306 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
307 return $class->search($query, { %$attrs });
310 =head2 slice($first, $last)
312 Returns a subset of elements from the resultset.
317 my ($self, $min, $max) = @_;
318 my $attrs = { %{ $self->{attrs} || {} } };
319 $attrs->{offset} ||= 0;
320 $attrs->{offset} += $min;
321 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
322 my $slice = (ref $self)->new($self->result_source, $attrs);
323 return (wantarray ? $slice->all : $slice);
328 Returns the next element in the resultset (C<undef> is there is none).
330 Can be used to efficiently iterate over records in the resultset:
332 my $rs = $schema->resultset('CD')->search({});
333 while (my $cd = $rs->next) {
341 my @row = $self->cursor->next;
342 # warn Dumper(\@row); use Data::Dumper;
343 return unless (@row);
344 return $self->_construct_object(@row);
347 sub _construct_object {
348 my ($self, @row) = @_;
349 my @as = @{ $self->{attrs}{as} };
350 #warn "@cols -> @row";
351 my $info = [ {}, {} ];
352 foreach my $as (@as) {
354 my @parts = split(/\./, $as);
355 my $col = pop(@parts);
356 foreach my $p (@parts) {
357 $target = $target->[1]->{$p} ||= [];
359 $target->[0]->{$col} = shift @row;
361 #use Data::Dumper; warn Dumper(\@as, $info);
362 my $new = $self->result_source->result_class->inflate_result(
363 $self->result_source, @$info);
364 $new = $self->{attrs}{record_filter}->($new)
365 if exists $self->{attrs}{record_filter};
371 Returns a reference to the result source for this recordset.
378 Performs an SQL C<COUNT> with the same query as the resultset was built
379 with to find the number of elements. If passed arguments, does a search
380 on the resultset and counts the results of that.
382 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
383 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
384 not support C<DISTINCT> with multiple columns. If you are using such a
385 database, you should only use columns from the main table in your C<group_by>
392 return $self->search(@_)->count if @_ && defined $_[0];
393 unless (defined $self->{count}) {
395 my $select = { 'count' => '*' };
396 if( $group_by = delete $self->{attrs}{group_by} ) {
397 my @distinct = @$group_by;
398 # todo: try CONCAT for multi-column pk
399 my @pk = $self->result_source->primary_columns;
400 if( scalar(@pk) == 1 ) {
402 my $alias = $self->{attrs}{alias};
403 my $re = qr/^($alias\.)?$pk$/;
404 foreach my $column ( @$group_by ) {
405 if( $column =~ $re ) {
406 @distinct = ( $column );
412 $select = { count => { 'distinct' => \@distinct } };
413 #use Data::Dumper; die Dumper $select;
416 my $attrs = { %{ $self->{attrs} },
419 # offset, order by and page are not needed to count. record_filter is cdbi
420 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
422 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
423 $self->{attrs}{group_by} = $group_by;
425 return 0 unless $self->{count};
426 my $count = $self->{count};
427 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
428 $count = $self->{attrs}{rows} if
429 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
435 Calls L</search_literal> with the passed arguments, then L</count>.
439 sub count_literal { shift->search_literal(@_)->count; }
443 Returns all elements in the resultset. Called implictly if the resultset
444 is returned in list context.
450 return map { $self->_construct_object(@$_); }
456 Resets the resultset's cursor, so you can iterate through the elements again.
462 $self->cursor->reset;
468 Resets the resultset and returns the first element.
473 return $_[0]->reset->next;
476 =head2 update(\%values)
478 Sets the specified columns in the resultset to the supplied values.
483 my ($self, $values) = @_;
484 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
485 return $self->result_source->storage->update(
486 $self->result_source->from, $values, $self->{cond});
489 =head2 update_all(\%values)
491 Fetches all objects and updates them one at a time. Note that C<update_all>
492 will run cascade triggers while L</update> will not.
497 my ($self, $values) = @_;
498 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
499 foreach my $obj ($self->all) {
500 $obj->set_columns($values)->update;
507 Deletes the contents of the resultset from its result source.
513 $self->result_source->storage->delete($self->result_source->from, $self->{cond});
519 Fetches all objects and deletes them one at a time. Note that C<delete_all>
520 will run cascade triggers while L</delete> will not.
526 $_->delete for $self->all;
532 Returns a L<Data::Page> object for the current resultset. Only makes
533 sense for queries with a C<page> attribute.
539 my $attrs = $self->{attrs};
540 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
541 $attrs->{rows} ||= 10;
543 return $self->{pager} ||= Data::Page->new(
544 $self->{count}, $attrs->{rows}, $self->{page});
547 =head2 page($page_num)
549 Returns a new resultset for the specified page.
554 my ($self, $page) = @_;
555 my $attrs = { %{$self->{attrs}} };
556 $attrs->{page} = $page;
557 return (ref $self)->new($self->result_source, $attrs);
560 =head2 new_result(\%vals)
562 Creates a result in the resultset's result class.
567 my ($self, $values) = @_;
568 $self->throw_exception( "new_result needs a hash" )
569 unless (ref $values eq 'HASH');
570 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
571 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
573 my $alias = $self->{attrs}{alias};
574 foreach my $key (keys %{$self->{cond}||{}}) {
575 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
577 my $obj = $self->result_source->result_class->new(\%new);
578 $obj->result_source($self->result_source) if $obj->can('result_source');
582 =head2 create(\%vals)
584 Inserts a record into the resultset and returns the object.
586 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
591 my ($self, $attrs) = @_;
592 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
593 return $self->new_result($attrs)->insert;
596 =head2 find_or_create(\%vals, \%attrs?)
598 $class->find_or_create({ key => $val, ... });
600 Searches for a record matching the search condition; if it doesn't find one,
601 creates one and returns that instead.
603 my $cd = $schema->resultset('CD')->find_or_create({
605 artist => 'Massive Attack',
606 title => 'Mezzanine',
610 Also takes an optional C<key> attribute, to search by a specific key or unique
611 constraint. For example:
613 my $cd = $schema->resultset('CD')->find_or_create(
615 artist => 'Massive Attack',
616 title => 'Mezzanine',
618 { key => 'artist_title' }
621 See also L</find> and L</update_or_create>.
627 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
628 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
629 my $exists = $self->find($hash, $attrs);
630 return defined($exists) ? $exists : $self->create($hash);
633 =head2 update_or_create
635 $class->update_or_create({ key => $val, ... });
637 First, search for an existing row matching one of the unique constraints
638 (including the primary key) on the source of this resultset. If a row is
639 found, update it with the other given column values. Otherwise, create a new
642 Takes an optional C<key> attribute to search on a specific unique constraint.
645 # In your application
646 my $cd = $schema->resultset('CD')->update_or_create(
648 artist => 'Massive Attack',
649 title => 'Mezzanine',
652 { key => 'artist_title' }
655 If no C<key> is specified, it searches on all unique constraints defined on the
656 source, including the primary key.
658 If the C<key> is specified as C<primary>, search only on the primary key.
660 See also L</find> and L</find_or_create>.
664 sub update_or_create {
667 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
668 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
670 my %unique_constraints = $self->result_source->unique_constraints;
671 my @constraint_names = (exists $attrs->{key}
673 : keys %unique_constraints);
676 foreach my $name (@constraint_names) {
677 my @unique_cols = @{ $unique_constraints{$name} };
679 map { $_ => $hash->{$_} }
680 grep { exists $hash->{$_} }
683 push @unique_hashes, \%unique_hash
684 if (scalar keys %unique_hash == scalar @unique_cols);
688 if (@unique_hashes) {
689 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
691 $row->set_columns($hash);
697 $row = $self->create($hash);
703 =head2 throw_exception
705 See Schema's throw_exception
709 sub throw_exception {
711 $self->result_source->schema->throw_exception(@_);
716 The resultset takes various attributes that modify its behavior. Here's an
721 Which column(s) to order the results by. This is currently passed through
722 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
724 =head2 cols (arrayref)
726 Shortcut to request a particular set of columns to be retrieved. Adds
727 C<me.> onto the start of any column without a C<.> in it and sets C<select>
728 from that, then auto-populates C<as> from C<select> as normal.
730 =head2 include_columns (arrayref)
732 Shortcut to include additional columns in the returned results - for example
734 { include_columns => ['foo.name'], join => ['foo'] }
736 would add a 'name' column to the information passed to object inflation
738 =head2 select (arrayref)
740 Indicates which columns should be selected from the storage. You can use
741 column names, or in the case of RDBMS back ends, function or stored procedure
744 $rs = $schema->resultset('Foo')->search(
749 { count => 'column_to_count' },
750 { sum => 'column_to_sum' }
755 When you use function/stored procedure names and do not supply an C<as>
756 attribute, the column names returned are storage-dependent. E.g. MySQL would
757 return a column named C<count(column_to_count)> in the above example.
761 Indicates column names for object inflation. This is used in conjunction with
762 C<select>, usually when C<select> contains one or more function or stored
765 $rs = $schema->resultset('Foo')->search(
770 { count => 'column2' }
772 as => [qw/ column1 column2_count /]
776 my $foo = $rs->first(); # get the first Foo
778 If the object against which the search is performed already has an accessor
779 matching a column name specified in C<as>, the value can be retrieved using
780 the accessor as normal:
782 my $column1 = $foo->column1();
784 If on the other hand an accessor does not exist in the object, you need to
785 use C<get_column> instead:
787 my $column2_count = $foo->get_column('column2_count');
789 You can create your own accessors if required - see
790 L<DBIx::Class::Manual::Cookbook> for details.
794 Contains a list of relationships that should be joined for this query. For
797 # Get CDs by Nine Inch Nails
798 my $rs = $schema->resultset('CD')->search(
799 { 'artist.name' => 'Nine Inch Nails' },
803 Can also contain a hash reference to refer to the other relation's relations.
806 package MyApp::Schema::Track;
807 use base qw/DBIx::Class/;
808 __PACKAGE__->table('track');
809 __PACKAGE__->add_columns(qw/trackid cd position title/);
810 __PACKAGE__->set_primary_key('trackid');
811 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
814 # In your application
815 my $rs = $schema->resultset('Artist')->search(
816 { 'track.title' => 'Teardrop' },
818 join => { cd => 'track' },
819 order_by => 'artist.name',
823 If the same join is supplied twice, it will be aliased to <rel>_2 (and
824 similarly for a third time). For e.g.
826 my $rs = $schema->resultset('Artist')->search(
827 { 'cds.title' => 'Foo',
828 'cds_2.title' => 'Bar' },
829 { join => [ qw/cds cds/ ] });
831 will return a set of all artists that have both a cd with title Foo and a cd
834 If you want to fetch related objects from other tables as well, see C<prefetch>
837 =head2 prefetch arrayref/hashref
839 Contains one or more relationships that should be fetched along with the main
840 query (when they are accessed afterwards they will have already been
841 "prefetched"). This is useful for when you know you will need the related
842 objects, because it saves at least one query:
844 my $rs = $schema->resultset('Tag')->search(
853 The initial search results in SQL like the following:
855 SELECT tag.*, cd.*, artist.* FROM tag
856 JOIN cd ON tag.cd = cd.cdid
857 JOIN artist ON cd.artist = artist.artistid
859 L<DBIx::Class> has no need to go back to the database when we access the
860 C<cd> or C<artist> relationships, which saves us two SQL statements in this
863 Simple prefetches will be joined automatically, so there is no need
864 for a C<join> attribute in the above search. If you're prefetching to
865 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
866 specify the join as well.
868 C<prefetch> can be used with the following relationship types: C<belongs_to>,
869 C<has_one> (or if you're using C<add_relationship>, any relationship declared
870 with an accessor type of 'single' or 'filter').
872 =head2 from (arrayref)
874 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
875 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
878 NOTE: Use this on your own risk. This allows you to shoot off your foot!
879 C<join> will usually do what you need and it is strongly recommended that you
880 avoid using C<from> unless you cannot achieve the desired result using C<join>.
882 In simple terms, C<from> works as follows:
885 { <alias> => <table>, -join-type => 'inner|left|right' }
886 [] # nested JOIN (optional)
887 { <table.column> = <foreign_table.foreign_key> }
893 ON <table.column> = <foreign_table.foreign_key>
895 An easy way to follow the examples below is to remember the following:
897 Anything inside "[]" is a JOIN
898 Anything inside "{}" is a condition for the enclosing JOIN
900 The following examples utilize a "person" table in a family tree application.
901 In order to express parent->child relationships, this table is self-joined:
903 # Person->belongs_to('father' => 'Person');
904 # Person->belongs_to('mother' => 'Person');
906 C<from> can be used to nest joins. Here we return all children with a father,
907 then search against all mothers of those children:
909 $rs = $schema->resultset('Person')->search(
912 alias => 'mother', # alias columns in accordance with "from"
914 { mother => 'person' },
917 { child => 'person' },
919 { father => 'person' },
920 { 'father.person_id' => 'child.father_id' }
923 { 'mother.person_id' => 'child.mother_id' }
930 # SELECT mother.* FROM person mother
934 # ON ( father.person_id = child.father_id )
936 # ON ( mother.person_id = child.mother_id )
938 The type of any join can be controlled manually. To search against only people
939 with a father in the person table, we could explicitly use C<INNER JOIN>:
941 $rs = $schema->resultset('Person')->search(
944 alias => 'child', # alias columns in accordance with "from"
946 { child => 'person' },
948 { father => 'person', -join-type => 'inner' },
949 { 'father.id' => 'child.father_id' }
956 # SELECT child.* FROM person child
957 # INNER JOIN person father ON child.father_id = father.id
961 For a paged resultset, specifies which page to retrieve. Leave unset
962 for an unpaged resultset.
966 For a paged resultset, how many rows per page:
970 Can also be used to simulate an SQL C<LIMIT>.
972 =head2 group_by (arrayref)
974 A arrayref of columns to group by. Can include columns of joined tables.
976 group_by => [qw/ column1 column2 ... /]
980 Set to 1 to group by all columns.
982 For more examples of using these attributes, see
983 L<DBIx::Class::Manual::Cookbook>.