1 package DBIx::Class::ResultSet;
9 use Carp::Clan qw/^DBIx::Class/;
12 use DBIx::Class::ResultSetColumn;
13 use DBIx::Class::ResultSourceHandle;
16 use base qw/DBIx::Class/;
18 __PACKAGE__->mk_group_accessors('simple' => qw/_result_class _source_handle/);
22 DBIx::Class::ResultSet - Represents a query used for fetching a set of results.
26 my $users_rs = $schema->resultset('User');
27 my $registered_users_rs = $schema->resultset('User')->search({ registered => 1 });
28 my @cds_in_2005 = $schema->resultset('CD')->search({ year => 2005 })->all();
32 A ResultSet is an object which stores a set of conditions representing
33 a query. It is the backbone of DBIx::Class (i.e. the really
34 important/useful bit).
36 No SQL is executed on the database when a ResultSet is created, it
37 just stores all the conditions needed to create the query.
39 A basic ResultSet representing the data of an entire table is returned
40 by calling C<resultset> on a L<DBIx::Class::Schema> and passing in a
41 L<Source|DBIx::Class::Manual::Glossary/Source> name.
43 my $users_rs = $schema->resultset('User');
45 A new ResultSet is returned from calling L</search> on an existing
46 ResultSet. The new one will contain all the conditions of the
47 original, plus any new conditions added in the C<search> call.
49 A ResultSet is also an iterator. L</next> is used to return all the
50 L<DBIx::Class::Row>s the ResultSet represents.
52 The query that the ResultSet represents is B<only> executed against
53 the database when these methods are called:
73 =head2 Chaining resultsets
75 Let's say you've got a query that needs to be run to return some data
76 to the user. But, you have an authorization system in place that
77 prevents certain users from seeing certain information. So, you want
78 to construct the basic query in one method, but add constraints to it in
83 my $request = $self->get_request; # Get a request object somehow.
84 my $schema = $self->get_schema; # Get the DBIC schema object somehow.
86 my $cd_rs = $schema->resultset('CD')->search({
87 title => $request->param('title'),
88 year => $request->param('year'),
91 $self->apply_security_policy( $cd_rs );
96 sub apply_security_policy {
105 =head3 Resolving conditions and attributes
107 When a resultset is chained from another resultset, conditions and
108 attributes with the same keys need resolving.
110 L</join>, L</prefetch>, L</+select>, L</+as> attributes are merged
111 into the existing ones from the original resultset.
113 The L</where>, L</having> attribute, and any search conditions are
114 merged with an SQL C<AND> to the existing condition from the original
117 All other attributes are overridden by any new ones supplied in the
120 =head2 Multiple queries
122 Since a resultset just defines a query, you can do all sorts of
123 things with it with the same object.
125 # Don't hit the DB yet.
126 my $cd_rs = $schema->resultset('CD')->search({
127 title => 'something',
131 # Each of these hits the DB individually.
132 my $count = $cd_rs->count;
133 my $most_recent = $cd_rs->get_column('date_released')->max();
134 my @records = $cd_rs->all;
136 And it's not just limited to SELECT statements.
142 $cd_rs->create({ artist => 'Fred' });
144 Which is the same as:
146 $schema->resultset('CD')->create({
147 title => 'something',
152 See: L</search>, L</count>, L</get_column>, L</all>, L</create>.
156 If a resultset is used in a numeric context it returns the L</count>.
157 However, if it is used in a booleand context it is always true. So if
158 you want to check if a resultset has any results use C<if $rs != 0>.
159 C<if $rs> will always be true.
167 =item Arguments: $source, \%$attrs
169 =item Return Value: $rs
173 The resultset constructor. Takes a source object (usually a
174 L<DBIx::Class::ResultSourceProxy::Table>) and an attribute hash (see
175 L</ATTRIBUTES> below). Does not perform any queries -- these are
176 executed as needed by the other methods.
178 Generally you won't need to construct a resultset manually. You'll
179 automatically get one from e.g. a L</search> called in scalar context:
181 my $rs = $schema->resultset('CD')->search({ title => '100th Window' });
183 IMPORTANT: If called on an object, proxies to new_result instead so
185 my $cd = $schema->resultset('CD')->new({ title => 'Spoon' });
187 will return a CD object, not a ResultSet.
193 return $class->new_result(@_) if ref $class;
195 my ($source, $attrs) = @_;
196 $source = $source->handle
197 unless $source->isa('DBIx::Class::ResultSourceHandle');
198 $attrs = { %{$attrs||{}} };
200 if ($attrs->{page}) {
201 $attrs->{rows} ||= 10;
204 $attrs->{alias} ||= 'me';
206 # Creation of {} and bless separated to mitigate RH perl bug
207 # see https://bugzilla.redhat.com/show_bug.cgi?id=196836
209 _source_handle => $source,
210 cond => $attrs->{where},
219 $attrs->{result_class} || $source->resolve->result_class
229 =item Arguments: $cond, \%attrs?
231 =item Return Value: $resultset (scalar context), @row_objs (list context)
235 my @cds = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
236 my $new_rs = $cd_rs->search({ year => 2005 });
238 my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
239 # year = 2005 OR year = 2004
241 If you need to pass in additional attributes but no additional condition,
242 call it as C<search(undef, \%attrs)>.
244 # "SELECT name, artistid FROM $artist_table"
245 my @all_artists = $schema->resultset('Artist')->search(undef, {
246 columns => [qw/name artistid/],
249 For a list of attributes that can be passed to C<search>, see
250 L</ATTRIBUTES>. For more examples of using this function, see
251 L<Searching|DBIx::Class::Manual::Cookbook/Searching>. For a complete
252 documentation for the first argument, see L<SQL::Abstract>.
254 For more help on using joins with search, see L<DBIx::Class::Manual::Joining>.
260 my $rs = $self->search_rs( @_ );
261 return (wantarray ? $rs->all : $rs);
268 =item Arguments: $cond, \%attrs?
270 =item Return Value: $resultset
274 This method does the same exact thing as search() except it will
275 always return a resultset, even in list context.
282 # Special-case handling for (undef, undef).
283 if ( @_ == 2 && !defined $_[1] && !defined $_[0] ) {
288 $attrs = pop(@_) if @_ > 1 and ref $_[$#_] eq 'HASH';
289 my $our_attrs = { %{$self->{attrs}} };
290 my $having = delete $our_attrs->{having};
291 my $where = delete $our_attrs->{where};
295 my %safe = (alias => 1, cache => 1);
298 (@_ && defined($_[0])) # @_ == () or (undef)
300 (keys %$attrs # empty attrs or only 'safe' attrs
301 && List::Util::first { !$safe{$_} } keys %$attrs)
303 # no search, effectively just a clone
304 $rows = $self->get_cache;
307 my $new_attrs = { %{$our_attrs}, %{$attrs} };
309 # merge new attrs into inherited
310 foreach my $key (qw/join prefetch +select +as bind/) {
311 next unless exists $attrs->{$key};
312 $new_attrs->{$key} = $self->_merge_attr($our_attrs->{$key}, $attrs->{$key});
317 (@_ == 1 || ref $_[0] eq "HASH")
319 (ref $_[0] eq 'HASH')
321 (keys %{ $_[0] } > 0)
329 ? $self->throw_exception("Odd number of arguments to search")
336 if (defined $where) {
337 $new_attrs->{where} = (
338 defined $new_attrs->{where}
341 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
342 } $where, $new_attrs->{where}
349 $new_attrs->{where} = (
350 defined $new_attrs->{where}
353 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
354 } $cond, $new_attrs->{where}
360 if (defined $having) {
361 $new_attrs->{having} = (
362 defined $new_attrs->{having}
365 ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_
366 } $having, $new_attrs->{having}
372 my $rs = (ref $self)->new($self->result_source, $new_attrs);
374 $rs->set_cache($rows);
379 =head2 search_literal
383 =item Arguments: $sql_fragment, @bind_values
385 =item Return Value: $resultset (scalar context), @row_objs (list context)
389 my @cds = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 Reload/);
390 my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
392 Pass a literal chunk of SQL to be added to the conditional part of the
395 CAVEAT: C<search_literal> is provided for Class::DBI compatibility and should
396 only be used in that context. C<search_literal> is a convenience method.
397 It is equivalent to calling $schema->search(\[]), but if you want to ensure
398 columns are bound correctly, use C<search>.
400 Example of how to use C<search> instead of C<search_literal>
402 my @cds = $cd_rs->search_literal('cdid = ? AND (artist = ? OR artist = ?)', (2, 1, 2));
403 my @cds = $cd_rs->search(\[ 'cdid = ? AND (artist = ? OR artist = ?)', [ 'cdid', 2 ], [ 'artist', 1 ], [ 'artist', 2 ] ]);
406 See L<DBIx::Class::Manual::Cookbook/Searching> and
407 L<DBIx::Class::Manual::FAQ/Searching> for searching techniques that do not
408 require C<search_literal>.
413 my ($self, $sql, @bind) = @_;
415 if ( @bind && ref($bind[-1]) eq 'HASH' ) {
418 return $self->search(\[ $sql, map [ __DUMMY__ => $_ ], @bind ], ($attr || () ));
425 =item Arguments: @values | \%cols, \%attrs?
427 =item Return Value: $row_object | undef
431 Finds a row based on its primary key or unique constraint. For example, to find
432 a row by its primary key:
434 my $cd = $schema->resultset('CD')->find(5);
436 You can also find a row by a specific unique constraint using the C<key>
437 attribute. For example:
439 my $cd = $schema->resultset('CD')->find('Massive Attack', 'Mezzanine', {
440 key => 'cd_artist_title'
443 Additionally, you can specify the columns explicitly by name:
445 my $cd = $schema->resultset('CD')->find(
447 artist => 'Massive Attack',
448 title => 'Mezzanine',
450 { key => 'cd_artist_title' }
453 If the C<key> is specified as C<primary>, it searches only on the primary key.
455 If no C<key> is specified, it searches on all unique constraints defined on the
456 source for which column data is provided, including the primary key.
458 If your table does not have a primary key, you B<must> provide a value for the
459 C<key> attribute matching one of the unique constraints on the source.
461 In addition to C<key>, L</find> recognizes and applies standard
462 L<resultset attributes|/ATTRIBUTES> in the same way as L</search> does.
464 Note: If your query does not return only one row, a warning is generated:
466 Query returned more than one row
468 See also L</find_or_create> and L</update_or_create>. For information on how to
469 declare unique constraints, see
470 L<DBIx::Class::ResultSource/add_unique_constraint>.
476 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
478 # Default to the primary key, but allow a specific key
479 my @cols = exists $attrs->{key}
480 ? $self->result_source->unique_constraint_columns($attrs->{key})
481 : $self->result_source->primary_columns;
482 $self->throw_exception(
483 "Can't find unless a primary key is defined or unique constraint is specified"
486 # Parse out a hashref from input
488 if (ref $_[0] eq 'HASH') {
489 $input_query = { %{$_[0]} };
491 elsif (@_ == @cols) {
493 @{$input_query}{@cols} = @_;
496 # Compatibility: Allow e.g. find(id => $value)
497 carp "Find by key => value deprecated; please use a hashref instead";
501 my (%related, $info);
503 KEY: foreach my $key (keys %$input_query) {
504 if (ref($input_query->{$key})
505 && ($info = $self->result_source->relationship_info($key))) {
506 my $val = delete $input_query->{$key};
507 next KEY if (ref($val) eq 'ARRAY'); # has_many for multi_create
508 my $rel_q = $self->result_source->resolve_condition(
509 $info->{cond}, $val, $key
511 die "Can't handle OR join condition in find" if ref($rel_q) eq 'ARRAY';
512 @related{keys %$rel_q} = values %$rel_q;
515 if (my @keys = keys %related) {
516 @{$input_query}{@keys} = values %related;
520 # Build the final query: Default to the disjunction of the unique queries,
521 # but allow the input query in case the ResultSet defines the query or the
522 # user is abusing find
523 my $alias = exists $attrs->{alias} ? $attrs->{alias} : $self->{attrs}{alias};
525 if (exists $attrs->{key}) {
526 my @unique_cols = $self->result_source->unique_constraint_columns($attrs->{key});
527 my $unique_query = $self->_build_unique_query($input_query, \@unique_cols);
528 $query = $self->_add_alias($unique_query, $alias);
531 my @unique_queries = $self->_unique_queries($input_query, $attrs);
532 $query = @unique_queries
533 ? [ map { $self->_add_alias($_, $alias) } @unique_queries ]
534 : $self->_add_alias($input_query, $alias);
539 my $rs = $self->search($query, $attrs);
540 if (keys %{$rs->_resolved_attrs->{collapse}}) {
542 carp "Query returned more than one row" if $rs->next;
550 if (keys %{$self->_resolved_attrs->{collapse}}) {
551 my $rs = $self->search($query);
553 carp "Query returned more than one row" if $rs->next;
557 return $self->single($query);
564 # Add the specified alias to the specified query hash. A copy is made so the
565 # original query is not modified.
568 my ($self, $query, $alias) = @_;
570 my %aliased = %$query;
571 foreach my $col (grep { ! m/\./ } keys %aliased) {
572 $aliased{"$alias.$col"} = delete $aliased{$col};
580 # Build a list of queries which satisfy unique constraints.
582 sub _unique_queries {
583 my ($self, $query, $attrs) = @_;
585 my @constraint_names = exists $attrs->{key}
587 : $self->result_source->unique_constraint_names;
589 my $where = $self->_collapse_cond($self->{attrs}{where} || {});
590 my $num_where = scalar keys %$where;
593 foreach my $name (@constraint_names) {
594 my @unique_cols = $self->result_source->unique_constraint_columns($name);
595 my $unique_query = $self->_build_unique_query($query, \@unique_cols);
597 my $num_cols = scalar @unique_cols;
598 my $num_query = scalar keys %$unique_query;
600 my $total = $num_query + $num_where;
601 if ($num_query && ($num_query == $num_cols || $total == $num_cols)) {
602 # The query is either unique on its own or is unique in combination with
603 # the existing where clause
604 push @unique_queries, $unique_query;
608 return @unique_queries;
611 # _build_unique_query
613 # Constrain the specified query hash based on the specified column names.
615 sub _build_unique_query {
616 my ($self, $query, $unique_cols) = @_;
619 map { $_ => $query->{$_} }
620 grep { exists $query->{$_} }
625 =head2 search_related
629 =item Arguments: $rel, $cond, \%attrs?
631 =item Return Value: $new_resultset
635 $new_rs = $cd_rs->search_related('artist', {
639 Searches the specified relationship, optionally specifying a condition and
640 attributes for matching records. See L</ATTRIBUTES> for more information.
645 return shift->related_resultset(shift)->search(@_);
648 =head2 search_related_rs
650 This method works exactly the same as search_related, except that
651 it guarantees a restultset, even in list context.
655 sub search_related_rs {
656 return shift->related_resultset(shift)->search_rs(@_);
663 =item Arguments: none
665 =item Return Value: $cursor
669 Returns a storage-driven cursor to the given resultset. See
670 L<DBIx::Class::Cursor> for more information.
677 my $attrs = { %{$self->_resolved_attrs} };
678 return $self->{cursor}
679 ||= $self->result_source->storage->select($attrs->{from}, $attrs->{select},
680 $attrs->{where},$attrs);
687 =item Arguments: $cond?
689 =item Return Value: $row_object?
693 my $cd = $schema->resultset('CD')->single({ year => 2001 });
695 Inflates the first result without creating a cursor if the resultset has
696 any records in it; if not returns nothing. Used by L</find> as a lean version of
699 While this method can take an optional search condition (just like L</search>)
700 being a fast-code-path it does not recognize search attributes. If you need to
701 add extra joins or similar, call L</search> and then chain-call L</single> on the
702 L<DBIx::Class::ResultSet> returned.
708 As of 0.08100, this method enforces the assumption that the preceeding
709 query returns only one row. If more than one row is returned, you will receive
712 Query returned more than one row
714 In this case, you should be using L</first> or L</find> instead, or if you really
715 know what you are doing, use the L</rows> attribute to explicitly limit the size
723 my ($self, $where) = @_;
725 $self->throw_exception('single() only takes search conditions, no attributes. You want ->search( $cond, $attrs )->single()');
728 my $attrs = { %{$self->_resolved_attrs} };
730 if (defined $attrs->{where}) {
733 [ map { ref $_ eq 'ARRAY' ? [ -or => $_ ] : $_ }
734 $where, delete $attrs->{where} ]
737 $attrs->{where} = $where;
741 # XXX: Disabled since it doesn't infer uniqueness in all cases
742 # unless ($self->_is_unique_query($attrs->{where})) {
743 # carp "Query not guaranteed to return a single row"
744 # . "; please declare your unique constraints or use search instead";
747 my @data = $self->result_source->storage->select_single(
748 $attrs->{from}, $attrs->{select},
749 $attrs->{where}, $attrs
752 return (@data ? ($self->_construct_object(@data))[0] : undef);
757 # Try to determine if the specified query is guaranteed to be unique, based on
758 # the declared unique constraints.
760 sub _is_unique_query {
761 my ($self, $query) = @_;
763 my $collapsed = $self->_collapse_query($query);
764 my $alias = $self->{attrs}{alias};
766 foreach my $name ($self->result_source->unique_constraint_names) {
767 my @unique_cols = map {
769 } $self->result_source->unique_constraint_columns($name);
771 # Count the values for each unique column
772 my %seen = map { $_ => 0 } @unique_cols;
774 foreach my $key (keys %$collapsed) {
775 my $aliased = $key =~ /\./ ? $key : "$alias.$key";
776 next unless exists $seen{$aliased}; # Additional constraints are okay
777 $seen{$aliased} = scalar keys %{ $collapsed->{$key} };
780 # If we get 0 or more than 1 value for a column, it's not necessarily unique
781 return 1 unless grep { $_ != 1 } values %seen;
789 # Recursively collapse the query, accumulating values for each column.
791 sub _collapse_query {
792 my ($self, $query, $collapsed) = @_;
796 if (ref $query eq 'ARRAY') {
797 foreach my $subquery (@$query) {
798 next unless ref $subquery; # -or
799 $collapsed = $self->_collapse_query($subquery, $collapsed);
802 elsif (ref $query eq 'HASH') {
803 if (keys %$query and (keys %$query)[0] eq '-and') {
804 foreach my $subquery (@{$query->{-and}}) {
805 $collapsed = $self->_collapse_query($subquery, $collapsed);
809 foreach my $col (keys %$query) {
810 my $value = $query->{$col};
811 $collapsed->{$col}{$value}++;
823 =item Arguments: $cond?
825 =item Return Value: $resultsetcolumn
829 my $max_length = $rs->get_column('length')->max;
831 Returns a L<DBIx::Class::ResultSetColumn> instance for a column of the ResultSet.
836 my ($self, $column) = @_;
837 my $new = DBIx::Class::ResultSetColumn->new($self, $column);
845 =item Arguments: $cond, \%attrs?
847 =item Return Value: $resultset (scalar context), @row_objs (list context)
851 # WHERE title LIKE '%blue%'
852 $cd_rs = $rs->search_like({ title => '%blue%'});
854 Performs a search, but uses C<LIKE> instead of C<=> as the condition. Note
855 that this is simply a convenience method retained for ex Class::DBI users.
856 You most likely want to use L</search> with specific operators.
858 For more information, see L<DBIx::Class::Manual::Cookbook>.
860 This method is deprecated and will be removed in 0.09. Use L</search()>
861 instead. An example conversion is:
863 ->search_like({ foo => 'bar' });
867 ->search({ foo => { like => 'bar' } });
874 'search_like() is deprecated and will be removed in 0.09.',
875 'Instead use ->search({ x => { -like => "y%" } })',
876 '(note the outer pair of {}s - they are important!)'
878 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
879 my $query = ref $_[0] eq 'HASH' ? { %{shift()} }: {@_};
880 $query->{$_} = { 'like' => $query->{$_} } for keys %$query;
881 return $class->search($query, { %$attrs });
888 =item Arguments: $first, $last
890 =item Return Value: $resultset (scalar context), @row_objs (list context)
894 Returns a resultset or object list representing a subset of elements from the
895 resultset slice is called on. Indexes are from 0, i.e., to get the first
898 my ($one, $two, $three) = $rs->slice(0, 2);
903 my ($self, $min, $max) = @_;
904 my $attrs = {}; # = { %{ $self->{attrs} || {} } };
905 $attrs->{offset} = $self->{attrs}{offset} || 0;
906 $attrs->{offset} += $min;
907 $attrs->{rows} = ($max ? ($max - $min + 1) : 1);
908 return $self->search(undef(), $attrs);
909 #my $slice = (ref $self)->new($self->result_source, $attrs);
910 #return (wantarray ? $slice->all : $slice);
917 =item Arguments: none
919 =item Return Value: $result?
923 Returns the next element in the resultset (C<undef> is there is none).
925 Can be used to efficiently iterate over records in the resultset:
927 my $rs = $schema->resultset('CD')->search;
928 while (my $cd = $rs->next) {
932 Note that you need to store the resultset object, and call C<next> on it.
933 Calling C<< resultset('Table')->next >> repeatedly will always return the
934 first record from the resultset.
940 if (my $cache = $self->get_cache) {
941 $self->{all_cache_position} ||= 0;
942 return $cache->[$self->{all_cache_position}++];
944 if ($self->{attrs}{cache}) {
945 $self->{all_cache_position} = 1;
946 return ($self->all)[0];
948 if ($self->{stashed_objects}) {
949 my $obj = shift(@{$self->{stashed_objects}});
950 delete $self->{stashed_objects} unless @{$self->{stashed_objects}};
954 exists $self->{stashed_row}
955 ? @{delete $self->{stashed_row}}
956 : $self->cursor->next
958 return undef unless (@row);
959 my ($row, @more) = $self->_construct_object(@row);
960 $self->{stashed_objects} = \@more if @more;
964 sub _construct_object {
965 my ($self, @row) = @_;
966 my $info = $self->_collapse_result($self->{_attrs}{as}, \@row);
967 my @new = $self->result_class->inflate_result($self->result_source, @$info);
968 @new = $self->{_attrs}{record_filter}->(@new)
969 if exists $self->{_attrs}{record_filter};
973 sub _collapse_result {
974 my ($self, $as_proto, $row) = @_;
978 # 'foo' => [ undef, 'foo' ]
979 # 'foo.bar' => [ 'foo', 'bar' ]
980 # 'foo.bar.baz' => [ 'foo.bar', 'baz' ]
982 my @construct_as = map { [ (/^(?:(.*)\.)?([^.]+)$/) ] } @$as_proto;
984 my %collapse = %{$self->{_attrs}{collapse}||{}};
988 # if we're doing collapsing (has_many prefetch) we need to grab records
989 # until the PK changes, so fill @pri_index. if not, we leave it empty so
990 # we know we don't have to bother.
992 # the reason for not using the collapse stuff directly is because if you
993 # had for e.g. two artists in a row with no cds, the collapse info for
994 # both would be NULL (undef) so you'd lose the second artist
996 # store just the index so we can check the array positions from the row
997 # without having to contruct the full hash
999 if (keys %collapse) {
1000 my %pri = map { ($_ => 1) } $self->result_source->primary_columns;
1001 foreach my $i (0 .. $#construct_as) {
1002 next if defined($construct_as[$i][0]); # only self table
1003 if (delete $pri{$construct_as[$i][1]}) {
1004 push(@pri_index, $i);
1006 last unless keys %pri; # short circuit (Johnny Five Is Alive!)
1010 # no need to do an if, it'll be empty if @pri_index is empty anyway
1012 my %pri_vals = map { ($_ => $copy[$_]) } @pri_index;
1016 do { # no need to check anything at the front, we always want the first row
1020 foreach my $this_as (@construct_as) {
1021 $const{$this_as->[0]||''}{$this_as->[1]} = shift(@copy);
1024 push(@const_rows, \%const);
1026 } until ( # no pri_index => no collapse => drop straight out
1029 do { # get another row, stash it, drop out if different PK
1031 @copy = $self->cursor->next;
1032 $self->{stashed_row} = \@copy;
1034 # last thing in do block, counts as true if anything doesn't match
1036 # check xor defined first for NULL vs. NOT NULL then if one is
1037 # defined the other must be so check string equality
1040 (defined $pri_vals{$_} ^ defined $copy[$_])
1041 || (defined $pri_vals{$_} && ($pri_vals{$_} ne $copy[$_]))
1046 my $alias = $self->{attrs}{alias};
1053 foreach my $const (@const_rows) {
1054 scalar @const_keys or do {
1055 @const_keys = sort { length($a) <=> length($b) } keys %$const;
1057 foreach my $key (@const_keys) {
1060 my @parts = split(/\./, $key);
1062 my $data = $const->{$key};
1063 foreach my $p (@parts) {
1064 $target = $target->[1]->{$p} ||= [];
1066 if ($cur eq ".${key}" && (my @ckey = @{$collapse{$cur}||[]})) {
1067 # collapsing at this point and on final part
1068 my $pos = $collapse_pos{$cur};
1069 CK: foreach my $ck (@ckey) {
1070 if (!defined $pos->{$ck} || $pos->{$ck} ne $data->{$ck}) {
1071 $collapse_pos{$cur} = $data;
1072 delete @collapse_pos{ # clear all positioning for sub-entries
1073 grep { m/^\Q${cur}.\E/ } keys %collapse_pos
1080 if (exists $collapse{$cur}) {
1081 $target = $target->[-1];
1084 $target->[0] = $data;
1086 $info->[0] = $const->{$key};
1094 =head2 result_source
1098 =item Arguments: $result_source?
1100 =item Return Value: $result_source
1104 An accessor for the primary ResultSource object from which this ResultSet
1111 =item Arguments: $result_class?
1113 =item Return Value: $result_class
1117 An accessor for the class to use when creating row objects. Defaults to
1118 C<< result_source->result_class >> - which in most cases is the name of the
1119 L<"table"|DBIx::Class::Manual::Glossary/"ResultSource"> class.
1121 Note that changing the result_class will also remove any components
1122 that were originally loaded in the source class via
1123 L<DBIx::Class::ResultSource/load_components>. Any overloaded methods
1124 in the original source class will not run.
1129 my ($self, $result_class) = @_;
1130 if ($result_class) {
1131 $self->ensure_class_loaded($result_class);
1132 $self->_result_class($result_class);
1134 $self->_result_class;
1141 =item Arguments: $cond, \%attrs??
1143 =item Return Value: $count
1147 Performs an SQL C<COUNT> with the same query as the resultset was built
1148 with to find the number of elements. If passed arguments, does a search
1149 on the resultset and counts the results of that.
1153 my @count_via_subq_attrs = qw/join seen_join group_by/;
1156 return $self->search(@_)->count if @_ and defined $_[0];
1157 return scalar @{ $self->get_cache } if $self->get_cache;
1159 my @check_attrs = @count_via_subq_attrs;
1161 # if we are not paged - we are simply asking for a limit
1162 if (not $self->{attrs}{page} and not $self->{attrs}{software_limit}) {
1163 push @check_attrs, qw/rows offset/;
1166 return $self->_has_attr (@check_attrs)
1167 ? $self->_count_subq
1168 : $self->_count_simple
1174 my $attrs = { %{$self->_resolved_attrs} };
1176 my $select_cols = $attrs->{group_by} || [ map { "$attrs->{alias}.$_" } ($self->result_source->primary_columns) ];
1178 count_subq => $self->search ({}, { columns => $select_cols, group_by => $select_cols })
1182 # the subquery above will integrate everything, including 'where' and any pagers
1183 delete $attrs->{$_} for (@count_via_subq_attrs, qw/where rows offset pager page/ );
1185 return $self->__count ($attrs);
1191 my $count = $self->__count;
1192 return 0 unless $count;
1194 # need to take offset from resolved attrs
1196 $count -= $self->{_attrs}{offset} if $self->{_attrs}{offset};
1197 $count = $self->{attrs}{rows} if
1198 $self->{attrs}{rows} and $self->{attrs}{rows} < $count;
1199 $count = 0 if ($count < 0);
1204 my ($self, $attrs) = @_;
1206 $attrs ||= { %{$self->{attrs}} };
1208 $attrs->{select} = { count => '*' };
1209 $attrs->{as} = [qw/count/];
1211 # take off any pagers, record_filter is cdbi, and no point of ordering a count
1212 delete $attrs->{$_} for qw/rows offset page pager order_by record_filter/;
1214 my $tmp_rs = (ref $self)->new($self->result_source, $attrs);
1215 my ($count) = $tmp_rs->cursor->next;
1224 =head2 count_literal
1228 =item Arguments: $sql_fragment, @bind_values
1230 =item Return Value: $count
1234 Counts the results in a literal query. Equivalent to calling L</search_literal>
1235 with the passed arguments, then L</count>.
1239 sub count_literal { shift->search_literal(@_)->count; }
1245 =item Arguments: none
1247 =item Return Value: @objects
1251 Returns all elements in the resultset. Called implicitly if the resultset
1252 is returned in list context.
1259 $self->throw_exception("all() doesn't take any arguments, you probably wanted ->search(...)->all()");
1262 return @{ $self->get_cache } if $self->get_cache;
1266 # TODO: don't call resolve here
1267 if (keys %{$self->_resolved_attrs->{collapse}}) {
1268 # if ($self->{attrs}{prefetch}) {
1269 # Using $self->cursor->all is really just an optimisation.
1270 # If we're collapsing has_many prefetches it probably makes
1271 # very little difference, and this is cleaner than hacking
1272 # _construct_object to survive the approach
1273 my @row = $self->cursor->next;
1275 push(@obj, $self->_construct_object(@row));
1276 @row = (exists $self->{stashed_row}
1277 ? @{delete $self->{stashed_row}}
1278 : $self->cursor->next);
1281 @obj = map { $self->_construct_object(@$_) } $self->cursor->all;
1284 $self->set_cache(\@obj) if $self->{attrs}{cache};
1292 =item Arguments: none
1294 =item Return Value: $self
1298 Resets the resultset's cursor, so you can iterate through the elements again.
1304 delete $self->{_attrs} if exists $self->{_attrs};
1305 $self->{all_cache_position} = 0;
1306 $self->cursor->reset;
1314 =item Arguments: none
1316 =item Return Value: $object?
1320 Resets the resultset and returns an object for the first result (if the
1321 resultset returns anything).
1326 return $_[0]->reset->next;
1329 # _cond_for_update_delete
1331 # update/delete require the condition to be modified to handle
1332 # the differing SQL syntax available. This transforms the $self->{cond}
1333 # appropriately, returning the new condition.
1335 sub _cond_for_update_delete {
1336 my ($self, $full_cond) = @_;
1339 $full_cond ||= $self->{cond};
1340 # No-op. No condition, we're updating/deleting everything
1341 return $cond unless ref $full_cond;
1343 # Some attributes when present require a subquery
1344 # This might not work on some database (mysql), but...
1345 # it won't work without the subquery either so who cares
1346 if ($self->_has_attr (qw/join seen_join group_by row offset page/) ) {
1348 foreach my $pk ($self->result_source->primary_columns) {
1349 $cond->{$pk} = { -in => $self->get_column($pk)->as_query };
1355 if (ref $full_cond eq 'ARRAY') {
1359 foreach my $key (keys %{$_}) {
1361 $hash{$1} = $_->{$key};
1367 elsif (ref $full_cond eq 'HASH') {
1368 if ((keys %{$full_cond})[0] eq '-and') {
1370 my @cond = @{$full_cond->{-and}};
1371 for (my $i = 0; $i < @cond; $i++) {
1372 my $entry = $cond[$i];
1374 if (ref $entry eq 'HASH') {
1375 $hash = $self->_cond_for_update_delete($entry);
1378 $entry =~ /([^.]+)$/;
1379 $hash->{$1} = $cond[++$i];
1381 push @{$cond->{-and}}, $hash;
1385 foreach my $key (keys %{$full_cond}) {
1387 $cond->{$1} = $full_cond->{$key};
1392 $self->throw_exception("Can't update/delete on resultset with condition unless hash or array");
1403 =item Arguments: \%values
1405 =item Return Value: $storage_rv
1409 Sets the specified columns in the resultset to the supplied values in a
1410 single query. Return value will be true if the update succeeded or false
1411 if no records were updated; exact type of success value is storage-dependent.
1416 my ($self, $values) = @_;
1417 $self->throw_exception("Values for update must be a hash")
1418 unless ref $values eq 'HASH';
1420 my $cond = $self->_cond_for_update_delete;
1422 return $self->result_source->storage->update(
1423 $self->result_source, $values, $cond
1431 =item Arguments: \%values
1433 =item Return Value: 1
1437 Fetches all objects and updates them one at a time. Note that C<update_all>
1438 will run DBIC cascade triggers, while L</update> will not.
1443 my ($self, $values) = @_;
1444 $self->throw_exception("Values for update must be a hash")
1445 unless ref $values eq 'HASH';
1446 foreach my $obj ($self->all) {
1447 $obj->set_columns($values)->update;
1456 =item Arguments: none
1458 =item Return Value: 1
1462 Deletes the contents of the resultset from its result source. Note that this
1463 will not run DBIC cascade triggers. See L</delete_all> if you need triggers
1464 to run. See also L<DBIx::Class::Row/delete>.
1466 delete may not generate correct SQL for a query with joins or a resultset
1467 chained from a related resultset. In this case it will generate a warning:-
1469 In these cases you may find that delete_all is more appropriate, or you
1470 need to respecify your query in a way that can be expressed without a join.
1476 $self->throw_exception("Delete should not be passed any arguments")
1479 my $cond = $self->_cond_for_update_delete;
1481 $self->result_source->storage->delete($self->result_source, $cond);
1489 =item Arguments: none
1491 =item Return Value: 1
1495 Fetches all objects and deletes them one at a time. Note that C<delete_all>
1496 will run DBIC cascade triggers, while L</delete> will not.
1502 $_->delete for $self->all;
1510 =item Arguments: \@data;
1514 Accepts either an arrayref of hashrefs or alternatively an arrayref of arrayrefs.
1515 For the arrayref of hashrefs style each hashref should be a structure suitable
1516 forsubmitting to a $resultset->create(...) method.
1518 In void context, C<insert_bulk> in L<DBIx::Class::Storage::DBI> is used
1519 to insert the data, as this is a faster method.
1521 Otherwise, each set of data is inserted into the database using
1522 L<DBIx::Class::ResultSet/create>, and a arrayref of the resulting row
1523 objects is returned.
1525 Example: Assuming an Artist Class that has many CDs Classes relating:
1527 my $Artist_rs = $schema->resultset("Artist");
1529 ## Void Context Example
1530 $Artist_rs->populate([
1531 { artistid => 4, name => 'Manufactured Crap', cds => [
1532 { title => 'My First CD', year => 2006 },
1533 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
1536 { artistid => 5, name => 'Angsty-Whiny Girl', cds => [
1537 { title => 'My parents sold me to a record company' ,year => 2005 },
1538 { title => 'Why Am I So Ugly?', year => 2006 },
1539 { title => 'I Got Surgery and am now Popular', year => 2007 }
1544 ## Array Context Example
1545 my ($ArtistOne, $ArtistTwo, $ArtistThree) = $Artist_rs->populate([
1546 { name => "Artist One"},
1547 { name => "Artist Two"},
1548 { name => "Artist Three", cds=> [
1549 { title => "First CD", year => 2007},
1550 { title => "Second CD", year => 2008},
1554 print $ArtistOne->name; ## response is 'Artist One'
1555 print $ArtistThree->cds->count ## reponse is '2'
1557 For the arrayref of arrayrefs style, the first element should be a list of the
1558 fieldsnames to which the remaining elements are rows being inserted. For
1561 $Arstist_rs->populate([
1562 [qw/artistid name/],
1563 [100, 'A Formally Unknown Singer'],
1564 [101, 'A singer that jumped the shark two albums ago'],
1565 [102, 'An actually cool singer.'],
1568 Please note an important effect on your data when choosing between void and
1569 wantarray context. Since void context goes straight to C<insert_bulk> in
1570 L<DBIx::Class::Storage::DBI> this will skip any component that is overriding
1571 c<insert>. So if you are using something like L<DBIx-Class-UUIDColumns> to
1572 create primary keys for you, you will find that your PKs are empty. In this
1573 case you will have to use the wantarray context in order to create those
1579 my $self = shift @_;
1580 my $data = ref $_[0][0] eq 'HASH'
1581 ? $_[0] : ref $_[0][0] eq 'ARRAY' ? $self->_normalize_populate_args($_[0]) :
1582 $self->throw_exception('Populate expects an arrayref of hashes or arrayref of arrayrefs');
1584 if(defined wantarray) {
1586 foreach my $item (@$data) {
1587 push(@created, $self->create($item));
1591 my ($first, @rest) = @$data;
1593 my @names = grep {!ref $first->{$_}} keys %$first;
1594 my @rels = grep { $self->result_source->has_relationship($_) } keys %$first;
1595 my @pks = $self->result_source->primary_columns;
1597 ## do the belongs_to relationships
1598 foreach my $index (0..$#$data) {
1599 if( grep { !defined $data->[$index]->{$_} } @pks ) {
1600 my @ret = $self->populate($data);
1604 foreach my $rel (@rels) {
1605 next unless $data->[$index]->{$rel} && ref $data->[$index]->{$rel} eq "HASH";
1606 my $result = $self->related_resultset($rel)->create($data->[$index]->{$rel});
1607 my ($reverse) = keys %{$self->result_source->reverse_relationship_info($rel)};
1608 my $related = $result->result_source->resolve_condition(
1609 $result->result_source->relationship_info($reverse)->{cond},
1614 delete $data->[$index]->{$rel};
1615 $data->[$index] = {%{$data->[$index]}, %$related};
1617 push @names, keys %$related if $index == 0;
1621 ## do bulk insert on current row
1622 my @values = map { [ @$_{@names} ] } @$data;
1624 $self->result_source->storage->insert_bulk(
1625 $self->result_source,
1630 ## do the has_many relationships
1631 foreach my $item (@$data) {
1633 foreach my $rel (@rels) {
1634 next unless $item->{$rel} && ref $item->{$rel} eq "ARRAY";
1636 my $parent = $self->find(map {{$_=>$item->{$_}} } @pks)
1637 || $self->throw_exception('Cannot find the relating object.');
1639 my $child = $parent->$rel;
1641 my $related = $child->result_source->resolve_condition(
1642 $parent->result_source->relationship_info($rel)->{cond},
1647 my @rows_to_add = ref $item->{$rel} eq 'ARRAY' ? @{$item->{$rel}} : ($item->{$rel});
1648 my @populate = map { {%$_, %$related} } @rows_to_add;
1650 $child->populate( \@populate );
1656 =head2 _normalize_populate_args ($args)
1658 Private method used by L</populate> to normalize its incoming arguments. Factored
1659 out in case you want to subclass and accept new argument structures to the
1660 L</populate> method.
1664 sub _normalize_populate_args {
1665 my ($self, $data) = @_;
1666 my @names = @{shift(@$data)};
1667 my @results_to_create;
1668 foreach my $datum (@$data) {
1669 my %result_to_create;
1670 foreach my $index (0..$#names) {
1671 $result_to_create{$names[$index]} = $$datum[$index];
1673 push @results_to_create, \%result_to_create;
1675 return \@results_to_create;
1682 =item Arguments: none
1684 =item Return Value: $pager
1688 Return Value a L<Data::Page> object for the current resultset. Only makes
1689 sense for queries with a C<page> attribute.
1691 To get the full count of entries for a paged resultset, call
1692 C<total_entries> on the L<Data::Page> object.
1698 my $attrs = $self->{attrs};
1699 $self->throw_exception("Can't create pager for non-paged rs")
1700 unless $self->{attrs}{page};
1701 $attrs->{rows} ||= 10;
1702 return $self->{pager} ||= Data::Page->new(
1703 $self->__count, $attrs->{rows}, $self->{attrs}{page});
1710 =item Arguments: $page_number
1712 =item Return Value: $rs
1716 Returns a resultset for the $page_number page of the resultset on which page
1717 is called, where each page contains a number of rows equal to the 'rows'
1718 attribute set on the resultset (10 by default).
1723 my ($self, $page) = @_;
1724 return (ref $self)->new($self->result_source, { %{$self->{attrs}}, page => $page });
1731 =item Arguments: \%vals
1733 =item Return Value: $rowobject
1737 Creates a new row object in the resultset's result class and returns
1738 it. The row is not inserted into the database at this point, call
1739 L<DBIx::Class::Row/insert> to do that. Calling L<DBIx::Class::Row/in_storage>
1740 will tell you whether the row object has been inserted or not.
1742 Passes the hashref of input on to L<DBIx::Class::Row/new>.
1747 my ($self, $values) = @_;
1748 $self->throw_exception( "new_result needs a hash" )
1749 unless (ref $values eq 'HASH');
1752 my $alias = $self->{attrs}{alias};
1755 defined $self->{cond}
1756 && $self->{cond} eq $DBIx::Class::ResultSource::UNRESOLVABLE_CONDITION
1758 %new = %{ $self->{attrs}{related_objects} || {} }; # nothing might have been inserted yet
1759 $new{-from_resultset} = [ keys %new ] if keys %new;
1761 $self->throw_exception(
1762 "Can't abstract implicit construct, condition not a hash"
1763 ) if ($self->{cond} && !(ref $self->{cond} eq 'HASH'));
1765 my $collapsed_cond = (
1767 ? $self->_collapse_cond($self->{cond})
1771 # precendence must be given to passed values over values inherited from
1772 # the cond, so the order here is important.
1773 my %implied = %{$self->_remove_alias($collapsed_cond, $alias)};
1774 while( my($col,$value) = each %implied ){
1775 if(ref($value) eq 'HASH' && keys(%$value) && (keys %$value)[0] eq '='){
1776 $new{$col} = $value->{'='};
1779 $new{$col} = $value if $self->_is_deterministic_value($value);
1785 %{ $self->_remove_alias($values, $alias) },
1786 -source_handle => $self->_source_handle,
1787 -result_source => $self->result_source, # DO NOT REMOVE THIS, REQUIRED
1790 return $self->result_class->new(\%new);
1793 # _is_deterministic_value
1795 # Make an effor to strip non-deterministic values from the condition,
1796 # to make sure new_result chokes less
1798 sub _is_deterministic_value {
1801 my $ref_type = ref $value;
1802 return 1 if $ref_type eq '' || $ref_type eq 'SCALAR';
1803 return 1 if Scalar::Util::blessed($value);
1809 # determines if the resultset defines at least one
1810 # of the attributes supplied
1812 # used to determine if a subquery is neccessary
1815 my ($self, @attr_names) = @_;
1817 my $attrs = $self->_resolved_attrs;
1821 for my $n (@attr_names) {
1822 return 1 if defined $attrs->{$n};
1823 ++$join_check_req if $n =~ /join/;
1826 # a join can be expressed as a multi-level from
1830 ref $attrs->{from} eq 'ARRAY'
1832 @{$attrs->{from}} > 1
1840 # Recursively collapse the condition.
1842 sub _collapse_cond {
1843 my ($self, $cond, $collapsed) = @_;
1847 if (ref $cond eq 'ARRAY') {
1848 foreach my $subcond (@$cond) {
1849 next unless ref $subcond; # -or
1850 $collapsed = $self->_collapse_cond($subcond, $collapsed);
1853 elsif (ref $cond eq 'HASH') {
1854 if (keys %$cond and (keys %$cond)[0] eq '-and') {
1855 foreach my $subcond (@{$cond->{-and}}) {
1856 $collapsed = $self->_collapse_cond($subcond, $collapsed);
1860 foreach my $col (keys %$cond) {
1861 my $value = $cond->{$col};
1862 $collapsed->{$col} = $value;
1872 # Remove the specified alias from the specified query hash. A copy is made so
1873 # the original query is not modified.
1876 my ($self, $query, $alias) = @_;
1878 my %orig = %{ $query || {} };
1881 foreach my $key (keys %orig) {
1883 $unaliased{$key} = $orig{$key};
1886 $unaliased{$1} = $orig{$key}
1887 if $key =~ m/^(?:\Q$alias\E\.)?([^.]+)$/;
1893 =head2 as_query (EXPERIMENTAL)
1897 =item Arguments: none
1899 =item Return Value: \[ $sql, @bind ]
1903 Returns the SQL query and bind vars associated with the invocant.
1905 This is generally used as the RHS for a subquery.
1907 B<NOTE>: This feature is still experimental.
1911 sub as_query { return shift->cursor->as_query(@_) }
1917 =item Arguments: \%vals, \%attrs?
1919 =item Return Value: $rowobject
1923 my $artist = $schema->resultset('Artist')->find_or_new(
1924 { artist => 'fred' }, { key => 'artists' });
1926 $cd->cd_to_producer->find_or_new({ producer => $producer },
1927 { key => 'primary });
1929 Find an existing record from this resultset, based on its primary
1930 key, or a unique constraint. If none exists, instantiate a new result
1931 object and return it. The object will not be saved into your storage
1932 until you call L<DBIx::Class::Row/insert> on it.
1934 You most likely want this method when looking for existing rows using
1935 a unique constraint that is not the primary key, or looking for
1938 If you want objects to be saved immediately, use L</find_or_create> instead.
1940 B<Note>: C<find_or_new> is probably not what you want when creating a
1941 new row in a table that uses primary keys supplied by the
1942 database. Passing in a primary key column with a value of I<undef>
1943 will cause L</find> to attempt to search for a row with a value of
1950 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
1951 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
1952 my $exists = $self->find($hash, $attrs);
1953 return defined $exists ? $exists : $self->new_result($hash);
1960 =item Arguments: \%vals
1962 =item Return Value: a L<DBIx::Class::Row> $object
1966 Attempt to create a single new row or a row with multiple related rows
1967 in the table represented by the resultset (and related tables). This
1968 will not check for duplicate rows before inserting, use
1969 L</find_or_create> to do that.
1971 To create one row for this resultset, pass a hashref of key/value
1972 pairs representing the columns of the table and the values you wish to
1973 store. If the appropriate relationships are set up, foreign key fields
1974 can also be passed an object representing the foreign row, and the
1975 value will be set to its primary key.
1977 To create related objects, pass a hashref for the value if the related
1978 item is a foreign key relationship (L<DBIx::Class::Relationship/belongs_to>),
1979 and use the name of the relationship as the key. (NOT the name of the field,
1980 necessarily). For C<has_many> and C<has_one> relationships, pass an arrayref
1981 of hashrefs containing the data for each of the rows to create in the foreign
1982 tables, again using the relationship name as the key.
1984 Instead of hashrefs of plain related data (key/value pairs), you may
1985 also pass new or inserted objects. New objects (not inserted yet, see
1986 L</new>), will be inserted into their appropriate tables.
1988 Effectively a shortcut for C<< ->new_result(\%vals)->insert >>.
1990 Example of creating a new row.
1992 $person_rs->create({
1993 name=>"Some Person",
1994 email=>"somebody@someplace.com"
1997 Example of creating a new row and also creating rows in a related C<has_many>
1998 or C<has_one> resultset. Note Arrayref.
2001 { artistid => 4, name => 'Manufactured Crap', cds => [
2002 { title => 'My First CD', year => 2006 },
2003 { title => 'Yet More Tweeny-Pop crap', year => 2007 },
2008 Example of creating a new row and also creating a row in a related
2009 C<belongs_to>resultset. Note Hashref.
2012 title=>"Music for Silly Walks",
2015 name=>"Silly Musician",
2022 my ($self, $attrs) = @_;
2023 $self->throw_exception( "create needs a hashref" )
2024 unless ref $attrs eq 'HASH';
2025 return $self->new_result($attrs)->insert;
2028 =head2 find_or_create
2032 =item Arguments: \%vals, \%attrs?
2034 =item Return Value: $rowobject
2038 $cd->cd_to_producer->find_or_create({ producer => $producer },
2039 { key => 'primary });
2041 Tries to find a record based on its primary key or unique constraints; if none
2042 is found, creates one and returns that instead.
2044 my $cd = $schema->resultset('CD')->find_or_create({
2046 artist => 'Massive Attack',
2047 title => 'Mezzanine',
2051 Also takes an optional C<key> attribute, to search by a specific key or unique
2052 constraint. For example:
2054 my $cd = $schema->resultset('CD')->find_or_create(
2056 artist => 'Massive Attack',
2057 title => 'Mezzanine',
2059 { key => 'cd_artist_title' }
2062 B<Note>: Because find_or_create() reads from the database and then
2063 possibly inserts based on the result, this method is subject to a race
2064 condition. Another process could create a record in the table after
2065 the find has completed and before the create has started. To avoid
2066 this problem, use find_or_create() inside a transaction.
2068 B<Note>: C<find_or_create> is probably not what you want when creating
2069 a new row in a table that uses primary keys supplied by the
2070 database. Passing in a primary key column with a value of I<undef>
2071 will cause L</find> to attempt to search for a row with a value of
2074 See also L</find> and L</update_or_create>. For information on how to declare
2075 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
2079 sub find_or_create {
2081 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
2082 my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
2083 my $exists = $self->find($hash, $attrs);
2084 return defined $exists ? $exists : $self->create($hash);
2087 =head2 update_or_create
2091 =item Arguments: \%col_values, { key => $unique_constraint }?
2093 =item Return Value: $rowobject
2097 $resultset->update_or_create({ col => $val, ... });
2099 First, searches for an existing row matching one of the unique constraints
2100 (including the primary key) on the source of this resultset. If a row is
2101 found, updates it with the other given column values. Otherwise, creates a new
2104 Takes an optional C<key> attribute to search on a specific unique constraint.
2107 # In your application
2108 my $cd = $schema->resultset('CD')->update_or_create(
2110 artist => 'Massive Attack',
2111 title => 'Mezzanine',
2114 { key => 'cd_artist_title' }
2117 $cd->cd_to_producer->update_or_create({
2118 producer => $producer,
2125 If no C<key> is specified, it searches on all unique constraints defined on the
2126 source, including the primary key.
2128 If the C<key> is specified as C<primary>, it searches only on the primary key.
2130 See also L</find> and L</find_or_create>. For information on how to declare
2131 unique constraints, see L<DBIx::Class::ResultSource/add_unique_constraint>.
2133 B<Note>: C<update_or_create> is probably not what you want when
2134 looking for a row in a table that uses primary keys supplied by the
2135 database, unless you actually have a key value. Passing in a primary
2136 key column with a value of I<undef> will cause L</find> to attempt to
2137 search for a row with a value of I<NULL>.
2141 sub update_or_create {
2143 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
2144 my $cond = ref $_[0] eq 'HASH' ? shift : {@_};
2146 my $row = $self->find($cond, $attrs);
2148 $row->update($cond);
2152 return $self->create($cond);
2155 =head2 update_or_new
2159 =item Arguments: \%col_values, { key => $unique_constraint }?
2161 =item Return Value: $rowobject
2165 $resultset->update_or_new({ col => $val, ... });
2167 First, searches for an existing row matching one of the unique constraints
2168 (including the primary key) on the source of this resultset. If a row is
2169 found, updates it with the other given column values. Otherwise, instantiate
2170 a new result object and return it. The object will not be saved into your storage
2171 until you call L<DBIx::Class::Row/insert> on it.
2173 Takes an optional C<key> attribute to search on a specific unique constraint.
2176 # In your application
2177 my $cd = $schema->resultset('CD')->update_or_new(
2179 artist => 'Massive Attack',
2180 title => 'Mezzanine',
2183 { key => 'cd_artist_title' }
2186 if ($cd->in_storage) {
2187 # the cd was updated
2190 # the cd is not yet in the database, let's insert it
2194 See also L</find>, L</find_or_create> and L<find_or_new>.
2200 my $attrs = ( @_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {} );
2201 my $cond = ref $_[0] eq 'HASH' ? shift : {@_};
2203 my $row = $self->find( $cond, $attrs );
2204 if ( defined $row ) {
2205 $row->update($cond);
2209 return $self->new_result($cond);
2216 =item Arguments: none
2218 =item Return Value: \@cache_objects?
2222 Gets the contents of the cache for the resultset, if the cache is set.
2224 The cache is populated either by using the L</prefetch> attribute to
2225 L</search> or by calling L</set_cache>.
2237 =item Arguments: \@cache_objects
2239 =item Return Value: \@cache_objects
2243 Sets the contents of the cache for the resultset. Expects an arrayref
2244 of objects of the same class as those produced by the resultset. Note that
2245 if the cache is set the resultset will return the cached objects rather
2246 than re-querying the database even if the cache attr is not set.
2248 The contents of the cache can also be populated by using the
2249 L</prefetch> attribute to L</search>.
2254 my ( $self, $data ) = @_;
2255 $self->throw_exception("set_cache requires an arrayref")
2256 if defined($data) && (ref $data ne 'ARRAY');
2257 $self->{all_cache} = $data;
2264 =item Arguments: none
2266 =item Return Value: []
2270 Clears the cache for the resultset.
2275 shift->set_cache(undef);
2278 =head2 related_resultset
2282 =item Arguments: $relationship_name
2284 =item Return Value: $resultset
2288 Returns a related resultset for the supplied relationship name.
2290 $artist_rs = $schema->resultset('CD')->related_resultset('Artist');
2294 sub related_resultset {
2295 my ($self, $rel) = @_;
2297 $self->{related_resultsets} ||= {};
2298 return $self->{related_resultsets}{$rel} ||= do {
2299 my $rel_obj = $self->result_source->relationship_info($rel);
2301 $self->throw_exception(
2302 "search_related: result source '" . $self->result_source->source_name .
2303 "' has no such relationship $rel")
2306 my ($from,$seen) = $self->_resolve_from($rel);
2308 my $join_count = $seen->{$rel};
2309 my $alias = ($join_count > 1 ? join('_', $rel, $join_count) : $rel);
2311 #XXX - temp fix for result_class bug. There likely is a more elegant fix -groditi
2312 my %attrs = %{$self->{attrs}||{}};
2313 delete @attrs{qw(result_class alias)};
2317 if (my $cache = $self->get_cache) {
2318 if ($cache->[0] && $cache->[0]->related_resultset($rel)->get_cache) {
2319 $new_cache = [ map { @{$_->related_resultset($rel)->get_cache} }
2324 my $rel_source = $self->result_source->related_source($rel);
2328 # The reason we do this now instead of passing the alias to the
2329 # search_rs below is that if you wrap/overload resultset on the
2330 # source you need to know what alias it's -going- to have for things
2331 # to work sanely (e.g. RestrictWithObject wants to be able to add
2332 # extra query restrictions, and these may need to be $alias.)
2334 my $attrs = $rel_source->resultset_attributes;
2335 local $attrs->{alias} = $alias;
2337 $rel_source->resultset
2345 where => $self->{cond},
2350 $new->set_cache($new_cache) if $new_cache;
2355 =head2 current_source_alias
2359 =item Arguments: none
2361 =item Return Value: $source_alias
2365 Returns the current table alias for the result source this resultset is built
2366 on, that will be used in the SQL query. Usually it is C<me>.
2368 Currently the source alias that refers to the result set returned by a
2369 L</search>/L</find> family method depends on how you got to the resultset: it's
2370 C<me> by default, but eg. L</search_related> aliases it to the related result
2371 source name (and keeps C<me> referring to the original result set). The long
2372 term goal is to make L<DBIx::Class> always alias the current resultset as C<me>
2373 (and make this method unnecessary).
2375 Thus it's currently necessary to use this method in predefined queries (see
2376 L<DBIx::Class::Manual::Cookbook/Predefined searches>) when referring to the
2377 source alias of the current result set:
2379 # in a result set class
2381 my ($self, $user) = @_;
2383 my $me = $self->current_source_alias;
2385 return $self->search(
2386 "$me.modified" => $user->id,
2392 sub current_source_alias {
2395 return ($self->{attrs} || {})->{alias} || 'me';
2399 my ($self, $extra_join) = @_;
2400 my $source = $self->result_source;
2401 my $attrs = $self->{attrs};
2403 my $from = $attrs->{from}
2404 || [ { $attrs->{alias} => $source->from } ];
2406 my $seen = { %{$attrs->{seen_join}||{}} };
2408 my $join = ($attrs->{join}
2409 ? [ $attrs->{join}, $extra_join ]
2412 # we need to take the prefetch the attrs into account before we
2413 # ->resolve_join as otherwise they get lost - captainL
2414 my $merged = $self->_merge_attr( $join, $attrs->{prefetch} );
2418 ($join ? $source->resolve_join($merged, $attrs->{alias}, $seen) : ()),
2421 return ($from,$seen);
2424 sub _resolved_attrs {
2426 return $self->{_attrs} if $self->{_attrs};
2428 my $attrs = { %{ $self->{attrs} || {} } };
2429 my $source = $self->result_source;
2430 my $alias = $attrs->{alias};
2432 $attrs->{columns} ||= delete $attrs->{cols} if exists $attrs->{cols};
2435 # build columns (as long as select isn't set) into a set of as/select hashes
2436 unless ( $attrs->{select} ) {
2438 ( ref($_) eq 'HASH' )
2442 /^\Q${alias}.\E(.+)$/
2453 } ( ref($attrs->{columns}) eq 'ARRAY' ) ? @{ delete $attrs->{columns}} : (delete $attrs->{columns} || $source->columns );
2455 # add the additional columns on
2456 foreach ( 'include_columns', '+columns' ) {
2457 push @colbits, map {
2458 ( ref($_) eq 'HASH' )
2460 : { ( split( /\./, $_ ) )[-1] => ( /\./ ? $_ : "${alias}.$_" ) }
2461 } ( ref($attrs->{$_}) eq 'ARRAY' ) ? @{ delete $attrs->{$_} } : delete $attrs->{$_} if ( $attrs->{$_} );
2464 # start with initial select items
2465 if ( $attrs->{select} ) {
2467 ( ref $attrs->{select} eq 'ARRAY' )
2468 ? [ @{ $attrs->{select} } ]
2469 : [ $attrs->{select} ];
2473 ref $attrs->{as} eq 'ARRAY'
2474 ? [ @{ $attrs->{as} } ]
2477 : [ map { m/^\Q${alias}.\E(.+)$/ ? $1 : $_ } @{ $attrs->{select} } ]
2482 # otherwise we intialise select & as to empty
2483 $attrs->{select} = [];
2487 # now add colbits to select/as
2488 push( @{ $attrs->{select} }, map { values( %{$_} ) } @colbits );
2489 push( @{ $attrs->{as} }, map { keys( %{$_} ) } @colbits );
2492 if ( $adds = delete $attrs->{'+select'} ) {
2493 $adds = [$adds] unless ref $adds eq 'ARRAY';
2495 @{ $attrs->{select} },
2496 map { /\./ || ref $_ ? $_ : "${alias}.$_" } @$adds
2499 if ( $adds = delete $attrs->{'+as'} ) {
2500 $adds = [$adds] unless ref $adds eq 'ARRAY';
2501 push( @{ $attrs->{as} }, @$adds );
2504 $attrs->{from} ||= [ { $self->{attrs}{alias} => $source->from } ];
2506 if ( exists $attrs->{join} || exists $attrs->{prefetch} ) {
2507 my $join = delete $attrs->{join} || {};
2509 if ( defined $attrs->{prefetch} ) {
2510 $join = $self->_merge_attr( $join, $attrs->{prefetch} );
2514 $attrs->{from} = # have to copy here to avoid corrupting the original
2516 @{ $attrs->{from} },
2517 $source->resolve_join(
2518 $join, $alias, { %{ $attrs->{seen_join} || {} } }
2524 $attrs->{group_by} ||= $attrs->{select}
2525 if delete $attrs->{distinct};
2526 if ( $attrs->{order_by} ) {
2527 $attrs->{order_by} = (
2528 ref( $attrs->{order_by} ) eq 'ARRAY'
2529 ? [ @{ $attrs->{order_by} } ]
2530 : [ $attrs->{order_by} ]
2534 $attrs->{order_by} = [];
2537 my $collapse = $attrs->{collapse} || {};
2538 if ( my $prefetch = delete $attrs->{prefetch} ) {
2539 $prefetch = $self->_merge_attr( {}, $prefetch );
2541 my $seen = { %{ $attrs->{seen_join} || {} } };
2542 foreach my $p ( ref $prefetch eq 'ARRAY' ? @$prefetch : ($prefetch) ) {
2544 # bring joins back to level of current class
2546 $source->resolve_prefetch( $p, $alias, $seen, \@pre_order, $collapse );
2547 push( @{ $attrs->{select} }, map { $_->[0] } @prefetch );
2548 push( @{ $attrs->{as} }, map { $_->[1] } @prefetch );
2550 push( @{ $attrs->{order_by} }, @pre_order );
2552 $attrs->{collapse} = $collapse;
2554 if ( $attrs->{page} ) {
2555 $attrs->{offset} ||= 0;
2556 $attrs->{offset} += ( $attrs->{rows} * ( $attrs->{page} - 1 ) );
2559 return $self->{_attrs} = $attrs;
2563 my ($self, $attr) = @_;
2565 if (ref $attr eq 'HASH') {
2566 return $self->_rollout_hash($attr);
2567 } elsif (ref $attr eq 'ARRAY') {
2568 return $self->_rollout_array($attr);
2574 sub _rollout_array {
2575 my ($self, $attr) = @_;
2578 foreach my $element (@{$attr}) {
2579 if (ref $element eq 'HASH') {
2580 push( @rolled_array, @{ $self->_rollout_hash( $element ) } );
2581 } elsif (ref $element eq 'ARRAY') {
2582 # XXX - should probably recurse here
2583 push( @rolled_array, @{$self->_rollout_array($element)} );
2585 push( @rolled_array, $element );
2588 return \@rolled_array;
2592 my ($self, $attr) = @_;
2595 foreach my $key (keys %{$attr}) {
2596 push( @rolled_array, { $key => $attr->{$key} } );
2598 return \@rolled_array;
2601 sub _calculate_score {
2602 my ($self, $a, $b) = @_;
2604 if (ref $b eq 'HASH') {
2605 my ($b_key) = keys %{$b};
2606 if (ref $a eq 'HASH') {
2607 my ($a_key) = keys %{$a};
2608 if ($a_key eq $b_key) {
2609 return (1 + $self->_calculate_score( $a->{$a_key}, $b->{$b_key} ));
2614 return ($a eq $b_key) ? 1 : 0;
2617 if (ref $a eq 'HASH') {
2618 my ($a_key) = keys %{$a};
2619 return ($b eq $a_key) ? 1 : 0;
2621 return ($b eq $a) ? 1 : 0;
2627 my ($self, $orig, $import) = @_;
2629 return $import unless defined($orig);
2630 return $orig unless defined($import);
2632 $orig = $self->_rollout_attr($orig);
2633 $import = $self->_rollout_attr($import);
2636 foreach my $import_element ( @{$import} ) {
2637 # find best candidate from $orig to merge $b_element into
2638 my $best_candidate = { position => undef, score => 0 }; my $position = 0;
2639 foreach my $orig_element ( @{$orig} ) {
2640 my $score = $self->_calculate_score( $orig_element, $import_element );
2641 if ($score > $best_candidate->{score}) {
2642 $best_candidate->{position} = $position;
2643 $best_candidate->{score} = $score;
2647 my ($import_key) = ( ref $import_element eq 'HASH' ) ? keys %{$import_element} : ($import_element);
2649 if ($best_candidate->{score} == 0 || exists $seen_keys->{$import_key}) {
2650 push( @{$orig}, $import_element );
2652 my $orig_best = $orig->[$best_candidate->{position}];
2653 # merge orig_best and b_element together and replace original with merged
2654 if (ref $orig_best ne 'HASH') {
2655 $orig->[$best_candidate->{position}] = $import_element;
2656 } elsif (ref $import_element eq 'HASH') {
2657 my ($key) = keys %{$orig_best};
2658 $orig->[$best_candidate->{position}] = { $key => $self->_merge_attr($orig_best->{$key}, $import_element->{$key}) };
2661 $seen_keys->{$import_key} = 1; # don't merge the same key twice
2671 $self->_source_handle($_[0]->handle);
2673 $self->_source_handle->resolve;
2677 =head2 throw_exception
2679 See L<DBIx::Class::Schema/throw_exception> for details.
2683 sub throw_exception {
2685 if (ref $self && $self->_source_handle->schema) {
2686 $self->_source_handle->schema->throw_exception(@_)
2693 # XXX: FIXME: Attributes docs need clearing up
2697 Attributes are used to refine a ResultSet in various ways when
2698 searching for data. They can be passed to any method which takes an
2699 C<\%attrs> argument. See L</search>, L</search_rs>, L</find>,
2702 These are in no particular order:
2708 =item Value: ( $order_by | \@order_by | \%order_by )
2712 Which column(s) to order the results by. If a single column name, or
2713 an arrayref of names is supplied, the argument is passed through
2714 directly to SQL. The hashref syntax allows for connection-agnostic
2715 specification of ordering direction:
2717 For descending order:
2719 order_by => { -desc => [qw/col1 col2 col3/] }
2721 For explicit ascending order:
2723 order_by => { -asc => 'col' }
2725 The old scalarref syntax (i.e. order_by => \'year DESC') is still
2726 supported, although you are strongly encouraged to use the hashref
2727 syntax as outlined above.
2733 =item Value: \@columns
2737 Shortcut to request a particular set of columns to be retrieved. Each
2738 column spec may be a string (a table column name), or a hash (in which
2739 case the key is the C<as> value, and the value is used as the C<select>
2740 expression). Adds C<me.> onto the start of any column without a C<.> in
2741 it and sets C<select> from that, then auto-populates C<as> from
2742 C<select> as normal. (You may also use the C<cols> attribute, as in
2743 earlier versions of DBIC.)
2749 =item Value: \@columns
2753 Indicates additional columns to be selected from storage. Works the same
2754 as L</columns> but adds columns to the selection. (You may also use the
2755 C<include_columns> attribute, as in earlier versions of DBIC). For
2758 $schema->resultset('CD')->search(undef, {
2759 '+columns' => ['artist.name'],
2763 would return all CDs and include a 'name' column to the information
2764 passed to object inflation. Note that the 'artist' is the name of the
2765 column (or relationship) accessor, and 'name' is the name of the column
2766 accessor in the related table.
2768 =head2 include_columns
2772 =item Value: \@columns
2776 Deprecated. Acts as a synonym for L</+columns> for backward compatibility.
2782 =item Value: \@select_columns
2786 Indicates which columns should be selected from the storage. You can use
2787 column names, or in the case of RDBMS back ends, function or stored procedure
2790 $rs = $schema->resultset('Employee')->search(undef, {
2793 { count => 'employeeid' },
2798 When you use function/stored procedure names and do not supply an C<as>
2799 attribute, the column names returned are storage-dependent. E.g. MySQL would
2800 return a column named C<count(employeeid)> in the above example.
2806 Indicates additional columns to be selected from storage. Works the same as
2807 L</select> but adds columns to the selection.
2815 Indicates additional column names for those added via L</+select>. See L</as>.
2823 =item Value: \@inflation_names
2827 Indicates column names for object inflation. That is, C<as>
2828 indicates the name that the column can be accessed as via the
2829 C<get_column> method (or via the object accessor, B<if one already
2830 exists>). It has nothing to do with the SQL code C<SELECT foo AS bar>.
2832 The C<as> attribute is used in conjunction with C<select>,
2833 usually when C<select> contains one or more function or stored
2836 $rs = $schema->resultset('Employee')->search(undef, {
2839 { count => 'employeeid' }
2841 as => ['name', 'employee_count'],
2844 my $employee = $rs->first(); # get the first Employee
2846 If the object against which the search is performed already has an accessor
2847 matching a column name specified in C<as>, the value can be retrieved using
2848 the accessor as normal:
2850 my $name = $employee->name();
2852 If on the other hand an accessor does not exist in the object, you need to
2853 use C<get_column> instead:
2855 my $employee_count = $employee->get_column('employee_count');
2857 You can create your own accessors if required - see
2858 L<DBIx::Class::Manual::Cookbook> for details.
2860 Please note: This will NOT insert an C<AS employee_count> into the SQL
2861 statement produced, it is used for internal access only. Thus
2862 attempting to use the accessor in an C<order_by> clause or similar
2863 will fail miserably.
2865 To get around this limitation, you can supply literal SQL to your
2866 C<select> attibute that contains the C<AS alias> text, eg:
2868 select => [\'myfield AS alias']
2874 =item Value: ($rel_name | \@rel_names | \%rel_names)
2878 Contains a list of relationships that should be joined for this query. For
2881 # Get CDs by Nine Inch Nails
2882 my $rs = $schema->resultset('CD')->search(
2883 { 'artist.name' => 'Nine Inch Nails' },
2884 { join => 'artist' }
2887 Can also contain a hash reference to refer to the other relation's relations.
2890 package MyApp::Schema::Track;
2891 use base qw/DBIx::Class/;
2892 __PACKAGE__->table('track');
2893 __PACKAGE__->add_columns(qw/trackid cd position title/);
2894 __PACKAGE__->set_primary_key('trackid');
2895 __PACKAGE__->belongs_to(cd => 'MyApp::Schema::CD');
2898 # In your application
2899 my $rs = $schema->resultset('Artist')->search(
2900 { 'track.title' => 'Teardrop' },
2902 join => { cd => 'track' },
2903 order_by => 'artist.name',
2907 You need to use the relationship (not the table) name in conditions,
2908 because they are aliased as such. The current table is aliased as "me", so
2909 you need to use me.column_name in order to avoid ambiguity. For example:
2911 # Get CDs from 1984 with a 'Foo' track
2912 my $rs = $schema->resultset('CD')->search(
2915 'tracks.name' => 'Foo'
2917 { join => 'tracks' }
2920 If the same join is supplied twice, it will be aliased to <rel>_2 (and
2921 similarly for a third time). For e.g.
2923 my $rs = $schema->resultset('Artist')->search({
2924 'cds.title' => 'Down to Earth',
2925 'cds_2.title' => 'Popular',
2927 join => [ qw/cds cds/ ],
2930 will return a set of all artists that have both a cd with title 'Down
2931 to Earth' and a cd with title 'Popular'.
2933 If you want to fetch related objects from other tables as well, see C<prefetch>
2936 For more help on using joins with search, see L<DBIx::Class::Manual::Joining>.
2942 =item Value: ($rel_name | \@rel_names | \%rel_names)
2946 Contains one or more relationships that should be fetched along with
2947 the main query (when they are accessed afterwards the data will
2948 already be available, without extra queries to the database). This is
2949 useful for when you know you will need the related objects, because it
2950 saves at least one query:
2952 my $rs = $schema->resultset('Tag')->search(
2961 The initial search results in SQL like the following:
2963 SELECT tag.*, cd.*, artist.* FROM tag
2964 JOIN cd ON tag.cd = cd.cdid
2965 JOIN artist ON cd.artist = artist.artistid
2967 L<DBIx::Class> has no need to go back to the database when we access the
2968 C<cd> or C<artist> relationships, which saves us two SQL statements in this
2971 Simple prefetches will be joined automatically, so there is no need
2972 for a C<join> attribute in the above search.
2974 C<prefetch> can be used with the following relationship types: C<belongs_to>,
2975 C<has_one> (or if you're using C<add_relationship>, any relationship declared
2976 with an accessor type of 'single' or 'filter'). A more complex example that
2977 prefetches an artists cds, the tracks on those cds, and the tags associted
2978 with that artist is given below (assuming many-to-many from artists to tags):
2980 my $rs = $schema->resultset('Artist')->search(
2984 { cds => 'tracks' },
2985 { artist_tags => 'tags' }
2991 B<NOTE:> If you specify a C<prefetch> attribute, the C<join> and C<select>
2992 attributes will be ignored.
3002 Makes the resultset paged and specifies the page to retrieve. Effectively
3003 identical to creating a non-pages resultset and then calling ->page($page)
3006 If L<rows> attribute is not specified it defualts to 10 rows per page.
3008 When you have a paged resultset, L</count> will only return the number
3009 of rows in the page. To get the total, use the L</pager> and call
3010 C<total_entries> on it.
3020 Specifes the maximum number of rows for direct retrieval or the number of
3021 rows per page if the page attribute or method is used.
3027 =item Value: $offset
3031 Specifies the (zero-based) row number for the first row to be returned, or the
3032 of the first row of the first page if paging is used.
3038 =item Value: \@columns
3042 A arrayref of columns to group by. Can include columns of joined tables.
3044 group_by => [qw/ column1 column2 ... /]
3050 =item Value: $condition
3054 HAVING is a select statement attribute that is applied between GROUP BY and
3055 ORDER BY. It is applied to the after the grouping calculations have been
3058 having => { 'count(employee)' => { '>=', 100 } }
3064 =item Value: (0 | 1)
3068 Set to 1 to group by all columns.
3074 Adds to the WHERE clause.
3076 # only return rows WHERE deleted IS NULL for all searches
3077 __PACKAGE__->resultset_attributes({ where => { deleted => undef } }); )
3079 Can be overridden by passing C<{ where => undef }> as an attribute
3086 Set to 1 to cache search results. This prevents extra SQL queries if you
3087 revisit rows in your ResultSet:
3089 my $resultset = $schema->resultset('Artist')->search( undef, { cache => 1 } );
3091 while( my $artist = $resultset->next ) {
3095 $rs->first; # without cache, this would issue a query
3097 By default, searches are not cached.
3099 For more examples of using these attributes, see
3100 L<DBIx::Class::Manual::Cookbook>.
3106 =item Value: \@from_clause
3110 The C<from> attribute gives you manual control over the C<FROM> clause of SQL
3111 statements generated by L<DBIx::Class>, allowing you to express custom C<JOIN>
3114 NOTE: Use this on your own risk. This allows you to shoot off your foot!
3116 C<join> will usually do what you need and it is strongly recommended that you
3117 avoid using C<from> unless you cannot achieve the desired result using C<join>.
3118 And we really do mean "cannot", not just tried and failed. Attempting to use
3119 this because you're having problems with C<join> is like trying to use x86
3120 ASM because you've got a syntax error in your C. Trust us on this.
3122 Now, if you're still really, really sure you need to use this (and if you're
3123 not 100% sure, ask the mailing list first), here's an explanation of how this
3126 The syntax is as follows -
3129 { <alias1> => <table1> },
3131 { <alias2> => <table2>, -join_type => 'inner|left|right' },
3132 [], # nested JOIN (optional)
3133 { <table1.column1> => <table2.column2>, ... (more conditions) },
3135 # More of the above [ ] may follow for additional joins
3142 ON <table1.column1> = <table2.column2>
3143 <more joins may follow>
3145 An easy way to follow the examples below is to remember the following:
3147 Anything inside "[]" is a JOIN
3148 Anything inside "{}" is a condition for the enclosing JOIN
3150 The following examples utilize a "person" table in a family tree application.
3151 In order to express parent->child relationships, this table is self-joined:
3153 # Person->belongs_to('father' => 'Person');
3154 # Person->belongs_to('mother' => 'Person');
3156 C<from> can be used to nest joins. Here we return all children with a father,
3157 then search against all mothers of those children:
3159 $rs = $schema->resultset('Person')->search(
3162 alias => 'mother', # alias columns in accordance with "from"
3164 { mother => 'person' },
3167 { child => 'person' },
3169 { father => 'person' },
3170 { 'father.person_id' => 'child.father_id' }
3173 { 'mother.person_id' => 'child.mother_id' }
3180 # SELECT mother.* FROM person mother
3183 # JOIN person father
3184 # ON ( father.person_id = child.father_id )
3186 # ON ( mother.person_id = child.mother_id )
3188 The type of any join can be controlled manually. To search against only people
3189 with a father in the person table, we could explicitly use C<INNER JOIN>:
3191 $rs = $schema->resultset('Person')->search(
3194 alias => 'child', # alias columns in accordance with "from"
3196 { child => 'person' },
3198 { father => 'person', -join_type => 'inner' },
3199 { 'father.id' => 'child.father_id' }
3206 # SELECT child.* FROM person child
3207 # INNER JOIN person father ON child.father_id = father.id
3209 If you need to express really complex joins or you need a subselect, you
3210 can supply literal SQL to C<from> via a scalar reference. In this case
3211 the contents of the scalar will replace the table name asscoiated with the
3214 WARNING: This technique might very well not work as expected on chained
3215 searches - you have been warned.
3217 # Assuming the Event resultsource is defined as:
3219 MySchema::Event->add_columns (
3222 is_auto_increment => 1,
3231 MySchema::Event->set_primary_key ('sequence');
3233 # This will get back the latest event for every location. The column
3234 # selector is still provided by DBIC, all we do is add a JOIN/WHERE
3235 # combo to limit the resultset
3237 $rs = $schema->resultset('Event');
3238 $table = $rs->result_source->name;
3239 $latest = $rs->search (
3242 (SELECT e1.* FROM $table e1
3244 ON e1.location = e2.location
3245 AND e1.sequence < e2.sequence
3246 WHERE e2.sequence is NULL
3251 # Equivalent SQL (with the DBIC chunks added):
3253 SELECT me.sequence, me.location, me.type FROM
3254 (SELECT e1.* FROM events e1
3256 ON e1.location = e2.location
3257 AND e1.sequence < e2.sequence
3258 WHERE e2.sequence is NULL
3265 =item Value: ( 'update' | 'shared' )
3269 Set to 'update' for a SELECT ... FOR UPDATE or 'shared' for a SELECT