1 package DBIx::Class::ResultSet;
12 use base qw/DBIx::Class/;
13 __PACKAGE__->load_components(qw/AccessorGroup/);
14 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
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__->load_components(qw/Core/);
36 __PACKAGE__->table('artist');
37 __PACKAGE__->add_columns(qw/artistid name/);
38 __PACKAGE__->set_primary_key('artistid');
39 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
42 package MyApp::Schema::CD;
43 use base qw/DBIx::Class/;
44 __PACKAGE__->load_components(qw/Core/);
45 __PACKAGE__->table('cd');
46 __PACKAGE__->add_columns(qw/cdid artist title year/);
47 __PACKAGE__->set_primary_key('cdid');
48 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
55 =head3 Arguments: ($source, \%$attrs)
57 The resultset constructor. Takes a source object (usually a
58 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see L</ATTRIBUTES>
59 below). Does not perform any queries -- these are executed as needed by the
62 Generally you won't need to construct a resultset manually. You'll
63 automatically get one from e.g. a L</search> called in scalar context:
65 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
71 return $class->new_result(@_) if ref $class;
73 my ($source, $attrs) = @_;
74 #use Data::Dumper; warn Dumper($attrs);
75 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
76 my $alias = ($attrs->{alias} ||= 'me');
78 $attrs->{columns} ||= delete $attrs->{cols} if $attrs->{cols};
79 delete $attrs->{as} if $attrs->{columns};
80 $attrs->{columns} ||= [ $source->columns ] unless $attrs->{select};
81 $attrs->{select} = [ map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}} ]
83 $attrs->{as} ||= [ map { m/^\Q$alias.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}} ];
84 if (my $include = delete $attrs->{include_columns}) {
85 push(@{$attrs->{select}}, @$include);
86 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1; } @$include);
88 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
90 $attrs->{from} ||= [ { $alias => $source->from } ];
91 $attrs->{seen_join} ||= {};
93 if (my $join = delete $attrs->{join}) {
94 foreach my $j (ref $join eq 'ARRAY' ? @$join : ($join)) {
95 if (ref $j eq 'HASH') {
96 $seen{$_} = 1 foreach keys %$j;
101 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}, $attrs->{seen_join}));
104 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
105 $attrs->{order_by} = [ $attrs->{order_by} ] if $attrs->{order_by} and !ref($attrs->{order_by});
106 $attrs->{order_by} ||= [];
108 my $collapse = $attrs->{collapse} || {};
109 if (my $prefetch = delete $attrs->{prefetch}) {
111 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
112 if ( ref $p eq 'HASH' ) {
113 foreach my $key (keys %$p) {
114 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
118 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
121 my @prefetch = $source->resolve_prefetch(
122 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
123 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
124 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
126 push(@{$attrs->{order_by}}, @pre_order);
128 $attrs->{collapse} = $collapse;
129 # use Data::Dumper; warn Dumper($collapse) if keys %{$collapse};
131 if ($attrs->{page}) {
132 $attrs->{rows} ||= 10;
133 $attrs->{offset} ||= 0;
134 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
138 result_source => $source,
139 result_class => $attrs->{result_class} || $source->result_class,
140 cond => $attrs->{where},
141 from => $attrs->{from},
142 collapse => $collapse,
144 page => delete $attrs->{page},
152 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
153 my $new_rs = $rs->search({ foo => 3 });
155 If you need to pass in additional attributes but no additional condition,
156 call it as C<search(undef, \%attrs);>.
158 # "SELECT foo, bar FROM $class_table"
159 my @all = $class->search(undef, { columns => [qw/foo bar/] });
169 my $attrs = { %{$self->{attrs}} };
170 my $having = delete $attrs->{having};
171 $attrs = { %$attrs, %{ pop(@_) } } if @_ > 1 and ref $_[$#_] eq 'HASH';
174 ? ((@_ == 1 || ref $_[0] eq "HASH")
177 ? $self->throw_exception(
178 "Odd number of arguments to search")
181 if (defined $where) {
182 $attrs->{where} = (defined $attrs->{where}
184 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
185 $where, $attrs->{where} ] }
189 if (defined $having) {
190 $attrs->{having} = (defined $attrs->{having}
192 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
193 $having, $attrs->{having} ] }
197 $rs = (ref $self)->new($self->result_source, $attrs);
203 return (wantarray ? $rs->all : $rs);
206 =head2 search_literal
208 my @obj = $rs->search_literal($literal_where_cond, @bind);
209 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
211 Pass a literal chunk of SQL to be added to the conditional part of the
217 my ($self, $cond, @vals) = @_;
218 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
219 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
220 return $self->search(\$cond, $attrs);
225 =head3 Arguments: (@colvalues) | (\%cols, \%attrs?)
227 Finds a row based on its primary key or unique constraint. For example:
229 my $cd = $schema->resultset('CD')->find(5);
231 Also takes an optional C<key> attribute, to search by a specific key or unique
232 constraint. For example:
234 my $cd = $schema->resultset('CD')->find(
236 artist => 'Massive Attack',
237 title => 'Mezzanine',
239 { key => 'artist_title' }
242 See also L</find_or_create> and L</update_or_create>.
247 my ($self, @vals) = @_;
248 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
250 my @cols = $self->result_source->primary_columns;
251 if (exists $attrs->{key}) {
252 my %uniq = $self->result_source->unique_constraints;
253 $self->throw_exception( "Unknown key $attrs->{key} on $self->name" )
254 unless exists $uniq{$attrs->{key}};
255 @cols = @{ $uniq{$attrs->{key}} };
257 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
258 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
262 if (ref $vals[0] eq 'HASH') {
263 $query = { %{$vals[0]} };
264 } elsif (@cols == @vals) {
266 @{$query}{@cols} = @vals;
270 foreach my $key (grep { ! m/\./ } keys %$query) {
271 $query->{"$self->{attrs}{alias}.$key"} = delete $query->{$key};
273 #warn Dumper($query);
276 my $rs = $self->search($query,$attrs);
277 return keys %{$rs->{collapse}} ? $rs->next : $rs->single;
279 return keys %{$self->{collapse}} ? $self->search($query)->next : $self->single($query);
283 =head2 search_related
285 $rs->search_related('relname', $cond?, $attrs?);
287 Search the specified relationship. Optionally specify a condition for matching
293 return shift->related_resultset(shift)->search(@_);
298 Returns a storage-driven cursor to the given resultset.
304 my $attrs = { %{$self->{attrs}} };
305 return $self->{cursor}
306 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
307 $attrs->{where},$attrs);
312 Inflates the first result without creating a cursor
317 my ($self, $where) = @_;
318 my $attrs = { %{$self->{attrs}} };
320 if (defined $attrs->{where}) {
323 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
324 $where, delete $attrs->{where} ]
327 $attrs->{where} = $where;
330 my @data = $self->result_source->storage->select_single(
331 $self->{from}, $attrs->{select},
332 $attrs->{where},$attrs);
333 return (@data ? $self->_construct_object(@data) : ());
339 Perform a search, but use C<LIKE> instead of equality as the condition. Note
340 that this is simply a convenience method; you most likely want to use
341 L</search> with specific operators.
343 For more information, see L<DBIx::Class::Manual::Cookbook>.
349 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
350 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
351 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
352 return $class->search($query, { %$attrs });
357 =head3 Arguments: ($first, $last)
359 Returns a subset of elements from the resultset.
364 my ($self, $min, $max) = @_;
365 my $attrs = { %{ $self->{attrs} || {} } };
366 $attrs->{offset} ||= 0;
367 $attrs->{offset} += $min;
368 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
369 my $slice = (ref $self)->new($self->result_source, $attrs);
370 return (wantarray ? $slice->all : $slice);
375 Returns the next element in the resultset (C<undef> is there is none).
377 Can be used to efficiently iterate over records in the resultset:
379 my $rs = $schema->resultset('CD')->search;
380 while (my $cd = $rs->next) {
388 if (@{$self->{all_cache} || []}) {
389 $self->{all_cache_position} ||= 0;
390 return $self->{all_cache}->[$self->{all_cache_position}++];
392 if ($self->{attrs}{cache}) {
393 $self->{all_cache_position} = 1;
394 return ($self->all)[0];
396 my @row = (exists $self->{stashed_row}
397 ? @{delete $self->{stashed_row}}
398 : $self->cursor->next);
399 # warn Dumper(\@row); use Data::Dumper;
400 return unless (@row);
401 return $self->_construct_object(@row);
404 sub _construct_object {
405 my ($self, @row) = @_;
406 my @as = @{ $self->{attrs}{as} };
408 my $info = $self->_collapse_result(\@as, \@row);
410 my $new = $self->result_class->inflate_result($self->result_source, @$info);
412 $new = $self->{attrs}{record_filter}->($new)
413 if exists $self->{attrs}{record_filter};
417 sub _collapse_result {
418 my ($self, $as, $row, $prefix) = @_;
423 foreach my $this_as (@$as) {
424 my $val = shift @copy;
425 if (defined $prefix) {
426 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
428 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
429 $const{$1||''}{$2} = $val;
432 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
433 $const{$1||''}{$2} = $val;
437 my $info = [ {}, {} ];
438 foreach my $key (keys %const) {
441 my @parts = split(/\./, $key);
442 foreach my $p (@parts) {
443 $target = $target->[1]->{$p} ||= [];
445 $target->[0] = $const{$key};
447 $info->[0] = $const{$key};
451 my @collapse = (defined($prefix)
452 ? (map { (m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()); }
453 keys %{$self->{collapse}})
454 : keys %{$self->{collapse}});
456 my ($c) = sort { length $a <=> length $b } @collapse;
458 foreach my $p (split(/\./, $c)) {
459 $target = $target->[1]->{$p} ||= [];
461 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
462 my @co_key = @{$self->{collapse}{$c_prefix}};
463 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
464 my $tree = $self->_collapse_result($as, $row, $c_prefix);
467 !defined($tree->[0]->{$_})
468 || $co_check{$_} ne $tree->[0]->{$_}
471 last unless (@raw = $self->cursor->next);
472 $row = $self->{stashed_row} = \@raw;
473 $tree = $self->_collapse_result($as, $row, $c_prefix);
474 #warn Data::Dumper::Dumper($tree, $row);
484 Returns a reference to the result source for this recordset.
491 Performs an SQL C<COUNT> with the same query as the resultset was built
492 with to find the number of elements. If passed arguments, does a search
493 on the resultset and counts the results of that.
495 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
496 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
497 not support C<DISTINCT> with multiple columns. If you are using such a
498 database, you should only use columns from the main table in your C<group_by>
505 return $self->search(@_)->count if @_ and defined $_[0];
506 unless (defined $self->{count}) {
507 return scalar @{ $self->get_cache } if @{ $self->get_cache };
508 my $select = { count => '*' };
509 my $attrs = { %{ $self->{attrs} } };
510 if (my $group_by = delete $attrs->{group_by}) {
511 delete $attrs->{having};
512 my @distinct = (ref $group_by ? @$group_by : ($group_by));
513 # todo: try CONCAT for multi-column pk
514 my @pk = $self->result_source->primary_columns;
516 foreach my $column (@distinct) {
517 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
518 @distinct = ($column);
524 $select = { count => { distinct => \@distinct } };
525 #use Data::Dumper; die Dumper $select;
528 $attrs->{select} = $select;
529 $attrs->{as} = [qw/count/];
530 # offset, order by and page are not needed to count. record_filter is cdbi
531 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
533 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
535 return 0 unless $self->{count};
536 my $count = $self->{count};
537 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
538 $count = $self->{attrs}{rows} if
539 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
545 Calls L</search_literal> with the passed arguments, then L</count>.
549 sub count_literal { shift->search_literal(@_)->count; }
553 Returns all elements in the resultset. Called implictly if the resultset
554 is returned in list context.
560 return @{ $self->get_cache } if @{ $self->get_cache };
564 if (keys %{$self->{collapse}}) {
565 # Using $self->cursor->all is really just an optimisation.
566 # If we're collapsing has_many prefetches it probably makes
567 # very little difference, and this is cleaner than hacking
568 # _construct_object to survive the approach
570 $self->cursor->reset;
571 while (@row = $self->cursor->next) {
572 push(@obj, $self->_construct_object(@row));
575 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
578 $self->set_cache(\@obj) if $self->{attrs}{cache};
584 Resets the resultset's cursor, so you can iterate through the elements again.
590 $self->{all_cache_position} = 0;
591 $self->cursor->reset;
597 Resets the resultset and returns the first element.
602 return $_[0]->reset->next;
607 =head3 Arguments: (\%values)
609 Sets the specified columns in the resultset to the supplied values.
614 my ($self, $values) = @_;
615 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
616 return $self->result_source->storage->update(
617 $self->result_source->from, $values, $self->{cond});
622 =head3 Arguments: (\%values)
624 Fetches all objects and updates them one at a time. Note that C<update_all>
625 will run cascade triggers while L</update> will not.
630 my ($self, $values) = @_;
631 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
632 foreach my $obj ($self->all) {
633 $obj->set_columns($values)->update;
640 Deletes the contents of the resultset from its result source.
647 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
648 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
649 if (ref $self->{cond} eq 'ARRAY') {
650 $del = [ map { my %hash;
651 foreach my $key (keys %{$_}) {
653 $hash{$1} = $_->{$key};
654 }; \%hash; } @{$self->{cond}} ];
655 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
656 $del->{-and} = [ map { my %hash;
657 foreach my $key (keys %{$_}) {
659 $hash{$1} = $_->{$key};
660 }; \%hash; } @{$self->{cond}{-and}} ];
662 foreach my $key (keys %{$self->{cond}}) {
664 $del->{$1} = $self->{cond}{$key};
667 $self->result_source->storage->delete($self->result_source->from, $del);
673 Fetches all objects and deletes them one at a time. Note that C<delete_all>
674 will run cascade triggers while L</delete> will not.
680 $_->delete for $self->all;
686 Returns a L<Data::Page> object for the current resultset. Only makes
687 sense for queries with a C<page> attribute.
693 my $attrs = $self->{attrs};
694 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
695 $attrs->{rows} ||= 10;
697 return $self->{pager} ||= Data::Page->new(
698 $self->{count}, $attrs->{rows}, $self->{page});
703 =head3 Arguments: ($page_num)
705 Returns a new resultset for the specified page.
710 my ($self, $page) = @_;
711 my $attrs = { %{$self->{attrs}} };
712 $attrs->{page} = $page;
713 return (ref $self)->new($self->result_source, $attrs);
718 =head3 Arguments: (\%vals)
720 Creates a result in the resultset's result class.
725 my ($self, $values) = @_;
726 $self->throw_exception( "new_result needs a hash" )
727 unless (ref $values eq 'HASH');
728 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
729 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
731 my $alias = $self->{attrs}{alias};
732 foreach my $key (keys %{$self->{cond}||{}}) {
733 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
735 my $obj = $self->result_class->new(\%new);
736 $obj->result_source($self->result_source) if $obj->can('result_source');
742 =head3 Arguments: (\%vals)
744 Inserts a record into the resultset and returns the object.
746 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
751 my ($self, $attrs) = @_;
752 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
753 return $self->new_result($attrs)->insert;
756 =head2 find_or_create
758 =head3 Arguments: (\%vals, \%attrs?)
760 $class->find_or_create({ key => $val, ... });
762 Searches for a record matching the search condition; if it doesn't find one,
763 creates one and returns that instead.
765 my $cd = $schema->resultset('CD')->find_or_create({
767 artist => 'Massive Attack',
768 title => 'Mezzanine',
772 Also takes an optional C<key> attribute, to search by a specific key or unique
773 constraint. For example:
775 my $cd = $schema->resultset('CD')->find_or_create(
777 artist => 'Massive Attack',
778 title => 'Mezzanine',
780 { key => 'artist_title' }
783 See also L</find> and L</update_or_create>.
789 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
790 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
791 my $exists = $self->find($hash, $attrs);
792 return defined $exists ? $exists : $self->create($hash);
795 =head2 update_or_create
797 $class->update_or_create({ key => $val, ... });
799 First, search for an existing row matching one of the unique constraints
800 (including the primary key) on the source of this resultset. If a row is
801 found, update it with the other given column values. Otherwise, create a new
804 Takes an optional C<key> attribute to search on a specific unique constraint.
807 # In your application
808 my $cd = $schema->resultset('CD')->update_or_create(
810 artist => 'Massive Attack',
811 title => 'Mezzanine',
814 { key => 'artist_title' }
817 If no C<key> is specified, it searches on all unique constraints defined on the
818 source, including the primary key.
820 If the C<key> is specified as C<primary>, search only on the primary key.
822 See also L</find> and L</find_or_create>.
826 sub update_or_create {
828 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
829 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
831 my %unique_constraints = $self->result_source->unique_constraints;
832 my @constraint_names = (exists $attrs->{key}
834 : keys %unique_constraints);
837 foreach my $name (@constraint_names) {
838 my @unique_cols = @{ $unique_constraints{$name} };
840 map { $_ => $hash->{$_} }
841 grep { exists $hash->{$_} }
844 push @unique_hashes, \%unique_hash
845 if (scalar keys %unique_hash == scalar @unique_cols);
848 if (@unique_hashes) {
849 my $row = $self->single(\@unique_hashes);
851 $row->set_columns($hash);
857 return $self->create($hash);
862 Gets the contents of the cache for the resultset.
867 shift->{all_cache} || [];
872 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
877 my ( $self, $data ) = @_;
878 $self->throw_exception("set_cache requires an arrayref")
879 if ref $data ne 'ARRAY';
880 my $result_class = $self->result_class;
882 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
883 if ref $_ ne $result_class;
885 $self->{all_cache} = $data;
890 Clears the cache for the resultset.
895 shift->set_cache([]);
898 =head2 related_resultset
900 Returns a related resultset for the supplied relationship name.
902 $rs = $rs->related_resultset('foo');
906 sub related_resultset {
907 my ( $self, $rel, @rest ) = @_;
908 $self->{related_resultsets} ||= {};
909 return $self->{related_resultsets}{$rel} ||= do {
910 #warn "fetching related resultset for rel '$rel'";
911 my $rel_obj = $self->result_source->relationship_info($rel);
912 $self->throw_exception(
913 "search_related: result source '" . $self->result_source->name .
914 "' has no such relationship ${rel}")
915 unless $rel_obj; #die Dumper $self->{attrs};
917 my $rs = $self->search(undef, { join => $rel });
918 my $alias = defined $rs->{attrs}{seen_join}{$rel}
919 && $rs->{attrs}{seen_join}{$rel} > 1
920 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
923 $self->result_source->schema->resultset($rel_obj->{class}
933 =head2 throw_exception
935 See Schema's throw_exception
939 sub throw_exception {
941 $self->result_source->schema->throw_exception(@_);
946 The resultset takes various attributes that modify its behavior. Here's an
951 Which column(s) to order the results by. This is currently passed through
952 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
956 =head3 Arguments: (arrayref)
958 Shortcut to request a particular set of columns to be retrieved. Adds
959 C<me.> onto the start of any column without a C<.> in it and sets C<select>
960 from that, then auto-populates C<as> from C<select> as normal. (You may also
961 use the C<cols> attribute, as in earlier versions of DBIC.)
963 =head2 include_columns
965 =head3 Arguments: (arrayref)
967 Shortcut to include additional columns in the returned results - for example
969 { include_columns => ['foo.name'], join => ['foo'] }
971 would add a 'name' column to the information passed to object inflation
975 =head3 Arguments: (arrayref)
977 Indicates which columns should be selected from the storage. You can use
978 column names, or in the case of RDBMS back ends, function or stored procedure
981 $rs = $schema->resultset('Foo')->search(
986 { count => 'column_to_count' },
987 { sum => 'column_to_sum' }
992 When you use function/stored procedure names and do not supply an C<as>
993 attribute, the column names returned are storage-dependent. E.g. MySQL would
994 return a column named C<count(column_to_count)> in the above example.
998 =head3 Arguments: (arrayref)
1000 Indicates column names for object inflation. This is used in conjunction with
1001 C<select>, usually when C<select> contains one or more function or stored
1004 $rs = $schema->resultset('Foo')->search(
1009 { count => 'column2' }
1011 as => [qw/ column1 column2_count /]
1015 my $foo = $rs->first(); # get the first Foo
1017 If the object against which the search is performed already has an accessor
1018 matching a column name specified in C<as>, the value can be retrieved using
1019 the accessor as normal:
1021 my $column1 = $foo->column1();
1023 If on the other hand an accessor does not exist in the object, you need to
1024 use C<get_column> instead:
1026 my $column2_count = $foo->get_column('column2_count');
1028 You can create your own accessors if required - see
1029 L<DBIx::Class::Manual::Cookbook> for details.
1033 Contains a list of relationships that should be joined for this query. For
1036 # Get CDs by Nine Inch Nails
1037 my $rs = $schema->resultset('CD')->search(
1038 { 'artist.name' => 'Nine Inch Nails' },
1039 { join => 'artist' }
1042 Can also contain a hash reference to refer to the other relation's relations.
1045 package MyApp::Schema::Track;
1046 use base qw/DBIx::Class/;
1047 __PACKAGE__->table('track');
1048 __PACKAGE__->add_columns(qw/trackid cd position title/);
1049 __PACKAGE__->set_primary_key('trackid');
1050 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1053 # In your application
1054 my $rs = $schema->resultset('Artist')->search(
1055 { 'track.title' => 'Teardrop' },
1057 join => { cd => 'track' },
1058 order_by => 'artist.name',
1062 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1063 similarly for a third time). For e.g.
1065 my $rs = $schema->resultset('Artist')->search(
1066 { 'cds.title' => 'Foo',
1067 'cds_2.title' => 'Bar' },
1068 { join => [ qw/cds cds/ ] });
1070 will return a set of all artists that have both a cd with title Foo and a cd
1073 If you want to fetch related objects from other tables as well, see C<prefetch>
1078 =head3 Arguments: arrayref/hashref
1080 Contains one or more relationships that should be fetched along with the main
1081 query (when they are accessed afterwards they will have already been
1082 "prefetched"). This is useful for when you know you will need the related
1083 objects, because it saves at least one query:
1085 my $rs = $schema->resultset('Tag')->search(
1094 The initial search results in SQL like the following:
1096 SELECT tag.*, cd.*, artist.* FROM tag
1097 JOIN cd ON tag.cd = cd.cdid
1098 JOIN artist ON cd.artist = artist.artistid
1100 L<DBIx::Class> has no need to go back to the database when we access the
1101 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1104 Simple prefetches will be joined automatically, so there is no need
1105 for a C<join> attribute in the above search. If you're prefetching to
1106 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1107 specify the join as well.
1109 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1110 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1111 with an accessor type of 'single' or 'filter').
1115 =head3 Arguments: (arrayref)
1117 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1118 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1121 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1122 C<join> will usually do what you need and it is strongly recommended that you
1123 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1125 In simple terms, C<from> works as follows:
1128 { <alias> => <table>, -join-type => 'inner|left|right' }
1129 [] # nested JOIN (optional)
1130 { <table.column> = <foreign_table.foreign_key> }
1136 ON <table.column> = <foreign_table.foreign_key>
1138 An easy way to follow the examples below is to remember the following:
1140 Anything inside "[]" is a JOIN
1141 Anything inside "{}" is a condition for the enclosing JOIN
1143 The following examples utilize a "person" table in a family tree application.
1144 In order to express parent->child relationships, this table is self-joined:
1146 # Person->belongs_to('father' => 'Person');
1147 # Person->belongs_to('mother' => 'Person');
1149 C<from> can be used to nest joins. Here we return all children with a father,
1150 then search against all mothers of those children:
1152 $rs = $schema->resultset('Person')->search(
1155 alias => 'mother', # alias columns in accordance with "from"
1157 { mother => 'person' },
1160 { child => 'person' },
1162 { father => 'person' },
1163 { 'father.person_id' => 'child.father_id' }
1166 { 'mother.person_id' => 'child.mother_id' }
1173 # SELECT mother.* FROM person mother
1176 # JOIN person father
1177 # ON ( father.person_id = child.father_id )
1179 # ON ( mother.person_id = child.mother_id )
1181 The type of any join can be controlled manually. To search against only people
1182 with a father in the person table, we could explicitly use C<INNER JOIN>:
1184 $rs = $schema->resultset('Person')->search(
1187 alias => 'child', # alias columns in accordance with "from"
1189 { child => 'person' },
1191 { father => 'person', -join-type => 'inner' },
1192 { 'father.id' => 'child.father_id' }
1199 # SELECT child.* FROM person child
1200 # INNER JOIN person father ON child.father_id = father.id
1204 For a paged resultset, specifies which page to retrieve. Leave unset
1205 for an unpaged resultset.
1209 For a paged resultset, how many rows per page:
1213 Can also be used to simulate an SQL C<LIMIT>.
1217 =head3 Arguments: (arrayref)
1219 A arrayref of columns to group by. Can include columns of joined tables.
1221 group_by => [qw/ column1 column2 ... /]
1225 Set to 1 to group by all columns.
1227 For more examples of using these attributes, see
1228 L<DBIx::Class::Manual::Cookbook>.