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/] });
158 #use Data::Dumper;warn Dumper(@_);
162 my $attrs = { %{$self->{attrs}} };
163 my $having = delete $attrs->{having};
164 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
165 $attrs = { %$attrs, %{ pop(@_) } };
168 my $where = (@_ ? ((@_ == 1 || ref $_[0] eq "HASH") ? shift : {@_}) : undef());
169 if (defined $where) {
170 $where = (defined $attrs->{where}
172 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
173 $where, $attrs->{where} ] }
175 $attrs->{where} = $where;
178 if (defined $having) {
179 $having = (defined $attrs->{having}
181 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
182 $having, $attrs->{having} ] }
184 $attrs->{having} = $having;
187 $rs = (ref $self)->new($self->result_source, $attrs);
193 return (wantarray ? $rs->all : $rs);
196 =head2 search_literal
198 my @obj = $rs->search_literal($literal_where_cond, @bind);
199 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
201 Pass a literal chunk of SQL to be added to the conditional part of the
207 my ($self, $cond, @vals) = @_;
208 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
209 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
210 return $self->search(\$cond, $attrs);
215 =head3 Arguments: (@colvalues) | (\%cols, \%attrs?)
217 Finds a row based on its primary key or unique constraint. For example:
219 my $cd = $schema->resultset('CD')->find(5);
221 Also takes an optional C<key> attribute, to search by a specific key or unique
222 constraint. For example:
224 my $cd = $schema->resultset('CD')->find_or_create(
226 artist => 'Massive Attack',
227 title => 'Mezzanine',
229 { key => 'artist_title' }
232 See also L</find_or_create> and L</update_or_create>.
237 my ($self, @vals) = @_;
238 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
240 my @cols = $self->result_source->primary_columns;
241 if (exists $attrs->{key}) {
242 my %uniq = $self->result_source->unique_constraints;
243 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
244 unless exists $uniq{$attrs->{key}};
245 @cols = @{ $uniq{$attrs->{key}} };
247 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
248 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
252 if (ref $vals[0] eq 'HASH') {
253 $query = { %{$vals[0]} };
254 } elsif (@cols == @vals) {
256 @{$query}{@cols} = @vals;
260 foreach (keys %$query) {
262 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
264 #warn Dumper($query);
266 ? $self->search($query,$attrs)->single
267 : $self->single($query));
270 =head2 search_related
272 $rs->search_related('relname', $cond?, $attrs?);
274 Search the specified relationship. Optionally specify a condition for matching
280 return shift->related_resultset(shift)->search(@_);
285 Returns a storage-driven cursor to the given resultset.
291 my ($attrs) = $self->{attrs};
292 $attrs = { %$attrs };
293 return $self->{cursor}
294 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
295 $attrs->{where},$attrs);
300 Inflates the first result without creating a cursor
305 my ($self, $extra) = @_;
306 my ($attrs) = $self->{attrs};
307 $attrs = { %$attrs };
309 if (defined $attrs->{where}) {
312 => [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
313 delete $attrs->{where}, $extra ]
316 $attrs->{where} = $extra;
319 my @data = $self->result_source->storage->select_single(
320 $self->{from}, $attrs->{select},
321 $attrs->{where},$attrs);
322 return (@data ? $self->_construct_object(@data) : ());
328 Perform a search, but use C<LIKE> instead of equality as the condition. Note
329 that this is simply a convenience method; you most likely want to use
330 L</search> with specific operators.
332 For more information, see L<DBIx::Class::Manual::Cookbook>.
339 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
342 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
343 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
344 return $class->search($query, { %$attrs });
349 =head3 Arguments: ($first, $last)
351 Returns a subset of elements from the resultset.
356 my ($self, $min, $max) = @_;
357 my $attrs = { %{ $self->{attrs} || {} } };
358 $attrs->{offset} ||= 0;
359 $attrs->{offset} += $min;
360 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
361 my $slice = (ref $self)->new($self->result_source, $attrs);
362 return (wantarray ? $slice->all : $slice);
367 Returns the next element in the resultset (C<undef> is there is none).
369 Can be used to efficiently iterate over records in the resultset:
371 my $rs = $schema->resultset('CD')->search({});
372 while (my $cd = $rs->next) {
380 my $cache = $self->get_cache;
382 $self->{all_cache_position} ||= 0;
383 my $obj = $cache->[$self->{all_cache_position}];
384 $self->{all_cache_position}++;
387 my @row = $self->cursor->next;
388 # warn Dumper(\@row); use Data::Dumper;
389 return unless (@row);
390 return $self->_construct_object(@row);
393 sub _construct_object {
394 my ($self, @row) = @_;
395 my @row_orig = @row; # copy @row for key comparison later, because @row will change
396 my @as = @{ $self->{attrs}{as} };
397 #use Data::Dumper; warn Dumper \@as;
398 #warn "@cols -> @row";
399 my $info = [ {}, {} ];
400 foreach my $as (@as) {
403 my @parts = split(/\./, $as);
404 my $col = pop(@parts);
405 foreach my $p (@parts) {
406 $target = $target->[1]->{$p} ||= [];
408 $rs = $rs->related_resultset($p) if $rs->{attrs}->{cache};
411 $target->[0]->{$col} = shift @row
412 if ref($target->[0]) ne 'ARRAY'; # arrayref is pre-inflated objects, do not overwrite
414 #use Data::Dumper; warn Dumper(\@as, $info);
415 my $new = $self->result_source->result_class->inflate_result(
416 $self->result_source, @$info);
417 $new = $self->{attrs}{record_filter}->($new)
418 if exists $self->{attrs}{record_filter};
420 if( $self->{attrs}->{cache} ) {
421 while( my( $rel, $rs ) = each( %{$self->{related_resultsets}} ) ) {
423 #warn "$rel:", @{$rs->get_cache};
425 $self->build_rr( $self, $new );
432 # build related resultsets for supplied object
433 my ( $self, $context, $obj ) = @_;
436 while( my ($rel, $rs) = each( %{$context->{related_resultsets}} ) ) {
437 #warn "context:", $context->result_source->name, ", rel:$rel, rs:", $rs->result_source->name;
440 my $cond = $context->result_source->relationship_info($rel)->{cond};
442 while( my( $rel_key, $pk ) = each(%$cond) ) {
445 $map->{$rel_key} = $pk;
449 while( my $rel_obj = $rs->next ) {
450 while( my( $rel_key, $pk ) = each(%$map) ) {
451 if( $rel_obj->get_column($rel_key) eq $obj->get_column($pk) ) {
452 push @objs, $rel_obj;
457 my $rel_rs = $obj->related_resultset($rel);
458 $rel_rs->{attrs}->{cache} = 1;
459 $rel_rs->set_cache( \@objs );
461 while( my $rel_obj = $rel_rs->next ) {
462 $self->build_rr( $rs, $rel_obj );
471 Returns a reference to the result source for this recordset.
478 Performs an SQL C<COUNT> with the same query as the resultset was built
479 with to find the number of elements. If passed arguments, does a search
480 on the resultset and counts the results of that.
482 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
483 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
484 not support C<DISTINCT> with multiple columns. If you are using such a
485 database, you should only use columns from the main table in your C<group_by>
492 return $self->search(@_)->count if @_ && defined $_[0];
493 unless (defined $self->{count}) {
494 return scalar @{ $self->get_cache }
495 if @{ $self->get_cache };
497 my $select = { 'count' => '*' };
498 my $attrs = { %{ $self->{attrs} } };
499 if( $group_by = delete $attrs->{group_by} ) {
500 delete $attrs->{having};
501 my @distinct = (ref $group_by ? @$group_by : ($group_by));
502 # todo: try CONCAT for multi-column pk
503 my @pk = $self->result_source->primary_columns;
504 if( scalar(@pk) == 1 ) {
506 my $alias = $attrs->{alias};
507 my $re = qr/^($alias\.)?$pk$/;
508 foreach my $column ( @distinct) {
509 if( $column =~ $re ) {
510 @distinct = ( $column );
516 $select = { count => { 'distinct' => \@distinct } };
517 #use Data::Dumper; die Dumper $select;
520 $attrs->{select} = $select;
521 $attrs->{as} = [ 'count' ];
522 # offset, order by and page are not needed to count. record_filter is cdbi
523 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
525 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
527 return 0 unless $self->{count};
528 my $count = $self->{count};
529 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
530 $count = $self->{attrs}{rows} if
531 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
537 Calls L</search_literal> with the passed arguments, then L</count>.
541 sub count_literal { shift->search_literal(@_)->count; }
545 Returns all elements in the resultset. Called implictly if the resultset
546 is returned in list context.
552 return @{ $self->get_cache }
553 if @{ $self->get_cache };
554 if( $self->{attrs}->{cache} ) {
555 my @obj = map { $self->_construct_object(@$_); }
557 $self->set_cache( \@obj );
558 return @{ $self->get_cache };
560 return map { $self->_construct_object(@$_); }
566 Resets the resultset's cursor, so you can iterate through the elements again.
572 $self->{all_cache_position} = 0;
573 $self->cursor->reset;
579 Resets the resultset and returns the first element.
584 return $_[0]->reset->next;
589 =head3 Arguments: (\%values)
591 Sets the specified columns in the resultset to the supplied values.
596 my ($self, $values) = @_;
597 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
598 return $self->result_source->storage->update(
599 $self->result_source->from, $values, $self->{cond});
604 =head3 Arguments: (\%values)
606 Fetches all objects and updates them one at a time. Note that C<update_all>
607 will run cascade triggers while L</update> will not.
612 my ($self, $values) = @_;
613 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
614 foreach my $obj ($self->all) {
615 $obj->set_columns($values)->update;
622 Deletes the contents of the resultset from its result source.
629 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
630 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
631 if (ref $self->{cond} eq 'ARRAY') {
632 $del = [ map { my %hash;
633 foreach my $key (keys %{$_}) {
635 $hash{$1} = $_->{$key};
636 }; \%hash; } @{$self->{cond}} ];
637 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
638 $del->{-and} = [ map { my %hash;
639 foreach my $key (keys %{$_}) {
641 $hash{$1} = $_->{$key};
642 }; \%hash; } @{$self->{cond}{-and}} ];
644 foreach my $key (keys %{$self->{cond}}) {
646 $del->{$1} = $self->{cond}{$key};
649 $self->result_source->storage->delete($self->result_source->from, $del);
655 Fetches all objects and deletes them one at a time. Note that C<delete_all>
656 will run cascade triggers while L</delete> will not.
662 $_->delete for $self->all;
668 Returns a L<Data::Page> object for the current resultset. Only makes
669 sense for queries with a C<page> attribute.
675 my $attrs = $self->{attrs};
676 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
677 $attrs->{rows} ||= 10;
679 return $self->{pager} ||= Data::Page->new(
680 $self->{count}, $attrs->{rows}, $self->{page});
685 =head3 Arguments: ($page_num)
687 Returns a new resultset for the specified page.
692 my ($self, $page) = @_;
693 my $attrs = { %{$self->{attrs}} };
694 $attrs->{page} = $page;
695 return (ref $self)->new($self->result_source, $attrs);
700 =head3 Arguments: (\%vals)
702 Creates a result in the resultset's result class.
707 my ($self, $values) = @_;
708 $self->throw_exception( "new_result needs a hash" )
709 unless (ref $values eq 'HASH');
710 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
711 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
713 my $alias = $self->{attrs}{alias};
714 foreach my $key (keys %{$self->{cond}||{}}) {
715 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
717 my $obj = $self->result_source->result_class->new(\%new);
718 $obj->result_source($self->result_source) if $obj->can('result_source');
724 =head3 Arguments: (\%vals)
726 Inserts a record into the resultset and returns the object.
728 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
733 my ($self, $attrs) = @_;
734 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
735 return $self->new_result($attrs)->insert;
738 =head2 find_or_create
740 =head3 Arguments: (\%vals, \%attrs?)
742 $class->find_or_create({ key => $val, ... });
744 Searches for a record matching the search condition; if it doesn't find one,
745 creates one and returns that instead.
747 my $cd = $schema->resultset('CD')->find_or_create({
749 artist => 'Massive Attack',
750 title => 'Mezzanine',
754 Also takes an optional C<key> attribute, to search by a specific key or unique
755 constraint. For example:
757 my $cd = $schema->resultset('CD')->find_or_create(
759 artist => 'Massive Attack',
760 title => 'Mezzanine',
762 { key => 'artist_title' }
765 See also L</find> and L</update_or_create>.
771 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
772 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
773 my $exists = $self->find($hash, $attrs);
774 return defined($exists) ? $exists : $self->create($hash);
777 =head2 update_or_create
779 $class->update_or_create({ key => $val, ... });
781 First, search for an existing row matching one of the unique constraints
782 (including the primary key) on the source of this resultset. If a row is
783 found, update it with the other given column values. Otherwise, create a new
786 Takes an optional C<key> attribute to search on a specific unique constraint.
789 # In your application
790 my $cd = $schema->resultset('CD')->update_or_create(
792 artist => 'Massive Attack',
793 title => 'Mezzanine',
796 { key => 'artist_title' }
799 If no C<key> is specified, it searches on all unique constraints defined on the
800 source, including the primary key.
802 If the C<key> is specified as C<primary>, search only on the primary key.
804 See also L</find> and L</find_or_create>.
808 sub update_or_create {
811 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
812 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
814 my %unique_constraints = $self->result_source->unique_constraints;
815 my @constraint_names = (exists $attrs->{key}
817 : keys %unique_constraints);
820 foreach my $name (@constraint_names) {
821 my @unique_cols = @{ $unique_constraints{$name} };
823 map { $_ => $hash->{$_} }
824 grep { exists $hash->{$_} }
827 push @unique_hashes, \%unique_hash
828 if (scalar keys %unique_hash == scalar @unique_cols);
832 if (@unique_hashes) {
833 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
835 $row->set_columns($hash);
841 $row = $self->create($hash);
849 Gets the contents of the cache for the resultset.
855 return $self->{all_cache} || [];
860 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
865 my ( $self, $data ) = @_;
866 $self->throw_exception("set_cache requires an arrayref")
867 if ref $data ne 'ARRAY';
868 my $result_class = $self->result_source->result_class;
870 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
871 if ref $_ ne $result_class;
873 $self->{all_cache} = $data;
878 Clears the cache for the resultset.
884 $self->set_cache([]);
887 =head2 related_resultset
889 Returns a related resultset for the supplied relationship name.
891 $rs = $rs->related_resultset('foo');
895 sub related_resultset {
896 my ( $self, $rel, @rest ) = @_;
897 $self->{related_resultsets} ||= {};
898 my $resultsets = $self->{related_resultsets};
899 if( !exists $resultsets->{$rel} ) {
900 #warn "fetching related resultset for rel '$rel'";
901 my $rel_obj = $self->result_source->relationship_info($rel);
902 $self->throw_exception(
903 "search_related: result source '" . $self->result_source->name .
904 "' has no such relationship ${rel}")
905 unless $rel_obj; #die Dumper $self->{attrs};
907 if( $self->{attrs}->{cache} ) {
908 $rs = $self->search(undef);
911 $rs = $self->search(undef, { join => $rel });
913 #use Data::Dumper; die Dumper $rs->{attrs};#$rs = $self->search( undef );
914 #use Data::Dumper; warn Dumper $self->{attrs}, Dumper $rs->{attrs};
915 my $alias = (defined $rs->{attrs}{seen_join}{$rel}
916 && $rs->{attrs}{seen_join}{$rel} > 1
917 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
919 $resultsets->{$rel} =
920 $self->result_source->schema->resultset($rel_obj->{class}
928 return $resultsets->{$rel};
931 =head2 throw_exception
933 See Schema's throw_exception
937 sub throw_exception {
939 $self->result_source->schema->throw_exception(@_);
944 The resultset takes various attributes that modify its behavior. Here's an
949 Which column(s) to order the results by. This is currently passed through
950 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
954 =head3 Arguments: (arrayref)
956 Shortcut to request a particular set of columns to be retrieved. Adds
957 C<me.> onto the start of any column without a C<.> in it and sets C<select>
958 from that, then auto-populates C<as> from C<select> as normal.
960 =head2 include_columns
962 =head3 Arguments: (arrayref)
964 Shortcut to include additional columns in the returned results - for example
966 { include_columns => ['foo.name'], join => ['foo'] }
968 would add a 'name' column to the information passed to object inflation
972 =head3 Arguments: (arrayref)
974 Indicates which columns should be selected from the storage. You can use
975 column names, or in the case of RDBMS back ends, function or stored procedure
978 $rs = $schema->resultset('Foo')->search(
983 { count => 'column_to_count' },
984 { sum => 'column_to_sum' }
989 When you use function/stored procedure names and do not supply an C<as>
990 attribute, the column names returned are storage-dependent. E.g. MySQL would
991 return a column named C<count(column_to_count)> in the above example.
995 =head3 Arguments: (arrayref)
997 Indicates column names for object inflation. This is used in conjunction with
998 C<select>, usually when C<select> contains one or more function or stored
1001 $rs = $schema->resultset('Foo')->search(
1006 { count => 'column2' }
1008 as => [qw/ column1 column2_count /]
1012 my $foo = $rs->first(); # get the first Foo
1014 If the object against which the search is performed already has an accessor
1015 matching a column name specified in C<as>, the value can be retrieved using
1016 the accessor as normal:
1018 my $column1 = $foo->column1();
1020 If on the other hand an accessor does not exist in the object, you need to
1021 use C<get_column> instead:
1023 my $column2_count = $foo->get_column('column2_count');
1025 You can create your own accessors if required - see
1026 L<DBIx::Class::Manual::Cookbook> for details.
1030 Contains a list of relationships that should be joined for this query. For
1033 # Get CDs by Nine Inch Nails
1034 my $rs = $schema->resultset('CD')->search(
1035 { 'artist.name' => 'Nine Inch Nails' },
1036 { join => 'artist' }
1039 Can also contain a hash reference to refer to the other relation's relations.
1042 package MyApp::Schema::Track;
1043 use base qw/DBIx::Class/;
1044 __PACKAGE__->table('track');
1045 __PACKAGE__->add_columns(qw/trackid cd position title/);
1046 __PACKAGE__->set_primary_key('trackid');
1047 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1050 # In your application
1051 my $rs = $schema->resultset('Artist')->search(
1052 { 'track.title' => 'Teardrop' },
1054 join => { cd => 'track' },
1055 order_by => 'artist.name',
1059 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1060 similarly for a third time). For e.g.
1062 my $rs = $schema->resultset('Artist')->search(
1063 { 'cds.title' => 'Foo',
1064 'cds_2.title' => 'Bar' },
1065 { join => [ qw/cds cds/ ] });
1067 will return a set of all artists that have both a cd with title Foo and a cd
1070 If you want to fetch related objects from other tables as well, see C<prefetch>
1075 =head3 Arguments: arrayref/hashref
1077 Contains one or more relationships that should be fetched along with the main
1078 query (when they are accessed afterwards they will have already been
1079 "prefetched"). This is useful for when you know you will need the related
1080 objects, because it saves at least one query:
1082 my $rs = $schema->resultset('Tag')->search(
1091 The initial search results in SQL like the following:
1093 SELECT tag.*, cd.*, artist.* FROM tag
1094 JOIN cd ON tag.cd = cd.cdid
1095 JOIN artist ON cd.artist = artist.artistid
1097 L<DBIx::Class> has no need to go back to the database when we access the
1098 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1101 Simple prefetches will be joined automatically, so there is no need
1102 for a C<join> attribute in the above search. If you're prefetching to
1103 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1104 specify the join as well.
1106 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1107 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1108 with an accessor type of 'single' or 'filter').
1112 =head3 Arguments: (arrayref)
1114 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1115 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1118 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1119 C<join> will usually do what you need and it is strongly recommended that you
1120 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1122 In simple terms, C<from> works as follows:
1125 { <alias> => <table>, -join-type => 'inner|left|right' }
1126 [] # nested JOIN (optional)
1127 { <table.column> = <foreign_table.foreign_key> }
1133 ON <table.column> = <foreign_table.foreign_key>
1135 An easy way to follow the examples below is to remember the following:
1137 Anything inside "[]" is a JOIN
1138 Anything inside "{}" is a condition for the enclosing JOIN
1140 The following examples utilize a "person" table in a family tree application.
1141 In order to express parent->child relationships, this table is self-joined:
1143 # Person->belongs_to('father' => 'Person');
1144 # Person->belongs_to('mother' => 'Person');
1146 C<from> can be used to nest joins. Here we return all children with a father,
1147 then search against all mothers of those children:
1149 $rs = $schema->resultset('Person')->search(
1152 alias => 'mother', # alias columns in accordance with "from"
1154 { mother => 'person' },
1157 { child => 'person' },
1159 { father => 'person' },
1160 { 'father.person_id' => 'child.father_id' }
1163 { 'mother.person_id' => 'child.mother_id' }
1170 # SELECT mother.* FROM person mother
1173 # JOIN person father
1174 # ON ( father.person_id = child.father_id )
1176 # ON ( mother.person_id = child.mother_id )
1178 The type of any join can be controlled manually. To search against only people
1179 with a father in the person table, we could explicitly use C<INNER JOIN>:
1181 $rs = $schema->resultset('Person')->search(
1184 alias => 'child', # alias columns in accordance with "from"
1186 { child => 'person' },
1188 { father => 'person', -join-type => 'inner' },
1189 { 'father.id' => 'child.father_id' }
1196 # SELECT child.* FROM person child
1197 # INNER JOIN person father ON child.father_id = father.id
1201 For a paged resultset, specifies which page to retrieve. Leave unset
1202 for an unpaged resultset.
1206 For a paged resultset, how many rows per page:
1210 Can also be used to simulate an SQL C<LIMIT>.
1214 =head3 Arguments: (arrayref)
1216 A arrayref of columns to group by. Can include columns of joined tables.
1218 group_by => [qw/ column1 column2 ... /]
1222 Set to 1 to group by all columns.
1224 For more examples of using these attributes, see
1225 L<DBIx::Class::Manual::Cookbook>.