1 package DBIx::Class::ResultSet;
15 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
19 my $rs = $schema->resultset('User')->search(registered => 1);
20 my @rows = $schema->resultset('Foo')->search(bar => 'baz');
24 The resultset is also known as an iterator. It is responsible for handling
25 queries that may return an arbitrary number of rows, e.g. via L</search>
26 or a C<has_many> relationship.
28 In the examples below, the following table classes are used:
30 package MyApp::Schema::Artist;
31 use base qw/DBIx::Class/;
32 __PACKAGE__->table('artist');
33 __PACKAGE__->add_columns(qw/artistid name/);
34 __PACKAGE__->set_primary_key('artistid');
35 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
38 package MyApp::Schema::CD;
39 use base qw/DBIx::Class/;
40 __PACKAGE__->table('artist');
41 __PACKAGE__->add_columns(qw/cdid artist title year/);
42 __PACKAGE__->set_primary_key('cdid');
43 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
48 =head2 new($source, \%$attrs)
50 The resultset constructor. Takes a source object (usually a
51 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see L</ATRRIBUTES>
52 below). Does not perform any queries -- these are executed as needed by the
55 Generally you won't need to construct a resultset manually. You'll
56 automatically get one from e.g. a L</search> called in scalar context:
58 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
64 return $class->new_result(@_) if ref $class;
65 my ($source, $attrs) = @_;
66 #use Data::Dumper; warn Dumper($attrs);
67 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
69 my $alias = ($attrs->{alias} ||= 'me');
70 if ($attrs->{cols} || !$attrs->{select}) {
71 delete $attrs->{as} if $attrs->{cols};
72 my @cols = ($attrs->{cols}
73 ? @{delete $attrs->{cols}}
75 $attrs->{select} = [ map { m/\./ ? $_ : "${alias}.$_" } @cols ];
77 $attrs->{as} ||= [ map { m/^$alias\.(.*)$/ ? $1 : $_ } @{$attrs->{select}} ];
78 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
79 $attrs->{from} ||= [ { $alias => $source->from } ];
80 if (my $join = delete $attrs->{join}) {
81 foreach my $j (ref $join eq 'ARRAY'
82 ? (@{$join}) : ($join)) {
83 if (ref $j eq 'HASH') {
84 $seen{$_} = 1 foreach keys %$j;
89 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}));
91 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
93 if (my $prefetch = delete $attrs->{prefetch}) {
94 foreach my $p (ref $prefetch eq 'ARRAY'
95 ? (@{$prefetch}) : ($prefetch)) {
96 if( ref $p eq 'HASH' ) {
97 foreach my $key (keys %$p) {
98 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
103 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
107 push @cols, $source->resolve_prefetch($p, $attrs->{alias});
109 push(@{$attrs->{select}}, @cols);
110 push(@{$attrs->{as}}, @cols);
114 if ($attrs->{page}) {
115 $attrs->{rows} ||= 10;
116 $attrs->{offset} ||= 0;
117 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
121 cond => $attrs->{where},
122 from => $attrs->{from},
124 page => delete $attrs->{page},
127 bless ($new, $class);
133 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
134 my $new_rs = $rs->search({ foo => 3 });
136 If you need to pass in additional attributes but no additional condition,
137 call it as C<search({}, \%attrs);>.
139 # "SELECT foo, bar FROM $class_table"
140 my @all = $class->search({}, { cols => [qw/foo bar/] });
147 #use Data::Dumper;warn Dumper(@_);
149 my $attrs = { %{$self->{attrs}} };
150 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
151 $attrs = { %$attrs, %{ pop(@_) } };
154 my $where = (@_ ? ((@_ == 1 || ref $_[0] eq "HASH") ? shift : {@_}) : undef());
155 if (defined $where) {
156 $where = (defined $attrs->{where}
158 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
159 $where, $attrs->{where} ] }
161 $attrs->{where} = $where;
164 my $rs = (ref $self)->new($self->{source}, $attrs);
166 return (wantarray ? $rs->all : $rs);
169 =head2 search_literal
171 my @obj = $rs->search_literal($literal_where_cond, @bind);
172 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
174 Pass a literal chunk of SQL to be added to the conditional part of the
180 my ($self, $cond, @vals) = @_;
181 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
182 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
183 return $self->search(\$cond, $attrs);
186 =head2 find(@colvalues), find(\%cols, \%attrs?)
188 Finds a row based on its primary key or unique constraint. For example:
190 my $cd = $schema->resultset('CD')->find(5);
192 Also takes an optional C<key> attribute, to search by a specific key or unique
193 constraint. For example:
195 my $cd = $schema->resultset('CD')->find_or_create(
197 artist => 'Massive Attack',
198 title => 'Mezzanine',
200 { key => 'artist_title' }
203 See also L</find_or_create> and L</update_or_create>.
208 my ($self, @vals) = @_;
209 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
211 my @cols = $self->{source}->primary_columns;
212 if (exists $attrs->{key}) {
213 my %uniq = $self->{source}->unique_constraints;
214 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
215 unless exists $uniq{$attrs->{key}};
216 @cols = @{ $uniq{$attrs->{key}} };
218 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
219 $self->{source}->result_class->throw( "Can't find unless a primary key or unique constraint is defined" )
223 if (ref $vals[0] eq 'HASH') {
225 } elsif (@cols == @vals) {
227 @{$query}{@cols} = @vals;
231 #warn Dumper($query);
232 # Useless -> disabled
233 #$self->{source}->result_class->throw( "Can't find unless all primary keys are specified" )
234 # unless (keys %$query >= @pk); # If we check 'em we run afoul of uc/lc
235 # column names etc. Not sure what to do yet
236 return $self->search($query)->next;
239 =head2 search_related
241 $rs->search_related('relname', $cond?, $attrs?);
243 Search the specified relationship. Optionally specify a condition for matching
249 my ($self, $rel, @rest) = @_;
250 my $rel_obj = $self->{source}->relationship_info($rel);
251 $self->{source}->result_class->throw(
252 "No such relationship ${rel} in search_related")
254 my $rs = $self->search(undef, { join => $rel });
255 return $self->{source}->schema->resultset($rel_obj->{class}
266 Returns a storage-driven cursor to the given resultset.
272 my ($source, $attrs) = @{$self}{qw/source attrs/};
273 $attrs = { %$attrs };
274 return $self->{cursor}
275 ||= $source->storage->select($self->{from}, $attrs->{select},
276 $attrs->{where},$attrs);
281 Perform a search, but use C<LIKE> instead of equality as the condition. Note
282 that this is simply a convenience method; you most likely want to use
283 L</search> with specific operators.
285 For more information, see L<DBIx::Class::Manual::Cookbook>.
292 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
295 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
296 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
297 return $class->search($query, { %$attrs });
300 =head2 slice($first, $last)
302 Returns a subset of elements from the resultset.
307 my ($self, $min, $max) = @_;
308 my $attrs = { %{ $self->{attrs} || {} } };
309 $attrs->{offset} ||= 0;
310 $attrs->{offset} += $min;
311 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
312 my $slice = (ref $self)->new($self->{source}, $attrs);
313 return (wantarray ? $slice->all : $slice);
318 Returns the next element in the resultset (C<undef> is there is none).
320 Can be used to efficiently iterate over records in the resultset:
322 my $rs = $schema->resultset('CD')->search({});
323 while (my $cd = $rs->next) {
331 my @row = $self->cursor->next;
332 # warn Dumper(\@row); use Data::Dumper;
333 return unless (@row);
334 return $self->_construct_object(@row);
337 sub _construct_object {
338 my ($self, @row) = @_;
339 my @as = @{ $self->{attrs}{as} };
340 #warn "@cols -> @row";
341 my $info = [ {}, {} ];
342 foreach my $as (@as) {
344 my @parts = split(/\./, $as);
345 my $col = pop(@parts);
346 foreach my $p (@parts) {
347 $target = $target->[1]->{$p} ||= [];
349 $target->[0]->{$col} = shift @row;
351 #use Data::Dumper; warn Dumper(\@as, $info);
352 my $new = $self->{source}->result_class->inflate_result(
353 $self->{source}, @$info);
354 $new = $self->{attrs}{record_filter}->($new)
355 if exists $self->{attrs}{record_filter};
361 Performs an SQL C<COUNT> with the same query as the resultset was built
362 with to find the number of elements. If passed arguments, does a search
363 on the resultset and counts the results of that.
369 return $self->search(@_)->count if @_ && defined $_[0];
370 croak "Unable to ->count with a GROUP BY" if defined $self->{attrs}{group_by};
371 unless (defined $self->{count}) {
372 my $attrs = { %{ $self->{attrs} },
373 select => { 'count' => '*' },
375 # offset, order by and page are not needed to count. record_filter is cdbi
376 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
378 ($self->{count}) = (ref $self)->new($self->{source}, $attrs)->cursor->next;
380 return 0 unless $self->{count};
381 my $count = $self->{count};
382 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
383 $count = $self->{attrs}{rows} if
384 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
390 Calls L</search_literal> with the passed arguments, then L</count>.
394 sub count_literal { shift->search_literal(@_)->count; }
398 Returns all elements in the resultset. Called implictly if the resultset
399 is returned in list context.
405 return map { $self->_construct_object(@$_); }
411 Resets the resultset's cursor, so you can iterate through the elements again.
417 $self->cursor->reset;
423 Resets the resultset and returns the first element.
428 return $_[0]->reset->next;
431 =head2 update(\%values)
433 Sets the specified columns in the resultset to the supplied values.
438 my ($self, $values) = @_;
439 croak "Values for update must be a hash" unless ref $values eq 'HASH';
440 return $self->{source}->storage->update(
441 $self->{source}->from, $values, $self->{cond});
444 =head2 update_all(\%values)
446 Fetches all objects and updates them one at a time. Note that C<update_all>
447 will run cascade triggers while L</update> will not.
452 my ($self, $values) = @_;
453 croak "Values for update must be a hash" unless ref $values eq 'HASH';
454 foreach my $obj ($self->all) {
455 $obj->set_columns($values)->update;
462 Deletes the contents of the resultset from its result source.
468 $self->{source}->storage->delete($self->{source}->from, $self->{cond});
474 Fetches all objects and deletes them one at a time. Note that C<delete_all>
475 will run cascade triggers while L</delete> will not.
481 $_->delete for $self->all;
487 Returns a L<Data::Page> object for the current resultset. Only makes
488 sense for queries with a C<page> attribute.
494 my $attrs = $self->{attrs};
495 croak "Can't create pager for non-paged rs" unless $self->{page};
496 $attrs->{rows} ||= 10;
498 return $self->{pager} ||= Data::Page->new(
499 $self->{count}, $attrs->{rows}, $self->{page});
502 =head2 page($page_num)
504 Returns a new resultset for the specified page.
509 my ($self, $page) = @_;
510 my $attrs = { %{$self->{attrs}} };
511 $attrs->{page} = $page;
512 return (ref $self)->new($self->{source}, $attrs);
515 =head2 new_result(\%vals)
517 Creates a result in the resultset's result class.
522 my ($self, $values) = @_;
523 $self->{source}->result_class->throw( "new_result needs a hash" )
524 unless (ref $values eq 'HASH');
525 $self->{source}->result_class->throw( "Can't abstract implicit construct, condition not a hash" )
526 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
528 my $alias = $self->{attrs}{alias};
529 foreach my $key (keys %{$self->{cond}||{}}) {
530 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
532 my $obj = $self->{source}->result_class->new(\%new);
533 $obj->result_source($self->{source}) if $obj->can('result_source');
537 =head2 create(\%vals)
539 Inserts a record into the resultset and returns the object.
541 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
546 my ($self, $attrs) = @_;
547 $self->{source}->result_class->throw( "create needs a hashref" ) unless ref $attrs eq 'HASH';
548 return $self->new_result($attrs)->insert;
551 =head2 find_or_create(\%vals, \%attrs?)
553 $class->find_or_create({ key => $val, ... });
555 Searches for a record matching the search condition; if it doesn't find one,
556 creates one and returns that instead.
558 my $cd = $schema->resultset('CD')->find_or_create({
560 artist => 'Massive Attack',
561 title => 'Mezzanine',
565 Also takes an optional C<key> attribute, to search by a specific key or unique
566 constraint. For example:
568 my $cd = $schema->resultset('CD')->find_or_create(
570 artist => 'Massive Attack',
571 title => 'Mezzanine',
573 { key => 'artist_title' }
576 See also L</find> and L</update_or_create>.
582 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
583 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
584 my $exists = $self->find($hash, $attrs);
585 return defined($exists) ? $exists : $self->create($hash);
588 =head2 update_or_create
590 $class->update_or_create({ key => $val, ... });
592 First, search for an existing row matching one of the unique constraints
593 (including the primary key) on the source of this resultset. If a row is
594 found, update it with the other given column values. Otherwise, create a new
597 Takes an optional C<key> attribute to search on a specific unique constraint.
600 # In your application
601 my $cd = $schema->resultset('CD')->update_or_create(
603 artist => 'Massive Attack',
604 title => 'Mezzanine',
607 { key => 'artist_title' }
610 If no C<key> is specified, it searches on all unique constraints defined on the
611 source, including the primary key.
613 If the C<key> is specified as C<primary>, search only on the primary key.
615 See also L</find> and L</find_or_create>.
619 sub update_or_create {
622 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
623 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
625 my %unique_constraints = $self->{source}->unique_constraints;
626 my @constraint_names = (exists $attrs->{key}
628 : keys %unique_constraints);
631 foreach my $name (@constraint_names) {
632 my @unique_cols = @{ $unique_constraints{$name} };
634 map { $_ => $hash->{$_} }
635 grep { exists $hash->{$_} }
638 push @unique_hashes, \%unique_hash
639 if (scalar keys %unique_hash == scalar @unique_cols);
643 if (@unique_hashes) {
644 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
646 $row->set_columns($hash);
652 $row = $self->create($hash);
660 The resultset takes various attributes that modify its behavior. Here's an
665 Which column(s) to order the results by. This is currently passed through
666 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
668 =head2 cols (arrayref)
670 Shortcut to request a particular set of columns to be retrieved. Adds
671 C<me.> onto the start of any column without a C<.> in it and sets C<select>
672 from that, then auto-populates C<as> from C<select> as normal.
674 =head2 select (arrayref)
676 Indicates which columns should be selected from the storage. You can use
677 column names, or in the case of RDBMS back ends, function or stored procedure
680 $rs = $schema->resultset('Foo')->search(
685 { count => 'column_to_count' },
686 { sum => 'column_to_sum' }
691 When you use function/stored procedure names and do not supply an C<as>
692 attribute, the column names returned are storage-dependent. E.g. MySQL would
693 return a column named C<count(column_to_count)> in the above example.
697 Indicates column names for object inflation. This is used in conjunction with
698 C<select>, usually when C<select> contains one or more function or stored
701 $rs = $schema->resultset('Foo')->search(
706 { count => 'column2' }
708 as => [qw/ column1 column2_count /]
712 my $foo = $rs->first(); # get the first Foo
714 If the object against which the search is performed already has an accessor
715 matching a column name specified in C<as>, the value can be retrieved using
716 the accessor as normal:
718 my $column1 = $foo->column1();
720 If on the other hand an accessor does not exist in the object, you need to
721 use C<get_column> instead:
723 my $column2_count = $foo->get_column('column2_count');
725 You can create your own accessors if required - see
726 L<DBIx::Class::Manual::Cookbook> for details.
730 Contains a list of relationships that should be joined for this query. For
733 # Get CDs by Nine Inch Nails
734 my $rs = $schema->resultset('CD')->search(
735 { 'artist.name' => 'Nine Inch Nails' },
739 Can also contain a hash reference to refer to the other relation's relations.
742 package MyApp::Schema::Track;
743 use base qw/DBIx::Class/;
744 __PACKAGE__->table('track');
745 __PACKAGE__->add_columns(qw/trackid cd position title/);
746 __PACKAGE__->set_primary_key('trackid');
747 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
750 # In your application
751 my $rs = $schema->resultset('Artist')->search(
752 { 'track.title' => 'Teardrop' },
754 join => { cd => 'track' },
755 order_by => 'artist.name',
759 If you want to fetch columns from related tables as well, see C<prefetch>
762 =head2 prefetch arrayref/hashref
764 Contains one or more relationships that should be fetched along with the main
765 query (when they are accessed afterwards they will have already been
766 "prefetched"). This is useful for when you know you will need the related
767 objects, because it saves at least one query:
769 my $rs = $schema->resultset('Tag')->search(
778 The initial search results in SQL like the following:
780 SELECT tag.*, cd.*, artist.* FROM tag
781 JOIN cd ON tag.cd = cd.cdid
782 JOIN artist ON cd.artist = artist.artistid
784 L<DBIx::Class> has no need to go back to the database when we access the
785 C<cd> or C<artist> relationships, which saves us two SQL statements in this
788 Any prefetched relationship will be joined automatically, so there is no need
789 for a C<join> attribute in the above search.
791 C<prefetch> can be used with the following relationship types: C<belongs_to>,
794 =head2 from (arrayref)
796 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
797 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
800 NOTE: Use this on your own risk. This allows you to shoot off your foot!
801 C<join> will usually do what you need and it is strongly recommended that you
802 avoid using C<from> unless you cannot achieve the desired result using C<join>.
804 In simple terms, C<from> works as follows:
807 { <alias> => <table>, -join-type => 'inner|left|right' }
808 [] # nested JOIN (optional)
809 { <table.column> = <foreign_table.foreign_key> }
815 ON <table.column> = <foreign_table.foreign_key>
817 An easy way to follow the examples below is to remember the following:
819 Anything inside "[]" is a JOIN
820 Anything inside "{}" is a condition for the enclosing JOIN
822 The following examples utilize a "person" table in a family tree application.
823 In order to express parent->child relationships, this table is self-joined:
825 # Person->belongs_to('father' => 'Person');
826 # Person->belongs_to('mother' => 'Person');
828 C<from> can be used to nest joins. Here we return all children with a father,
829 then search against all mothers of those children:
831 $rs = $schema->resultset('Person')->search(
834 alias => 'mother', # alias columns in accordance with "from"
836 { mother => 'person' },
839 { child => 'person' },
841 { father => 'person' },
842 { 'father.person_id' => 'child.father_id' }
845 { 'mother.person_id' => 'child.mother_id' }
852 # SELECT mother.* FROM person mother
856 # ON ( father.person_id = child.father_id )
858 # ON ( mother.person_id = child.mother_id )
860 The type of any join can be controlled manually. To search against only people
861 with a father in the person table, we could explicitly use C<INNER JOIN>:
863 $rs = $schema->resultset('Person')->search(
866 alias => 'child', # alias columns in accordance with "from"
868 { child => 'person' },
870 { father => 'person', -join-type => 'inner' },
871 { 'father.id' => 'child.father_id' }
878 # SELECT child.* FROM person child
879 # INNER JOIN person father ON child.father_id = father.id
883 For a paged resultset, specifies which page to retrieve. Leave unset
884 for an unpaged resultset.
888 For a paged resultset, how many rows per page:
892 Can also be used to simulate an SQL C<LIMIT>.
894 =head2 group_by (arrayref)
896 A arrayref of columns to group by. Can include columns of joined tables. Note
897 note that L</count> doesn't work on grouped resultsets.
899 group_by => [qw/ column1 column2 ... /]
903 Set to 1 to group by all columns.
905 For more examples of using these attributes, see
906 L<DBIx::Class::Manual::Cookbook>.