=head1 SYNOPSIS
+ __PACKAGE__->add_relationship(
+ spiders => 'My::DB::Result::Creatures',
+ sub {
+ my $args = shift;
+ return {
+ "$args->{foreign_alias}.id" => { -ident => "$args->{self_alias}.id" },
+ "$args->{foreign_alias}.type" => 'arachnid'
+ };
+ },
+ );
+
=head1 DESCRIPTION
This class provides methods to describe the relationships between the
=over 4
-=item Arguments: 'relname', 'Foreign::Class', $cond, $attrs
+=item Arguments: 'relname', 'Foreign::Class', $condition, $attrs
=back
- __PACKAGE__->add_relationship('relname', 'Foreign::Class', $cond, $attrs);
+ __PACKAGE__->add_relationship('relname',
+ 'Foreign::Class',
+ $condition, $attrs);
+
+Create a custom relationship between one result source and another
+source, indicated by its class name.
=head3 condition
-The condition needs to be an L<SQL::Abstract>-style representation of the
-join between the tables. When resolving the condition for use in a C<JOIN>,
-keys using the pseudo-table C<foreign> are resolved to mean "the Table on the
-other side of the relationship", and values using the pseudo-table C<self>
-are resolved to mean "the Table this class is representing". Other
-restrictions, such as by value, sub-select and other tables, may also be
-used. Please check your database for C<JOIN> parameter support.
+The condition argument describes the C<ON> clause of the C<JOIN>
+expression used to connect the two sources when creating SQL queries.
+
+To create simple equality joins, supply a hashref containing the
+remote table column name as the key(s), and the local table column
+name as the value(s), for example given:
+
+ My::Schema::Author->has_many(
+ books => 'My::Schema::Book',
+ { 'foreign.author_id' => 'self.id' }
+ );
+
+A query like:
+
+ $author_rs->search_related('books')->next
+
+will result in the following C<JOIN> clause:
+
+ ... FROM author me LEFT JOIN book books ON books.author_id = me.id ...
+
+This describes a relationship between the C<Author> table and the
+C<Book> table where the C<Book> table has a column C<author_id>
+containing the ID value of the C<Author>.
+
+C<foreign> and C<self> are pseudo aliases and must be entered
+literally. They will be replaced with the actual correct table alias
+when the SQL is produced.
+
+Similarly:
+
+ My::Schema::Book->has_many(
+ editions => 'My::Schema::Edition',
+ {
+ 'foreign.publisher_id' => 'self.publisher_id',
+ 'foreign.type_id' => 'self.type_id',
+ }
+ );
+
+ ...
+
+ $book_rs->search_related('editions')->next
+
+will result in the C<JOIN> clause:
+
+ ... FROM book me
+ LEFT JOIN edition editions ON
+ editions.publisher_id = me.publisher_id
+ AND editions.type_id = me.type_id ...
+
+This describes the relationship from C<Book> to C<Edition>, where the
+C<Edition> table refers to a publisher and a type (e.g. "paperback"):
+
+As is the default in L<SQL::Abstract>, the key-value pairs will be
+C<AND>ed in the result. C<OR> can be achieved with an arrayref, for
+example a condition like:
+
+ My::Schema::Item->has_many(
+ related_item_links => My::Schema::Item::Links,
+ [
+ { 'foreign.left_itemid' => 'self.id' },
+ { 'foreign.right_itemid' => 'self.id' },
+ ],
+ );
+
+will translate to the following C<JOIN> clause:
+
+ ... FROM item me JOIN item_relations related_item_links ON
+ related_item_links.left_itemid = me.id
+ OR related_item_links.right_itemid = me.id ...
+
+This describes the relationship from C<Item> to C<Item::Links>, where
+C<Item::Links> is a many-to-many linking table, linking items back to
+themselves in a peer fashion (without a "parent-child" designation)
+
+To specify joins which describe more than a simple equality of column
+values, the custom join condition coderef syntax can be used. For
+example:
+
+ My::Schema::Artist->has_many(
+ cds_80s => 'My::Schema::CD',
+ sub {
+ my $args = shift;
+
+ return {
+ "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
+ "$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
+ };
+ }
+ );
+
+ ...
+
+ $artist_rs->search_related('cds_80s')->next;
+
+will result in the C<JOIN> clause:
-For example, if you're creating a relationship from C<Author> to C<Book>, where
-the C<Book> table has a column C<author_id> containing the ID of the C<Author>
-row:
+ ... FROM artist me LEFT JOIN cd cds_80s ON
+ cds_80s.artist = me.artistid
+ AND cds_80s.year < ?
+ AND cds_80s.year > ?
- { 'foreign.author_id' => 'self.id' }
+with the bind values:
-will result in the C<JOIN> clause
+ '1990', '1979'
- author me JOIN book book ON book.author_id = me.id
+C<< $args->{foreign_alias} >> and C<< $args->{self_alias} >> are supplied the
+same values that would be otherwise substituted for C<foreign> and C<self>
+in the simple hashref syntax case.
-For multi-column foreign keys, you will need to specify a C<foreign>-to-C<self>
-mapping for each column in the key. For example, if you're creating a
-relationship from C<Book> to C<Edition>, where the C<Edition> table refers to a
-publisher and a type (e.g. "paperback"):
+The coderef is expected to return a valid L<SQL::Abstract> query-structure, just
+like what one would supply as the first argument to
+L<DBIx::Class::ResultSet/search>. The return value will be passed directly to
+L<SQL::Abstract> and the resulting SQL will be used verbatim as the C<ON>
+clause of the C<JOIN> statement associated with this relationship.
- {
- 'foreign.publisher_id' => 'self.publisher_id',
- 'foreign.type_id' => 'self.type_id',
+While every coderef-based condition must return a valid C<ON> clause, it may
+elect to additionally return a simplified join-free condition hashref when
+invoked as C<< $row_object->relationship >>, as opposed to
+C<< $rs->related_resultset('relationship') >>. In this case C<$row_object> is
+passed to the coderef as C<< $args->{self_rowobj} >>, so a user can do the
+following:
+
+ sub {
+ my $args = shift;
+
+ return (
+ {
+ "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
+ "$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
+ },
+ $args->{self_rowobj} && {
+ "$args->{foreign_alias}.artist" => $args->{self_rowobj}->artistid,
+ "$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
+ },
+ );
}
-This will result in the C<JOIN> clause:
+Now this code:
+
+ my $artist = $schema->resultset("Artist")->find({ id => 4 });
+ $artist->cds_80s->all;
+
+Can skip a C<JOIN> altogether and instead produce:
+
+ SELECT cds_80s.cdid, cds_80s.artist, cds_80s.title, cds_80s.year, cds_80s.genreid, cds_80s.single_track
+ FROM cd cds_80s
+ WHERE cds_80s.artist = ?
+ AND cds_80s.year < ?
+ AND cds_80s.year > ?
+
+With the bind values:
+
+ '4', '1990', '1979'
- book me JOIN edition edition ON edition.publisher_id = me.publisher_id
- AND edition.type_id = me.type_id
+Note that in order to be able to use
+L<< $row->create_related|DBIx::Class::Relationship::Base/create_related >>,
+the coderef must not only return as its second such a "simple" condition
+hashref which does not depend on joins being available, but the hashref must
+contain only plain values/deflatable objects, such that the result can be
+passed directly to L<DBIx::Class::Relationship::Base/set_from_related>. For
+instance the C<year> constraint in the above example prevents the relationship
+from being used to to create related objects (an exception will be thrown).
-Each key-value pair provided in a hashref will be used as C<AND>ed conditions.
-To add an C<OR>ed condition, use an arrayref of hashrefs. See the
-L<SQL::Abstract> documentation for more details.
+In order to allow the user to go truly crazy when generating a custom C<ON>
+clause, the C<$args> hashref passed to the subroutine contains some extra
+metadata. Currently the supplied coderef is executed as:
+
+ $relationship_info->{cond}->({
+ self_alias => The alias of the invoking resultset ('me' in case of a row object),
+ foreign_alias => The alias of the to-be-joined resultset (often matches relname),
+ self_resultsource => The invocant's resultsource,
+ foreign_relname => The relationship name (does *not* always match foreign_alias),
+ self_rowobj => The invocant itself in case of $row_obj->relationship
+ });
=head3 attributes
# additional condition in order to avoid an unecessary join if
# that is at all possible.
my ($cond, $extended_cond) = try {
- $source->_resolve_condition( $rel_info->{cond}, $rel, $self )
+ $source->_resolve_condition( $rel_info->{cond}, $rel, $self, $rel )
}
catch {
if ($self->in_storage) {
}
}
- # this is where we're going to check if we have an extended
- # rel. In that case, we need to: 1) If there's a second
- # condition, we use that instead. 2) If there is only one
- # condition, we need to join the current resultsource and have
- # additional conditions.
- if (ref $rel_info->{cond} eq 'CODE') {
- # this is an extended relationship.
- if ($extended_cond) {
+ if (ref $rel_info->{cond} eq 'CODE' && !$extended_cond) {
+ # since we don't have the extended condition, we need to step
+ # back, get a resultset for the current row and do a
+ # search_related there.
+
+ my $row_srcname = $source->source_name;
+ my $base_rs_class = $source->resultset_class;
+ my $base_rs_attr = $source->resultset_attributes;
+ my $base_rs = $base_rs_class->new
+ ($source,
+ {
+ %$base_rs_attr,
+ alias => $source->storage->relname_to_table_alias(lc($row_srcname).'__row',1)
+ });
+ my $alias = $base_rs->current_source_alias;
+ my %identity = map { ( "${alias}.${_}" => $self->get_column($_) ) } $source->primary_columns;
+ my $row_rs = $base_rs->search(\%identity);
+ my $related = $row_rs->related_resultset($rel, { %$attrs, alias => 'me' });
+ $related->search($query);
+
+ } else {
+ # when we have the extended condition or we have a simple
+ # relationship declaration, it can optimize the JOIN away by
+ # simply adding the identity in WHERE.
+
+ if (ref $rel_info->{cond} eq 'CODE' && $extended_cond) {
$cond = $extended_cond;
-
- } else {
-
- # it's a bit hard to find out what to do with other joins
- $self->throw_exception('Extended relationship '.$rel.' with additional join requires optimized declaration')
- if exists $attrs->{join} && $attrs->{join};
-
- # aliases get a bit more complicated, so we won't accept additional queries
- $self->throw_exception('Extended relationship '.$rel.' with additional query requires optimized declaration')
- if $query;
-
- $attrs->{from} =
- [ { $rel => $self->result_source->from },
- [ { 'me' => $self->result_source->related_source($rel)->from }, { 1 => 1 } ] ];
-
- $cond->{"${rel}.${_}"} = $self->get_column($_) for $self->result_source->primary_columns;
}
- }
- if (ref $cond eq 'ARRAY') {
- $cond = [ map {
- if (ref $_ eq 'HASH') {
- my $hash;
- foreach my $key (keys %$_) {
- my $newkey = $key !~ /\./ ? "me.$key" : $key;
- $hash->{$newkey} = $_->{$key};
+ if (ref $cond eq 'ARRAY') {
+ $cond = [ map {
+ if (ref $_ eq 'HASH') {
+ my $hash;
+ foreach my $key (keys %$_) {
+ my $newkey = $key !~ /\./ ? "me.$key" : $key;
+ $hash->{$newkey} = $_->{$key};
+ }
+ $hash;
+ } else {
+ $_;
}
- $hash;
- } else {
- $_;
+ } @$cond ];
+ } elsif (ref $cond eq 'HASH') {
+ foreach my $key (grep { ! /\./ } keys %$cond) {
+ $cond->{"me.$key"} = delete $cond->{$key};
}
- } @$cond ];
- } elsif (ref $cond eq 'HASH') {
- foreach my $key (grep { ! /\./ } keys %$cond) {
- $cond->{"me.$key"} = delete $cond->{$key};
}
- }
- $query = ($query ? { '-and' => [ $cond, $query ] } : $cond);
- $self->result_source->related_source($rel)->resultset->search(
- $query, $attrs
- );
+ $query = ($query ? { '-and' => [ $cond, $query ] } : $cond);
+ $self->result_source->related_source($rel)->resultset->search(
+ $query, $attrs);
+ }
};
}
sub create_related {
my $self = shift;
my $rel = shift;
+
+ $self->throw_exception("Can't call *_related as class methods")
+ unless ref $self;
+
+ # we need to stop and check if this is at all possible. If this is
+ # an extended relationship with an incomplete definition, we should
+ # just forbid it right now.
+ my $rel_info = $self->result_source->relationship_info($rel);
+ if (ref $rel_info->{cond} eq 'CODE') {
+ my ($cond, $ext) = $rel_info->{cond}->({
+ self_alias => 'me',
+ foreign_alias => $rel,
+ self_rowobj => $self,
+ self_resultsource => $self->result_source,
+ foreign_relname => $rel,
+ });
+ $self->throw_exception("unable to set_from_related - no simplified condition available for '${rel}'")
+ unless $ext;
+
+ # now we need to make sure all non-identity relationship
+ # definitions are overriden.
+ my ($argref) = @_;
+ while ( my($col, $value) = each %$ext ) {
+ $col =~ s/^$rel\.//;
+ my $vref = ref $value;
+ if ($vref eq 'HASH') {
+ if (keys(%$value) && (keys %$value)[0] ne '=' &&
+ !exists $argref->{$col}) {
+ $self->throw_exception("unable to set_from_related via complex '${rel}' condition on column(s): '${col}'")
+ }
+ }
+ }
+ }
+
my $obj = $self->search_related($rel)->create(@_);
delete $self->{related_resultsets}->{$rel};
return $obj;
# _resolve_condition might return two hashrefs, specially in the
# current case, since we know $f_object is an object.
my ($condref1, $condref2) = $self->result_source->_resolve_condition
- ($rel_info->{cond}, $f_obj, $rel);
+ ($rel_info->{cond}, $f_obj, $rel, $rel);
# if we get two condrefs, we need to use the second, otherwise we
# use the first.
projects / dbsrgits/DBIx-Class.git / blobdiff