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};
92 foreach my $pre (@{delete $attrs->{prefetch} || []}) {
93 push(@{$attrs->{from}}, $source->resolve_join($pre, $attrs->{alias}))
97 $source->related_source($pre)->columns;
98 push(@{$attrs->{select}}, @pre);
99 push(@{$attrs->{as}}, @pre);
101 if ($attrs->{page}) {
102 $attrs->{rows} ||= 10;
103 $attrs->{offset} ||= 0;
104 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
108 cond => $attrs->{where},
109 from => $attrs->{from},
111 page => delete $attrs->{page},
114 bless ($new, $class);
120 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
121 my $new_rs = $rs->search({ foo => 3 });
123 If you need to pass in additional attributes but no additional condition,
124 call it as C<search({}, \%attrs);>.
126 # "SELECT foo, bar FROM $class_table"
127 my @all = $class->search({}, { cols => [qw/foo bar/] });
134 #use Data::Dumper;warn Dumper(@_);
136 my $attrs = { %{$self->{attrs}} };
137 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
138 $attrs = { %$attrs, %{ pop(@_) } };
141 my $where = (@_ ? ((@_ == 1 || ref $_[0] eq "HASH") ? shift : {@_}) : undef());
142 if (defined $where) {
143 $where = (defined $attrs->{where}
145 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
146 $where, $attrs->{where} ] }
148 $attrs->{where} = $where;
151 my $rs = (ref $self)->new($self->{source}, $attrs);
153 return (wantarray ? $rs->all : $rs);
156 =head2 search_literal
158 my @obj = $rs->search_literal($literal_where_cond, @bind);
159 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
161 Pass a literal chunk of SQL to be added to the conditional part of the
167 my ($self, $cond, @vals) = @_;
168 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
169 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
170 return $self->search(\$cond, $attrs);
173 =head2 find(@colvalues), find(\%cols, \%attrs?)
175 Finds a row based on its primary key or unique constraint. For example:
177 my $cd = $schema->resultset('CD')->find(5);
179 Also takes an optional C<key> attribute, to search by a specific key or unique
180 constraint. For example:
182 my $cd = $schema->resultset('CD')->find_or_create(
184 artist => 'Massive Attack',
185 title => 'Mezzanine',
187 { key => 'artist_title' }
190 See also L</find_or_create> and L</update_or_create>.
195 my ($self, @vals) = @_;
196 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
198 my @cols = $self->{source}->primary_columns;
199 if (exists $attrs->{key}) {
200 my %uniq = $self->{source}->unique_constraints;
201 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
202 unless exists $uniq{$attrs->{key}};
203 @cols = @{ $uniq{$attrs->{key}} };
205 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
206 $self->{source}->result_class->throw( "Can't find unless a primary key or unique constraint is defined" )
210 if (ref $vals[0] eq 'HASH') {
212 } elsif (@cols == @vals) {
214 @{$query}{@cols} = @vals;
218 #warn Dumper($query);
219 # Useless -> disabled
220 #$self->{source}->result_class->throw( "Can't find unless all primary keys are specified" )
221 # unless (keys %$query >= @pk); # If we check 'em we run afoul of uc/lc
222 # column names etc. Not sure what to do yet
223 return $self->search($query)->next;
226 =head2 search_related
228 $rs->search_related('relname', $cond?, $attrs?);
230 Search the specified relationship. Optionally specify a condition for matching
236 my ($self, $rel, @rest) = @_;
237 my $rel_obj = $self->{source}->relationship_info($rel);
238 $self->{source}->result_class->throw(
239 "No such relationship ${rel} in search_related")
241 my $rs = $self->search(undef, { join => $rel });
242 return $self->{source}->schema->resultset($rel_obj->{class}
253 Returns a storage-driven cursor to the given resultset.
259 my ($source, $attrs) = @{$self}{qw/source attrs/};
260 $attrs = { %$attrs };
261 return $self->{cursor}
262 ||= $source->storage->select($self->{from}, $attrs->{select},
263 $attrs->{where},$attrs);
268 Perform a search, but use C<LIKE> instead of equality as the condition. Note
269 that this is simply a convenience method; you most likely want to use
270 L</search> with specific operators.
272 For more information, see L<DBIx::Class::Manual::Cookbook>.
279 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
282 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
283 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
284 return $class->search($query, { %$attrs });
287 =head2 slice($first, $last)
289 Returns a subset of elements from the resultset.
294 my ($self, $min, $max) = @_;
295 my $attrs = { %{ $self->{attrs} || {} } };
296 $attrs->{offset} ||= 0;
297 $attrs->{offset} += $min;
298 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
299 my $slice = (ref $self)->new($self->{source}, $attrs);
300 return (wantarray ? $slice->all : $slice);
305 Returns the next element in the resultset (C<undef> is there is none).
307 Can be used to efficiently iterate over records in the resultset:
309 my $rs = $schema->resultset('CD')->search({});
310 while (my $cd = $rs->next) {
318 my @row = $self->cursor->next;
319 # warn Dumper(\@row); use Data::Dumper;
320 return unless (@row);
321 return $self->_construct_object(@row);
324 sub _construct_object {
325 my ($self, @row) = @_;
326 my @cols = @{ $self->{attrs}{as} };
327 #warn "@cols -> @row";
329 foreach my $col (@cols) {
330 if ($col =~ /([^\.]+)\.([^\.]+)/) {
331 $pre{$1}[0]{$2} = shift @row;
333 $me{$col} = shift @row;
336 my $new = $self->{source}->result_class->inflate_result(
337 $self->{source}, \%me, \%pre);
338 $new = $self->{attrs}{record_filter}->($new)
339 if exists $self->{attrs}{record_filter};
345 Performs an SQL C<COUNT> with the same query as the resultset was built
346 with to find the number of elements. If passed arguments, does a search
347 on the resultset and counts the results of that.
353 return $self->search(@_)->count if @_ && defined $_[0];
354 croak "Unable to ->count with a GROUP BY" if defined $self->{attrs}{group_by};
355 unless (defined $self->{count}) {
356 my $attrs = { %{ $self->{attrs} },
357 select => { 'count' => '*' },
359 # offset, order by and page are not needed to count. record_filter is cdbi
360 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
362 ($self->{count}) = (ref $self)->new($self->{source}, $attrs)->cursor->next;
364 return 0 unless $self->{count};
365 my $count = $self->{count};
366 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
367 $count = $self->{attrs}{rows} if
368 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
374 Calls L</search_literal> with the passed arguments, then L</count>.
378 sub count_literal { shift->search_literal(@_)->count; }
382 Returns all elements in the resultset. Called implictly if the resultset
383 is returned in list context.
389 return map { $self->_construct_object(@$_); }
395 Resets the resultset's cursor, so you can iterate through the elements again.
401 $self->cursor->reset;
407 Resets the resultset and returns the first element.
412 return $_[0]->reset->next;
415 =head2 update(\%values)
417 Sets the specified columns in the resultset to the supplied values.
422 my ($self, $values) = @_;
423 croak "Values for update must be a hash" unless ref $values eq 'HASH';
424 return $self->{source}->storage->update(
425 $self->{source}->from, $values, $self->{cond});
428 =head2 update_all(\%values)
430 Fetches all objects and updates them one at a time. Note that C<update_all>
431 will run cascade triggers while L</update> will not.
436 my ($self, $values) = @_;
437 croak "Values for update must be a hash" unless ref $values eq 'HASH';
438 foreach my $obj ($self->all) {
439 $obj->set_columns($values)->update;
446 Deletes the contents of the resultset from its result source.
452 $self->{source}->storage->delete($self->{source}->from, $self->{cond});
458 Fetches all objects and deletes them one at a time. Note that C<delete_all>
459 will run cascade triggers while L</delete> will not.
465 $_->delete for $self->all;
471 Returns a L<Data::Page> object for the current resultset. Only makes
472 sense for queries with a C<page> attribute.
478 my $attrs = $self->{attrs};
479 croak "Can't create pager for non-paged rs" unless $self->{page};
480 $attrs->{rows} ||= 10;
482 return $self->{pager} ||= Data::Page->new(
483 $self->{count}, $attrs->{rows}, $self->{page});
486 =head2 page($page_num)
488 Returns a new resultset for the specified page.
493 my ($self, $page) = @_;
494 my $attrs = { %{$self->{attrs}} };
495 $attrs->{page} = $page;
496 return (ref $self)->new($self->{source}, $attrs);
499 =head2 new_result(\%vals)
501 Creates a result in the resultset's result class.
506 my ($self, $values) = @_;
507 $self->{source}->result_class->throw( "new_result needs a hash" )
508 unless (ref $values eq 'HASH');
509 $self->{source}->result_class->throw( "Can't abstract implicit construct, condition not a hash" )
510 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
512 my $alias = $self->{attrs}{alias};
513 foreach my $key (keys %{$self->{cond}||{}}) {
514 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
516 my $obj = $self->{source}->result_class->new(\%new);
517 $obj->result_source($self->{source}) if $obj->can('result_source');
521 =head2 create(\%vals)
523 Inserts a record into the resultset and returns the object.
525 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
530 my ($self, $attrs) = @_;
531 $self->{source}->result_class->throw( "create needs a hashref" ) unless ref $attrs eq 'HASH';
532 return $self->new_result($attrs)->insert;
535 =head2 find_or_create(\%vals, \%attrs?)
537 $class->find_or_create({ key => $val, ... });
539 Searches for a record matching the search condition; if it doesn't find one,
540 creates one and returns that instead.
542 my $cd = $schema->resultset('CD')->find_or_create({
544 artist => 'Massive Attack',
545 title => 'Mezzanine',
549 Also takes an optional C<key> attribute, to search by a specific key or unique
550 constraint. For example:
552 my $cd = $schema->resultset('CD')->find_or_create(
554 artist => 'Massive Attack',
555 title => 'Mezzanine',
557 { key => 'artist_title' }
560 See also L</find> and L</update_or_create>.
566 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
567 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
568 my $exists = $self->find($hash, $attrs);
569 return defined($exists) ? $exists : $self->create($hash);
572 =head2 update_or_create
574 $class->update_or_create({ key => $val, ... });
576 First, search for an existing row matching one of the unique constraints
577 (including the primary key) on the source of this resultset. If a row is
578 found, update it with the other given column values. Otherwise, create a new
581 Takes an optional C<key> attribute to search on a specific unique constraint.
584 # In your application
585 my $cd = $schema->resultset('CD')->update_or_create(
587 artist => 'Massive Attack',
588 title => 'Mezzanine',
591 { key => 'artist_title' }
594 If no C<key> is specified, it searches on all unique constraints defined on the
595 source, including the primary key.
597 If the C<key> is specified as C<primary>, search only on the primary key.
599 See also L</find> and L</find_or_create>.
603 sub update_or_create {
606 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
607 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
609 my %unique_constraints = $self->{source}->unique_constraints;
610 my @constraint_names = (exists $attrs->{key}
612 : keys %unique_constraints);
615 foreach my $name (@constraint_names) {
616 my @unique_cols = @{ $unique_constraints{$name} };
618 map { $_ => $hash->{$_} }
619 grep { exists $hash->{$_} }
622 push @unique_hashes, \%unique_hash
623 if (scalar keys %unique_hash == scalar @unique_cols);
627 if (@unique_hashes) {
628 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
630 $row->set_columns($hash);
636 $row = $self->create($hash);
644 The resultset takes various attributes that modify its behavior. Here's an
649 Which column(s) to order the results by. This is currently passed through
650 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
652 =head2 cols (arrayref)
654 Shortcut to request a particular set of columns to be retrieved. Adds
655 C<me.> onto the start of any column without a C<.> in it and sets C<select>
656 from that, then auto-populates C<as> from C<select> as normal.
658 =head2 select (arrayref)
660 Indicates which columns should be selected from the storage. You can use
661 column names, or in the case of RDBMS back ends, function or stored procedure
664 $rs = $schema->resultset('Foo')->search(
669 { count => 'column_to_count' },
670 { sum => 'column_to_sum' }
675 When you use function/stored procedure names and do not supply an C<as>
676 attribute, the column names returned are storage-dependent. E.g. MySQL would
677 return a column named C<count(column_to_count)> in the above example.
681 Indicates column names for object inflation. This is used in conjunction with
682 C<select>, usually when C<select> contains one or more function or stored
685 $rs = $schema->resultset('Foo')->search(
690 { count => 'column2' }
692 as => [qw/ column1 column2_count /]
696 my $foo = $rs->first(); # get the first Foo
698 If the object against which the search is performed already has an accessor
699 matching a column name specified in C<as>, the value can be retrieved using
700 the accessor as normal:
702 my $column1 = $foo->column1();
704 If on the other hand an accessor does not exist in the object, you need to
705 use C<get_column> instead:
707 my $column2_count = $foo->get_column('column2_count');
709 You can create your own accessors if required - see
710 L<DBIx::Class::Manual::Cookbook> for details.
714 Contains a list of relationships that should be joined for this query. For
717 # Get CDs by Nine Inch Nails
718 my $rs = $schema->resultset('CD')->search(
719 { 'artist.name' => 'Nine Inch Nails' },
723 Can also contain a hash reference to refer to the other relation's relations.
726 package MyApp::Schema::Track;
727 use base qw/DBIx::Class/;
728 __PACKAGE__->table('track');
729 __PACKAGE__->add_columns(qw/trackid cd position title/);
730 __PACKAGE__->set_primary_key('trackid');
731 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
734 # In your application
735 my $rs = $schema->resultset('Artist')->search(
736 { 'track.title' => 'Teardrop' },
738 join => { cd => 'track' },
739 order_by => 'artist.name',
743 If you want to fetch the columns from the related table as well, see
748 Contains a list of relationships that should be fetched along with the main
749 query (when they are accessed afterwards they will have already been
750 "prefetched"). This is useful for when you know you will need the related
751 objects, because it saves a query. Currently limited to prefetching
752 one relationship deep, so unlike C<join>, prefetch must be an arrayref.
754 =head2 from (arrayref)
756 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
757 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
760 NOTE: Use this on your own risk. This allows you to shoot off your foot!
761 C<join> will usually do what you need and it is strongly recommended that you
762 avoid using C<from> unless you cannot achieve the desired result using C<join>.
764 In simple terms, C<from> works as follows:
767 { <alias> => <table>, -join-type => 'inner|left|right' }
768 [] # nested JOIN (optional)
769 { <table.column> = <foreign_table.foreign_key> }
775 ON <table.column> = <foreign_table.foreign_key>
777 An easy way to follow the examples below is to remember the following:
779 Anything inside "[]" is a JOIN
780 Anything inside "{}" is a condition for the enclosing JOIN
782 The following examples utilize a "person" table in a family tree application.
783 In order to express parent->child relationships, this table is self-joined:
785 # Person->belongs_to('father' => 'Person');
786 # Person->belongs_to('mother' => 'Person');
788 C<from> can be used to nest joins. Here we return all children with a father,
789 then search against all mothers of those children:
791 $rs = $schema->resultset('Person')->search(
794 alias => 'mother', # alias columns in accordance with "from"
796 { mother => 'person' },
799 { child => 'person' },
801 { father => 'person' },
802 { 'father.person_id' => 'child.father_id' }
805 { 'mother.person_id' => 'child.mother_id' }
812 # SELECT mother.* FROM person mother
816 # ON ( father.person_id = child.father_id )
818 # ON ( mother.person_id = child.mother_id )
820 The type of any join can be controlled manually. To search against only people
821 with a father in the person table, we could explicitly use C<INNER JOIN>:
823 $rs = $schema->resultset('Person')->search(
826 alias => 'child', # alias columns in accordance with "from"
828 { child => 'person' },
830 { father => 'person', -join-type => 'inner' },
831 { 'father.id' => 'child.father_id' }
838 # SELECT child.* FROM person child
839 # INNER JOIN person father ON child.father_id = father.id
843 For a paged resultset, specifies which page to retrieve. Leave unset
844 for an unpaged resultset.
848 For a paged resultset, how many rows per page:
852 Can also be used to simulate an SQL C<LIMIT>.
854 =head2 group_by (arrayref)
856 A arrayref of columns to group by. Can include columns of joined tables. Note
857 note that L</count> doesn't work on grouped resultsets.
859 group_by => [qw/ column1 column2 ... /]
863 Set to 1 to group by all columns.
865 For more examples of using these attributes, see
866 L<DBIx::Class::Manual::Cookbook>.