1 package DBIx::Class::ResultSet;
11 use Scalar::Util qw/weaken/;
13 use base qw/DBIx::Class/;
14 __PACKAGE__->load_components(qw/AccessorGroup/);
15 __PACKAGE__->mk_group_accessors('simple' => qw/result_source result_class/);
19 DBIx::Class::ResultSet - Responsible for fetching and creating resultset.
23 my $rs = $schema->resultset('User')->search(registered => 1);
24 my @rows = $schema->resultset('CD')->search(year => 2005);
28 The resultset is also known as an iterator. It is responsible for handling
29 queries that may return an arbitrary number of rows, e.g. via L</search>
30 or a C<has_many> relationship.
32 In the examples below, the following table classes are used:
34 package MyApp::Schema::Artist;
35 use base qw/DBIx::Class/;
36 __PACKAGE__->load_components(qw/Core/);
37 __PACKAGE__->table('artist');
38 __PACKAGE__->add_columns(qw/artistid name/);
39 __PACKAGE__->set_primary_key('artistid');
40 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
43 package MyApp::Schema::CD;
44 use base qw/DBIx::Class/;
45 __PACKAGE__->load_components(qw/Core/);
46 __PACKAGE__->table('cd');
47 __PACKAGE__->add_columns(qw/cdid artist title year/);
48 __PACKAGE__->set_primary_key('cdid');
49 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
58 =item Arguments: ($source, \%$attrs)
62 The resultset constructor. Takes a source object (usually a
63 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
64 L</ATTRIBUTES> below). Does not perform any queries -- these are
65 executed as needed by the other methods.
67 Generally you won't need to construct a resultset manually. You'll
68 automatically get one from e.g. a L</search> called in scalar context:
70 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
76 return $class->new_result(@_) if ref $class;
78 my ($source, $attrs) = @_;
80 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
81 #use Data::Dumper; warn Dumper($attrs);
82 my $alias = ($attrs->{alias} ||= 'me');
84 $attrs->{columns} ||= delete $attrs->{cols} if $attrs->{cols};
85 delete $attrs->{as} if $attrs->{columns};
86 $attrs->{columns} ||= [ $source->columns ] unless $attrs->{select};
88 map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}}
89 ] if $attrs->{columns};
91 map { m/^\Q$alias.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}}
93 if (my $include = delete $attrs->{include_columns}) {
94 push(@{$attrs->{select}}, @$include);
95 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1; } @$include);
97 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
99 $attrs->{from} ||= [ { $alias => $source->from } ];
100 $attrs->{seen_join} ||= {};
102 if (my $join = delete $attrs->{join}) {
103 foreach my $j (ref $join eq 'ARRAY' ? @$join : ($join)) {
104 if (ref $j eq 'HASH') {
105 $seen{$_} = 1 foreach keys %$j;
110 push(@{$attrs->{from}}, $source->resolve_join(
111 $join, $attrs->{alias}, $attrs->{seen_join})
115 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
116 $attrs->{order_by} = [ $attrs->{order_by} ] if
117 $attrs->{order_by} and !ref($attrs->{order_by});
118 $attrs->{order_by} ||= [];
120 my $collapse = $attrs->{collapse} || {};
121 if (my $prefetch = delete $attrs->{prefetch}) {
123 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
124 if ( ref $p eq 'HASH' ) {
125 foreach my $key (keys %$p) {
126 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
130 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
133 my @prefetch = $source->resolve_prefetch(
134 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
135 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
136 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
138 push(@{$attrs->{order_by}}, @pre_order);
140 $attrs->{collapse} = $collapse;
141 # use Data::Dumper; warn Dumper($collapse) if keys %{$collapse};
143 if ($attrs->{page}) {
144 $attrs->{rows} ||= 10;
145 $attrs->{offset} ||= 0;
146 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
150 result_source => $source,
151 result_class => $attrs->{result_class} || $source->result_class,
152 cond => $attrs->{where},
153 from => $attrs->{from},
154 collapse => $collapse,
156 page => delete $attrs->{page},
164 my @cds = $rs->search({ year => 2001 }); # "... WHERE year = 2001"
165 my $new_rs = $rs->search({ year => 2005 });
167 If you need to pass in additional attributes but no additional condition,
168 call it as C<search(undef, \%attrs);>.
170 # "SELECT name, artistid FROM $artist_table"
171 my @all_artists = $schema->resultset('Artist')->search(undef, {
172 columns => [qw/name artistid/],
183 my $attrs = { %{$self->{attrs}} };
184 my $having = delete $attrs->{having};
185 $attrs = { %$attrs, %{ pop(@_) } } if @_ > 1 and ref $_[$#_] eq 'HASH';
188 ? ((@_ == 1 || ref $_[0] eq "HASH")
191 ? $self->throw_exception(
192 "Odd number of arguments to search")
195 if (defined $where) {
196 $attrs->{where} = (defined $attrs->{where}
198 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
199 $where, $attrs->{where} ] }
203 if (defined $having) {
204 $attrs->{having} = (defined $attrs->{having}
206 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
207 $having, $attrs->{having} ] }
211 $rs = (ref $self)->new($self->result_source, $attrs);
217 return (wantarray ? $rs->all : $rs);
220 =head2 search_literal
222 my @obj = $rs->search_literal($literal_where_cond, @bind);
223 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
225 Pass a literal chunk of SQL to be added to the conditional part of the
231 my ($self, $cond, @vals) = @_;
232 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
233 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
234 return $self->search(\$cond, $attrs);
241 =item Arguments: (@colvalues) | (\%cols, \%attrs?)
245 Finds a row based on its primary key or unique constraint. For example:
247 my $cd = $schema->resultset('CD')->find(5);
249 Also takes an optional C<key> attribute, to search by a specific key or unique
250 constraint. For example:
252 my $cd = $schema->resultset('CD')->find(
254 artist => 'Massive Attack',
255 title => 'Mezzanine',
257 { key => 'artist_title' }
260 See also L</find_or_create> and L</update_or_create>.
265 my ($self, @vals) = @_;
266 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
268 my @cols = $self->result_source->primary_columns;
269 if (exists $attrs->{key}) {
270 my %uniq = $self->result_source->unique_constraints;
271 $self->throw_exception(
272 "Unknown key $attrs->{key} on '" . $self->result_source->name . "'"
273 ) unless exists $uniq{$attrs->{key}};
274 @cols = @{ $uniq{$attrs->{key}} };
276 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
277 $self->throw_exception(
278 "Can't find unless a primary key or unique constraint is defined"
282 if (ref $vals[0] eq 'HASH') {
283 $query = { %{$vals[0]} };
284 } elsif (@cols == @vals) {
286 @{$query}{@cols} = @vals;
290 foreach my $key (grep { ! m/\./ } keys %$query) {
291 $query->{"$self->{attrs}{alias}.$key"} = delete $query->{$key};
293 #warn Dumper($query);
296 my $rs = $self->search($query,$attrs);
297 return keys %{$rs->{collapse}} ? $rs->next : $rs->single;
299 return keys %{$self->{collapse}} ?
300 $self->search($query)->next :
301 $self->single($query);
305 =head2 search_related
307 $rs->search_related('relname', $cond?, $attrs?);
309 Search the specified relationship. Optionally specify a condition for matching
315 return shift->related_resultset(shift)->search(@_);
320 Returns a storage-driven cursor to the given resultset.
326 my $attrs = { %{$self->{attrs}} };
327 return $self->{cursor}
328 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
329 $attrs->{where},$attrs);
334 Inflates the first result without creating a cursor
339 my ($self, $where) = @_;
340 my $attrs = { %{$self->{attrs}} };
342 if (defined $attrs->{where}) {
345 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
346 $where, delete $attrs->{where} ]
349 $attrs->{where} = $where;
352 my @data = $self->result_source->storage->select_single(
353 $self->{from}, $attrs->{select},
354 $attrs->{where},$attrs);
355 return (@data ? $self->_construct_object(@data) : ());
361 Perform a search, but use C<LIKE> instead of equality as the condition. Note
362 that this is simply a convenience method; you most likely want to use
363 L</search> with specific operators.
365 For more information, see L<DBIx::Class::Manual::Cookbook>.
371 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
372 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
373 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
374 return $class->search($query, { %$attrs });
381 =item Arguments: ($first, $last)
385 Returns a subset of elements from the resultset.
390 my ($self, $min, $max) = @_;
391 my $attrs = { %{ $self->{attrs} || {} } };
392 $attrs->{offset} ||= 0;
393 $attrs->{offset} += $min;
394 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
395 my $slice = (ref $self)->new($self->result_source, $attrs);
396 return (wantarray ? $slice->all : $slice);
401 Returns the next element in the resultset (C<undef> is there is none).
403 Can be used to efficiently iterate over records in the resultset:
405 my $rs = $schema->resultset('CD')->search;
406 while (my $cd = $rs->next) {
414 if (@{$self->{all_cache} || []}) {
415 $self->{all_cache_position} ||= 0;
416 return $self->{all_cache}->[$self->{all_cache_position}++];
418 if ($self->{attrs}{cache}) {
419 $self->{all_cache_position} = 1;
420 return ($self->all)[0];
422 my @row = (exists $self->{stashed_row} ?
423 @{delete $self->{stashed_row}} :
426 # warn Dumper(\@row); use Data::Dumper;
427 return unless (@row);
428 return $self->_construct_object(@row);
431 sub _construct_object {
432 my ($self, @row) = @_;
433 my @as = @{ $self->{attrs}{as} };
435 my $info = $self->_collapse_result(\@as, \@row);
437 my $new = $self->result_class->inflate_result($self->result_source, @$info);
439 $new = $self->{attrs}{record_filter}->($new)
440 if exists $self->{attrs}{record_filter};
444 sub _collapse_result {
445 my ($self, $as, $row, $prefix) = @_;
450 foreach my $this_as (@$as) {
451 my $val = shift @copy;
452 if (defined $prefix) {
453 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
455 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
456 $const{$1||''}{$2} = $val;
459 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
460 $const{$1||''}{$2} = $val;
464 my $info = [ {}, {} ];
465 foreach my $key (keys %const) {
468 my @parts = split(/\./, $key);
469 foreach my $p (@parts) {
470 $target = $target->[1]->{$p} ||= [];
472 $target->[0] = $const{$key};
474 $info->[0] = $const{$key};
479 if (defined $prefix) {
481 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
482 } keys %{$self->{collapse}}
484 @collapse = keys %{$self->{collapse}};
488 my ($c) = sort { length $a <=> length $b } @collapse;
490 foreach my $p (split(/\./, $c)) {
491 $target = $target->[1]->{$p} ||= [];
493 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
494 my @co_key = @{$self->{collapse}{$c_prefix}};
495 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
496 my $tree = $self->_collapse_result($as, $row, $c_prefix);
499 !defined($tree->[0]->{$_}) ||
500 $co_check{$_} ne $tree->[0]->{$_}
503 last unless (@raw = $self->cursor->next);
504 $row = $self->{stashed_row} = \@raw;
505 $tree = $self->_collapse_result($as, $row, $c_prefix);
506 #warn Data::Dumper::Dumper($tree, $row);
516 Returns a reference to the result source for this recordset.
523 Performs an SQL C<COUNT> with the same query as the resultset was built
524 with to find the number of elements. If passed arguments, does a search
525 on the resultset and counts the results of that.
527 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
528 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
529 not support C<DISTINCT> with multiple columns. If you are using such a
530 database, you should only use columns from the main table in your C<group_by>
537 return $self->search(@_)->count if @_ and defined $_[0];
538 return scalar @{ $self->get_cache } if @{ $self->get_cache };
540 my $count = $self->_count;
541 return 0 unless $count;
543 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
544 $count = $self->{attrs}{rows} if
545 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
549 sub _count { # Separated out so pager can get the full count
551 my $select = { count => '*' };
552 my $attrs = { %{ $self->{attrs} } };
553 if (my $group_by = delete $attrs->{group_by}) {
554 delete $attrs->{having};
555 my @distinct = (ref $group_by ? @$group_by : ($group_by));
556 # todo: try CONCAT for multi-column pk
557 my @pk = $self->result_source->primary_columns;
559 foreach my $column (@distinct) {
560 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
561 @distinct = ($column);
567 $select = { count => { distinct => \@distinct } };
568 #use Data::Dumper; die Dumper $select;
571 $attrs->{select} = $select;
572 $attrs->{as} = [qw/count/];
574 # offset, order by and page are not needed to count. record_filter is cdbi
575 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
577 my ($count) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
583 Calls L</search_literal> with the passed arguments, then L</count>.
587 sub count_literal { shift->search_literal(@_)->count; }
591 Returns all elements in the resultset. Called implicitly if the resultset
592 is returned in list context.
598 return @{ $self->get_cache } if @{ $self->get_cache };
602 if (keys %{$self->{collapse}}) {
603 # Using $self->cursor->all is really just an optimisation.
604 # If we're collapsing has_many prefetches it probably makes
605 # very little difference, and this is cleaner than hacking
606 # _construct_object to survive the approach
607 $self->cursor->reset;
608 my @row = $self->cursor->next;
610 push(@obj, $self->_construct_object(@row));
611 @row = (exists $self->{stashed_row}
612 ? @{delete $self->{stashed_row}}
613 : $self->cursor->next);
616 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
619 $self->set_cache(\@obj) if $self->{attrs}{cache};
625 Resets the resultset's cursor, so you can iterate through the elements again.
631 $self->{all_cache_position} = 0;
632 $self->cursor->reset;
638 Resets the resultset and returns the first element.
643 return $_[0]->reset->next;
650 =item Arguments: (\%values)
654 Sets the specified columns in the resultset to the supplied values.
659 my ($self, $values) = @_;
660 $self->throw_exception("Values for update must be a hash")
661 unless ref $values eq 'HASH';
662 return $self->result_source->storage->update(
663 $self->result_source->from, $values, $self->{cond}
671 =item Arguments: (\%values)
675 Fetches all objects and updates them one at a time. Note that C<update_all>
676 will run cascade triggers while L</update> will not.
681 my ($self, $values) = @_;
682 $self->throw_exception("Values for update must be a hash")
683 unless ref $values eq 'HASH';
684 foreach my $obj ($self->all) {
685 $obj->set_columns($values)->update;
692 Deletes the contents of the resultset from its result source.
700 if (!ref($self->{cond})) {
702 # No-op. No condition, we're deleting everything
704 } elsif (ref $self->{cond} eq 'ARRAY') {
706 $del = [ map { my %hash;
707 foreach my $key (keys %{$_}) {
709 $hash{$1} = $_->{$key};
710 }; \%hash; } @{$self->{cond}} ];
712 } elsif (ref $self->{cond} eq 'HASH') {
714 if ((keys %{$self->{cond}})[0] eq '-and') {
716 $del->{-and} = [ map { my %hash;
717 foreach my $key (keys %{$_}) {
719 $hash{$1} = $_->{$key};
720 }; \%hash; } @{$self->{cond}{-and}} ];
724 foreach my $key (keys %{$self->{cond}}) {
726 $del->{$1} = $self->{cond}{$key};
731 $self->throw_exception(
732 "Can't delete on resultset with condition unless hash or array"
736 $self->result_source->storage->delete($self->result_source->from, $del);
742 Fetches all objects and deletes them one at a time. Note that C<delete_all>
743 will run cascade triggers while L</delete> will not.
749 $_->delete for $self->all;
755 Returns a L<Data::Page> object for the current resultset. Only makes
756 sense for queries with a C<page> attribute.
762 my $attrs = $self->{attrs};
763 $self->throw_exception("Can't create pager for non-paged rs")
764 unless $self->{page};
765 $attrs->{rows} ||= 10;
766 return $self->{pager} ||= Data::Page->new(
767 $self->_count, $attrs->{rows}, $self->{page});
774 =item Arguments: ($page_num)
778 Returns a new resultset for the specified page.
783 my ($self, $page) = @_;
784 my $attrs = { %{$self->{attrs}} };
785 $attrs->{page} = $page;
786 return (ref $self)->new($self->result_source, $attrs);
793 =item Arguments: (\%vals)
797 Creates a result in the resultset's result class.
802 my ($self, $values) = @_;
803 $self->throw_exception( "new_result needs a hash" )
804 unless (ref $values eq 'HASH');
805 $self->throw_exception(
806 "Can't abstract implicit construct, condition not a hash"
807 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
809 my $alias = $self->{attrs}{alias};
810 foreach my $key (keys %{$self->{cond}||{}}) {
811 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
813 my $obj = $self->result_class->new(\%new);
814 $obj->result_source($self->result_source) if $obj->can('result_source');
822 =item Arguments: (\%vals)
826 Inserts a record into the resultset and returns the object.
828 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
833 my ($self, $attrs) = @_;
834 $self->throw_exception( "create needs a hashref" )
835 unless ref $attrs eq 'HASH';
836 return $self->new_result($attrs)->insert;
839 =head2 find_or_create
843 =item Arguments: (\%vals, \%attrs?)
847 $class->find_or_create({ key => $val, ... });
849 Searches for a record matching the search condition; if it doesn't find one,
850 creates one and returns that instead.
852 my $cd = $schema->resultset('CD')->find_or_create({
854 artist => 'Massive Attack',
855 title => 'Mezzanine',
859 Also takes an optional C<key> attribute, to search by a specific key or unique
860 constraint. For example:
862 my $cd = $schema->resultset('CD')->find_or_create(
864 artist => 'Massive Attack',
865 title => 'Mezzanine',
867 { key => 'artist_title' }
870 See also L</find> and L</update_or_create>.
876 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
877 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
878 my $exists = $self->find($hash, $attrs);
879 return defined $exists ? $exists : $self->create($hash);
882 =head2 update_or_create
884 $class->update_or_create({ key => $val, ... });
886 First, search for an existing row matching one of the unique constraints
887 (including the primary key) on the source of this resultset. If a row is
888 found, update it with the other given column values. Otherwise, create a new
891 Takes an optional C<key> attribute to search on a specific unique constraint.
894 # In your application
895 my $cd = $schema->resultset('CD')->update_or_create(
897 artist => 'Massive Attack',
898 title => 'Mezzanine',
901 { key => 'artist_title' }
904 If no C<key> is specified, it searches on all unique constraints defined on the
905 source, including the primary key.
907 If the C<key> is specified as C<primary>, search only on the primary key.
909 See also L</find> and L</find_or_create>.
913 sub update_or_create {
915 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
916 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
918 my %unique_constraints = $self->result_source->unique_constraints;
919 my @constraint_names = (exists $attrs->{key}
921 : keys %unique_constraints);
924 foreach my $name (@constraint_names) {
925 my @unique_cols = @{ $unique_constraints{$name} };
927 map { $_ => $hash->{$_} }
928 grep { exists $hash->{$_} }
931 push @unique_hashes, \%unique_hash
932 if (scalar keys %unique_hash == scalar @unique_cols);
935 if (@unique_hashes) {
936 my $row = $self->single(\@unique_hashes);
938 $row->set_columns($hash);
944 return $self->create($hash);
949 Gets the contents of the cache for the resultset.
954 shift->{all_cache} || [];
959 Sets the contents of the cache for the resultset. Expects an arrayref
960 of objects of the same class as those produced by the resultset.
965 my ( $self, $data ) = @_;
966 $self->throw_exception("set_cache requires an arrayref")
967 if ref $data ne 'ARRAY';
968 my $result_class = $self->result_class;
970 $self->throw_exception(
971 "cannot cache object of type '$_', expected '$result_class'"
972 ) if ref $_ ne $result_class;
974 $self->{all_cache} = $data;
979 Clears the cache for the resultset.
984 shift->set_cache([]);
987 =head2 related_resultset
989 Returns a related resultset for the supplied relationship name.
991 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
995 sub related_resultset {
996 my ( $self, $rel, @rest ) = @_;
997 $self->{related_resultsets} ||= {};
998 return $self->{related_resultsets}{$rel} ||= do {
999 #warn "fetching related resultset for rel '$rel'";
1000 my $rel_obj = $self->result_source->relationship_info($rel);
1001 $self->throw_exception(
1002 "search_related: result source '" . $self->result_source->name .
1003 "' has no such relationship ${rel}")
1004 unless $rel_obj; #die Dumper $self->{attrs};
1006 my $rs = $self->search(undef, { join => $rel });
1007 my $alias = defined $rs->{attrs}{seen_join}{$rel}
1008 && $rs->{attrs}{seen_join}{$rel} > 1
1009 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
1012 $self->result_source->schema->resultset($rel_obj->{class}
1022 =head2 throw_exception
1024 See Schema's throw_exception
1028 sub throw_exception {
1030 $self->result_source->schema->throw_exception(@_);
1035 XXX: FIXME: Attributes docs need clearing up
1037 The resultset takes various attributes that modify its behavior. Here's an
1042 Which column(s) to order the results by. This is currently passed
1043 through directly to SQL, so you can give e.g. C<year DESC> for a
1044 descending order on the column `year'.
1050 =item Arguments: (\@columns)
1054 Shortcut to request a particular set of columns to be retrieved. Adds
1055 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1056 from that, then auto-populates C<as> from C<select> as normal. (You may also
1057 use the C<cols> attribute, as in earlier versions of DBIC.)
1059 =head2 include_columns
1063 =item Arguments: (\@columns)
1067 Shortcut to include additional columns in the returned results - for example
1069 $schema->resultset('CD')->search(undef, {
1070 include_columns => ['artist.name'],
1074 would return all CDs and include a 'name' column to the information
1075 passed to object inflation
1081 =item Arguments: (\@columns)
1085 Indicates which columns should be selected from the storage. You can use
1086 column names, or in the case of RDBMS back ends, function or stored procedure
1089 $rs = $schema->resultset('Employee')->search(undef, {
1092 { count => 'employeeid' },
1097 When you use function/stored procedure names and do not supply an C<as>
1098 attribute, the column names returned are storage-dependent. E.g. MySQL would
1099 return a column named C<count(employeeid)> in the above example.
1105 =item Arguments: (\@names)
1109 Indicates column names for object inflation. This is used in conjunction with
1110 C<select>, usually when C<select> contains one or more function or stored
1113 $rs = $schema->resultset('Employee')->search(undef, {
1116 { count => 'employeeid' }
1118 as => ['name', 'employee_count'],
1121 my $employee = $rs->first(); # get the first Employee
1123 If the object against which the search is performed already has an accessor
1124 matching a column name specified in C<as>, the value can be retrieved using
1125 the accessor as normal:
1127 my $name = $employee->name();
1129 If on the other hand an accessor does not exist in the object, you need to
1130 use C<get_column> instead:
1132 my $employee_count = $employee->get_column('employee_count');
1134 You can create your own accessors if required - see
1135 L<DBIx::Class::Manual::Cookbook> for details.
1139 Contains a list of relationships that should be joined for this query. For
1142 # Get CDs by Nine Inch Nails
1143 my $rs = $schema->resultset('CD')->search(
1144 { 'artist.name' => 'Nine Inch Nails' },
1145 { join => 'artist' }
1148 Can also contain a hash reference to refer to the other relation's relations.
1151 package MyApp::Schema::Track;
1152 use base qw/DBIx::Class/;
1153 __PACKAGE__->table('track');
1154 __PACKAGE__->add_columns(qw/trackid cd position title/);
1155 __PACKAGE__->set_primary_key('trackid');
1156 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1159 # In your application
1160 my $rs = $schema->resultset('Artist')->search(
1161 { 'track.title' => 'Teardrop' },
1163 join => { cd => 'track' },
1164 order_by => 'artist.name',
1168 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1169 similarly for a third time). For e.g.
1171 my $rs = $schema->resultset('Artist')->search({
1172 'cds.title' => 'Down to Earth',
1173 'cds_2.title' => 'Popular',
1175 join => [ qw/cds cds/ ],
1178 will return a set of all artists that have both a cd with title 'Down
1179 to Earth' and a cd with title 'Popular'.
1181 If you want to fetch related objects from other tables as well, see C<prefetch>
1188 =item Arguments: (\@relationships)
1192 Contains one or more relationships that should be fetched along with the main
1193 query (when they are accessed afterwards they will have already been
1194 "prefetched"). This is useful for when you know you will need the related
1195 objects, because it saves at least one query:
1197 my $rs = $schema->resultset('Tag')->search(
1206 The initial search results in SQL like the following:
1208 SELECT tag.*, cd.*, artist.* FROM tag
1209 JOIN cd ON tag.cd = cd.cdid
1210 JOIN artist ON cd.artist = artist.artistid
1212 L<DBIx::Class> has no need to go back to the database when we access the
1213 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1216 Simple prefetches will be joined automatically, so there is no need
1217 for a C<join> attribute in the above search. If you're prefetching to
1218 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1219 specify the join as well.
1221 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1222 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1223 with an accessor type of 'single' or 'filter').
1229 =item Arguments: (\@array)
1233 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1234 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1237 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1238 C<join> will usually do what you need and it is strongly recommended that you
1239 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1241 In simple terms, C<from> works as follows:
1244 { <alias> => <table>, -join-type => 'inner|left|right' }
1245 [] # nested JOIN (optional)
1246 { <table.column> = <foreign_table.foreign_key> }
1252 ON <table.column> = <foreign_table.foreign_key>
1254 An easy way to follow the examples below is to remember the following:
1256 Anything inside "[]" is a JOIN
1257 Anything inside "{}" is a condition for the enclosing JOIN
1259 The following examples utilize a "person" table in a family tree application.
1260 In order to express parent->child relationships, this table is self-joined:
1262 # Person->belongs_to('father' => 'Person');
1263 # Person->belongs_to('mother' => 'Person');
1265 C<from> can be used to nest joins. Here we return all children with a father,
1266 then search against all mothers of those children:
1268 $rs = $schema->resultset('Person')->search(
1271 alias => 'mother', # alias columns in accordance with "from"
1273 { mother => 'person' },
1276 { child => 'person' },
1278 { father => 'person' },
1279 { 'father.person_id' => 'child.father_id' }
1282 { 'mother.person_id' => 'child.mother_id' }
1289 # SELECT mother.* FROM person mother
1292 # JOIN person father
1293 # ON ( father.person_id = child.father_id )
1295 # ON ( mother.person_id = child.mother_id )
1297 The type of any join can be controlled manually. To search against only people
1298 with a father in the person table, we could explicitly use C<INNER JOIN>:
1300 $rs = $schema->resultset('Person')->search(
1303 alias => 'child', # alias columns in accordance with "from"
1305 { child => 'person' },
1307 { father => 'person', -join-type => 'inner' },
1308 { 'father.id' => 'child.father_id' }
1315 # SELECT child.* FROM person child
1316 # INNER JOIN person father ON child.father_id = father.id
1322 =item Arguments: ($page)
1326 For a paged resultset, specifies which page to retrieve. Leave unset
1327 for an unpaged resultset.
1333 =item Arguments: ($rows)
1337 For a paged resultset, specifies how many rows are in each page:
1341 Can also be used to simulate an SQL C<LIMIT>.
1347 =item Arguments: (\@columns)
1351 A arrayref of columns to group by. Can include columns of joined tables.
1353 group_by => [qw/ column1 column2 ... /]
1357 Set to 1 to group by all columns.
1361 Set to 1 to cache search results. This prevents extra SQL queries if you
1362 revisit rows in your ResultSet:
1364 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1366 while( my $artist = $resultset->next ) {
1370 $rs->first; # without cache, this would issue a query
1372 By default, searches are not cached.
1374 For more examples of using these attributes, see
1375 L<DBIx::Class::Manual::Cookbook>.