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
569 $self->cursor->reset;
570 my @row = $self->cursor->next;
572 push(@obj, $self->_construct_object(@row));
573 @row = (exists $self->{stashed_row}
574 ? @{delete $self->{stashed_row}}
575 : $self->cursor->next);
578 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
581 $self->set_cache(\@obj) if $self->{attrs}{cache};
587 Resets the resultset's cursor, so you can iterate through the elements again.
593 $self->{all_cache_position} = 0;
594 $self->cursor->reset;
600 Resets the resultset and returns the first element.
605 return $_[0]->reset->next;
610 =head3 Arguments: (\%values)
612 Sets the specified columns in the resultset to the supplied values.
617 my ($self, $values) = @_;
618 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
619 return $self->result_source->storage->update(
620 $self->result_source->from, $values, $self->{cond});
625 =head3 Arguments: (\%values)
627 Fetches all objects and updates them one at a time. Note that C<update_all>
628 will run cascade triggers while L</update> will not.
633 my ($self, $values) = @_;
634 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
635 foreach my $obj ($self->all) {
636 $obj->set_columns($values)->update;
643 Deletes the contents of the resultset from its result source.
650 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
651 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
652 if (ref $self->{cond} eq 'ARRAY') {
653 $del = [ map { my %hash;
654 foreach my $key (keys %{$_}) {
656 $hash{$1} = $_->{$key};
657 }; \%hash; } @{$self->{cond}} ];
658 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
659 $del->{-and} = [ map { my %hash;
660 foreach my $key (keys %{$_}) {
662 $hash{$1} = $_->{$key};
663 }; \%hash; } @{$self->{cond}{-and}} ];
665 foreach my $key (keys %{$self->{cond}}) {
667 $del->{$1} = $self->{cond}{$key};
670 $self->result_source->storage->delete($self->result_source->from, $del);
676 Fetches all objects and deletes them one at a time. Note that C<delete_all>
677 will run cascade triggers while L</delete> will not.
683 $_->delete for $self->all;
689 Returns a L<Data::Page> object for the current resultset. Only makes
690 sense for queries with a C<page> attribute.
696 my $attrs = $self->{attrs};
697 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
698 $attrs->{rows} ||= 10;
700 return $self->{pager} ||= Data::Page->new(
701 $self->{count}, $attrs->{rows}, $self->{page});
706 =head3 Arguments: ($page_num)
708 Returns a new resultset for the specified page.
713 my ($self, $page) = @_;
714 my $attrs = { %{$self->{attrs}} };
715 $attrs->{page} = $page;
716 return (ref $self)->new($self->result_source, $attrs);
721 =head3 Arguments: (\%vals)
723 Creates a result in the resultset's result class.
728 my ($self, $values) = @_;
729 $self->throw_exception( "new_result needs a hash" )
730 unless (ref $values eq 'HASH');
731 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
732 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
734 my $alias = $self->{attrs}{alias};
735 foreach my $key (keys %{$self->{cond}||{}}) {
736 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:\Q${alias}.\E)?([^.]+)$/);
738 my $obj = $self->result_class->new(\%new);
739 $obj->result_source($self->result_source) if $obj->can('result_source');
745 =head3 Arguments: (\%vals)
747 Inserts a record into the resultset and returns the object.
749 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
754 my ($self, $attrs) = @_;
755 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
756 return $self->new_result($attrs)->insert;
759 =head2 find_or_create
761 =head3 Arguments: (\%vals, \%attrs?)
763 $class->find_or_create({ key => $val, ... });
765 Searches for a record matching the search condition; if it doesn't find one,
766 creates one and returns that instead.
768 my $cd = $schema->resultset('CD')->find_or_create({
770 artist => 'Massive Attack',
771 title => 'Mezzanine',
775 Also takes an optional C<key> attribute, to search by a specific key or unique
776 constraint. For example:
778 my $cd = $schema->resultset('CD')->find_or_create(
780 artist => 'Massive Attack',
781 title => 'Mezzanine',
783 { key => 'artist_title' }
786 See also L</find> and L</update_or_create>.
792 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
793 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
794 my $exists = $self->find($hash, $attrs);
795 return defined $exists ? $exists : $self->create($hash);
798 =head2 update_or_create
800 $class->update_or_create({ key => $val, ... });
802 First, search for an existing row matching one of the unique constraints
803 (including the primary key) on the source of this resultset. If a row is
804 found, update it with the other given column values. Otherwise, create a new
807 Takes an optional C<key> attribute to search on a specific unique constraint.
810 # In your application
811 my $cd = $schema->resultset('CD')->update_or_create(
813 artist => 'Massive Attack',
814 title => 'Mezzanine',
817 { key => 'artist_title' }
820 If no C<key> is specified, it searches on all unique constraints defined on the
821 source, including the primary key.
823 If the C<key> is specified as C<primary>, search only on the primary key.
825 See also L</find> and L</find_or_create>.
829 sub update_or_create {
831 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
832 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
834 my %unique_constraints = $self->result_source->unique_constraints;
835 my @constraint_names = (exists $attrs->{key}
837 : keys %unique_constraints);
840 foreach my $name (@constraint_names) {
841 my @unique_cols = @{ $unique_constraints{$name} };
843 map { $_ => $hash->{$_} }
844 grep { exists $hash->{$_} }
847 push @unique_hashes, \%unique_hash
848 if (scalar keys %unique_hash == scalar @unique_cols);
851 if (@unique_hashes) {
852 my $row = $self->single(\@unique_hashes);
854 $row->set_columns($hash);
860 return $self->create($hash);
865 Gets the contents of the cache for the resultset.
870 shift->{all_cache} || [];
875 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
880 my ( $self, $data ) = @_;
881 $self->throw_exception("set_cache requires an arrayref")
882 if ref $data ne 'ARRAY';
883 my $result_class = $self->result_class;
885 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
886 if ref $_ ne $result_class;
888 $self->{all_cache} = $data;
893 Clears the cache for the resultset.
898 shift->set_cache([]);
901 =head2 related_resultset
903 Returns a related resultset for the supplied relationship name.
905 $rs = $rs->related_resultset('foo');
909 sub related_resultset {
910 my ( $self, $rel, @rest ) = @_;
911 $self->{related_resultsets} ||= {};
912 return $self->{related_resultsets}{$rel} ||= do {
913 #warn "fetching related resultset for rel '$rel'";
914 my $rel_obj = $self->result_source->relationship_info($rel);
915 $self->throw_exception(
916 "search_related: result source '" . $self->result_source->name .
917 "' has no such relationship ${rel}")
918 unless $rel_obj; #die Dumper $self->{attrs};
920 my $rs = $self->search(undef, { join => $rel });
921 my $alias = defined $rs->{attrs}{seen_join}{$rel}
922 && $rs->{attrs}{seen_join}{$rel} > 1
923 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
926 $self->result_source->schema->resultset($rel_obj->{class}
936 =head2 throw_exception
938 See Schema's throw_exception
942 sub throw_exception {
944 $self->result_source->schema->throw_exception(@_);
949 The resultset takes various attributes that modify its behavior. Here's an
954 Which column(s) to order the results by. This is currently passed through
955 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
959 =head3 Arguments: (arrayref)
961 Shortcut to request a particular set of columns to be retrieved. Adds
962 C<me.> onto the start of any column without a C<.> in it and sets C<select>
963 from that, then auto-populates C<as> from C<select> as normal. (You may also
964 use the C<cols> attribute, as in earlier versions of DBIC.)
966 =head2 include_columns
968 =head3 Arguments: (arrayref)
970 Shortcut to include additional columns in the returned results - for example
972 { include_columns => ['foo.name'], join => ['foo'] }
974 would add a 'name' column to the information passed to object inflation
978 =head3 Arguments: (arrayref)
980 Indicates which columns should be selected from the storage. You can use
981 column names, or in the case of RDBMS back ends, function or stored procedure
984 $rs = $schema->resultset('Foo')->search(
989 { count => 'column_to_count' },
990 { sum => 'column_to_sum' }
995 When you use function/stored procedure names and do not supply an C<as>
996 attribute, the column names returned are storage-dependent. E.g. MySQL would
997 return a column named C<count(column_to_count)> in the above example.
1001 =head3 Arguments: (arrayref)
1003 Indicates column names for object inflation. This is used in conjunction with
1004 C<select>, usually when C<select> contains one or more function or stored
1007 $rs = $schema->resultset('Foo')->search(
1012 { count => 'column2' }
1014 as => [qw/ column1 column2_count /]
1018 my $foo = $rs->first(); # get the first Foo
1020 If the object against which the search is performed already has an accessor
1021 matching a column name specified in C<as>, the value can be retrieved using
1022 the accessor as normal:
1024 my $column1 = $foo->column1();
1026 If on the other hand an accessor does not exist in the object, you need to
1027 use C<get_column> instead:
1029 my $column2_count = $foo->get_column('column2_count');
1031 You can create your own accessors if required - see
1032 L<DBIx::Class::Manual::Cookbook> for details.
1036 Contains a list of relationships that should be joined for this query. For
1039 # Get CDs by Nine Inch Nails
1040 my $rs = $schema->resultset('CD')->search(
1041 { 'artist.name' => 'Nine Inch Nails' },
1042 { join => 'artist' }
1045 Can also contain a hash reference to refer to the other relation's relations.
1048 package MyApp::Schema::Track;
1049 use base qw/DBIx::Class/;
1050 __PACKAGE__->table('track');
1051 __PACKAGE__->add_columns(qw/trackid cd position title/);
1052 __PACKAGE__->set_primary_key('trackid');
1053 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1056 # In your application
1057 my $rs = $schema->resultset('Artist')->search(
1058 { 'track.title' => 'Teardrop' },
1060 join => { cd => 'track' },
1061 order_by => 'artist.name',
1065 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1066 similarly for a third time). For e.g.
1068 my $rs = $schema->resultset('Artist')->search(
1069 { 'cds.title' => 'Foo',
1070 'cds_2.title' => 'Bar' },
1071 { join => [ qw/cds cds/ ] });
1073 will return a set of all artists that have both a cd with title Foo and a cd
1076 If you want to fetch related objects from other tables as well, see C<prefetch>
1081 =head3 Arguments: arrayref/hashref
1083 Contains one or more relationships that should be fetched along with the main
1084 query (when they are accessed afterwards they will have already been
1085 "prefetched"). This is useful for when you know you will need the related
1086 objects, because it saves at least one query:
1088 my $rs = $schema->resultset('Tag')->search(
1097 The initial search results in SQL like the following:
1099 SELECT tag.*, cd.*, artist.* FROM tag
1100 JOIN cd ON tag.cd = cd.cdid
1101 JOIN artist ON cd.artist = artist.artistid
1103 L<DBIx::Class> has no need to go back to the database when we access the
1104 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1107 Simple prefetches will be joined automatically, so there is no need
1108 for a C<join> attribute in the above search. If you're prefetching to
1109 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1110 specify the join as well.
1112 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1113 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1114 with an accessor type of 'single' or 'filter').
1118 =head3 Arguments: (arrayref)
1120 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1121 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1124 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1125 C<join> will usually do what you need and it is strongly recommended that you
1126 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1128 In simple terms, C<from> works as follows:
1131 { <alias> => <table>, -join-type => 'inner|left|right' }
1132 [] # nested JOIN (optional)
1133 { <table.column> = <foreign_table.foreign_key> }
1139 ON <table.column> = <foreign_table.foreign_key>
1141 An easy way to follow the examples below is to remember the following:
1143 Anything inside "[]" is a JOIN
1144 Anything inside "{}" is a condition for the enclosing JOIN
1146 The following examples utilize a "person" table in a family tree application.
1147 In order to express parent->child relationships, this table is self-joined:
1149 # Person->belongs_to('father' => 'Person');
1150 # Person->belongs_to('mother' => 'Person');
1152 C<from> can be used to nest joins. Here we return all children with a father,
1153 then search against all mothers of those children:
1155 $rs = $schema->resultset('Person')->search(
1158 alias => 'mother', # alias columns in accordance with "from"
1160 { mother => 'person' },
1163 { child => 'person' },
1165 { father => 'person' },
1166 { 'father.person_id' => 'child.father_id' }
1169 { 'mother.person_id' => 'child.mother_id' }
1176 # SELECT mother.* FROM person mother
1179 # JOIN person father
1180 # ON ( father.person_id = child.father_id )
1182 # ON ( mother.person_id = child.mother_id )
1184 The type of any join can be controlled manually. To search against only people
1185 with a father in the person table, we could explicitly use C<INNER JOIN>:
1187 $rs = $schema->resultset('Person')->search(
1190 alias => 'child', # alias columns in accordance with "from"
1192 { child => 'person' },
1194 { father => 'person', -join-type => 'inner' },
1195 { 'father.id' => 'child.father_id' }
1202 # SELECT child.* FROM person child
1203 # INNER JOIN person father ON child.father_id = father.id
1207 For a paged resultset, specifies which page to retrieve. Leave unset
1208 for an unpaged resultset.
1212 For a paged resultset, how many rows per page:
1216 Can also be used to simulate an SQL C<LIMIT>.
1220 =head3 Arguments: (arrayref)
1222 A arrayref of columns to group by. Can include columns of joined tables.
1224 group_by => [qw/ column1 column2 ... /]
1228 Set to 1 to group by all columns.
1230 For more examples of using these attributes, see
1231 L<DBIx::Class::Manual::Cookbook>.