1 package DBIx::Class::ResultSet;
12 use base qw/DBIx::Class/;
13 __PACKAGE__->load_components(qw/AccessorGroup/);
14 __PACKAGE__->mk_group_accessors('simple' => 'result_source');
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</ATRRIBUTES>
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 if (my $prefetch = delete $attrs->{prefetch}) {
106 foreach my $p (ref $prefetch eq 'ARRAY'
107 ? (@{$prefetch}) : ($prefetch)) {
108 if( ref $p eq 'HASH' ) {
109 foreach my $key (keys %$p) {
110 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
115 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
118 my @prefetch = $source->resolve_prefetch($p, $attrs->{alias});
120 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
121 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
125 if ($attrs->{page}) {
126 $attrs->{rows} ||= 10;
127 $attrs->{offset} ||= 0;
128 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
131 result_source => $source,
132 cond => $attrs->{where},
133 from => $attrs->{from},
135 page => delete $attrs->{page},
138 bless ($new, $class);
144 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
145 my $new_rs = $rs->search({ foo => 3 });
147 If you need to pass in additional attributes but no additional condition,
148 call it as C<search({}, \%attrs);>.
150 # "SELECT foo, bar FROM $class_table"
151 my @all = $class->search({}, { cols => [qw/foo bar/] });
161 my $attrs = { %{$self->{attrs}} };
162 my $having = delete $attrs->{having};
163 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
164 $attrs = { %$attrs, %{ pop(@_) } };
168 ? ((@_ == 1 || ref $_[0] eq "HASH")
171 ? $self->throw_exception(
172 "Odd number of arguments to search")
175 if (defined $where) {
176 $where = (defined $attrs->{where}
178 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
179 $where, $attrs->{where} ] }
181 $attrs->{where} = $where;
184 if (defined $having) {
185 $having = (defined $attrs->{having}
187 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
188 $having, $attrs->{having} ] }
190 $attrs->{having} = $having;
193 $rs = (ref $self)->new($self->result_source, $attrs);
199 return (wantarray ? $rs->all : $rs);
202 =head2 search_literal
204 my @obj = $rs->search_literal($literal_where_cond, @bind);
205 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
207 Pass a literal chunk of SQL to be added to the conditional part of the
213 my ($self, $cond, @vals) = @_;
214 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
215 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
216 return $self->search(\$cond, $attrs);
221 =head3 Arguments: (@colvalues) | (\%cols, \%attrs?)
223 Finds a row based on its primary key or unique constraint. For example:
225 my $cd = $schema->resultset('CD')->find(5);
227 Also takes an optional C<key> attribute, to search by a specific key or unique
228 constraint. For example:
230 my $cd = $schema->resultset('CD')->find_or_create(
232 artist => 'Massive Attack',
233 title => 'Mezzanine',
235 { key => 'artist_title' }
238 See also L</find_or_create> and L</update_or_create>.
243 my ($self, @vals) = @_;
244 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
246 my @cols = $self->result_source->primary_columns;
247 if (exists $attrs->{key}) {
248 my %uniq = $self->result_source->unique_constraints;
249 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
250 unless exists $uniq{$attrs->{key}};
251 @cols = @{ $uniq{$attrs->{key}} };
253 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
254 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
258 if (ref $vals[0] eq 'HASH') {
259 $query = { %{$vals[0]} };
260 } elsif (@cols == @vals) {
262 @{$query}{@cols} = @vals;
266 foreach (keys %$query) {
268 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
270 #warn Dumper($query);
272 ? $self->search($query,$attrs)->single
273 : $self->single($query));
276 =head2 search_related
278 $rs->search_related('relname', $cond?, $attrs?);
280 Search the specified relationship. Optionally specify a condition for matching
286 return shift->related_resultset(shift)->search(@_);
291 Returns a storage-driven cursor to the given resultset.
297 my ($attrs) = $self->{attrs};
298 $attrs = { %$attrs };
299 return $self->{cursor}
300 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
301 $attrs->{where},$attrs);
306 Inflates the first result without creating a cursor
311 my ($self, $extra) = @_;
312 my ($attrs) = $self->{attrs};
313 $attrs = { %$attrs };
315 if (defined $attrs->{where}) {
318 => [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
319 delete $attrs->{where}, $extra ]
322 $attrs->{where} = $extra;
325 my @data = $self->result_source->storage->select_single(
326 $self->{from}, $attrs->{select},
327 $attrs->{where},$attrs);
328 return (@data ? $self->_construct_object(@data) : ());
334 Perform a search, but use C<LIKE> instead of equality as the condition. Note
335 that this is simply a convenience method; you most likely want to use
336 L</search> with specific operators.
338 For more information, see L<DBIx::Class::Manual::Cookbook>.
345 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
348 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
349 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
350 return $class->search($query, { %$attrs });
355 =head3 Arguments: ($first, $last)
357 Returns a subset of elements from the resultset.
362 my ($self, $min, $max) = @_;
363 my $attrs = { %{ $self->{attrs} || {} } };
364 $attrs->{offset} ||= 0;
365 $attrs->{offset} += $min;
366 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
367 my $slice = (ref $self)->new($self->result_source, $attrs);
368 return (wantarray ? $slice->all : $slice);
373 Returns the next element in the resultset (C<undef> is there is none).
375 Can be used to efficiently iterate over records in the resultset:
377 my $rs = $schema->resultset('CD')->search({});
378 while (my $cd = $rs->next) {
387 if( @{$cache = $self->{all_cache} || []}) {
388 $self->{all_cache_position} ||= 0;
389 my $obj = $cache->[$self->{all_cache_position}];
390 $self->{all_cache_position}++;
393 if ($self->{attrs}{cache}) {
394 $self->{all_cache_position} = 0;
395 return ($self->all)[0];
397 my @row = $self->cursor->next;
398 # warn Dumper(\@row); use Data::Dumper;
399 return unless (@row);
400 return $self->_construct_object(@row);
403 sub _construct_object {
404 my ($self, @row) = @_;
405 my @row_orig = @row; # copy @row for key comparison later, because @row will change
406 my @as = @{ $self->{attrs}{as} };
407 #use Data::Dumper; warn Dumper \@as;
408 #warn "@cols -> @row";
409 my $info = [ {}, {} ];
410 foreach my $as (@as) {
413 my @parts = split(/\./, $as);
414 my $col = pop(@parts);
415 foreach my $p (@parts) {
416 $target = $target->[1]->{$p} ||= [];
418 $rs = $rs->related_resultset($p) if $rs->{attrs}->{cache};
421 $target->[0]->{$col} = shift @row
422 if ref($target->[0]) ne 'ARRAY'; # arrayref is pre-inflated objects, do not overwrite
424 #use Data::Dumper; warn Dumper(\@as, $info);
425 my $new = $self->result_source->result_class->inflate_result(
426 $self->result_source, @$info);
427 $new = $self->{attrs}{record_filter}->($new)
428 if exists $self->{attrs}{record_filter};
430 if( $self->{attrs}->{cache} ) {
431 while( my( $rel, $rs ) = each( %{$self->{related_resultsets}} ) ) {
433 #warn "$rel:", @{$rs->get_cache};
435 $self->build_rr( $self, $new );
442 # build related resultsets for supplied object
443 my ( $self, $context, $obj ) = @_;
446 while( my ($rel, $rs) = each( %{$context->{related_resultsets}} ) ) {
447 #warn "context:", $context->result_source->name, ", rel:$rel, rs:", $rs->result_source->name;
450 my $cond = $context->result_source->relationship_info($rel)->{cond};
452 while( my( $rel_key, $pk ) = each(%$cond) ) {
455 $map->{$rel_key} = $pk;
459 while( my $rel_obj = $rs->next ) {
460 while( my( $rel_key, $pk ) = each(%$map) ) {
461 if( $rel_obj->get_column($rel_key) eq $obj->get_column($pk) ) {
462 push @objs, $rel_obj;
467 my $rel_rs = $obj->related_resultset($rel);
468 $rel_rs->{attrs}->{cache} = 1;
469 $rel_rs->set_cache( \@objs );
471 while( my $rel_obj = $rel_rs->next ) {
472 $self->build_rr( $rs, $rel_obj );
481 Returns a reference to the result source for this recordset.
488 Performs an SQL C<COUNT> with the same query as the resultset was built
489 with to find the number of elements. If passed arguments, does a search
490 on the resultset and counts the results of that.
492 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
493 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
494 not support C<DISTINCT> with multiple columns. If you are using such a
495 database, you should only use columns from the main table in your C<group_by>
502 return $self->search(@_)->count if @_ && defined $_[0];
503 unless (defined $self->{count}) {
504 return scalar @{ $self->get_cache }
505 if @{ $self->get_cache };
507 my $select = { 'count' => '*' };
508 my $attrs = { %{ $self->{attrs} } };
509 if( $group_by = delete $attrs->{group_by} ) {
510 delete $attrs->{having};
511 my @distinct = (ref $group_by ? @$group_by : ($group_by));
512 # todo: try CONCAT for multi-column pk
513 my @pk = $self->result_source->primary_columns;
514 if( scalar(@pk) == 1 ) {
516 my $alias = $attrs->{alias};
517 my $re = qr/^($alias\.)?$pk$/;
518 foreach my $column ( @distinct) {
519 if( $column =~ $re ) {
520 @distinct = ( $column );
526 $select = { count => { 'distinct' => \@distinct } };
527 #use Data::Dumper; die Dumper $select;
530 $attrs->{select} = $select;
531 $attrs->{as} = [ 'count' ];
532 # offset, order by and page are not needed to count. record_filter is cdbi
533 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
535 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
537 return 0 unless $self->{count};
538 my $count = $self->{count};
539 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
540 $count = $self->{attrs}{rows} if
541 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
547 Calls L</search_literal> with the passed arguments, then L</count>.
551 sub count_literal { shift->search_literal(@_)->count; }
555 Returns all elements in the resultset. Called implictly if the resultset
556 is returned in list context.
562 return @{ $self->get_cache }
563 if @{ $self->get_cache };
564 if( $self->{attrs}->{cache} ) {
565 my @obj = map { $self->_construct_object(@$_); }
567 $self->set_cache( \@obj );
570 return map { $self->_construct_object(@$_); }
576 Resets the resultset's cursor, so you can iterate through the elements again.
582 $self->{all_cache_position} = 0;
583 $self->cursor->reset;
589 Resets the resultset and returns the first element.
594 return $_[0]->reset->next;
599 =head3 Arguments: (\%values)
601 Sets the specified columns in the resultset to the supplied values.
606 my ($self, $values) = @_;
607 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
608 return $self->result_source->storage->update(
609 $self->result_source->from, $values, $self->{cond});
614 =head3 Arguments: (\%values)
616 Fetches all objects and updates them one at a time. Note that C<update_all>
617 will run cascade triggers while L</update> will not.
622 my ($self, $values) = @_;
623 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
624 foreach my $obj ($self->all) {
625 $obj->set_columns($values)->update;
632 Deletes the contents of the resultset from its result source.
639 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
640 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
641 if (ref $self->{cond} eq 'ARRAY') {
642 $del = [ map { my %hash;
643 foreach my $key (keys %{$_}) {
645 $hash{$1} = $_->{$key};
646 }; \%hash; } @{$self->{cond}} ];
647 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
648 $del->{-and} = [ map { my %hash;
649 foreach my $key (keys %{$_}) {
651 $hash{$1} = $_->{$key};
652 }; \%hash; } @{$self->{cond}{-and}} ];
654 foreach my $key (keys %{$self->{cond}}) {
656 $del->{$1} = $self->{cond}{$key};
659 $self->result_source->storage->delete($self->result_source->from, $del);
665 Fetches all objects and deletes them one at a time. Note that C<delete_all>
666 will run cascade triggers while L</delete> will not.
672 $_->delete for $self->all;
678 Returns a L<Data::Page> object for the current resultset. Only makes
679 sense for queries with a C<page> attribute.
685 my $attrs = $self->{attrs};
686 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
687 $attrs->{rows} ||= 10;
689 return $self->{pager} ||= Data::Page->new(
690 $self->{count}, $attrs->{rows}, $self->{page});
695 =head3 Arguments: ($page_num)
697 Returns a new resultset for the specified page.
702 my ($self, $page) = @_;
703 my $attrs = { %{$self->{attrs}} };
704 $attrs->{page} = $page;
705 return (ref $self)->new($self->result_source, $attrs);
710 =head3 Arguments: (\%vals)
712 Creates a result in the resultset's result class.
717 my ($self, $values) = @_;
718 $self->throw_exception( "new_result needs a hash" )
719 unless (ref $values eq 'HASH');
720 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
721 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
723 my $alias = $self->{attrs}{alias};
724 foreach my $key (keys %{$self->{cond}||{}}) {
725 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
727 my $obj = $self->result_source->result_class->new(\%new);
728 $obj->result_source($self->result_source) if $obj->can('result_source');
734 =head3 Arguments: (\%vals)
736 Inserts a record into the resultset and returns the object.
738 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
743 my ($self, $attrs) = @_;
744 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
745 return $self->new_result($attrs)->insert;
748 =head2 find_or_create
750 =head3 Arguments: (\%vals, \%attrs?)
752 $class->find_or_create({ key => $val, ... });
754 Searches for a record matching the search condition; if it doesn't find one,
755 creates one and returns that instead.
757 my $cd = $schema->resultset('CD')->find_or_create({
759 artist => 'Massive Attack',
760 title => 'Mezzanine',
764 Also takes an optional C<key> attribute, to search by a specific key or unique
765 constraint. For example:
767 my $cd = $schema->resultset('CD')->find_or_create(
769 artist => 'Massive Attack',
770 title => 'Mezzanine',
772 { key => 'artist_title' }
775 See also L</find> and L</update_or_create>.
781 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
782 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
783 my $exists = $self->find($hash, $attrs);
784 return defined($exists) ? $exists : $self->create($hash);
787 =head2 update_or_create
789 $class->update_or_create({ key => $val, ... });
791 First, search for an existing row matching one of the unique constraints
792 (including the primary key) on the source of this resultset. If a row is
793 found, update it with the other given column values. Otherwise, create a new
796 Takes an optional C<key> attribute to search on a specific unique constraint.
799 # In your application
800 my $cd = $schema->resultset('CD')->update_or_create(
802 artist => 'Massive Attack',
803 title => 'Mezzanine',
806 { key => 'artist_title' }
809 If no C<key> is specified, it searches on all unique constraints defined on the
810 source, including the primary key.
812 If the C<key> is specified as C<primary>, search only on the primary key.
814 See also L</find> and L</find_or_create>.
818 sub update_or_create {
821 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
822 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
824 my %unique_constraints = $self->result_source->unique_constraints;
825 my @constraint_names = (exists $attrs->{key}
827 : keys %unique_constraints);
830 foreach my $name (@constraint_names) {
831 my @unique_cols = @{ $unique_constraints{$name} };
833 map { $_ => $hash->{$_} }
834 grep { exists $hash->{$_} }
837 push @unique_hashes, \%unique_hash
838 if (scalar keys %unique_hash == scalar @unique_cols);
842 if (@unique_hashes) {
843 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
845 $row->set_columns($hash);
851 $row = $self->create($hash);
859 Gets the contents of the cache for the resultset.
865 return $self->{all_cache} || [];
870 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
875 my ( $self, $data ) = @_;
876 $self->throw_exception("set_cache requires an arrayref")
877 if ref $data ne 'ARRAY';
878 my $result_class = $self->result_source->result_class;
880 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
881 if ref $_ ne $result_class;
883 $self->{all_cache} = $data;
888 Clears the cache for the resultset.
894 $self->set_cache([]);
897 =head2 related_resultset
899 Returns a related resultset for the supplied relationship name.
901 $rs = $rs->related_resultset('foo');
905 sub related_resultset {
906 my ( $self, $rel, @rest ) = @_;
907 $self->{related_resultsets} ||= {};
908 my $resultsets = $self->{related_resultsets};
909 if( !exists $resultsets->{$rel} ) {
910 #warn "fetching related resultset for rel '$rel'";
911 my $rel_obj = $self->result_source->relationship_info($rel);
912 $self->throw_exception(
913 "search_related: result source '" . $self->result_source->name .
914 "' has no such relationship ${rel}")
915 unless $rel_obj; #die Dumper $self->{attrs};
917 if( $self->{attrs}->{cache} ) {
918 $rs = $self->search(undef);
921 $rs = $self->search(undef, { join => $rel });
923 #use Data::Dumper; die Dumper $rs->{attrs};#$rs = $self->search( undef );
924 #use Data::Dumper; warn Dumper $self->{attrs}, Dumper $rs->{attrs};
925 my $alias = (defined $rs->{attrs}{seen_join}{$rel}
926 && $rs->{attrs}{seen_join}{$rel} > 1
927 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
929 $resultsets->{$rel} =
930 $self->result_source->schema->resultset($rel_obj->{class}
938 return $resultsets->{$rel};
941 =head2 throw_exception
943 See Schema's throw_exception
947 sub throw_exception {
949 $self->result_source->schema->throw_exception(@_);
954 The resultset takes various attributes that modify its behavior. Here's an
959 Which column(s) to order the results by. This is currently passed through
960 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
964 =head3 Arguments: (arrayref)
966 Shortcut to request a particular set of columns to be retrieved. Adds
967 C<me.> onto the start of any column without a C<.> in it and sets C<select>
968 from that, then auto-populates C<as> from C<select> as normal.
970 =head2 include_columns
972 =head3 Arguments: (arrayref)
974 Shortcut to include additional columns in the returned results - for example
976 { include_columns => ['foo.name'], join => ['foo'] }
978 would add a 'name' column to the information passed to object inflation
982 =head3 Arguments: (arrayref)
984 Indicates which columns should be selected from the storage. You can use
985 column names, or in the case of RDBMS back ends, function or stored procedure
988 $rs = $schema->resultset('Foo')->search(
993 { count => 'column_to_count' },
994 { sum => 'column_to_sum' }
999 When you use function/stored procedure names and do not supply an C<as>
1000 attribute, the column names returned are storage-dependent. E.g. MySQL would
1001 return a column named C<count(column_to_count)> in the above example.
1005 =head3 Arguments: (arrayref)
1007 Indicates column names for object inflation. This is used in conjunction with
1008 C<select>, usually when C<select> contains one or more function or stored
1011 $rs = $schema->resultset('Foo')->search(
1016 { count => 'column2' }
1018 as => [qw/ column1 column2_count /]
1022 my $foo = $rs->first(); # get the first Foo
1024 If the object against which the search is performed already has an accessor
1025 matching a column name specified in C<as>, the value can be retrieved using
1026 the accessor as normal:
1028 my $column1 = $foo->column1();
1030 If on the other hand an accessor does not exist in the object, you need to
1031 use C<get_column> instead:
1033 my $column2_count = $foo->get_column('column2_count');
1035 You can create your own accessors if required - see
1036 L<DBIx::Class::Manual::Cookbook> for details.
1040 Contains a list of relationships that should be joined for this query. For
1043 # Get CDs by Nine Inch Nails
1044 my $rs = $schema->resultset('CD')->search(
1045 { 'artist.name' => 'Nine Inch Nails' },
1046 { join => 'artist' }
1049 Can also contain a hash reference to refer to the other relation's relations.
1052 package MyApp::Schema::Track;
1053 use base qw/DBIx::Class/;
1054 __PACKAGE__->table('track');
1055 __PACKAGE__->add_columns(qw/trackid cd position title/);
1056 __PACKAGE__->set_primary_key('trackid');
1057 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1060 # In your application
1061 my $rs = $schema->resultset('Artist')->search(
1062 { 'track.title' => 'Teardrop' },
1064 join => { cd => 'track' },
1065 order_by => 'artist.name',
1069 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1070 similarly for a third time). For e.g.
1072 my $rs = $schema->resultset('Artist')->search(
1073 { 'cds.title' => 'Foo',
1074 'cds_2.title' => 'Bar' },
1075 { join => [ qw/cds cds/ ] });
1077 will return a set of all artists that have both a cd with title Foo and a cd
1080 If you want to fetch related objects from other tables as well, see C<prefetch>
1085 =head3 Arguments: arrayref/hashref
1087 Contains one or more relationships that should be fetched along with the main
1088 query (when they are accessed afterwards they will have already been
1089 "prefetched"). This is useful for when you know you will need the related
1090 objects, because it saves at least one query:
1092 my $rs = $schema->resultset('Tag')->search(
1101 The initial search results in SQL like the following:
1103 SELECT tag.*, cd.*, artist.* FROM tag
1104 JOIN cd ON tag.cd = cd.cdid
1105 JOIN artist ON cd.artist = artist.artistid
1107 L<DBIx::Class> has no need to go back to the database when we access the
1108 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1111 Simple prefetches will be joined automatically, so there is no need
1112 for a C<join> attribute in the above search. If you're prefetching to
1113 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1114 specify the join as well.
1116 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1117 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1118 with an accessor type of 'single' or 'filter').
1122 =head3 Arguments: (arrayref)
1124 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1125 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1128 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1129 C<join> will usually do what you need and it is strongly recommended that you
1130 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1132 In simple terms, C<from> works as follows:
1135 { <alias> => <table>, -join-type => 'inner|left|right' }
1136 [] # nested JOIN (optional)
1137 { <table.column> = <foreign_table.foreign_key> }
1143 ON <table.column> = <foreign_table.foreign_key>
1145 An easy way to follow the examples below is to remember the following:
1147 Anything inside "[]" is a JOIN
1148 Anything inside "{}" is a condition for the enclosing JOIN
1150 The following examples utilize a "person" table in a family tree application.
1151 In order to express parent->child relationships, this table is self-joined:
1153 # Person->belongs_to('father' => 'Person');
1154 # Person->belongs_to('mother' => 'Person');
1156 C<from> can be used to nest joins. Here we return all children with a father,
1157 then search against all mothers of those children:
1159 $rs = $schema->resultset('Person')->search(
1162 alias => 'mother', # alias columns in accordance with "from"
1164 { mother => 'person' },
1167 { child => 'person' },
1169 { father => 'person' },
1170 { 'father.person_id' => 'child.father_id' }
1173 { 'mother.person_id' => 'child.mother_id' }
1180 # SELECT mother.* FROM person mother
1183 # JOIN person father
1184 # ON ( father.person_id = child.father_id )
1186 # ON ( mother.person_id = child.mother_id )
1188 The type of any join can be controlled manually. To search against only people
1189 with a father in the person table, we could explicitly use C<INNER JOIN>:
1191 $rs = $schema->resultset('Person')->search(
1194 alias => 'child', # alias columns in accordance with "from"
1196 { child => 'person' },
1198 { father => 'person', -join-type => 'inner' },
1199 { 'father.id' => 'child.father_id' }
1206 # SELECT child.* FROM person child
1207 # INNER JOIN person father ON child.father_id = father.id
1211 For a paged resultset, specifies which page to retrieve. Leave unset
1212 for an unpaged resultset.
1216 For a paged resultset, how many rows per page:
1220 Can also be used to simulate an SQL C<LIMIT>.
1224 =head3 Arguments: (arrayref)
1226 A arrayref of columns to group by. Can include columns of joined tables.
1228 group_by => [qw/ column1 column2 ... /]
1232 Set to 1 to group by all columns.
1234 For more examples of using these attributes, see
1235 L<DBIx::Class::Manual::Cookbook>.