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__->table('artist');
36 __PACKAGE__->add_columns(qw/artistid name/);
37 __PACKAGE__->set_primary_key('artistid');
38 __PACKAGE__->has_many(cds => 'MyApp::Schema::CD');
41 package MyApp::Schema::CD;
42 use base qw/DBIx::Class/;
43 __PACKAGE__->table('artist');
44 __PACKAGE__->add_columns(qw/cdid artist title year/);
45 __PACKAGE__->set_primary_key('cdid');
46 __PACKAGE__->belongs_to(artist => 'MyApp::Schema::Artist');
51 =head2 new($source, \%$attrs)
53 The resultset constructor. Takes a source object (usually a
54 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see L</ATRRIBUTES>
55 below). Does not perform any queries -- these are executed as needed by the
58 Generally you won't need to construct a resultset manually. You'll
59 automatically get one from e.g. a L</search> called in scalar context:
61 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
67 return $class->new_result(@_) if ref $class;
68 my ($source, $attrs) = @_;
69 #use Data::Dumper; warn Dumper($attrs);
70 $attrs = Storable::dclone($attrs || {}); # { %{ $attrs || {} } };
72 my $alias = ($attrs->{alias} ||= 'me');
73 if ($attrs->{cols} || !$attrs->{select}) {
74 delete $attrs->{as} if $attrs->{cols};
75 my @cols = ($attrs->{cols}
76 ? @{delete $attrs->{cols}}
78 $attrs->{select} = [ map { m/\./ ? $_ : "${alias}.$_" } @cols ];
80 $attrs->{as} ||= [ map { m/^$alias\.(.*)$/ ? $1 : $_ } @{$attrs->{select}} ];
81 if (my $include = delete $attrs->{include_columns}) {
82 push(@{$attrs->{select}}, @$include);
83 push(@{$attrs->{as}}, map { m/([^\.]+)$/; $1; } @$include);
85 #use Data::Dumper; warn Dumper(@{$attrs}{qw/select as/});
86 $attrs->{from} ||= [ { $alias => $source->from } ];
87 $attrs->{seen_join} ||= {};
88 if (my $join = delete $attrs->{join}) {
89 foreach my $j (ref $join eq 'ARRAY'
90 ? (@{$join}) : ($join)) {
91 if (ref $j eq 'HASH') {
92 $seen{$_} = 1 foreach keys %$j;
97 push(@{$attrs->{from}}, $source->resolve_join($join, $attrs->{alias}, $attrs->{seen_join}));
99 $attrs->{group_by} ||= $attrs->{select} if delete $attrs->{distinct};
101 if (my $prefetch = delete $attrs->{prefetch}) {
102 foreach my $p (ref $prefetch eq 'ARRAY'
103 ? (@{$prefetch}) : ($prefetch)) {
104 if( ref $p eq 'HASH' ) {
105 foreach my $key (keys %$p) {
106 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
111 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
114 my @prefetch = $source->resolve_prefetch($p, $attrs->{alias});
116 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
117 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
121 if ($attrs->{page}) {
122 $attrs->{rows} ||= 10;
123 $attrs->{offset} ||= 0;
124 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
127 result_source => $source,
128 cond => $attrs->{where},
129 from => $attrs->{from},
131 page => delete $attrs->{page},
134 bless ($new, $class);
140 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
141 my $new_rs = $rs->search({ foo => 3 });
143 If you need to pass in additional attributes but no additional condition,
144 call it as C<search({}, \%attrs);>.
146 # "SELECT foo, bar FROM $class_table"
147 my @all = $class->search({}, { cols => [qw/foo bar/] });
154 #use Data::Dumper;warn Dumper(@_);
156 my $attrs = { %{$self->{attrs}} };
157 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
158 $attrs = { %$attrs, %{ pop(@_) } };
161 my $where = (@_ ? ((@_ == 1 || ref $_[0] eq "HASH") ? shift : {@_}) : undef());
162 if (defined $where) {
163 $where = (defined $attrs->{where}
165 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
166 $where, $attrs->{where} ] }
168 $attrs->{where} = $where;
171 my $rs = (ref $self)->new($self->result_source, $attrs);
173 return (wantarray ? $rs->all : $rs);
176 =head2 search_literal
178 my @obj = $rs->search_literal($literal_where_cond, @bind);
179 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
181 Pass a literal chunk of SQL to be added to the conditional part of the
187 my ($self, $cond, @vals) = @_;
188 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
189 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
190 return $self->search(\$cond, $attrs);
193 =head2 find(@colvalues), find(\%cols, \%attrs?)
195 Finds a row based on its primary key or unique constraint. For example:
197 my $cd = $schema->resultset('CD')->find(5);
199 Also takes an optional C<key> attribute, to search by a specific key or unique
200 constraint. For example:
202 my $cd = $schema->resultset('CD')->find_or_create(
204 artist => 'Massive Attack',
205 title => 'Mezzanine',
207 { key => 'artist_title' }
210 See also L</find_or_create> and L</update_or_create>.
215 my ($self, @vals) = @_;
216 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
218 my @cols = $self->result_source->primary_columns;
219 if (exists $attrs->{key}) {
220 my %uniq = $self->result_source->unique_constraints;
221 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
222 unless exists $uniq{$attrs->{key}};
223 @cols = @{ $uniq{$attrs->{key}} };
225 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
226 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
230 if (ref $vals[0] eq 'HASH') {
231 $query = { %{$vals[0]} };
232 } elsif (@cols == @vals) {
234 @{$query}{@cols} = @vals;
238 foreach (keys %$query) {
240 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
242 #warn Dumper($query);
244 ? $self->search($query,$attrs)->single
245 : $self->single($query));
248 =head2 search_related
250 $rs->search_related('relname', $cond?, $attrs?);
252 Search the specified relationship. Optionally specify a condition for matching
258 return shift->related_resultset(shift)->search(@_);
263 Returns a storage-driven cursor to the given resultset.
269 my ($attrs) = $self->{attrs};
270 $attrs = { %$attrs };
271 return $self->{cursor}
272 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
273 $attrs->{where},$attrs);
278 Inflates the first result without creating a cursor
283 my ($self, $extra) = @_;
284 my ($attrs) = $self->{attrs};
285 $attrs = { %$attrs };
287 if (defined $attrs->{where}) {
290 => [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
291 delete $attrs->{where}, $extra ]
294 $attrs->{where} = $extra;
297 my @data = $self->result_source->storage->select_single(
298 $self->{from}, $attrs->{select},
299 $attrs->{where},$attrs);
300 return (@data ? $self->_construct_object(@data) : ());
306 Perform a search, but use C<LIKE> instead of equality as the condition. Note
307 that this is simply a convenience method; you most likely want to use
308 L</search> with specific operators.
310 For more information, see L<DBIx::Class::Manual::Cookbook>.
317 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
320 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
321 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
322 return $class->search($query, { %$attrs });
325 =head2 slice($first, $last)
327 Returns a subset of elements from the resultset.
332 my ($self, $min, $max) = @_;
333 my $attrs = { %{ $self->{attrs} || {} } };
334 $attrs->{offset} ||= 0;
335 $attrs->{offset} += $min;
336 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
337 my $slice = (ref $self)->new($self->result_source, $attrs);
338 return (wantarray ? $slice->all : $slice);
343 Returns the next element in the resultset (C<undef> is there is none).
345 Can be used to efficiently iterate over records in the resultset:
347 my $rs = $schema->resultset('CD')->search({});
348 while (my $cd = $rs->next) {
356 my $cache = $self->get_cache;
358 $self->{all_cache_position} ||= 0;
359 my $obj = $cache->[$self->{all_cache_position}];
360 $self->{all_cache_position}++;
363 my @row = $self->cursor->next;
364 # warn Dumper(\@row); use Data::Dumper;
365 return unless (@row);
366 return $self->_construct_object(@row);
369 sub _construct_object {
370 my ($self, @row) = @_;
371 my @row_orig = @row; # copy @row for key comparison later, because @row will change
372 my @as = @{ $self->{attrs}{as} };
373 #warn "@cols -> @row";
374 my $info = [ {}, {} ];
375 foreach my $as (@as) {
378 my @parts = split(/\./, $as);
379 my $col = pop(@parts);
380 foreach my $p (@parts) {
381 $target = $target->[1]->{$p} ||= [];
383 # if cache is enabled, fetch inflated objs for prefetch
384 if( $rs->{attrs}->{cache} ) {
385 my $rel_info = $rs->result_source->relationship_info($p);
386 my $cond = $rel_info->{cond};
388 $rs = $rs->related_resultset($p);
389 $rs->{attrs}->{cache} = 1;
392 # populate related resultset's cache if empty
393 if( !@{ $rs->get_cache } ) {
397 # get ordinals for pk columns in $row, so values can be compared
401 while( my( $rel_key, $pk ) = ( each %$cond ) ) {
404 $map->{$rel_key} = $pk;
408 while( my( $rel_key, $pk ) = each( %$map ) ) {
410 foreach my $col ( $parent_rs->result_source->columns ) {
412 $map->{$rel_key} = $i;
418 $rs->reset(); # reset cursor/cache position
420 # get matching objects for inflation
421 OBJ: while( my $rel_obj = $rs->next ) {
423 KEYS: while( my( $rel_key, $ordinal ) = each %$map ) {
424 # use get_column to avoid auto inflation (want scalar value)
425 if( $rel_obj->get_column($rel_key) ne $row_orig[$ordinal] ) {
428 push @objs, $rel_obj;
431 $target->[0] = \@objs;
434 $target->[0]->{$col} = shift @row
435 if ref($target->[0]) ne 'ARRAY'; # arrayref is pre-inflated objects, do not overwrite
437 #use Data::Dumper; warn Dumper(\@as, $info);
438 my $new = $self->result_source->result_class->inflate_result(
439 $self->result_source, @$info);
440 $new = $self->{attrs}{record_filter}->($new)
441 if exists $self->{attrs}{record_filter};
447 Returns a reference to the result source for this recordset.
454 Performs an SQL C<COUNT> with the same query as the resultset was built
455 with to find the number of elements. If passed arguments, does a search
456 on the resultset and counts the results of that.
458 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
459 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
460 not support C<DISTINCT> with multiple columns. If you are using such a
461 database, you should only use columns from the main table in your C<group_by>
468 return $self->search(@_)->count if @_ && defined $_[0];
469 unless (defined $self->{count}) {
470 return scalar @{ $self->get_cache }
471 if @{ $self->get_cache };
473 my $select = { 'count' => '*' };
474 if( $group_by = delete $self->{attrs}{group_by} ) {
475 my @distinct = (ref $group_by ? @$group_by : ($group_by));
476 # todo: try CONCAT for multi-column pk
477 my @pk = $self->result_source->primary_columns;
478 if( scalar(@pk) == 1 ) {
480 my $alias = $self->{attrs}{alias};
481 my $re = qr/^($alias\.)?$pk$/;
482 foreach my $column ( @distinct) {
483 if( $column =~ $re ) {
484 @distinct = ( $column );
490 $select = { count => { 'distinct' => \@distinct } };
491 #use Data::Dumper; die Dumper $select;
494 my $attrs = { %{ $self->{attrs} },
497 # offset, order by and page are not needed to count. record_filter is cdbi
498 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
500 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
501 $self->{attrs}{group_by} = $group_by;
503 return 0 unless $self->{count};
504 my $count = $self->{count};
505 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
506 $count = $self->{attrs}{rows} if
507 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
513 Calls L</search_literal> with the passed arguments, then L</count>.
517 sub count_literal { shift->search_literal(@_)->count; }
521 Returns all elements in the resultset. Called implictly if the resultset
522 is returned in list context.
528 return @{ $self->get_cache }
529 if @{ $self->get_cache };
530 if( $self->{attrs}->{cache} ) {
531 my @obj = map { $self->_construct_object(@$_); }
533 $self->set_cache( \@obj );
534 return @{ $self->get_cache };
536 return map { $self->_construct_object(@$_); }
542 Resets the resultset's cursor, so you can iterate through the elements again.
548 $self->{all_cache_position} = 0;
549 $self->cursor->reset;
555 Resets the resultset and returns the first element.
560 return $_[0]->reset->next;
563 =head2 update(\%values)
565 Sets the specified columns in the resultset to the supplied values.
570 my ($self, $values) = @_;
571 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
572 return $self->result_source->storage->update(
573 $self->result_source->from, $values, $self->{cond});
576 =head2 update_all(\%values)
578 Fetches all objects and updates them one at a time. Note that C<update_all>
579 will run cascade triggers while L</update> will not.
584 my ($self, $values) = @_;
585 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
586 foreach my $obj ($self->all) {
587 $obj->set_columns($values)->update;
594 Deletes the contents of the resultset from its result source.
601 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
602 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
603 if (ref $self->{cond} eq 'ARRAY') {
604 $del = [ map { my %hash;
605 foreach my $key (keys %{$_}) {
607 $hash{$1} = $_->{$key};
608 }; \%hash; } @{$self->{cond}} ];
609 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
610 $del->{-and} = [ map { my %hash;
611 foreach my $key (keys %{$_}) {
613 $hash{$1} = $_->{$key};
614 }; \%hash; } @{$self->{cond}{-and}} ];
616 foreach my $key (keys %{$self->{cond}}) {
618 $del->{$1} = $self->{cond}{$key};
621 $self->result_source->storage->delete($self->result_source->from, $del);
627 Fetches all objects and deletes them one at a time. Note that C<delete_all>
628 will run cascade triggers while L</delete> will not.
634 $_->delete for $self->all;
640 Returns a L<Data::Page> object for the current resultset. Only makes
641 sense for queries with a C<page> attribute.
647 my $attrs = $self->{attrs};
648 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
649 $attrs->{rows} ||= 10;
651 return $self->{pager} ||= Data::Page->new(
652 $self->{count}, $attrs->{rows}, $self->{page});
655 =head2 page($page_num)
657 Returns a new resultset for the specified page.
662 my ($self, $page) = @_;
663 my $attrs = { %{$self->{attrs}} };
664 $attrs->{page} = $page;
665 return (ref $self)->new($self->result_source, $attrs);
668 =head2 new_result(\%vals)
670 Creates a result in the resultset's result class.
675 my ($self, $values) = @_;
676 $self->throw_exception( "new_result needs a hash" )
677 unless (ref $values eq 'HASH');
678 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
679 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
681 my $alias = $self->{attrs}{alias};
682 foreach my $key (keys %{$self->{cond}||{}}) {
683 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
685 my $obj = $self->result_source->result_class->new(\%new);
686 $obj->result_source($self->result_source) if $obj->can('result_source');
690 =head2 create(\%vals)
692 Inserts a record into the resultset and returns the object.
694 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
699 my ($self, $attrs) = @_;
700 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
701 return $self->new_result($attrs)->insert;
704 =head2 find_or_create(\%vals, \%attrs?)
706 $class->find_or_create({ key => $val, ... });
708 Searches for a record matching the search condition; if it doesn't find one,
709 creates one and returns that instead.
711 my $cd = $schema->resultset('CD')->find_or_create({
713 artist => 'Massive Attack',
714 title => 'Mezzanine',
718 Also takes an optional C<key> attribute, to search by a specific key or unique
719 constraint. For example:
721 my $cd = $schema->resultset('CD')->find_or_create(
723 artist => 'Massive Attack',
724 title => 'Mezzanine',
726 { key => 'artist_title' }
729 See also L</find> and L</update_or_create>.
735 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
736 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
737 my $exists = $self->find($hash, $attrs);
738 return defined($exists) ? $exists : $self->create($hash);
741 =head2 update_or_create
743 $class->update_or_create({ key => $val, ... });
745 First, search for an existing row matching one of the unique constraints
746 (including the primary key) on the source of this resultset. If a row is
747 found, update it with the other given column values. Otherwise, create a new
750 Takes an optional C<key> attribute to search on a specific unique constraint.
753 # In your application
754 my $cd = $schema->resultset('CD')->update_or_create(
756 artist => 'Massive Attack',
757 title => 'Mezzanine',
760 { key => 'artist_title' }
763 If no C<key> is specified, it searches on all unique constraints defined on the
764 source, including the primary key.
766 If the C<key> is specified as C<primary>, search only on the primary key.
768 See also L</find> and L</find_or_create>.
772 sub update_or_create {
775 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
776 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
778 my %unique_constraints = $self->result_source->unique_constraints;
779 my @constraint_names = (exists $attrs->{key}
781 : keys %unique_constraints);
784 foreach my $name (@constraint_names) {
785 my @unique_cols = @{ $unique_constraints{$name} };
787 map { $_ => $hash->{$_} }
788 grep { exists $hash->{$_} }
791 push @unique_hashes, \%unique_hash
792 if (scalar keys %unique_hash == scalar @unique_cols);
796 if (@unique_hashes) {
797 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
799 $row->set_columns($hash);
805 $row = $self->create($hash);
813 Gets the contents of the cache for the resultset.
819 return $self->{all_cache} || [];
824 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
829 my ( $self, $data ) = @_;
830 $self->throw_exception("set_cache requires an arrayref")
831 if ref $data ne 'ARRAY';
832 my $result_class = $self->result_source->result_class;
834 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
835 if ref $_ ne $result_class;
837 $self->{all_cache} = $data;
842 Clears the cache for the resultset.
848 $self->set_cache([]);
851 =head2 related_resultset
853 Returns a related resultset for the supplied relationship name.
855 $rs = $rs->related_resultset('foo');
859 sub related_resultset {
860 my ( $self, $rel, @rest ) = @_;
861 $self->{related_resultsets} ||= {};
862 my $resultsets = $self->{related_resultsets};
863 if( !exists $resultsets->{$rel} ) {
864 #warn "fetching related resultset for rel '$rel'";
865 my $rel_obj = $self->result_source->relationship_info($rel);
866 $self->throw_exception(
867 "search_related: result source '" . $self->result_source->name .
868 "' has no such relationship ${rel}")
869 unless $rel_obj; #die Dumper $self->{attrs};
871 if( $self->{attrs}->{cache} ) {
872 $rs = $self->search(undef);
875 $rs = $self->search(undef, { join => $rel });
877 #use Data::Dumper; die Dumper $rs->{attrs};#$rs = $self->search( undef );
878 #use Data::Dumper; warn Dumper $self->{attrs}, Dumper $rs->{attrs};
879 my $alias = (defined $rs->{attrs}{seen_join}{$rel}
880 && $rs->{attrs}{seen_join}{$rel} > 1
881 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
883 $resultsets->{$rel} =
884 $self->result_source->schema->resultset($rel_obj->{class}
892 return $resultsets->{$rel};
895 =head2 throw_exception
897 See Schema's throw_exception
901 sub throw_exception {
903 $self->result_source->schema->throw_exception(@_);
908 The resultset takes various attributes that modify its behavior. Here's an
913 Which column(s) to order the results by. This is currently passed through
914 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
916 =head2 cols (arrayref)
918 Shortcut to request a particular set of columns to be retrieved. Adds
919 C<me.> onto the start of any column without a C<.> in it and sets C<select>
920 from that, then auto-populates C<as> from C<select> as normal.
922 =head2 include_columns (arrayref)
924 Shortcut to include additional columns in the returned results - for example
926 { include_columns => ['foo.name'], join => ['foo'] }
928 would add a 'name' column to the information passed to object inflation
930 =head2 select (arrayref)
932 Indicates which columns should be selected from the storage. You can use
933 column names, or in the case of RDBMS back ends, function or stored procedure
936 $rs = $schema->resultset('Foo')->search(
941 { count => 'column_to_count' },
942 { sum => 'column_to_sum' }
947 When you use function/stored procedure names and do not supply an C<as>
948 attribute, the column names returned are storage-dependent. E.g. MySQL would
949 return a column named C<count(column_to_count)> in the above example.
953 Indicates column names for object inflation. This is used in conjunction with
954 C<select>, usually when C<select> contains one or more function or stored
957 $rs = $schema->resultset('Foo')->search(
962 { count => 'column2' }
964 as => [qw/ column1 column2_count /]
968 my $foo = $rs->first(); # get the first Foo
970 If the object against which the search is performed already has an accessor
971 matching a column name specified in C<as>, the value can be retrieved using
972 the accessor as normal:
974 my $column1 = $foo->column1();
976 If on the other hand an accessor does not exist in the object, you need to
977 use C<get_column> instead:
979 my $column2_count = $foo->get_column('column2_count');
981 You can create your own accessors if required - see
982 L<DBIx::Class::Manual::Cookbook> for details.
986 Contains a list of relationships that should be joined for this query. For
989 # Get CDs by Nine Inch Nails
990 my $rs = $schema->resultset('CD')->search(
991 { 'artist.name' => 'Nine Inch Nails' },
995 Can also contain a hash reference to refer to the other relation's relations.
998 package MyApp::Schema::Track;
999 use base qw/DBIx::Class/;
1000 __PACKAGE__->table('track');
1001 __PACKAGE__->add_columns(qw/trackid cd position title/);
1002 __PACKAGE__->set_primary_key('trackid');
1003 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1006 # In your application
1007 my $rs = $schema->resultset('Artist')->search(
1008 { 'track.title' => 'Teardrop' },
1010 join => { cd => 'track' },
1011 order_by => 'artist.name',
1015 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1016 similarly for a third time). For e.g.
1018 my $rs = $schema->resultset('Artist')->search(
1019 { 'cds.title' => 'Foo',
1020 'cds_2.title' => 'Bar' },
1021 { join => [ qw/cds cds/ ] });
1023 will return a set of all artists that have both a cd with title Foo and a cd
1026 If you want to fetch related objects from other tables as well, see C<prefetch>
1029 =head2 prefetch arrayref/hashref
1031 Contains one or more relationships that should be fetched along with the main
1032 query (when they are accessed afterwards they will have already been
1033 "prefetched"). This is useful for when you know you will need the related
1034 objects, because it saves at least one query:
1036 my $rs = $schema->resultset('Tag')->search(
1045 The initial search results in SQL like the following:
1047 SELECT tag.*, cd.*, artist.* FROM tag
1048 JOIN cd ON tag.cd = cd.cdid
1049 JOIN artist ON cd.artist = artist.artistid
1051 L<DBIx::Class> has no need to go back to the database when we access the
1052 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1055 Simple prefetches will be joined automatically, so there is no need
1056 for a C<join> attribute in the above search. If you're prefetching to
1057 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1058 specify the join as well.
1060 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1061 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1062 with an accessor type of 'single' or 'filter').
1064 =head2 from (arrayref)
1066 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1067 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1070 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1071 C<join> will usually do what you need and it is strongly recommended that you
1072 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1074 In simple terms, C<from> works as follows:
1077 { <alias> => <table>, -join-type => 'inner|left|right' }
1078 [] # nested JOIN (optional)
1079 { <table.column> = <foreign_table.foreign_key> }
1085 ON <table.column> = <foreign_table.foreign_key>
1087 An easy way to follow the examples below is to remember the following:
1089 Anything inside "[]" is a JOIN
1090 Anything inside "{}" is a condition for the enclosing JOIN
1092 The following examples utilize a "person" table in a family tree application.
1093 In order to express parent->child relationships, this table is self-joined:
1095 # Person->belongs_to('father' => 'Person');
1096 # Person->belongs_to('mother' => 'Person');
1098 C<from> can be used to nest joins. Here we return all children with a father,
1099 then search against all mothers of those children:
1101 $rs = $schema->resultset('Person')->search(
1104 alias => 'mother', # alias columns in accordance with "from"
1106 { mother => 'person' },
1109 { child => 'person' },
1111 { father => 'person' },
1112 { 'father.person_id' => 'child.father_id' }
1115 { 'mother.person_id' => 'child.mother_id' }
1122 # SELECT mother.* FROM person mother
1125 # JOIN person father
1126 # ON ( father.person_id = child.father_id )
1128 # ON ( mother.person_id = child.mother_id )
1130 The type of any join can be controlled manually. To search against only people
1131 with a father in the person table, we could explicitly use C<INNER JOIN>:
1133 $rs = $schema->resultset('Person')->search(
1136 alias => 'child', # alias columns in accordance with "from"
1138 { child => 'person' },
1140 { father => 'person', -join-type => 'inner' },
1141 { 'father.id' => 'child.father_id' }
1148 # SELECT child.* FROM person child
1149 # INNER JOIN person father ON child.father_id = father.id
1153 For a paged resultset, specifies which page to retrieve. Leave unset
1154 for an unpaged resultset.
1158 For a paged resultset, how many rows per page:
1162 Can also be used to simulate an SQL C<LIMIT>.
1164 =head2 group_by (arrayref)
1166 A arrayref of columns to group by. Can include columns of joined tables.
1168 group_by => [qw/ column1 column2 ... /]
1172 Set to 1 to group by all columns.
1174 For more examples of using these attributes, see
1175 L<DBIx::Class::Manual::Cookbook>.