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 unless (defined $self->{count}) {
378 my $select = { 'count' => '*' };
379 if( $group_by = delete $self->{attrs}{group_by} ) {
380 my @distinct = @$group_by;
381 # todo: try CONCAT for multi-column pk
382 my @pk = $self->result_source->primary_columns;
383 if( scalar(@pk) == 1 ) {
385 my $alias = $self->{attrs}{alias};
386 my $re = qr/^($alias\.)?$pk$/;
387 foreach my $column ( @$group_by ) {
388 if( $column =~ $re ) {
389 @distinct = ( $column );
395 $select = { count => { 'distinct' => \@distinct } };
396 #use Data::Dumper; die Dumper $select;
399 my $attrs = { %{ $self->{attrs} },
402 # offset, order by and page are not needed to count. record_filter is cdbi
403 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
405 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
406 $self->{attrs}{group_by} = $group_by;
408 return 0 unless $self->{count};
409 my $count = $self->{count};
410 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
411 $count = $self->{attrs}{rows} if
412 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
418 Calls L</search_literal> with the passed arguments, then L</count>.
422 sub count_literal { shift->search_literal(@_)->count; }
426 Returns all elements in the resultset. Called implictly if the resultset
427 is returned in list context.
433 return map { $self->_construct_object(@$_); }
439 Resets the resultset's cursor, so you can iterate through the elements again.
445 $self->cursor->reset;
451 Resets the resultset and returns the first element.
456 return $_[0]->reset->next;
459 =head2 update(\%values)
461 Sets the specified columns in the resultset to the supplied values.
466 my ($self, $values) = @_;
467 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
468 return $self->result_source->storage->update(
469 $self->result_source->from, $values, $self->{cond});
472 =head2 update_all(\%values)
474 Fetches all objects and updates them one at a time. Note that C<update_all>
475 will run cascade triggers while L</update> will not.
480 my ($self, $values) = @_;
481 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
482 foreach my $obj ($self->all) {
483 $obj->set_columns($values)->update;
490 Deletes the contents of the resultset from its result source.
496 $self->result_source->storage->delete($self->result_source->from, $self->{cond});
502 Fetches all objects and deletes them one at a time. Note that C<delete_all>
503 will run cascade triggers while L</delete> will not.
509 $_->delete for $self->all;
515 Returns a L<Data::Page> object for the current resultset. Only makes
516 sense for queries with a C<page> attribute.
522 my $attrs = $self->{attrs};
523 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
524 $attrs->{rows} ||= 10;
526 return $self->{pager} ||= Data::Page->new(
527 $self->{count}, $attrs->{rows}, $self->{page});
530 =head2 page($page_num)
532 Returns a new resultset for the specified page.
537 my ($self, $page) = @_;
538 my $attrs = { %{$self->{attrs}} };
539 $attrs->{page} = $page;
540 return (ref $self)->new($self->result_source, $attrs);
543 =head2 new_result(\%vals)
545 Creates a result in the resultset's result class.
550 my ($self, $values) = @_;
551 $self->throw_exception( "new_result needs a hash" )
552 unless (ref $values eq 'HASH');
553 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
554 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
556 my $alias = $self->{attrs}{alias};
557 foreach my $key (keys %{$self->{cond}||{}}) {
558 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
560 my $obj = $self->result_source->result_class->new(\%new);
561 $obj->result_source($self->result_source) if $obj->can('result_source');
565 =head2 create(\%vals)
567 Inserts a record into the resultset and returns the object.
569 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
574 my ($self, $attrs) = @_;
575 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
576 return $self->new_result($attrs)->insert;
579 =head2 find_or_create(\%vals, \%attrs?)
581 $class->find_or_create({ key => $val, ... });
583 Searches for a record matching the search condition; if it doesn't find one,
584 creates one and returns that instead.
586 my $cd = $schema->resultset('CD')->find_or_create({
588 artist => 'Massive Attack',
589 title => 'Mezzanine',
593 Also takes an optional C<key> attribute, to search by a specific key or unique
594 constraint. For example:
596 my $cd = $schema->resultset('CD')->find_or_create(
598 artist => 'Massive Attack',
599 title => 'Mezzanine',
601 { key => 'artist_title' }
604 See also L</find> and L</update_or_create>.
610 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
611 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
612 my $exists = $self->find($hash, $attrs);
613 return defined($exists) ? $exists : $self->create($hash);
616 =head2 update_or_create
618 $class->update_or_create({ key => $val, ... });
620 First, search for an existing row matching one of the unique constraints
621 (including the primary key) on the source of this resultset. If a row is
622 found, update it with the other given column values. Otherwise, create a new
625 Takes an optional C<key> attribute to search on a specific unique constraint.
628 # In your application
629 my $cd = $schema->resultset('CD')->update_or_create(
631 artist => 'Massive Attack',
632 title => 'Mezzanine',
635 { key => 'artist_title' }
638 If no C<key> is specified, it searches on all unique constraints defined on the
639 source, including the primary key.
641 If the C<key> is specified as C<primary>, search only on the primary key.
643 See also L</find> and L</find_or_create>.
647 sub update_or_create {
650 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
651 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
653 my %unique_constraints = $self->result_source->unique_constraints;
654 my @constraint_names = (exists $attrs->{key}
656 : keys %unique_constraints);
659 foreach my $name (@constraint_names) {
660 my @unique_cols = @{ $unique_constraints{$name} };
662 map { $_ => $hash->{$_} }
663 grep { exists $hash->{$_} }
666 push @unique_hashes, \%unique_hash
667 if (scalar keys %unique_hash == scalar @unique_cols);
671 if (@unique_hashes) {
672 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
674 $row->set_columns($hash);
680 $row = $self->create($hash);
686 =head2 throw_exception
688 See Schema's throw_exception
692 sub throw_exception {
694 $self->result_source->schema->throw_exception(@_);
699 The resultset takes various attributes that modify its behavior. Here's an
704 Which column(s) to order the results by. This is currently passed through
705 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
707 =head2 cols (arrayref)
709 Shortcut to request a particular set of columns to be retrieved. Adds
710 C<me.> onto the start of any column without a C<.> in it and sets C<select>
711 from that, then auto-populates C<as> from C<select> as normal.
713 =head2 select (arrayref)
715 Indicates which columns should be selected from the storage. You can use
716 column names, or in the case of RDBMS back ends, function or stored procedure
719 $rs = $schema->resultset('Foo')->search(
724 { count => 'column_to_count' },
725 { sum => 'column_to_sum' }
730 When you use function/stored procedure names and do not supply an C<as>
731 attribute, the column names returned are storage-dependent. E.g. MySQL would
732 return a column named C<count(column_to_count)> in the above example.
736 Indicates column names for object inflation. This is used in conjunction with
737 C<select>, usually when C<select> contains one or more function or stored
740 $rs = $schema->resultset('Foo')->search(
745 { count => 'column2' }
747 as => [qw/ column1 column2_count /]
751 my $foo = $rs->first(); # get the first Foo
753 If the object against which the search is performed already has an accessor
754 matching a column name specified in C<as>, the value can be retrieved using
755 the accessor as normal:
757 my $column1 = $foo->column1();
759 If on the other hand an accessor does not exist in the object, you need to
760 use C<get_column> instead:
762 my $column2_count = $foo->get_column('column2_count');
764 You can create your own accessors if required - see
765 L<DBIx::Class::Manual::Cookbook> for details.
769 Contains a list of relationships that should be joined for this query. For
772 # Get CDs by Nine Inch Nails
773 my $rs = $schema->resultset('CD')->search(
774 { 'artist.name' => 'Nine Inch Nails' },
778 Can also contain a hash reference to refer to the other relation's relations.
781 package MyApp::Schema::Track;
782 use base qw/DBIx::Class/;
783 __PACKAGE__->table('track');
784 __PACKAGE__->add_columns(qw/trackid cd position title/);
785 __PACKAGE__->set_primary_key('trackid');
786 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
789 # In your application
790 my $rs = $schema->resultset('Artist')->search(
791 { 'track.title' => 'Teardrop' },
793 join => { cd => 'track' },
794 order_by => 'artist.name',
798 If you want to fetch columns from related tables as well, see C<prefetch>
801 =head2 prefetch arrayref/hashref
803 Contains one or more relationships that should be fetched along with the main
804 query (when they are accessed afterwards they will have already been
805 "prefetched"). This is useful for when you know you will need the related
806 objects, because it saves at least one query:
808 my $rs = $schema->resultset('Tag')->search(
817 The initial search results in SQL like the following:
819 SELECT tag.*, cd.*, artist.* FROM tag
820 JOIN cd ON tag.cd = cd.cdid
821 JOIN artist ON cd.artist = artist.artistid
823 L<DBIx::Class> has no need to go back to the database when we access the
824 C<cd> or C<artist> relationships, which saves us two SQL statements in this
827 Any prefetched relationship will be joined automatically, so there is no need
828 for a C<join> attribute in the above search.
830 C<prefetch> can be used with the following relationship types: C<belongs_to>,
833 =head2 from (arrayref)
835 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
836 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
839 NOTE: Use this on your own risk. This allows you to shoot off your foot!
840 C<join> will usually do what you need and it is strongly recommended that you
841 avoid using C<from> unless you cannot achieve the desired result using C<join>.
843 In simple terms, C<from> works as follows:
846 { <alias> => <table>, -join-type => 'inner|left|right' }
847 [] # nested JOIN (optional)
848 { <table.column> = <foreign_table.foreign_key> }
854 ON <table.column> = <foreign_table.foreign_key>
856 An easy way to follow the examples below is to remember the following:
858 Anything inside "[]" is a JOIN
859 Anything inside "{}" is a condition for the enclosing JOIN
861 The following examples utilize a "person" table in a family tree application.
862 In order to express parent->child relationships, this table is self-joined:
864 # Person->belongs_to('father' => 'Person');
865 # Person->belongs_to('mother' => 'Person');
867 C<from> can be used to nest joins. Here we return all children with a father,
868 then search against all mothers of those children:
870 $rs = $schema->resultset('Person')->search(
873 alias => 'mother', # alias columns in accordance with "from"
875 { mother => 'person' },
878 { child => 'person' },
880 { father => 'person' },
881 { 'father.person_id' => 'child.father_id' }
884 { 'mother.person_id' => 'child.mother_id' }
891 # SELECT mother.* FROM person mother
895 # ON ( father.person_id = child.father_id )
897 # ON ( mother.person_id = child.mother_id )
899 The type of any join can be controlled manually. To search against only people
900 with a father in the person table, we could explicitly use C<INNER JOIN>:
902 $rs = $schema->resultset('Person')->search(
905 alias => 'child', # alias columns in accordance with "from"
907 { child => 'person' },
909 { father => 'person', -join-type => 'inner' },
910 { 'father.id' => 'child.father_id' }
917 # SELECT child.* FROM person child
918 # INNER JOIN person father ON child.father_id = father.id
922 For a paged resultset, specifies which page to retrieve. Leave unset
923 for an unpaged resultset.
927 For a paged resultset, how many rows per page:
931 Can also be used to simulate an SQL C<LIMIT>.
933 =head2 group_by (arrayref)
935 A arrayref of columns to group by. Can include columns of joined tables. Note
936 note that L</count> doesn't work on grouped resultsets.
938 group_by => [qw/ column1 column2 ... /]
942 Set to 1 to group by all columns.
944 For more examples of using these attributes, see
945 L<DBIx::Class::Manual::Cookbook>.