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 if (my $join = delete $attrs->{join}) {
88 foreach my $j (ref $join eq 'ARRAY'
89 ? (@{$join}) : ($join)) {
90 if (ref $j eq 'HASH') {
91 $seen{$_} = 1 foreach keys %$j;
96 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}));
98 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
100 if (my $prefetch = delete $attrs->{prefetch}) {
101 foreach my $p (ref $prefetch eq 'ARRAY'
102 ? (@{$prefetch}) : ($prefetch)) {
103 if( ref $p eq 'HASH' ) {
104 foreach my $key (keys %$p) {
105 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
110 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
114 push @cols, $source->resolve_prefetch($p, $attrs->{alias});
116 push(@{$attrs->{select}}, @cols);
117 push(@{$attrs->{as}}, @cols);
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 return $self->result_source->schema->resultset($rel_obj->{class}
273 Returns a storage-driven cursor to the given resultset.
279 my ($attrs) = $self->{attrs};
280 $attrs = { %$attrs };
281 return $self->{cursor}
282 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
283 $attrs->{where},$attrs);
288 Perform a search, but use C<LIKE> instead of equality as the condition. Note
289 that this is simply a convenience method; you most likely want to use
290 L</search> with specific operators.
292 For more information, see L<DBIx::Class::Manual::Cookbook>.
299 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
302 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
303 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
304 return $class->search($query, { %$attrs });
307 =head2 slice($first, $last)
309 Returns a subset of elements from the resultset.
314 my ($self, $min, $max) = @_;
315 my $attrs = { %{ $self->{attrs} || {} } };
316 $attrs->{offset} ||= 0;
317 $attrs->{offset} += $min;
318 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
319 my $slice = (ref $self)->new($self->result_source, $attrs);
320 return (wantarray ? $slice->all : $slice);
325 Returns the next element in the resultset (C<undef> is there is none).
327 Can be used to efficiently iterate over records in the resultset:
329 my $rs = $schema->resultset('CD')->search({});
330 while (my $cd = $rs->next) {
338 my @row = $self->cursor->next;
339 # warn Dumper(\@row); use Data::Dumper;
340 return unless (@row);
341 return $self->_construct_object(@row);
344 sub _construct_object {
345 my ($self, @row) = @_;
346 my @as = @{ $self->{attrs}{as} };
347 #warn "@cols -> @row";
348 my $info = [ {}, {} ];
349 foreach my $as (@as) {
351 my @parts = split(/\./, $as);
352 my $col = pop(@parts);
353 foreach my $p (@parts) {
354 $target = $target->[1]->{$p} ||= [];
356 $target->[0]->{$col} = shift @row;
358 #use Data::Dumper; warn Dumper(\@as, $info);
359 my $new = $self->result_source->result_class->inflate_result(
360 $self->result_source, @$info);
361 $new = $self->{attrs}{record_filter}->($new)
362 if exists $self->{attrs}{record_filter};
368 Returns a reference to the result source for this recordset.
375 Performs an SQL C<COUNT> with the same query as the resultset was built
376 with to find the number of elements. If passed arguments, does a search
377 on the resultset and counts the results of that.
379 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
380 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
381 not support C<DISTINCT> with multiple columns. If you are using such a
382 database, you should only use columns from the main table in your C<group_by>
389 return $self->search(@_)->count if @_ && defined $_[0];
390 unless (defined $self->{count}) {
392 my $select = { 'count' => '*' };
393 if( $group_by = delete $self->{attrs}{group_by} ) {
394 my @distinct = @$group_by;
395 # todo: try CONCAT for multi-column pk
396 my @pk = $self->result_source->primary_columns;
397 if( scalar(@pk) == 1 ) {
399 my $alias = $self->{attrs}{alias};
400 my $re = qr/^($alias\.)?$pk$/;
401 foreach my $column ( @$group_by ) {
402 if( $column =~ $re ) {
403 @distinct = ( $column );
409 $select = { count => { 'distinct' => \@distinct } };
410 #use Data::Dumper; die Dumper $select;
413 my $attrs = { %{ $self->{attrs} },
416 # offset, order by and page are not needed to count. record_filter is cdbi
417 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
419 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
420 $self->{attrs}{group_by} = $group_by;
422 return 0 unless $self->{count};
423 my $count = $self->{count};
424 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
425 $count = $self->{attrs}{rows} if
426 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
432 Calls L</search_literal> with the passed arguments, then L</count>.
436 sub count_literal { shift->search_literal(@_)->count; }
440 Returns all elements in the resultset. Called implictly if the resultset
441 is returned in list context.
447 return map { $self->_construct_object(@$_); }
453 Resets the resultset's cursor, so you can iterate through the elements again.
459 $self->cursor->reset;
465 Resets the resultset and returns the first element.
470 return $_[0]->reset->next;
473 =head2 update(\%values)
475 Sets the specified columns in the resultset to the supplied values.
480 my ($self, $values) = @_;
481 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
482 return $self->result_source->storage->update(
483 $self->result_source->from, $values, $self->{cond});
486 =head2 update_all(\%values)
488 Fetches all objects and updates them one at a time. Note that C<update_all>
489 will run cascade triggers while L</update> will not.
494 my ($self, $values) = @_;
495 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
496 foreach my $obj ($self->all) {
497 $obj->set_columns($values)->update;
504 Deletes the contents of the resultset from its result source.
510 $self->result_source->storage->delete($self->result_source->from, $self->{cond});
516 Fetches all objects and deletes them one at a time. Note that C<delete_all>
517 will run cascade triggers while L</delete> will not.
523 $_->delete for $self->all;
529 Returns a L<Data::Page> object for the current resultset. Only makes
530 sense for queries with a C<page> attribute.
536 my $attrs = $self->{attrs};
537 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
538 $attrs->{rows} ||= 10;
540 return $self->{pager} ||= Data::Page->new(
541 $self->{count}, $attrs->{rows}, $self->{page});
544 =head2 page($page_num)
546 Returns a new resultset for the specified page.
551 my ($self, $page) = @_;
552 my $attrs = { %{$self->{attrs}} };
553 $attrs->{page} = $page;
554 return (ref $self)->new($self->result_source, $attrs);
557 =head2 new_result(\%vals)
559 Creates a result in the resultset's result class.
564 my ($self, $values) = @_;
565 $self->throw_exception( "new_result needs a hash" )
566 unless (ref $values eq 'HASH');
567 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
568 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
570 my $alias = $self->{attrs}{alias};
571 foreach my $key (keys %{$self->{cond}||{}}) {
572 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
574 my $obj = $self->result_source->result_class->new(\%new);
575 $obj->result_source($self->result_source) if $obj->can('result_source');
579 =head2 create(\%vals)
581 Inserts a record into the resultset and returns the object.
583 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
588 my ($self, $attrs) = @_;
589 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
590 return $self->new_result($attrs)->insert;
593 =head2 find_or_create(\%vals, \%attrs?)
595 $class->find_or_create({ key => $val, ... });
597 Searches for a record matching the search condition; if it doesn't find one,
598 creates one and returns that instead.
600 my $cd = $schema->resultset('CD')->find_or_create({
602 artist => 'Massive Attack',
603 title => 'Mezzanine',
607 Also takes an optional C<key> attribute, to search by a specific key or unique
608 constraint. For example:
610 my $cd = $schema->resultset('CD')->find_or_create(
612 artist => 'Massive Attack',
613 title => 'Mezzanine',
615 { key => 'artist_title' }
618 See also L</find> and L</update_or_create>.
624 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
625 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
626 my $exists = $self->find($hash, $attrs);
627 return defined($exists) ? $exists : $self->create($hash);
630 =head2 update_or_create
632 $class->update_or_create({ key => $val, ... });
634 First, search for an existing row matching one of the unique constraints
635 (including the primary key) on the source of this resultset. If a row is
636 found, update it with the other given column values. Otherwise, create a new
639 Takes an optional C<key> attribute to search on a specific unique constraint.
642 # In your application
643 my $cd = $schema->resultset('CD')->update_or_create(
645 artist => 'Massive Attack',
646 title => 'Mezzanine',
649 { key => 'artist_title' }
652 If no C<key> is specified, it searches on all unique constraints defined on the
653 source, including the primary key.
655 If the C<key> is specified as C<primary>, search only on the primary key.
657 See also L</find> and L</find_or_create>.
661 sub update_or_create {
664 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
665 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
667 my %unique_constraints = $self->result_source->unique_constraints;
668 my @constraint_names = (exists $attrs->{key}
670 : keys %unique_constraints);
673 foreach my $name (@constraint_names) {
674 my @unique_cols = @{ $unique_constraints{$name} };
676 map { $_ => $hash->{$_} }
677 grep { exists $hash->{$_} }
680 push @unique_hashes, \%unique_hash
681 if (scalar keys %unique_hash == scalar @unique_cols);
685 if (@unique_hashes) {
686 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
688 $row->set_columns($hash);
694 $row = $self->create($hash);
700 =head2 throw_exception
702 See Schema's throw_exception
706 sub throw_exception {
708 $self->result_source->schema->throw_exception(@_);
713 The resultset takes various attributes that modify its behavior. Here's an
718 Which column(s) to order the results by. This is currently passed through
719 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
721 =head2 cols (arrayref)
723 Shortcut to request a particular set of columns to be retrieved. Adds
724 C<me.> onto the start of any column without a C<.> in it and sets C<select>
725 from that, then auto-populates C<as> from C<select> as normal.
727 =head2 include_columns (arrayref)
729 Shortcut to include additional columns in the returned results - for example
731 { include_columns => ['foo.name'], join => ['foo'] }
733 would add a 'name' column to the information passed to object inflation
735 =head2 select (arrayref)
737 Indicates which columns should be selected from the storage. You can use
738 column names, or in the case of RDBMS back ends, function or stored procedure
741 $rs = $schema->resultset('Foo')->search(
746 { count => 'column_to_count' },
747 { sum => 'column_to_sum' }
752 When you use function/stored procedure names and do not supply an C<as>
753 attribute, the column names returned are storage-dependent. E.g. MySQL would
754 return a column named C<count(column_to_count)> in the above example.
758 Indicates column names for object inflation. This is used in conjunction with
759 C<select>, usually when C<select> contains one or more function or stored
762 $rs = $schema->resultset('Foo')->search(
767 { count => 'column2' }
769 as => [qw/ column1 column2_count /]
773 my $foo = $rs->first(); # get the first Foo
775 If the object against which the search is performed already has an accessor
776 matching a column name specified in C<as>, the value can be retrieved using
777 the accessor as normal:
779 my $column1 = $foo->column1();
781 If on the other hand an accessor does not exist in the object, you need to
782 use C<get_column> instead:
784 my $column2_count = $foo->get_column('column2_count');
786 You can create your own accessors if required - see
787 L<DBIx::Class::Manual::Cookbook> for details.
791 Contains a list of relationships that should be joined for this query. For
794 # Get CDs by Nine Inch Nails
795 my $rs = $schema->resultset('CD')->search(
796 { 'artist.name' => 'Nine Inch Nails' },
800 Can also contain a hash reference to refer to the other relation's relations.
803 package MyApp::Schema::Track;
804 use base qw/DBIx::Class/;
805 __PACKAGE__->table('track');
806 __PACKAGE__->add_columns(qw/trackid cd position title/);
807 __PACKAGE__->set_primary_key('trackid');
808 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
811 # In your application
812 my $rs = $schema->resultset('Artist')->search(
813 { 'track.title' => 'Teardrop' },
815 join => { cd => 'track' },
816 order_by => 'artist.name',
820 If you want to fetch columns from related tables as well, see C<prefetch>
823 =head2 prefetch arrayref/hashref
825 Contains one or more relationships that should be fetched along with the main
826 query (when they are accessed afterwards they will have already been
827 "prefetched"). This is useful for when you know you will need the related
828 objects, because it saves at least one query:
830 my $rs = $schema->resultset('Tag')->search(
839 The initial search results in SQL like the following:
841 SELECT tag.*, cd.*, artist.* FROM tag
842 JOIN cd ON tag.cd = cd.cdid
843 JOIN artist ON cd.artist = artist.artistid
845 L<DBIx::Class> has no need to go back to the database when we access the
846 C<cd> or C<artist> relationships, which saves us two SQL statements in this
849 Any prefetched relationship will be joined automatically, so there is no need
850 for a C<join> attribute in the above search.
852 C<prefetch> can be used with the following relationship types: C<belongs_to>,
855 =head2 from (arrayref)
857 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
858 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
861 NOTE: Use this on your own risk. This allows you to shoot off your foot!
862 C<join> will usually do what you need and it is strongly recommended that you
863 avoid using C<from> unless you cannot achieve the desired result using C<join>.
865 In simple terms, C<from> works as follows:
868 { <alias> => <table>, -join-type => 'inner|left|right' }
869 [] # nested JOIN (optional)
870 { <table.column> = <foreign_table.foreign_key> }
876 ON <table.column> = <foreign_table.foreign_key>
878 An easy way to follow the examples below is to remember the following:
880 Anything inside "[]" is a JOIN
881 Anything inside "{}" is a condition for the enclosing JOIN
883 The following examples utilize a "person" table in a family tree application.
884 In order to express parent->child relationships, this table is self-joined:
886 # Person->belongs_to('father' => 'Person');
887 # Person->belongs_to('mother' => 'Person');
889 C<from> can be used to nest joins. Here we return all children with a father,
890 then search against all mothers of those children:
892 $rs = $schema->resultset('Person')->search(
895 alias => 'mother', # alias columns in accordance with "from"
897 { mother => 'person' },
900 { child => 'person' },
902 { father => 'person' },
903 { 'father.person_id' => 'child.father_id' }
906 { 'mother.person_id' => 'child.mother_id' }
913 # SELECT mother.* FROM person mother
917 # ON ( father.person_id = child.father_id )
919 # ON ( mother.person_id = child.mother_id )
921 The type of any join can be controlled manually. To search against only people
922 with a father in the person table, we could explicitly use C<INNER JOIN>:
924 $rs = $schema->resultset('Person')->search(
927 alias => 'child', # alias columns in accordance with "from"
929 { child => 'person' },
931 { father => 'person', -join-type => 'inner' },
932 { 'father.id' => 'child.father_id' }
939 # SELECT child.* FROM person child
940 # INNER JOIN person father ON child.father_id = father.id
944 For a paged resultset, specifies which page to retrieve. Leave unset
945 for an unpaged resultset.
949 For a paged resultset, how many rows per page:
953 Can also be used to simulate an SQL C<LIMIT>.
955 =head2 group_by (arrayref)
957 A arrayref of columns to group by. Can include columns of joined tables.
959 group_by => [qw/ column1 column2 ... /]
963 Set to 1 to group by all columns.
965 For more examples of using these attributes, see
966 L<DBIx::Class::Manual::Cookbook>.