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');
56 =head3 Arguments: ($source, \%$attrs)
58 The resultset constructor. Takes a source object (usually a
59 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
60 L</ATTRIBUTES> below). Does not perform any queries -- these are
61 executed as needed by the other methods.
63 Generally you won't need to construct a resultset manually. You'll
64 automatically get one from e.g. a L</search> called in scalar context:
66 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
72 return $class->new_result(@_) if ref $class;
74 my ($source, $attrs) = @_;
76 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
77 #use Data::Dumper; warn Dumper($attrs);
78 my $alias = ($attrs->{alias} ||= 'me');
80 $attrs->{columns} ||= delete $attrs->{cols} if $attrs->{cols};
81 delete $attrs->{as} if $attrs->{columns};
82 $attrs->{columns} ||= [ $source->columns ] unless $attrs->{select};
84 map { m/\./ ? $_ : "${alias}.$_" } @{delete $attrs->{columns}}
85 ] if $attrs->{columns};
87 map { m/^\Q$alias.\E(.+)$/ ? $1 : $_ } @{$attrs->{select}}
89 if (my $include = delete $attrs->{include_columns}) {
90 push(@{$attrs->{select}}, @$include);
91 push(@{$attrs->{as}}, map { m/([^.]+)$/; $1; } @$include);
93 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
95 $attrs->{from} ||= [ { $alias => $source->from } ];
96 $attrs->{seen_join} ||= {};
98 if (my $join = delete $attrs->{join}) {
99 foreach my $j (ref $join eq 'ARRAY' ? @$join : ($join)) {
100 if (ref $j eq 'HASH') {
101 $seen{$_} = 1 foreach keys %$j;
106 push(@{$attrs->{from}}, $source->resolve_join(
107 $join, $attrs->{alias}, $attrs->{seen_join})
111 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
112 $attrs->{order_by} = [ $attrs->{order_by} ] if
113 $attrs->{order_by} and !ref($attrs->{order_by});
114 $attrs->{order_by} ||= [];
116 my $collapse = $attrs->{collapse} || {};
117 if (my $prefetch = delete $attrs->{prefetch}) {
119 foreach my $p (ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch)) {
120 if ( ref $p eq 'HASH' ) {
121 foreach my $key (keys %$p) {
122 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
126 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
129 my @prefetch = $source->resolve_prefetch(
130 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
131 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
132 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
134 push(@{$attrs->{order_by}}, @pre_order);
136 $attrs->{collapse} = $collapse;
137 # use Data::Dumper; warn Dumper($collapse) if keys %{$collapse};
139 if ($attrs->{page}) {
140 $attrs->{rows} ||= 10;
141 $attrs->{offset} ||= 0;
142 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
146 result_source => $source,
147 result_class => $attrs->{result_class} || $source->result_class,
148 cond => $attrs->{where},
149 from => $attrs->{from},
150 collapse => $collapse,
152 page => delete $attrs->{page},
160 my @cds = $rs->search({ year => 2001 }); # "... WHERE year = 2001"
161 my $new_rs = $rs->search({ year => 2005 });
163 If you need to pass in additional attributes but no additional condition,
164 call it as C<search(undef, \%attrs);>.
166 # "SELECT name, artistid FROM $artist_table"
167 my @all_artists = $schema->resultset('Artist')->search(undef, {
168 columns => [qw/name artistid/],
179 my $attrs = { %{$self->{attrs}} };
180 my $having = delete $attrs->{having};
181 $attrs = { %$attrs, %{ pop(@_) } } if @_ > 1 and ref $_[$#_] eq 'HASH';
184 ? ((@_ == 1 || ref $_[0] eq "HASH")
187 ? $self->throw_exception(
188 "Odd number of arguments to search")
191 if (defined $where) {
192 $attrs->{where} = (defined $attrs->{where}
194 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
195 $where, $attrs->{where} ] }
199 if (defined $having) {
200 $attrs->{having} = (defined $attrs->{having}
202 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
203 $having, $attrs->{having} ] }
207 $rs = (ref $self)->new($self->result_source, $attrs);
213 return (wantarray ? $rs->all : $rs);
216 =head2 search_literal
218 my @obj = $rs->search_literal($literal_where_cond, @bind);
219 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
221 Pass a literal chunk of SQL to be added to the conditional part of the
227 my ($self, $cond, @vals) = @_;
228 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
229 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
230 return $self->search(\$cond, $attrs);
235 =head3 Arguments: (@colvalues) | (\%cols, \%attrs?)
237 Finds a row based on its primary key or unique constraint. For example:
239 my $cd = $schema->resultset('CD')->find(5);
241 Also takes an optional C<key> attribute, to search by a specific key or unique
242 constraint. For example:
244 my $cd = $schema->resultset('CD')->find(
246 artist => 'Massive Attack',
247 title => 'Mezzanine',
249 { key => 'artist_title' }
252 See also L</find_or_create> and L</update_or_create>.
257 my ($self, @vals) = @_;
258 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
260 my @cols = $self->result_source->primary_columns;
261 if (exists $attrs->{key}) {
262 my %uniq = $self->result_source->unique_constraints;
263 $self->throw_exception(
264 "Unknown key $attrs->{key} on '" . $self->result_source->name . "'"
265 ) unless exists $uniq{$attrs->{key}};
266 @cols = @{ $uniq{$attrs->{key}} };
268 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
269 $self->throw_exception(
270 "Can't find unless a primary key or unique constraint is defined"
274 if (ref $vals[0] eq 'HASH') {
275 $query = { %{$vals[0]} };
276 } elsif (@cols == @vals) {
278 @{$query}{@cols} = @vals;
282 foreach my $key (grep { ! m/\./ } keys %$query) {
283 $query->{"$self->{attrs}{alias}.$key"} = delete $query->{$key};
285 #warn Dumper($query);
288 my $rs = $self->search($query,$attrs);
289 return keys %{$rs->{collapse}} ? $rs->next : $rs->single;
291 return keys %{$self->{collapse}} ?
292 $self->search($query)->next :
293 $self->single($query);
297 =head2 search_related
299 $rs->search_related('relname', $cond?, $attrs?);
301 Search the specified relationship. Optionally specify a condition for matching
307 return shift->related_resultset(shift)->search(@_);
312 Returns a storage-driven cursor to the given resultset.
318 my $attrs = { %{$self->{attrs}} };
319 return $self->{cursor}
320 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
321 $attrs->{where},$attrs);
326 Inflates the first result without creating a cursor
331 my ($self, $where) = @_;
332 my $attrs = { %{$self->{attrs}} };
334 if (defined $attrs->{where}) {
337 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
338 $where, delete $attrs->{where} ]
341 $attrs->{where} = $where;
344 my @data = $self->result_source->storage->select_single(
345 $self->{from}, $attrs->{select},
346 $attrs->{where},$attrs);
347 return (@data ? $self->_construct_object(@data) : ());
353 Perform a search, but use C<LIKE> instead of equality as the condition. Note
354 that this is simply a convenience method; you most likely want to use
355 L</search> with specific operators.
357 For more information, see L<DBIx::Class::Manual::Cookbook>.
363 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
364 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
365 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
366 return $class->search($query, { %$attrs });
371 =head3 Arguments: ($first, $last)
373 Returns a subset of elements from the resultset.
378 my ($self, $min, $max) = @_;
379 my $attrs = { %{ $self->{attrs} || {} } };
380 $attrs->{offset} ||= 0;
381 $attrs->{offset} += $min;
382 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
383 my $slice = (ref $self)->new($self->result_source, $attrs);
384 return (wantarray ? $slice->all : $slice);
389 Returns the next element in the resultset (C<undef> is there is none).
391 Can be used to efficiently iterate over records in the resultset:
393 my $rs = $schema->resultset('CD')->search;
394 while (my $cd = $rs->next) {
402 if (@{$self->{all_cache} || []}) {
403 $self->{all_cache_position} ||= 0;
404 return $self->{all_cache}->[$self->{all_cache_position}++];
406 if ($self->{attrs}{cache}) {
407 $self->{all_cache_position} = 1;
408 return ($self->all)[0];
410 my @row = (exists $self->{stashed_row} ?
411 @{delete $self->{stashed_row}} :
414 # warn Dumper(\@row); use Data::Dumper;
415 return unless (@row);
416 return $self->_construct_object(@row);
419 sub _construct_object {
420 my ($self, @row) = @_;
421 my @as = @{ $self->{attrs}{as} };
423 my $info = $self->_collapse_result(\@as, \@row);
425 my $new = $self->result_class->inflate_result($self->result_source, @$info);
427 $new = $self->{attrs}{record_filter}->($new)
428 if exists $self->{attrs}{record_filter};
432 sub _collapse_result {
433 my ($self, $as, $row, $prefix) = @_;
438 foreach my $this_as (@$as) {
439 my $val = shift @copy;
440 if (defined $prefix) {
441 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
443 $remain =~ /^(?:(.*)\.)?([^.]+)$/;
444 $const{$1||''}{$2} = $val;
447 $this_as =~ /^(?:(.*)\.)?([^.]+)$/;
448 $const{$1||''}{$2} = $val;
452 my $info = [ {}, {} ];
453 foreach my $key (keys %const) {
456 my @parts = split(/\./, $key);
457 foreach my $p (@parts) {
458 $target = $target->[1]->{$p} ||= [];
460 $target->[0] = $const{$key};
462 $info->[0] = $const{$key};
467 if (defined $prefix) {
469 m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()
470 } keys %{$self->{collapse}}
472 @collapse = keys %{$self->{collapse}};
476 my ($c) = sort { length $a <=> length $b } @collapse;
478 foreach my $p (split(/\./, $c)) {
479 $target = $target->[1]->{$p} ||= [];
481 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
482 my @co_key = @{$self->{collapse}{$c_prefix}};
483 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
484 my $tree = $self->_collapse_result($as, $row, $c_prefix);
487 !defined($tree->[0]->{$_}) ||
488 $co_check{$_} ne $tree->[0]->{$_}
491 last unless (@raw = $self->cursor->next);
492 $row = $self->{stashed_row} = \@raw;
493 $tree = $self->_collapse_result($as, $row, $c_prefix);
494 #warn Data::Dumper::Dumper($tree, $row);
504 Returns a reference to the result source for this recordset.
511 Performs an SQL C<COUNT> with the same query as the resultset was built
512 with to find the number of elements. If passed arguments, does a search
513 on the resultset and counts the results of that.
515 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
516 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
517 not support C<DISTINCT> with multiple columns. If you are using such a
518 database, you should only use columns from the main table in your C<group_by>
525 return $self->search(@_)->count if @_ and defined $_[0];
526 return scalar @{ $self->get_cache } if @{ $self->get_cache };
528 my $count = $self->_count;
529 return 0 unless $count;
531 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
532 $count = $self->{attrs}{rows} if
533 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
537 sub _count { # Separated out so pager can get the full count
539 my $select = { count => '*' };
540 my $attrs = { %{ $self->{attrs} } };
541 if (my $group_by = delete $attrs->{group_by}) {
542 delete $attrs->{having};
543 my @distinct = (ref $group_by ? @$group_by : ($group_by));
544 # todo: try CONCAT for multi-column pk
545 my @pk = $self->result_source->primary_columns;
547 foreach my $column (@distinct) {
548 if ($column =~ qr/^(?:\Q$attrs->{alias}.\E)?$pk[0]$/) {
549 @distinct = ($column);
555 $select = { count => { distinct => \@distinct } };
556 #use Data::Dumper; die Dumper $select;
559 $attrs->{select} = $select;
560 $attrs->{as} = [qw/count/];
562 # offset, order by and page are not needed to count. record_filter is cdbi
563 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
565 my ($count) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
571 Calls L</search_literal> with the passed arguments, then L</count>.
575 sub count_literal { shift->search_literal(@_)->count; }
579 Returns all elements in the resultset. Called implictly if the resultset
580 is returned in list context.
586 return @{ $self->get_cache } if @{ $self->get_cache };
590 if (keys %{$self->{collapse}}) {
591 # Using $self->cursor->all is really just an optimisation.
592 # If we're collapsing has_many prefetches it probably makes
593 # very little difference, and this is cleaner than hacking
594 # _construct_object to survive the approach
595 $self->cursor->reset;
596 my @row = $self->cursor->next;
598 push(@obj, $self->_construct_object(@row));
599 @row = (exists $self->{stashed_row}
600 ? @{delete $self->{stashed_row}}
601 : $self->cursor->next);
604 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
607 $self->set_cache(\@obj) if $self->{attrs}{cache};
613 Resets the resultset's cursor, so you can iterate through the elements again.
619 $self->{all_cache_position} = 0;
620 $self->cursor->reset;
626 Resets the resultset and returns the first element.
631 return $_[0]->reset->next;
636 =head3 Arguments: (\%values)
638 Sets the specified columns in the resultset to the supplied values.
643 my ($self, $values) = @_;
644 $self->throw_exception("Values for update must be a hash")
645 unless ref $values eq 'HASH';
646 return $self->result_source->storage->update(
647 $self->result_source->from, $values, $self->{cond});
652 =head3 Arguments: (\%values)
654 Fetches all objects and updates them one at a time. Note that C<update_all>
655 will run cascade triggers while L</update> will not.
660 my ($self, $values) = @_;
661 $self->throw_exception("Values for update must be a hash")
662 unless ref $values eq 'HASH';
663 foreach my $obj ($self->all) {
664 $obj->set_columns($values)->update;
671 Deletes the contents of the resultset from its result source.
679 if (!ref($self->{cond})) {
681 # No-op. No condition, we're deleting everything
683 } elsif (ref $self->{cond} eq 'ARRAY') {
685 $del = [ map { my %hash;
686 foreach my $key (keys %{$_}) {
688 $hash{$1} = $_->{$key};
689 }; \%hash; } @{$self->{cond}} ];
691 } elsif (ref $self->{cond} eq 'HASH') {
693 if ((keys %{$self->{cond}})[0] eq '-and') {
695 $del->{-and} = [ map { my %hash;
696 foreach my $key (keys %{$_}) {
698 $hash{$1} = $_->{$key};
699 }; \%hash; } @{$self->{cond}{-and}} ];
703 foreach my $key (keys %{$self->{cond}}) {
705 $del->{$1} = $self->{cond}{$key};
709 $self->throw_exception(
710 "Can't delete on resultset with condition unless hash or array");
713 $self->result_source->storage->delete($self->result_source->from, $del);
719 Fetches all objects and deletes them one at a time. Note that C<delete_all>
720 will run cascade triggers while L</delete> will not.
726 $_->delete for $self->all;
732 Returns a L<Data::Page> object for the current resultset. Only makes
733 sense for queries with a C<page> attribute.
739 my $attrs = $self->{attrs};
740 $self->throw_exception("Can't create pager for non-paged rs")
741 unless $self->{page};
742 $attrs->{rows} ||= 10;
743 return $self->{pager} ||= Data::Page->new(
744 $self->_count, $attrs->{rows}, $self->{page});
749 =head3 Arguments: ($page_num)
751 Returns a new resultset for the specified page.
756 my ($self, $page) = @_;
757 my $attrs = { %{$self->{attrs}} };
758 $attrs->{page} = $page;
759 return (ref $self)->new($self->result_source, $attrs);
764 =head3 Arguments: (\%vals)
766 Creates a result in the resultset's result class.
771 my ($self, $values) = @_;
772 $self->throw_exception( "new_result needs a hash" )
773 unless (ref $values eq 'HASH');
774 $self->throw_exception(
775 "Can't abstract implicit construct, condition not a hash"
776 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
778 my $alias = $self->{attrs}{alias};
779 foreach my $key (keys %{$self->{cond}||{}}) {
780 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
782 my $obj = $self->result_class->new(\%new);
783 $obj->result_source($self->result_source) if $obj->can('result_source');
789 =head3 Arguments: (\%vals)
791 Inserts a record into the resultset and returns the object.
793 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
798 my ($self, $attrs) = @_;
799 $self->throw_exception( "create needs a hashref" )
800 unless ref $attrs eq 'HASH';
801 return $self->new_result($attrs)->insert;
804 =head2 find_or_create
806 =head3 Arguments: (\%vals, \%attrs?)
808 $class->find_or_create({ key => $val, ... });
810 Searches for a record matching the search condition; if it doesn't find one,
811 creates one and returns that instead.
813 my $cd = $schema->resultset('CD')->find_or_create({
815 artist => 'Massive Attack',
816 title => 'Mezzanine',
820 Also takes an optional C<key> attribute, to search by a specific key or unique
821 constraint. For example:
823 my $cd = $schema->resultset('CD')->find_or_create(
825 artist => 'Massive Attack',
826 title => 'Mezzanine',
828 { key => 'artist_title' }
831 See also L</find> and L</update_or_create>.
837 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
838 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
839 my $exists = $self->find($hash, $attrs);
840 return defined $exists ? $exists : $self->create($hash);
843 =head2 update_or_create
845 $class->update_or_create({ key => $val, ... });
847 First, search for an existing row matching one of the unique constraints
848 (including the primary key) on the source of this resultset. If a row is
849 found, update it with the other given column values. Otherwise, create a new
852 Takes an optional C<key> attribute to search on a specific unique constraint.
855 # In your application
856 my $cd = $schema->resultset('CD')->update_or_create(
858 artist => 'Massive Attack',
859 title => 'Mezzanine',
862 { key => 'artist_title' }
865 If no C<key> is specified, it searches on all unique constraints defined on the
866 source, including the primary key.
868 If the C<key> is specified as C<primary>, search only on the primary key.
870 See also L</find> and L</find_or_create>.
874 sub update_or_create {
876 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
877 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
879 my %unique_constraints = $self->result_source->unique_constraints;
880 my @constraint_names = (exists $attrs->{key}
882 : keys %unique_constraints);
885 foreach my $name (@constraint_names) {
886 my @unique_cols = @{ $unique_constraints{$name} };
888 map { $_ => $hash->{$_} }
889 grep { exists $hash->{$_} }
892 push @unique_hashes, \%unique_hash
893 if (scalar keys %unique_hash == scalar @unique_cols);
896 if (@unique_hashes) {
897 my $row = $self->single(\@unique_hashes);
899 $row->set_columns($hash);
905 return $self->create($hash);
910 Gets the contents of the cache for the resultset.
915 shift->{all_cache} || [];
920 Sets the contents of the cache for the resultset. Expects an arrayref
921 of objects of the same class as those produced by the resultset.
926 my ( $self, $data ) = @_;
927 $self->throw_exception("set_cache requires an arrayref")
928 if ref $data ne 'ARRAY';
929 my $result_class = $self->result_class;
931 $self->throw_exception(
932 "cannot cache object of type '$_', expected '$result_class'"
933 ) if ref $_ ne $result_class;
935 $self->{all_cache} = $data;
940 Clears the cache for the resultset.
945 shift->set_cache([]);
948 =head2 related_resultset
950 Returns a related resultset for the supplied relationship name.
952 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
956 sub related_resultset {
957 my ( $self, $rel, @rest ) = @_;
958 $self->{related_resultsets} ||= {};
959 return $self->{related_resultsets}{$rel} ||= do {
960 #warn "fetching related resultset for rel '$rel'";
961 my $rel_obj = $self->result_source->relationship_info($rel);
962 $self->throw_exception(
963 "search_related: result source '" . $self->result_source->name .
964 "' has no such relationship ${rel}")
965 unless $rel_obj; #die Dumper $self->{attrs};
967 my $rs = $self->search(undef, { join => $rel });
968 my $alias = defined $rs->{attrs}{seen_join}{$rel}
969 && $rs->{attrs}{seen_join}{$rel} > 1
970 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
973 $self->result_source->schema->resultset($rel_obj->{class}
983 =head2 throw_exception
985 See Schema's throw_exception
989 sub throw_exception {
991 $self->result_source->schema->throw_exception(@_);
996 The resultset takes various attributes that modify its behavior. Here's an
1001 Which column(s) to order the results by. This is currently passed
1002 through directly to SQL, so you can give e.g. C<year DESC> for a
1003 descending order on the column `year'.
1007 =head3 Arguments: (arrayref)
1009 Shortcut to request a particular set of columns to be retrieved. Adds
1010 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1011 from that, then auto-populates C<as> from C<select> as normal. (You may also
1012 use the C<cols> attribute, as in earlier versions of DBIC.)
1014 =head2 include_columns
1016 =head3 Arguments: (arrayref)
1018 Shortcut to include additional columns in the returned results - for example
1020 $schema->resultset('CD')->search(undef, {
1021 include_columns => ['artist.name'],
1025 would return all CDs and include a 'name' column to the information
1026 passed to object inflation
1030 =head3 Arguments: (arrayref)
1032 Indicates which columns should be selected from the storage. You can use
1033 column names, or in the case of RDBMS back ends, function or stored procedure
1036 $rs = $schema->resultset('Employee')->search(undef, {
1039 { count => 'employeeid' },
1044 When you use function/stored procedure names and do not supply an C<as>
1045 attribute, the column names returned are storage-dependent. E.g. MySQL would
1046 return a column named C<count(employeeid)> in the above example.
1050 =head3 Arguments: (arrayref)
1052 Indicates column names for object inflation. This is used in conjunction with
1053 C<select>, usually when C<select> contains one or more function or stored
1056 $rs = $schema->resultset('Employee')->search(undef, {
1059 { count => 'employeeid' }
1061 as => ['name', 'employee_count'],
1064 my $employee = $rs->first(); # get the first Employee
1066 If the object against which the search is performed already has an accessor
1067 matching a column name specified in C<as>, the value can be retrieved using
1068 the accessor as normal:
1070 my $name = $employee->name();
1072 If on the other hand an accessor does not exist in the object, you need to
1073 use C<get_column> instead:
1075 my $employee_count = $employee->get_column('employee_count');
1077 You can create your own accessors if required - see
1078 L<DBIx::Class::Manual::Cookbook> for details.
1082 Contains a list of relationships that should be joined for this query. For
1085 # Get CDs by Nine Inch Nails
1086 my $rs = $schema->resultset('CD')->search(
1087 { 'artist.name' => 'Nine Inch Nails' },
1088 { join => 'artist' }
1091 Can also contain a hash reference to refer to the other relation's relations.
1094 package MyApp::Schema::Track;
1095 use base qw/DBIx::Class/;
1096 __PACKAGE__->table('track');
1097 __PACKAGE__->add_columns(qw/trackid cd position title/);
1098 __PACKAGE__->set_primary_key('trackid');
1099 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1102 # In your application
1103 my $rs = $schema->resultset('Artist')->search(
1104 { 'track.title' => 'Teardrop' },
1106 join => { cd => 'track' },
1107 order_by => 'artist.name',
1111 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1112 similarly for a third time). For e.g.
1114 my $rs = $schema->resultset('Artist')->search({
1115 'cds.title' => 'Down to Earth',
1116 'cds_2.title' => 'Popular',
1118 join => [ qw/cds cds/ ],
1121 will return a set of all artists that have both a cd with title 'Down
1122 to Earth' and a cd with title 'Popular'.
1124 If you want to fetch related objects from other tables as well, see C<prefetch>
1129 =head3 Arguments: arrayref/hashref
1131 Contains one or more relationships that should be fetched along with the main
1132 query (when they are accessed afterwards they will have already been
1133 "prefetched"). This is useful for when you know you will need the related
1134 objects, because it saves at least one query:
1136 my $rs = $schema->resultset('Tag')->search(
1145 The initial search results in SQL like the following:
1147 SELECT tag.*, cd.*, artist.* FROM tag
1148 JOIN cd ON tag.cd = cd.cdid
1149 JOIN artist ON cd.artist = artist.artistid
1151 L<DBIx::Class> has no need to go back to the database when we access the
1152 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1155 Simple prefetches will be joined automatically, so there is no need
1156 for a C<join> attribute in the above search. If you're prefetching to
1157 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1158 specify the join as well.
1160 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1161 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1162 with an accessor type of 'single' or 'filter').
1166 =head3 Arguments: (arrayref)
1168 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1169 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1172 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1173 C<join> will usually do what you need and it is strongly recommended that you
1174 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1176 In simple terms, C<from> works as follows:
1179 { <alias> => <table>, -join-type => 'inner|left|right' }
1180 [] # nested JOIN (optional)
1181 { <table.column> = <foreign_table.foreign_key> }
1187 ON <table.column> = <foreign_table.foreign_key>
1189 An easy way to follow the examples below is to remember the following:
1191 Anything inside "[]" is a JOIN
1192 Anything inside "{}" is a condition for the enclosing JOIN
1194 The following examples utilize a "person" table in a family tree application.
1195 In order to express parent->child relationships, this table is self-joined:
1197 # Person->belongs_to('father' => 'Person');
1198 # Person->belongs_to('mother' => 'Person');
1200 C<from> can be used to nest joins. Here we return all children with a father,
1201 then search against all mothers of those children:
1203 $rs = $schema->resultset('Person')->search(
1206 alias => 'mother', # alias columns in accordance with "from"
1208 { mother => 'person' },
1211 { child => 'person' },
1213 { father => 'person' },
1214 { 'father.person_id' => 'child.father_id' }
1217 { 'mother.person_id' => 'child.mother_id' }
1224 # SELECT mother.* FROM person mother
1227 # JOIN person father
1228 # ON ( father.person_id = child.father_id )
1230 # ON ( mother.person_id = child.mother_id )
1232 The type of any join can be controlled manually. To search against only people
1233 with a father in the person table, we could explicitly use C<INNER JOIN>:
1235 $rs = $schema->resultset('Person')->search(
1238 alias => 'child', # alias columns in accordance with "from"
1240 { child => 'person' },
1242 { father => 'person', -join-type => 'inner' },
1243 { 'father.id' => 'child.father_id' }
1250 # SELECT child.* FROM person child
1251 # INNER JOIN person father ON child.father_id = father.id
1255 For a paged resultset, specifies which page to retrieve. Leave unset
1256 for an unpaged resultset.
1260 For a paged resultset, how many rows per page:
1264 Can also be used to simulate an SQL C<LIMIT>.
1268 =head3 Arguments: (arrayref)
1270 A arrayref of columns to group by. Can include columns of joined tables.
1272 group_by => [qw/ column1 column2 ... /]
1276 Set to 1 to group by all columns.
1280 Set to 1 to cache search results. This prevents extra SQL queries if you
1281 revisit rows in your ResultSet:
1283 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
1285 while( my $artist = $resultset->next ) {
1289 $rs->first; # without cache, this would issue a query
1291 By default, searches are not cached.
1293 For more examples of using these attributes, see
1294 L<DBIx::Class::Manual::Cookbook>.