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;
72 my ($source, $attrs) = @_;
73 #use Data::Dumper; warn Dumper($attrs);
74 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
76 my $alias = ($attrs->{alias} ||= 'me');
77 if ($attrs->{cols} || !$attrs->{select}) {
78 delete $attrs->{as} if $attrs->{cols};
79 my @cols = ($attrs->{cols}
80 ? @{delete $attrs->{cols}}
82 $attrs->{select} = [ map { m/\./ ? $_ : "${alias}.$_" } @cols ];
84 $attrs->{as} ||= [ map { m/^$alias\.(.*)$/ ? $1 : $_ } @{$attrs->{select}} ];
85 if (my $include = delete $attrs->{include_columns}) {
86 push(@{$attrs->{select}}, @$include);
87 push(@{$attrs->{as}}, map { m/([^\.]+)$/; $1; } @$include);
89 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
90 $attrs->{from} ||= [ { $alias => $source->from } ];
91 $attrs->{seen_join} ||= {};
92 if (my $join = delete $attrs->{join}) {
93 foreach my $j (ref $join eq 'ARRAY'
94 ? (@{$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}));
103 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
105 $attrs->{order_by} = [ $attrs->{order_by} ]
106 if $attrs->{order_by} && !ref($attrs->{order_by});
107 $attrs->{order_by} ||= [];
111 if (my $prefetch = delete $attrs->{prefetch}) {
113 foreach my $p (ref $prefetch eq 'ARRAY'
114 ? (@{$prefetch}) : ($prefetch)) {
115 if( ref $p eq 'HASH' ) {
116 foreach my $key (keys %$p) {
117 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
122 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
125 my @prefetch = $source->resolve_prefetch(
126 $p, $attrs->{alias}, {}, \@pre_order, $collapse);
128 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
129 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
131 push(@{$attrs->{order_by}}, @pre_order);
134 if ($attrs->{page}) {
135 $attrs->{rows} ||= 10;
136 $attrs->{offset} ||= 0;
137 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
140 #if (keys %{$collapse}) {
141 # use Data::Dumper; warn Dumper($collapse);
145 result_source => $source,
146 result_class => $attrs->{result_class} || $source->result_class,
147 cond => $attrs->{where},
148 from => $attrs->{from},
149 collapse => $collapse,
151 page => delete $attrs->{page},
154 bless ($new, $class);
160 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
161 my $new_rs = $rs->search({ foo => 3 });
163 If you need to pass in additional attributes but no additional condition,
164 call it as C<search({}, \%attrs);>.
166 # "SELECT foo, bar FROM $class_table"
167 my @all = $class->search({}, { cols => [qw/foo bar/] });
177 my $attrs = { %{$self->{attrs}} };
178 my $having = delete $attrs->{having};
179 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
180 $attrs = { %$attrs, %{ pop(@_) } };
184 ? ((@_ == 1 || ref $_[0] eq "HASH")
187 ? $self->throw_exception(
188 "Odd number of arguments to search")
191 if (defined $where) {
192 $where = (defined $attrs->{where}
194 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
195 $where, $attrs->{where} ] }
197 $attrs->{where} = $where;
200 if (defined $having) {
201 $having = (defined $attrs->{having}
203 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
204 $having, $attrs->{having} ] }
206 $attrs->{having} = $having;
209 $rs = (ref $self)->new($self->result_source, $attrs);
215 return (wantarray ? $rs->all : $rs);
218 =head2 search_literal
220 my @obj = $rs->search_literal($literal_where_cond, @bind);
221 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
223 Pass a literal chunk of SQL to be added to the conditional part of the
229 my ($self, $cond, @vals) = @_;
230 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
231 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
232 return $self->search(\$cond, $attrs);
237 =head3 Arguments: (@colvalues) | (\%cols, \%attrs?)
239 Finds a row based on its primary key or unique constraint. For example:
241 my $cd = $schema->resultset('CD')->find(5);
243 Also takes an optional C<key> attribute, to search by a specific key or unique
244 constraint. For example:
246 my $cd = $schema->resultset('CD')->find(
248 artist => 'Massive Attack',
249 title => 'Mezzanine',
251 { key => 'artist_title' }
254 See also L</find_or_create> and L</update_or_create>.
259 my ($self, @vals) = @_;
260 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
262 my @cols = $self->result_source->primary_columns;
263 if (exists $attrs->{key}) {
264 my %uniq = $self->result_source->unique_constraints;
265 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
266 unless exists $uniq{$attrs->{key}};
267 @cols = @{ $uniq{$attrs->{key}} };
269 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
270 $self->throw_exception( "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 (keys %$query) {
284 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
286 #warn Dumper($query);
288 ? $self->search($query,$attrs)->single
289 : $self->single($query));
292 =head2 search_related
294 $rs->search_related('relname', $cond?, $attrs?);
296 Search the specified relationship. Optionally specify a condition for matching
302 return shift->related_resultset(shift)->search(@_);
307 Returns a storage-driven cursor to the given resultset.
313 my ($attrs) = $self->{attrs};
314 $attrs = { %$attrs };
315 return $self->{cursor}
316 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
317 $attrs->{where},$attrs);
322 Inflates the first result without creating a cursor
327 my ($self, $extra) = @_;
328 my ($attrs) = $self->{attrs};
329 $attrs = { %$attrs };
331 if (defined $attrs->{where}) {
334 => [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
335 delete $attrs->{where}, $extra ]
338 $attrs->{where} = $extra;
341 my @data = $self->result_source->storage->select_single(
342 $self->{from}, $attrs->{select},
343 $attrs->{where},$attrs);
344 return (@data ? $self->_construct_object(@data) : ());
350 Perform a search, but use C<LIKE> instead of equality as the condition. Note
351 that this is simply a convenience method; you most likely want to use
352 L</search> with specific operators.
354 For more information, see L<DBIx::Class::Manual::Cookbook>.
361 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
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) {
403 if( @{$cache = $self->{all_cache} || []}) {
404 $self->{all_cache_position} ||= 0;
405 my $obj = $cache->[$self->{all_cache_position}];
406 $self->{all_cache_position}++;
409 if ($self->{attrs}{cache}) {
410 $self->{all_cache_position} = 1;
411 return ($self->all)[0];
413 my @row = (exists $self->{stashed_row}
414 ? @{delete $self->{stashed_row}}
415 : $self->cursor->next);
416 # warn Dumper(\@row); use Data::Dumper;
417 return unless (@row);
418 return $self->_construct_object(@row);
421 sub _construct_object {
422 my ($self, @row) = @_;
423 my @as = @{ $self->{attrs}{as} };
425 my $info = $self->_collapse_result(\@as, \@row);
427 my $new = $self->result_class->inflate_result($self->result_source, @$info);
429 $new = $self->{attrs}{record_filter}->($new)
430 if exists $self->{attrs}{record_filter};
435 sub _collapse_result {
436 my ($self, $as, $row, $prefix) = @_;
441 foreach my $this_as (@$as) {
442 my $val = shift @copy;
443 if (defined $prefix) {
444 if ($this_as =~ m/^\Q${prefix}.\E(.+)$/) {
446 $remain =~ /^(?:(.*)\.)?([^\.]+)$/;
447 $const{$1||''}{$2} = $val;
450 $this_as =~ /^(?:(.*)\.)?([^\.]+)$/;
451 $const{$1||''}{$2} = $val;
455 my $info = [ {}, {} ];
456 foreach my $key (keys %const) {
459 my @parts = split(/\./, $key);
460 foreach my $p (@parts) {
461 $target = $target->[1]->{$p} ||= [];
463 $target->[0] = $const{$key};
465 $info->[0] = $const{$key};
469 my @collapse = (defined($prefix)
470 ? (map { (m/^\Q${prefix}.\E(.+)$/ ? ($1) : ()); }
471 keys %{$self->{collapse}})
472 : keys %{$self->{collapse}});
474 my ($c) = sort { length $a <=> length $b } @collapse;
476 foreach my $p (split(/\./, $c)) {
477 $target = $target->[1]->{$p} ||= [];
479 my $c_prefix = (defined($prefix) ? "${prefix}.${c}" : $c);
480 my @co_key = @{$self->{collapse}{$c_prefix}};
481 my %co_check = map { ($_, $target->[0]->{$_}); } @co_key;
482 my $tree = $self->_collapse_result($as, $row, $c_prefix);
485 !defined($tree->[0]->{$_})
486 || $co_check{$_} ne $tree->[0]->{$_}
489 last unless (@raw = $self->cursor->next);
490 $row = $self->{stashed_row} = \@raw;
491 $tree = $self->_collapse_result($as, $row, $c_prefix);
492 #warn Data::Dumper::Dumper($tree, $row);
502 Returns a reference to the result source for this recordset.
509 Performs an SQL C<COUNT> with the same query as the resultset was built
510 with to find the number of elements. If passed arguments, does a search
511 on the resultset and counts the results of that.
513 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
514 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
515 not support C<DISTINCT> with multiple columns. If you are using such a
516 database, you should only use columns from the main table in your C<group_by>
523 return $self->search(@_)->count if @_ && defined $_[0];
524 unless (defined $self->{count}) {
525 return scalar @{ $self->get_cache }
526 if @{ $self->get_cache };
528 my $select = { 'count' => '*' };
529 my $attrs = { %{ $self->{attrs} } };
530 if( $group_by = delete $attrs->{group_by} ) {
531 delete $attrs->{having};
532 my @distinct = (ref $group_by ? @$group_by : ($group_by));
533 # todo: try CONCAT for multi-column pk
534 my @pk = $self->result_source->primary_columns;
535 if( scalar(@pk) == 1 ) {
537 my $alias = $attrs->{alias};
538 my $re = qr/^($alias\.)?$pk$/;
539 foreach my $column ( @distinct) {
540 if( $column =~ $re ) {
541 @distinct = ( $column );
547 $select = { count => { 'distinct' => \@distinct } };
548 #use Data::Dumper; die Dumper $select;
551 $attrs->{select} = $select;
552 $attrs->{as} = [ 'count' ];
553 # offset, order by and page are not needed to count. record_filter is cdbi
554 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
556 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
558 return 0 unless $self->{count};
559 my $count = $self->{count};
560 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
561 $count = $self->{attrs}{rows} if
562 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
568 Calls L</search_literal> with the passed arguments, then L</count>.
572 sub count_literal { shift->search_literal(@_)->count; }
576 Returns all elements in the resultset. Called implictly if the resultset
577 is returned in list context.
583 return @{ $self->get_cache }
584 if @{ $self->get_cache };
588 if (keys %{$self->{collapse}}) {
589 # Using $self->cursor->all is really just an optimisation.
590 # If we're collapsing has_many prefetches it probably makes
591 # very little difference, and this is cleaner than hacking
592 # _construct_object to survive the approach
594 $self->cursor->reset;
595 while (@row = $self->cursor->next) {
596 push(@obj, $self->_construct_object(@row));
599 @obj = map { $self->_construct_object(@$_); }
603 if( $self->{attrs}->{cache} ) {
604 $self->set_cache( \@obj );
612 Resets the resultset's cursor, so you can iterate through the elements again.
618 $self->{all_cache_position} = 0;
619 $self->cursor->reset;
625 Resets the resultset and returns the first element.
630 return $_[0]->reset->next;
635 =head3 Arguments: (\%values)
637 Sets the specified columns in the resultset to the supplied values.
642 my ($self, $values) = @_;
643 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
644 return $self->result_source->storage->update(
645 $self->result_source->from, $values, $self->{cond});
650 =head3 Arguments: (\%values)
652 Fetches all objects and updates them one at a time. Note that C<update_all>
653 will run cascade triggers while L</update> will not.
658 my ($self, $values) = @_;
659 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
660 foreach my $obj ($self->all) {
661 $obj->set_columns($values)->update;
668 Deletes the contents of the resultset from its result source.
675 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
676 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
677 if (ref $self->{cond} eq 'ARRAY') {
678 $del = [ map { my %hash;
679 foreach my $key (keys %{$_}) {
681 $hash{$1} = $_->{$key};
682 }; \%hash; } @{$self->{cond}} ];
683 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
684 $del->{-and} = [ map { my %hash;
685 foreach my $key (keys %{$_}) {
687 $hash{$1} = $_->{$key};
688 }; \%hash; } @{$self->{cond}{-and}} ];
690 foreach my $key (keys %{$self->{cond}}) {
692 $del->{$1} = $self->{cond}{$key};
695 $self->result_source->storage->delete($self->result_source->from, $del);
701 Fetches all objects and deletes them one at a time. Note that C<delete_all>
702 will run cascade triggers while L</delete> will not.
708 $_->delete for $self->all;
714 Returns a L<Data::Page> object for the current resultset. Only makes
715 sense for queries with a C<page> attribute.
721 my $attrs = $self->{attrs};
722 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
723 $attrs->{rows} ||= 10;
725 return $self->{pager} ||= Data::Page->new(
726 $self->{count}, $attrs->{rows}, $self->{page});
731 =head3 Arguments: ($page_num)
733 Returns a new resultset for the specified page.
738 my ($self, $page) = @_;
739 my $attrs = { %{$self->{attrs}} };
740 $attrs->{page} = $page;
741 return (ref $self)->new($self->result_source, $attrs);
746 =head3 Arguments: (\%vals)
748 Creates a result in the resultset's result class.
753 my ($self, $values) = @_;
754 $self->throw_exception( "new_result needs a hash" )
755 unless (ref $values eq 'HASH');
756 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
757 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
759 my $alias = $self->{attrs}{alias};
760 foreach my $key (keys %{$self->{cond}||{}}) {
761 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
763 my $obj = $self->result_class->new(\%new);
764 $obj->result_source($self->result_source) if $obj->can('result_source');
770 =head3 Arguments: (\%vals)
772 Inserts a record into the resultset and returns the object.
774 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
779 my ($self, $attrs) = @_;
780 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
781 return $self->new_result($attrs)->insert;
784 =head2 find_or_create
786 =head3 Arguments: (\%vals, \%attrs?)
788 $class->find_or_create({ key => $val, ... });
790 Searches for a record matching the search condition; if it doesn't find one,
791 creates one and returns that instead.
793 my $cd = $schema->resultset('CD')->find_or_create({
795 artist => 'Massive Attack',
796 title => 'Mezzanine',
800 Also takes an optional C<key> attribute, to search by a specific key or unique
801 constraint. For example:
803 my $cd = $schema->resultset('CD')->find_or_create(
805 artist => 'Massive Attack',
806 title => 'Mezzanine',
808 { key => 'artist_title' }
811 See also L</find> and L</update_or_create>.
817 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
818 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
819 my $exists = $self->find($hash, $attrs);
820 return defined($exists) ? $exists : $self->create($hash);
823 =head2 update_or_create
825 $class->update_or_create({ key => $val, ... });
827 First, search for an existing row matching one of the unique constraints
828 (including the primary key) on the source of this resultset. If a row is
829 found, update it with the other given column values. Otherwise, create a new
832 Takes an optional C<key> attribute to search on a specific unique constraint.
835 # In your application
836 my $cd = $schema->resultset('CD')->update_or_create(
838 artist => 'Massive Attack',
839 title => 'Mezzanine',
842 { key => 'artist_title' }
845 If no C<key> is specified, it searches on all unique constraints defined on the
846 source, including the primary key.
848 If the C<key> is specified as C<primary>, search only on the primary key.
850 See also L</find> and L</find_or_create>.
854 sub update_or_create {
857 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
858 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
860 my %unique_constraints = $self->result_source->unique_constraints;
861 my @constraint_names = (exists $attrs->{key}
863 : keys %unique_constraints);
866 foreach my $name (@constraint_names) {
867 my @unique_cols = @{ $unique_constraints{$name} };
869 map { $_ => $hash->{$_} }
870 grep { exists $hash->{$_} }
873 push @unique_hashes, \%unique_hash
874 if (scalar keys %unique_hash == scalar @unique_cols);
878 if (@unique_hashes) {
879 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
881 $row->set_columns($hash);
887 $row = $self->create($hash);
895 Gets the contents of the cache for the resultset.
901 return $self->{all_cache} || [];
906 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
911 my ( $self, $data ) = @_;
912 $self->throw_exception("set_cache requires an arrayref")
913 if ref $data ne 'ARRAY';
914 my $result_class = $self->result_class;
916 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
917 if ref $_ ne $result_class;
919 $self->{all_cache} = $data;
924 Clears the cache for the resultset.
930 $self->set_cache([]);
933 =head2 related_resultset
935 Returns a related resultset for the supplied relationship name.
937 $rs = $rs->related_resultset('foo');
941 sub related_resultset {
942 my ( $self, $rel, @rest ) = @_;
943 $self->{related_resultsets} ||= {};
944 my $resultsets = $self->{related_resultsets};
945 if( !exists $resultsets->{$rel} ) {
946 #warn "fetching related resultset for rel '$rel'";
947 my $rel_obj = $self->result_source->relationship_info($rel);
948 $self->throw_exception(
949 "search_related: result source '" . $self->result_source->name .
950 "' has no such relationship ${rel}")
951 unless $rel_obj; #die Dumper $self->{attrs};
952 my $rs = $self->search(undef, { join => $rel });
953 #if( $self->{attrs}->{cache} ) {
954 # $rs = $self->search(undef);
958 #use Data::Dumper; die Dumper $rs->{attrs};#$rs = $self->search( undef );
959 #use Data::Dumper; warn Dumper $self->{attrs}, Dumper $rs->{attrs};
960 my $alias = (defined $rs->{attrs}{seen_join}{$rel}
961 && $rs->{attrs}{seen_join}{$rel} > 1
962 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
964 $resultsets->{$rel} =
965 $self->result_source->schema->resultset($rel_obj->{class}
973 return $resultsets->{$rel};
976 =head2 throw_exception
978 See Schema's throw_exception
982 sub throw_exception {
984 $self->result_source->schema->throw_exception(@_);
989 The resultset takes various attributes that modify its behavior. Here's an
994 Which column(s) to order the results by. This is currently passed through
995 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
999 =head3 Arguments: (arrayref)
1001 Shortcut to request a particular set of columns to be retrieved. Adds
1002 C<me.> onto the start of any column without a C<.> in it and sets C<select>
1003 from that, then auto-populates C<as> from C<select> as normal.
1005 =head2 include_columns
1007 =head3 Arguments: (arrayref)
1009 Shortcut to include additional columns in the returned results - for example
1011 { include_columns => ['foo.name'], join => ['foo'] }
1013 would add a 'name' column to the information passed to object inflation
1017 =head3 Arguments: (arrayref)
1019 Indicates which columns should be selected from the storage. You can use
1020 column names, or in the case of RDBMS back ends, function or stored procedure
1023 $rs = $schema->resultset('Foo')->search(
1028 { count => 'column_to_count' },
1029 { sum => 'column_to_sum' }
1034 When you use function/stored procedure names and do not supply an C<as>
1035 attribute, the column names returned are storage-dependent. E.g. MySQL would
1036 return a column named C<count(column_to_count)> in the above example.
1040 =head3 Arguments: (arrayref)
1042 Indicates column names for object inflation. This is used in conjunction with
1043 C<select>, usually when C<select> contains one or more function or stored
1046 $rs = $schema->resultset('Foo')->search(
1051 { count => 'column2' }
1053 as => [qw/ column1 column2_count /]
1057 my $foo = $rs->first(); # get the first Foo
1059 If the object against which the search is performed already has an accessor
1060 matching a column name specified in C<as>, the value can be retrieved using
1061 the accessor as normal:
1063 my $column1 = $foo->column1();
1065 If on the other hand an accessor does not exist in the object, you need to
1066 use C<get_column> instead:
1068 my $column2_count = $foo->get_column('column2_count');
1070 You can create your own accessors if required - see
1071 L<DBIx::Class::Manual::Cookbook> for details.
1075 Contains a list of relationships that should be joined for this query. For
1078 # Get CDs by Nine Inch Nails
1079 my $rs = $schema->resultset('CD')->search(
1080 { 'artist.name' => 'Nine Inch Nails' },
1081 { join => 'artist' }
1084 Can also contain a hash reference to refer to the other relation's relations.
1087 package MyApp::Schema::Track;
1088 use base qw/DBIx::Class/;
1089 __PACKAGE__->table('track');
1090 __PACKAGE__->add_columns(qw/trackid cd position title/);
1091 __PACKAGE__->set_primary_key('trackid');
1092 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1095 # In your application
1096 my $rs = $schema->resultset('Artist')->search(
1097 { 'track.title' => 'Teardrop' },
1099 join => { cd => 'track' },
1100 order_by => 'artist.name',
1104 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1105 similarly for a third time). For e.g.
1107 my $rs = $schema->resultset('Artist')->search(
1108 { 'cds.title' => 'Foo',
1109 'cds_2.title' => 'Bar' },
1110 { join => [ qw/cds cds/ ] });
1112 will return a set of all artists that have both a cd with title Foo and a cd
1115 If you want to fetch related objects from other tables as well, see C<prefetch>
1120 =head3 Arguments: arrayref/hashref
1122 Contains one or more relationships that should be fetched along with the main
1123 query (when they are accessed afterwards they will have already been
1124 "prefetched"). This is useful for when you know you will need the related
1125 objects, because it saves at least one query:
1127 my $rs = $schema->resultset('Tag')->search(
1136 The initial search results in SQL like the following:
1138 SELECT tag.*, cd.*, artist.* FROM tag
1139 JOIN cd ON tag.cd = cd.cdid
1140 JOIN artist ON cd.artist = artist.artistid
1142 L<DBIx::Class> has no need to go back to the database when we access the
1143 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1146 Simple prefetches will be joined automatically, so there is no need
1147 for a C<join> attribute in the above search. If you're prefetching to
1148 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1149 specify the join as well.
1151 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1152 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1153 with an accessor type of 'single' or 'filter').
1157 =head3 Arguments: (arrayref)
1159 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1160 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1163 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1164 C<join> will usually do what you need and it is strongly recommended that you
1165 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1167 In simple terms, C<from> works as follows:
1170 { <alias> => <table>, -join-type => 'inner|left|right' }
1171 [] # nested JOIN (optional)
1172 { <table.column> = <foreign_table.foreign_key> }
1178 ON <table.column> = <foreign_table.foreign_key>
1180 An easy way to follow the examples below is to remember the following:
1182 Anything inside "[]" is a JOIN
1183 Anything inside "{}" is a condition for the enclosing JOIN
1185 The following examples utilize a "person" table in a family tree application.
1186 In order to express parent->child relationships, this table is self-joined:
1188 # Person->belongs_to('father' => 'Person');
1189 # Person->belongs_to('mother' => 'Person');
1191 C<from> can be used to nest joins. Here we return all children with a father,
1192 then search against all mothers of those children:
1194 $rs = $schema->resultset('Person')->search(
1197 alias => 'mother', # alias columns in accordance with "from"
1199 { mother => 'person' },
1202 { child => 'person' },
1204 { father => 'person' },
1205 { 'father.person_id' => 'child.father_id' }
1208 { 'mother.person_id' => 'child.mother_id' }
1215 # SELECT mother.* FROM person mother
1218 # JOIN person father
1219 # ON ( father.person_id = child.father_id )
1221 # ON ( mother.person_id = child.mother_id )
1223 The type of any join can be controlled manually. To search against only people
1224 with a father in the person table, we could explicitly use C<INNER JOIN>:
1226 $rs = $schema->resultset('Person')->search(
1229 alias => 'child', # alias columns in accordance with "from"
1231 { child => 'person' },
1233 { father => 'person', -join-type => 'inner' },
1234 { 'father.id' => 'child.father_id' }
1241 # SELECT child.* FROM person child
1242 # INNER JOIN person father ON child.father_id = father.id
1246 For a paged resultset, specifies which page to retrieve. Leave unset
1247 for an unpaged resultset.
1251 For a paged resultset, how many rows per page:
1255 Can also be used to simulate an SQL C<LIMIT>.
1259 =head3 Arguments: (arrayref)
1261 A arrayref of columns to group by. Can include columns of joined tables.
1263 group_by => [qw/ column1 column2 ... /]
1267 Set to 1 to group by all columns.
1269 For more examples of using these attributes, see
1270 L<DBIx::Class::Manual::Cookbook>.