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.
375 return $self->search(@_)->count if @_ && defined $_[0];
376 $self->throw_exception(
377 "Unable to ->count with a GROUP BY"
378 ) if defined $self->{attrs}{group_by};
379 unless (defined $self->{count}) {
380 my $attrs = { %{ $self->{attrs} },
381 select => { 'count' => '*' },
383 # offset, order by and page are not needed to count. record_filter is cdbi
384 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
386 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
388 return 0 unless $self->{count};
389 my $count = $self->{count};
390 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
391 $count = $self->{attrs}{rows} if
392 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
398 Calls L</search_literal> with the passed arguments, then L</count>.
402 sub count_literal { shift->search_literal(@_)->count; }
406 Returns all elements in the resultset. Called implictly if the resultset
407 is returned in list context.
413 return map { $self->_construct_object(@$_); }
419 Resets the resultset's cursor, so you can iterate through the elements again.
425 $self->cursor->reset;
431 Resets the resultset and returns the first element.
436 return $_[0]->reset->next;
439 =head2 update(\%values)
441 Sets the specified columns in the resultset to the supplied values.
446 my ($self, $values) = @_;
447 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
448 return $self->result_source->storage->update(
449 $self->result_source->from, $values, $self->{cond});
452 =head2 update_all(\%values)
454 Fetches all objects and updates them one at a time. Note that C<update_all>
455 will run cascade triggers while L</update> will not.
460 my ($self, $values) = @_;
461 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
462 foreach my $obj ($self->all) {
463 $obj->set_columns($values)->update;
470 Deletes the contents of the resultset from its result source.
476 $self->result_source->storage->delete($self->result_source->from, $self->{cond});
482 Fetches all objects and deletes them one at a time. Note that C<delete_all>
483 will run cascade triggers while L</delete> will not.
489 $_->delete for $self->all;
495 Returns a L<Data::Page> object for the current resultset. Only makes
496 sense for queries with a C<page> attribute.
502 my $attrs = $self->{attrs};
503 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
504 $attrs->{rows} ||= 10;
506 return $self->{pager} ||= Data::Page->new(
507 $self->{count}, $attrs->{rows}, $self->{page});
510 =head2 page($page_num)
512 Returns a new resultset for the specified page.
517 my ($self, $page) = @_;
518 my $attrs = { %{$self->{attrs}} };
519 $attrs->{page} = $page;
520 return (ref $self)->new($self->result_source, $attrs);
523 =head2 new_result(\%vals)
525 Creates a result in the resultset's result class.
530 my ($self, $values) = @_;
531 $self->throw_exception( "new_result needs a hash" )
532 unless (ref $values eq 'HASH');
533 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
534 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
536 my $alias = $self->{attrs}{alias};
537 foreach my $key (keys %{$self->{cond}||{}}) {
538 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
540 my $obj = $self->result_source->result_class->new(\%new);
541 $obj->result_source($self->result_source) if $obj->can('result_source');
545 =head2 create(\%vals)
547 Inserts a record into the resultset and returns the object.
549 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
554 my ($self, $attrs) = @_;
555 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
556 return $self->new_result($attrs)->insert;
559 =head2 find_or_create(\%vals, \%attrs?)
561 $class->find_or_create({ key => $val, ... });
563 Searches for a record matching the search condition; if it doesn't find one,
564 creates one and returns that instead.
566 my $cd = $schema->resultset('CD')->find_or_create({
568 artist => 'Massive Attack',
569 title => 'Mezzanine',
573 Also takes an optional C<key> attribute, to search by a specific key or unique
574 constraint. For example:
576 my $cd = $schema->resultset('CD')->find_or_create(
578 artist => 'Massive Attack',
579 title => 'Mezzanine',
581 { key => 'artist_title' }
584 See also L</find> and L</update_or_create>.
590 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
591 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
592 my $exists = $self->find($hash, $attrs);
593 return defined($exists) ? $exists : $self->create($hash);
596 =head2 update_or_create
598 $class->update_or_create({ key => $val, ... });
600 First, search for an existing row matching one of the unique constraints
601 (including the primary key) on the source of this resultset. If a row is
602 found, update it with the other given column values. Otherwise, create a new
605 Takes an optional C<key> attribute to search on a specific unique constraint.
608 # In your application
609 my $cd = $schema->resultset('CD')->update_or_create(
611 artist => 'Massive Attack',
612 title => 'Mezzanine',
615 { key => 'artist_title' }
618 If no C<key> is specified, it searches on all unique constraints defined on the
619 source, including the primary key.
621 If the C<key> is specified as C<primary>, search only on the primary key.
623 See also L</find> and L</find_or_create>.
627 sub update_or_create {
630 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
631 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
633 my %unique_constraints = $self->result_source->unique_constraints;
634 my @constraint_names = (exists $attrs->{key}
636 : keys %unique_constraints);
639 foreach my $name (@constraint_names) {
640 my @unique_cols = @{ $unique_constraints{$name} };
642 map { $_ => $hash->{$_} }
643 grep { exists $hash->{$_} }
646 push @unique_hashes, \%unique_hash
647 if (scalar keys %unique_hash == scalar @unique_cols);
651 if (@unique_hashes) {
652 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
654 $row->set_columns($hash);
660 $row = $self->create($hash);
666 =head2 throw_exception
668 See Schema's throw_exception
672 sub throw_exception {
674 $self->result_source->schema->throw_exception(@_);
679 The resultset takes various attributes that modify its behavior. Here's an
684 Which column(s) to order the results by. This is currently passed through
685 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
687 =head2 cols (arrayref)
689 Shortcut to request a particular set of columns to be retrieved. Adds
690 C<me.> onto the start of any column without a C<.> in it and sets C<select>
691 from that, then auto-populates C<as> from C<select> as normal.
693 =head2 select (arrayref)
695 Indicates which columns should be selected from the storage. You can use
696 column names, or in the case of RDBMS back ends, function or stored procedure
699 $rs = $schema->resultset('Foo')->search(
704 { count => 'column_to_count' },
705 { sum => 'column_to_sum' }
710 When you use function/stored procedure names and do not supply an C<as>
711 attribute, the column names returned are storage-dependent. E.g. MySQL would
712 return a column named C<count(column_to_count)> in the above example.
716 Indicates column names for object inflation. This is used in conjunction with
717 C<select>, usually when C<select> contains one or more function or stored
720 $rs = $schema->resultset('Foo')->search(
725 { count => 'column2' }
727 as => [qw/ column1 column2_count /]
731 my $foo = $rs->first(); # get the first Foo
733 If the object against which the search is performed already has an accessor
734 matching a column name specified in C<as>, the value can be retrieved using
735 the accessor as normal:
737 my $column1 = $foo->column1();
739 If on the other hand an accessor does not exist in the object, you need to
740 use C<get_column> instead:
742 my $column2_count = $foo->get_column('column2_count');
744 You can create your own accessors if required - see
745 L<DBIx::Class::Manual::Cookbook> for details.
749 Contains a list of relationships that should be joined for this query. For
752 # Get CDs by Nine Inch Nails
753 my $rs = $schema->resultset('CD')->search(
754 { 'artist.name' => 'Nine Inch Nails' },
758 Can also contain a hash reference to refer to the other relation's relations.
761 package MyApp::Schema::Track;
762 use base qw/DBIx::Class/;
763 __PACKAGE__->table('track');
764 __PACKAGE__->add_columns(qw/trackid cd position title/);
765 __PACKAGE__->set_primary_key('trackid');
766 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
769 # In your application
770 my $rs = $schema->resultset('Artist')->search(
771 { 'track.title' => 'Teardrop' },
773 join => { cd => 'track' },
774 order_by => 'artist.name',
778 If you want to fetch columns from related tables as well, see C<prefetch>
781 =head2 prefetch arrayref/hashref
783 Contains one or more relationships that should be fetched along with the main
784 query (when they are accessed afterwards they will have already been
785 "prefetched"). This is useful for when you know you will need the related
786 objects, because it saves at least one query:
788 my $rs = $schema->resultset('Tag')->search(
797 The initial search results in SQL like the following:
799 SELECT tag.*, cd.*, artist.* FROM tag
800 JOIN cd ON tag.cd = cd.cdid
801 JOIN artist ON cd.artist = artist.artistid
803 L<DBIx::Class> has no need to go back to the database when we access the
804 C<cd> or C<artist> relationships, which saves us two SQL statements in this
807 Any prefetched relationship will be joined automatically, so there is no need
808 for a C<join> attribute in the above search.
810 C<prefetch> can be used with the following relationship types: C<belongs_to>,
813 =head2 from (arrayref)
815 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
816 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
819 NOTE: Use this on your own risk. This allows you to shoot off your foot!
820 C<join> will usually do what you need and it is strongly recommended that you
821 avoid using C<from> unless you cannot achieve the desired result using C<join>.
823 In simple terms, C<from> works as follows:
826 { <alias> => <table>, -join-type => 'inner|left|right' }
827 [] # nested JOIN (optional)
828 { <table.column> = <foreign_table.foreign_key> }
834 ON <table.column> = <foreign_table.foreign_key>
836 An easy way to follow the examples below is to remember the following:
838 Anything inside "[]" is a JOIN
839 Anything inside "{}" is a condition for the enclosing JOIN
841 The following examples utilize a "person" table in a family tree application.
842 In order to express parent->child relationships, this table is self-joined:
844 # Person->belongs_to('father' => 'Person');
845 # Person->belongs_to('mother' => 'Person');
847 C<from> can be used to nest joins. Here we return all children with a father,
848 then search against all mothers of those children:
850 $rs = $schema->resultset('Person')->search(
853 alias => 'mother', # alias columns in accordance with "from"
855 { mother => 'person' },
858 { child => 'person' },
860 { father => 'person' },
861 { 'father.person_id' => 'child.father_id' }
864 { 'mother.person_id' => 'child.mother_id' }
871 # SELECT mother.* FROM person mother
875 # ON ( father.person_id = child.father_id )
877 # ON ( mother.person_id = child.mother_id )
879 The type of any join can be controlled manually. To search against only people
880 with a father in the person table, we could explicitly use C<INNER JOIN>:
882 $rs = $schema->resultset('Person')->search(
885 alias => 'child', # alias columns in accordance with "from"
887 { child => 'person' },
889 { father => 'person', -join-type => 'inner' },
890 { 'father.id' => 'child.father_id' }
897 # SELECT child.* FROM person child
898 # INNER JOIN person father ON child.father_id = father.id
902 For a paged resultset, specifies which page to retrieve. Leave unset
903 for an unpaged resultset.
907 For a paged resultset, how many rows per page:
911 Can also be used to simulate an SQL C<LIMIT>.
913 =head2 group_by (arrayref)
915 A arrayref of columns to group by. Can include columns of joined tables. Note
916 note that L</count> doesn't work on grouped resultsets.
918 group_by => [qw/ column1 column2 ... /]
922 Set to 1 to group by all columns.
924 For more examples of using these attributes, see
925 L<DBIx::Class::Manual::Cookbook>.