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 $attrs->{order_by} = [ $attrs->{order_by} ]
106 if $attrs->{order_by} && !ref($attrs->{order_by});
107 $attrs->{order_by} ||= [];
109 if (my $prefetch = delete $attrs->{prefetch}) {
110 foreach my $p (ref $prefetch eq 'ARRAY'
111 ? (@{$prefetch}) : ($prefetch)) {
112 if( ref $p eq 'HASH' ) {
113 foreach my $key (keys %$p) {
114 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
119 push(@{$attrs->{from}}, $source->resolve_join($p, $attrs->{alias}))
122 my @prefetch = $source->resolve_prefetch(
123 $p, $attrs->{alias}, {}, $attrs->{order_by});
125 push(@{$attrs->{select}}, map { $_->[0] } @prefetch);
126 push(@{$attrs->{as}}, map { $_->[1] } @prefetch);
130 if ($attrs->{page}) {
131 $attrs->{rows} ||= 10;
132 $attrs->{offset} ||= 0;
133 $attrs->{offset} += ($attrs->{rows} * ($attrs->{page} - 1));
136 result_source => $source,
137 cond => $attrs->{where},
138 from => $attrs->{from},
140 page => delete $attrs->{page},
143 bless ($new, $class);
149 my @obj = $rs->search({ foo => 3 }); # "... WHERE foo = 3"
150 my $new_rs = $rs->search({ foo => 3 });
152 If you need to pass in additional attributes but no additional condition,
153 call it as C<search({}, \%attrs);>.
155 # "SELECT foo, bar FROM $class_table"
156 my @all = $class->search({}, { cols => [qw/foo bar/] });
166 my $attrs = { %{$self->{attrs}} };
167 my $having = delete $attrs->{having};
168 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
169 $attrs = { %$attrs, %{ pop(@_) } };
173 ? ((@_ == 1 || ref $_[0] eq "HASH")
176 ? $self->throw_exception(
177 "Odd number of arguments to search")
180 if (defined $where) {
181 $where = (defined $attrs->{where}
183 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
184 $where, $attrs->{where} ] }
186 $attrs->{where} = $where;
189 if (defined $having) {
190 $having = (defined $attrs->{having}
192 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
193 $having, $attrs->{having} ] }
195 $attrs->{having} = $having;
198 $rs = (ref $self)->new($self->result_source, $attrs);
204 return (wantarray ? $rs->all : $rs);
207 =head2 search_literal
209 my @obj = $rs->search_literal($literal_where_cond, @bind);
210 my $new_rs = $rs->search_literal($literal_where_cond, @bind);
212 Pass a literal chunk of SQL to be added to the conditional part of the
218 my ($self, $cond, @vals) = @_;
219 my $attrs = (ref $vals[$#vals] eq 'HASH' ? { %{ pop(@vals) } } : {});
220 $attrs->{bind} = [ @{$self->{attrs}{bind}||[]}, @vals ];
221 return $self->search(\$cond, $attrs);
226 =head3 Arguments: (@colvalues) | (\%cols, \%attrs?)
228 Finds a row based on its primary key or unique constraint. For example:
230 my $cd = $schema->resultset('CD')->find(5);
232 Also takes an optional C<key> attribute, to search by a specific key or unique
233 constraint. For example:
235 my $cd = $schema->resultset('CD')->find(
237 artist => 'Massive Attack',
238 title => 'Mezzanine',
240 { key => 'artist_title' }
243 See also L</find_or_create> and L</update_or_create>.
248 my ($self, @vals) = @_;
249 my $attrs = (@vals > 1 && ref $vals[$#vals] eq 'HASH' ? pop(@vals) : {});
251 my @cols = $self->result_source->primary_columns;
252 if (exists $attrs->{key}) {
253 my %uniq = $self->result_source->unique_constraints;
254 $self->( "Unknown key " . $attrs->{key} . " on " . $self->name )
255 unless exists $uniq{$attrs->{key}};
256 @cols = @{ $uniq{$attrs->{key}} };
258 #use Data::Dumper; warn Dumper($attrs, @vals, @cols);
259 $self->throw_exception( "Can't find unless a primary key or unique constraint is defined" )
263 if (ref $vals[0] eq 'HASH') {
264 $query = { %{$vals[0]} };
265 } elsif (@cols == @vals) {
267 @{$query}{@cols} = @vals;
271 foreach (keys %$query) {
273 $query->{$self->{attrs}{alias}.'.'.$_} = delete $query->{$_};
275 #warn Dumper($query);
277 ? $self->search($query,$attrs)->single
278 : $self->single($query));
281 =head2 search_related
283 $rs->search_related('relname', $cond?, $attrs?);
285 Search the specified relationship. Optionally specify a condition for matching
291 return shift->related_resultset(shift)->search(@_);
296 Returns a storage-driven cursor to the given resultset.
302 my ($attrs) = $self->{attrs};
303 $attrs = { %$attrs };
304 return $self->{cursor}
305 ||= $self->result_source->storage->select($self->{from}, $attrs->{select},
306 $attrs->{where},$attrs);
311 Inflates the first result without creating a cursor
316 my ($self, $extra) = @_;
317 my ($attrs) = $self->{attrs};
318 $attrs = { %$attrs };
320 if (defined $attrs->{where}) {
323 => [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
324 delete $attrs->{where}, $extra ]
327 $attrs->{where} = $extra;
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>.
350 if (@_ > 1 && ref $_[$#_] eq 'HASH') {
353 my $query = ref $_[0] eq "HASH" ? { %{shift()} }: {@_};
354 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
355 return $class->search($query, { %$attrs });
360 =head3 Arguments: ($first, $last)
362 Returns a subset of elements from the resultset.
367 my ($self, $min, $max) = @_;
368 my $attrs = { %{ $self->{attrs} || {} } };
369 $attrs->{offset} ||= 0;
370 $attrs->{offset} += $min;
371 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
372 my $slice = (ref $self)->new($self->result_source, $attrs);
373 return (wantarray ? $slice->all : $slice);
378 Returns the next element in the resultset (C<undef> is there is none).
380 Can be used to efficiently iterate over records in the resultset:
382 my $rs = $schema->resultset('CD')->search({});
383 while (my $cd = $rs->next) {
392 if( @{$cache = $self->{all_cache} || []}) {
393 $self->{all_cache_position} ||= 0;
394 my $obj = $cache->[$self->{all_cache_position}];
395 $self->{all_cache_position}++;
398 if ($self->{attrs}{cache}) {
399 $self->{all_cache_position} = 0;
400 return ($self->all)[0];
402 my @row = delete $self->{stashed_row} || $self->cursor->next;
403 # warn Dumper(\@row); use Data::Dumper;
404 return unless (@row);
405 return $self->_construct_object(@row);
408 sub _construct_object {
409 my ($self, @row) = @_;
410 my @row_orig = @row; # copy @row for key comparison later, because @row will change
411 my @as = @{ $self->{attrs}{as} };
412 #use Data::Dumper; warn Dumper \@as;
413 #warn "@cols -> @row";
414 my $info = [ {}, {} ];
415 foreach my $as (@as) {
418 my @parts = split(/\./, $as);
419 my $col = pop(@parts);
420 foreach my $p (@parts) {
421 $target = $target->[1]->{$p} ||= [];
423 $rs = $rs->related_resultset($p) if $rs->{attrs}->{cache};
426 $target->[0]->{$col} = shift @row
427 if ref($target->[0]) ne 'ARRAY'; # arrayref is pre-inflated objects, do not overwrite
429 #use Data::Dumper; warn Dumper(\@as, $info);
430 my $new = $self->result_source->result_class->inflate_result(
431 $self->result_source, @$info);
432 $new = $self->{attrs}{record_filter}->($new)
433 if exists $self->{attrs}{record_filter};
440 Returns a reference to the result source for this recordset.
447 Performs an SQL C<COUNT> with the same query as the resultset was built
448 with to find the number of elements. If passed arguments, does a search
449 on the resultset and counts the results of that.
451 Note: When using C<count> with C<group_by>, L<DBIX::Class> emulates C<GROUP BY>
452 using C<COUNT( DISTINCT( columns ) )>. Some databases (notably SQLite) do
453 not support C<DISTINCT> with multiple columns. If you are using such a
454 database, you should only use columns from the main table in your C<group_by>
461 return $self->search(@_)->count if @_ && defined $_[0];
462 unless (defined $self->{count}) {
463 return scalar @{ $self->get_cache }
464 if @{ $self->get_cache };
466 my $select = { 'count' => '*' };
467 my $attrs = { %{ $self->{attrs} } };
468 if( $group_by = delete $attrs->{group_by} ) {
469 delete $attrs->{having};
470 my @distinct = (ref $group_by ? @$group_by : ($group_by));
471 # todo: try CONCAT for multi-column pk
472 my @pk = $self->result_source->primary_columns;
473 if( scalar(@pk) == 1 ) {
475 my $alias = $attrs->{alias};
476 my $re = qr/^($alias\.)?$pk$/;
477 foreach my $column ( @distinct) {
478 if( $column =~ $re ) {
479 @distinct = ( $column );
485 $select = { count => { 'distinct' => \@distinct } };
486 #use Data::Dumper; die Dumper $select;
489 $attrs->{select} = $select;
490 $attrs->{as} = [ 'count' ];
491 # offset, order by and page are not needed to count. record_filter is cdbi
492 delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/;
494 ($self->{count}) = (ref $self)->new($self->result_source, $attrs)->cursor->next;
496 return 0 unless $self->{count};
497 my $count = $self->{count};
498 $count -= $self->{attrs}{offset} if $self->{attrs}{offset};
499 $count = $self->{attrs}{rows} if
500 ($self->{attrs}{rows} && $self->{attrs}{rows} < $count);
506 Calls L</search_literal> with the passed arguments, then L</count>.
510 sub count_literal { shift->search_literal(@_)->count; }
514 Returns all elements in the resultset. Called implictly if the resultset
515 is returned in list context.
521 return @{ $self->get_cache }
522 if @{ $self->get_cache };
523 if( $self->{attrs}->{cache} ) {
524 my @obj = map { $self->_construct_object(@$_); }
526 $self->set_cache( \@obj );
529 return map { $self->_construct_object(@$_); }
535 Resets the resultset's cursor, so you can iterate through the elements again.
541 $self->{all_cache_position} = 0;
542 $self->cursor->reset;
548 Resets the resultset and returns the first element.
553 return $_[0]->reset->next;
558 =head3 Arguments: (\%values)
560 Sets the specified columns in the resultset to the supplied values.
565 my ($self, $values) = @_;
566 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
567 return $self->result_source->storage->update(
568 $self->result_source->from, $values, $self->{cond});
573 =head3 Arguments: (\%values)
575 Fetches all objects and updates them one at a time. Note that C<update_all>
576 will run cascade triggers while L</update> will not.
581 my ($self, $values) = @_;
582 $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH';
583 foreach my $obj ($self->all) {
584 $obj->set_columns($values)->update;
591 Deletes the contents of the resultset from its result source.
598 $self->throw_exception("Can't delete on resultset with condition unless hash or array")
599 unless (ref($self->{cond}) eq 'HASH' || ref($self->{cond}) eq 'ARRAY');
600 if (ref $self->{cond} eq 'ARRAY') {
601 $del = [ map { my %hash;
602 foreach my $key (keys %{$_}) {
604 $hash{$1} = $_->{$key};
605 }; \%hash; } @{$self->{cond}} ];
606 } elsif ((keys %{$self->{cond}})[0] eq '-and') {
607 $del->{-and} = [ map { my %hash;
608 foreach my $key (keys %{$_}) {
610 $hash{$1} = $_->{$key};
611 }; \%hash; } @{$self->{cond}{-and}} ];
613 foreach my $key (keys %{$self->{cond}}) {
615 $del->{$1} = $self->{cond}{$key};
618 $self->result_source->storage->delete($self->result_source->from, $del);
624 Fetches all objects and deletes them one at a time. Note that C<delete_all>
625 will run cascade triggers while L</delete> will not.
631 $_->delete for $self->all;
637 Returns a L<Data::Page> object for the current resultset. Only makes
638 sense for queries with a C<page> attribute.
644 my $attrs = $self->{attrs};
645 $self->throw_exception("Can't create pager for non-paged rs") unless $self->{page};
646 $attrs->{rows} ||= 10;
648 return $self->{pager} ||= Data::Page->new(
649 $self->{count}, $attrs->{rows}, $self->{page});
654 =head3 Arguments: ($page_num)
656 Returns a new resultset for the specified page.
661 my ($self, $page) = @_;
662 my $attrs = { %{$self->{attrs}} };
663 $attrs->{page} = $page;
664 return (ref $self)->new($self->result_source, $attrs);
669 =head3 Arguments: (\%vals)
671 Creates a result in the resultset's result class.
676 my ($self, $values) = @_;
677 $self->throw_exception( "new_result needs a hash" )
678 unless (ref $values eq 'HASH');
679 $self->throw_exception( "Can't abstract implicit construct, condition not a hash" )
680 if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
682 my $alias = $self->{attrs}{alias};
683 foreach my $key (keys %{$self->{cond}||{}}) {
684 $new{$1} = $self->{cond}{$key} if ($key =~ m/^(?:$alias\.)?([^\.]+)$/);
686 my $obj = $self->result_source->result_class->new(\%new);
687 $obj->result_source($self->result_source) if $obj->can('result_source');
693 =head3 Arguments: (\%vals)
695 Inserts a record into the resultset and returns the object.
697 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
702 my ($self, $attrs) = @_;
703 $self->throw_exception( "create needs a hashref" ) unless ref $attrs eq 'HASH';
704 return $self->new_result($attrs)->insert;
707 =head2 find_or_create
709 =head3 Arguments: (\%vals, \%attrs?)
711 $class->find_or_create({ key => $val, ... });
713 Searches for a record matching the search condition; if it doesn't find one,
714 creates one and returns that instead.
716 my $cd = $schema->resultset('CD')->find_or_create({
718 artist => 'Massive Attack',
719 title => 'Mezzanine',
723 Also takes an optional C<key> attribute, to search by a specific key or unique
724 constraint. For example:
726 my $cd = $schema->resultset('CD')->find_or_create(
728 artist => 'Massive Attack',
729 title => 'Mezzanine',
731 { key => 'artist_title' }
734 See also L</find> and L</update_or_create>.
740 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
741 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
742 my $exists = $self->find($hash, $attrs);
743 return defined($exists) ? $exists : $self->create($hash);
746 =head2 update_or_create
748 $class->update_or_create({ key => $val, ... });
750 First, search for an existing row matching one of the unique constraints
751 (including the primary key) on the source of this resultset. If a row is
752 found, update it with the other given column values. Otherwise, create a new
755 Takes an optional C<key> attribute to search on a specific unique constraint.
758 # In your application
759 my $cd = $schema->resultset('CD')->update_or_create(
761 artist => 'Massive Attack',
762 title => 'Mezzanine',
765 { key => 'artist_title' }
768 If no C<key> is specified, it searches on all unique constraints defined on the
769 source, including the primary key.
771 If the C<key> is specified as C<primary>, search only on the primary key.
773 See also L</find> and L</find_or_create>.
777 sub update_or_create {
780 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
781 my $hash = ref $_[0] eq "HASH" ? shift : {@_};
783 my %unique_constraints = $self->result_source->unique_constraints;
784 my @constraint_names = (exists $attrs->{key}
786 : keys %unique_constraints);
789 foreach my $name (@constraint_names) {
790 my @unique_cols = @{ $unique_constraints{$name} };
792 map { $_ => $hash->{$_} }
793 grep { exists $hash->{$_} }
796 push @unique_hashes, \%unique_hash
797 if (scalar keys %unique_hash == scalar @unique_cols);
801 if (@unique_hashes) {
802 $row = $self->search(\@unique_hashes, { rows => 1 })->first;
804 $row->set_columns($hash);
810 $row = $self->create($hash);
818 Gets the contents of the cache for the resultset.
824 return $self->{all_cache} || [];
829 Sets the contents of the cache for the resultset. Expects an arrayref of objects of the same class as those produced by the resultset.
834 my ( $self, $data ) = @_;
835 $self->throw_exception("set_cache requires an arrayref")
836 if ref $data ne 'ARRAY';
837 my $result_class = $self->result_source->result_class;
839 $self->throw_exception("cannot cache object of type '$_', expected '$result_class'")
840 if ref $_ ne $result_class;
842 $self->{all_cache} = $data;
847 Clears the cache for the resultset.
853 $self->set_cache([]);
856 =head2 related_resultset
858 Returns a related resultset for the supplied relationship name.
860 $rs = $rs->related_resultset('foo');
864 sub related_resultset {
865 my ( $self, $rel, @rest ) = @_;
866 $self->{related_resultsets} ||= {};
867 my $resultsets = $self->{related_resultsets};
868 if( !exists $resultsets->{$rel} ) {
869 #warn "fetching related resultset for rel '$rel'";
870 my $rel_obj = $self->result_source->relationship_info($rel);
871 $self->throw_exception(
872 "search_related: result source '" . $self->result_source->name .
873 "' has no such relationship ${rel}")
874 unless $rel_obj; #die Dumper $self->{attrs};
875 my $rs = $self->search(undef, { join => $rel });
876 #if( $self->{attrs}->{cache} ) {
877 # $rs = $self->search(undef);
881 #use Data::Dumper; die Dumper $rs->{attrs};#$rs = $self->search( undef );
882 #use Data::Dumper; warn Dumper $self->{attrs}, Dumper $rs->{attrs};
883 my $alias = (defined $rs->{attrs}{seen_join}{$rel}
884 && $rs->{attrs}{seen_join}{$rel} > 1
885 ? join('_', $rel, $rs->{attrs}{seen_join}{$rel})
887 $resultsets->{$rel} =
888 $self->result_source->schema->resultset($rel_obj->{class}
896 return $resultsets->{$rel};
899 =head2 throw_exception
901 See Schema's throw_exception
905 sub throw_exception {
907 $self->result_source->schema->throw_exception(@_);
912 The resultset takes various attributes that modify its behavior. Here's an
917 Which column(s) to order the results by. This is currently passed through
918 directly to SQL, so you can give e.g. C<foo DESC> for a descending order.
922 =head3 Arguments: (arrayref)
924 Shortcut to request a particular set of columns to be retrieved. Adds
925 C<me.> onto the start of any column without a C<.> in it and sets C<select>
926 from that, then auto-populates C<as> from C<select> as normal.
928 =head2 include_columns
930 =head3 Arguments: (arrayref)
932 Shortcut to include additional columns in the returned results - for example
934 { include_columns => ['foo.name'], join => ['foo'] }
936 would add a 'name' column to the information passed to object inflation
940 =head3 Arguments: (arrayref)
942 Indicates which columns should be selected from the storage. You can use
943 column names, or in the case of RDBMS back ends, function or stored procedure
946 $rs = $schema->resultset('Foo')->search(
951 { count => 'column_to_count' },
952 { sum => 'column_to_sum' }
957 When you use function/stored procedure names and do not supply an C<as>
958 attribute, the column names returned are storage-dependent. E.g. MySQL would
959 return a column named C<count(column_to_count)> in the above example.
963 =head3 Arguments: (arrayref)
965 Indicates column names for object inflation. This is used in conjunction with
966 C<select>, usually when C<select> contains one or more function or stored
969 $rs = $schema->resultset('Foo')->search(
974 { count => 'column2' }
976 as => [qw/ column1 column2_count /]
980 my $foo = $rs->first(); # get the first Foo
982 If the object against which the search is performed already has an accessor
983 matching a column name specified in C<as>, the value can be retrieved using
984 the accessor as normal:
986 my $column1 = $foo->column1();
988 If on the other hand an accessor does not exist in the object, you need to
989 use C<get_column> instead:
991 my $column2_count = $foo->get_column('column2_count');
993 You can create your own accessors if required - see
994 L<DBIx::Class::Manual::Cookbook> for details.
998 Contains a list of relationships that should be joined for this query. For
1001 # Get CDs by Nine Inch Nails
1002 my $rs = $schema->resultset('CD')->search(
1003 { 'artist.name' => 'Nine Inch Nails' },
1004 { join => 'artist' }
1007 Can also contain a hash reference to refer to the other relation's relations.
1010 package MyApp::Schema::Track;
1011 use base qw/DBIx::Class/;
1012 __PACKAGE__->table('track');
1013 __PACKAGE__->add_columns(qw/trackid cd position title/);
1014 __PACKAGE__->set_primary_key('trackid');
1015 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
1018 # In your application
1019 my $rs = $schema->resultset('Artist')->search(
1020 { 'track.title' => 'Teardrop' },
1022 join => { cd => 'track' },
1023 order_by => 'artist.name',
1027 If the same join is supplied twice, it will be aliased to <rel>_2 (and
1028 similarly for a third time). For e.g.
1030 my $rs = $schema->resultset('Artist')->search(
1031 { 'cds.title' => 'Foo',
1032 'cds_2.title' => 'Bar' },
1033 { join => [ qw/cds cds/ ] });
1035 will return a set of all artists that have both a cd with title Foo and a cd
1038 If you want to fetch related objects from other tables as well, see C<prefetch>
1043 =head3 Arguments: arrayref/hashref
1045 Contains one or more relationships that should be fetched along with the main
1046 query (when they are accessed afterwards they will have already been
1047 "prefetched"). This is useful for when you know you will need the related
1048 objects, because it saves at least one query:
1050 my $rs = $schema->resultset('Tag')->search(
1059 The initial search results in SQL like the following:
1061 SELECT tag.*, cd.*, artist.* FROM tag
1062 JOIN cd ON tag.cd = cd.cdid
1063 JOIN artist ON cd.artist = artist.artistid
1065 L<DBIx::Class> has no need to go back to the database when we access the
1066 C<cd> or C<artist> relationships, which saves us two SQL statements in this
1069 Simple prefetches will be joined automatically, so there is no need
1070 for a C<join> attribute in the above search. If you're prefetching to
1071 depth (e.g. { cd => { artist => 'label' } or similar), you'll need to
1072 specify the join as well.
1074 C<prefetch> can be used with the following relationship types: C<belongs_to>,
1075 C<has_one> (or if you're using C<add_relationship>, any relationship declared
1076 with an accessor type of 'single' or 'filter').
1080 =head3 Arguments: (arrayref)
1082 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
1083 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
1086 NOTE: Use this on your own risk. This allows you to shoot off your foot!
1087 C<join> will usually do what you need and it is strongly recommended that you
1088 avoid using C<from> unless you cannot achieve the desired result using C<join>.
1090 In simple terms, C<from> works as follows:
1093 { <alias> => <table>, -join-type => 'inner|left|right' }
1094 [] # nested JOIN (optional)
1095 { <table.column> = <foreign_table.foreign_key> }
1101 ON <table.column> = <foreign_table.foreign_key>
1103 An easy way to follow the examples below is to remember the following:
1105 Anything inside "[]" is a JOIN
1106 Anything inside "{}" is a condition for the enclosing JOIN
1108 The following examples utilize a "person" table in a family tree application.
1109 In order to express parent->child relationships, this table is self-joined:
1111 # Person->belongs_to('father' => 'Person');
1112 # Person->belongs_to('mother' => 'Person');
1114 C<from> can be used to nest joins. Here we return all children with a father,
1115 then search against all mothers of those children:
1117 $rs = $schema->resultset('Person')->search(
1120 alias => 'mother', # alias columns in accordance with "from"
1122 { mother => 'person' },
1125 { child => 'person' },
1127 { father => 'person' },
1128 { 'father.person_id' => 'child.father_id' }
1131 { 'mother.person_id' => 'child.mother_id' }
1138 # SELECT mother.* FROM person mother
1141 # JOIN person father
1142 # ON ( father.person_id = child.father_id )
1144 # ON ( mother.person_id = child.mother_id )
1146 The type of any join can be controlled manually. To search against only people
1147 with a father in the person table, we could explicitly use C<INNER JOIN>:
1149 $rs = $schema->resultset('Person')->search(
1152 alias => 'child', # alias columns in accordance with "from"
1154 { child => 'person' },
1156 { father => 'person', -join-type => 'inner' },
1157 { 'father.id' => 'child.father_id' }
1164 # SELECT child.* FROM person child
1165 # INNER JOIN person father ON child.father_id = father.id
1169 For a paged resultset, specifies which page to retrieve. Leave unset
1170 for an unpaged resultset.
1174 For a paged resultset, how many rows per page:
1178 Can also be used to simulate an SQL C<LIMIT>.
1182 =head3 Arguments: (arrayref)
1184 A arrayref of columns to group by. Can include columns of joined tables.
1186 group_by => [qw/ column1 column2 ... /]
1190 Set to 1 to group by all columns.
1192 For more examples of using these attributes, see
1193 L<DBIx::Class::Manual::Cookbook>.