1 package DBIx::Class::Relationship::Base;
6 use base qw/DBIx::Class/;
8 use Scalar::Util qw/weaken blessed/;
14 DBIx::Class::Relationship::Base - Inter-table relationships
18 __PACKAGE__->add_relationship('spiders',
19 'My::DB::Result::Creatures',
21 my ( $me_alias, $rel_alias) = @_;
23 { "${rel_alias}.id" => { '=' => \"${me_alias}.id"},
24 "${rel_alias}.type" => { '=', "arachnid" },
31 This class provides methods to describe the relationships between the
32 tables in your database model. These are the "bare bones" relationships
33 methods, for predefined ones, look in L<DBIx::Class::Relationship>.
37 =head2 add_relationship
41 =item Arguments: 'relname', 'Foreign::Class', $condition, $attrs
45 __PACKAGE__->add_relationship('relname',
49 Create a custom relationship between one result source and another
50 source, indicated by its class name.
54 The condition argument describes the JOIN expression used to connect
55 the two sources when creating SQL queries.
57 To create simple equality joins, supply a hashref containing the
58 remote table column name as the key(s), and the local table column
59 name as the value(s), for example:
61 { 'foreign.author_id' => 'self.id' }
63 will result in the C<JOIN> clause:
65 author me JOIN book book ON book.author_id = me.id
67 This describes a relationship between the C<Author> table and the
68 C<Book> table where the C<Book> table has a column C<author_id>
69 containing the ID value of the C<Author>.
71 C<foreign> and C<self> are psuedo aliases and must be entered
72 literally. They will be replaced with the actual correct table alias
73 when the SQL is produced.
78 'foreign.publisher_id' => 'self.publisher_id',
79 'foreign.type_id' => 'self.type_id',
82 will result in the C<JOIN> clause:
84 book me JOIN edition edition ON edition.publisher_id = me.publisher_id
85 AND edition.type_id = me.type_id
87 This describes the relationship from C<Book> to C<Edition>, where the
88 C<Edition> table refers to a publisher and a type (e.g. "paperback"):
90 As is the default in L<SQL::Abstract>, the key-value pairs will be
91 C<AND>ed in the result. C<OR> can be achieved with an arrayref, for
95 { 'foreign.left_itemid' => 'self.id' },
96 { 'foreign.right_itemid' => 'self.id' },
99 which results in the C<JOIN> clause:
101 items me JOIN related_items rel_link ON rel_link.left_itemid = me.id
102 OR rel_link.right_itemid = me.id
104 This describes the relationship from C<Items> to C<RelatedItems>,
105 where C<RelatedItems> is a many-to-many linking table, linking Items
108 To create joins which describe more than a simple equality of column
109 values, the custom join condition coderef syntax can be used:
112 my ( $me_alias, $rel_alias ) = @_;
114 ({ "${rel_alias}.artist" => { '=' => \"${me_alias}.artistid"},
115 "${rel_alias}.year" => { '>', "1979",
120 this will result in the C<JOIN> clause:
122 artist me LEFT JOIN cd cds_80s_noopt ON
123 ( cds_80s_noopt.artist = me.artistid
124 AND ( cds_80s_noopt.year < ? AND cds_80s_noopt.year > ? )
127 with the bind values:
131 C<$rel_alias> is the equivalent to C<foreign> in the simple syntax,
132 and will be replaced by the actual remote table alias in the produced
133 SQL. Similarly, C<$me_alias> is the equivalent to C<self> and will be
134 replaced with the local table alias in the SQL.
136 The actual syntax returned by the coderef should be valid
137 L<SQL::Abstract> syntax, similar to normal
138 L<DBIx::Class::ResultSet/search> conditions.
140 To help optimise the SQL produced, a second optional hashref can be
141 returned to be used when the relationship accessor is called directly
145 my ( $me_alias, $rel_alias, $me_result_source,
146 $rel_name, $optional_me_object ) = @_;
148 ({ "${rel_alias}.artist" => { '=' => \"${me_alias}.artistid"},
149 "${rel_alias}.year" => { '>', "1979",
152 $optional_me_object &&
153 { "${rel_alias}.artist" => $optional_me_object->artistid,
154 "${rel_alias}.year" => { '>', "1979",
161 my $artist = $schema->resultset("Artist")->find({ id => 4 });
162 $artist->cds_80s->all;
166 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
168 WHERE ( ( me.artist = ? AND ( me.year < ? AND me.year > ? ) ) )
170 With the bind values:
174 The C<$optional_me_object> used to create the second hashref contains
175 a row object, the object that the relation accessor was called on.
177 C<$me_result_source> the L<DBIx::Class::ResultSource> of the table
178 being searched on, and C<$rel_name>, the name of the relation
179 containing this condition, are also provided as arguments. These may
180 be useful to more complicated condition calculation.
184 The L<standard ResultSet attributes|DBIx::Class::ResultSet/ATTRIBUTES> may
185 be used as relationship attributes. In particular, the 'where' attribute is
186 useful for filtering relationships:
188 __PACKAGE__->has_many( 'valid_users', 'MyApp::Schema::User',
189 { 'foreign.user_id' => 'self.user_id' },
190 { where => { valid => 1 } }
193 The following attributes are also valid:
199 Explicitly specifies the type of join to use in the relationship. Any SQL
200 join type is valid, e.g. C<LEFT> or C<RIGHT>. It will be placed in the SQL
201 command immediately before C<JOIN>.
203 =item proxy =E<gt> $column | \@columns | \%column
209 An arrayref containing a list of accessors in the foreign class to create in
210 the main class. If, for example, you do the following:
212 MyDB::Schema::CD->might_have(liner_notes => 'MyDB::Schema::LinerNotes',
214 proxy => [ qw/notes/ ],
217 Then, assuming MyDB::Schema::LinerNotes has an accessor named notes, you can do:
219 my $cd = MyDB::Schema::CD->find(1);
220 $cd->notes('Notes go here'); # set notes -- LinerNotes object is
221 # created if it doesn't exist
225 A hashref where each key is the accessor you want installed in the main class,
226 and its value is the name of the original in the fireign class.
228 MyDB::Schema::Track->belongs_to( cd => 'DBICTest::Schema::CD', 'cd', {
229 proxy => { cd_title => 'title' },
232 This will create an accessor named C<cd_title> on the C<$track> row object.
236 NOTE: you can pass a nested struct too, for example:
238 MyDB::Schema::Track->belongs_to( cd => 'DBICTest::Schema::CD', 'cd', {
239 proxy => [ 'year', { cd_title => 'title' } ],
244 Specifies the type of accessor that should be created for the relationship.
245 Valid values are C<single> (for when there is only a single related object),
246 C<multi> (when there can be many), and C<filter> (for when there is a single
247 related object, but you also want the relationship accessor to double as
248 a column accessor). For C<multi> accessors, an add_to_* method is also
249 created, which calls C<create_related> for the relationship.
251 =item is_foreign_key_constraint
253 If you are using L<SQL::Translator> to create SQL for you and you find that it
254 is creating constraints where it shouldn't, or not creating them where it
255 should, set this attribute to a true or false value to override the detection
256 of when to create constraints.
260 If C<cascade_copy> is true on a C<has_many> relationship for an
261 object, then when you copy the object all the related objects will
262 be copied too. To turn this behaviour off, pass C<< cascade_copy => 0 >>
263 in the C<$attr> hashref.
265 The behaviour defaults to C<< cascade_copy => 1 >> for C<has_many>
270 By default, DBIx::Class cascades deletes across C<has_many>,
271 C<has_one> and C<might_have> relationships. You can disable this
272 behaviour on a per-relationship basis by supplying
273 C<< cascade_delete => 0 >> in the relationship attributes.
275 The cascaded operations are performed after the requested delete,
276 so if your database has a constraint on the relationship, it will
277 have deleted/updated the related records or raised an exception
278 before DBIx::Class gets to perform the cascaded operation.
282 By default, DBIx::Class cascades updates across C<has_one> and
283 C<might_have> relationships. You can disable this behaviour on a
284 per-relationship basis by supplying C<< cascade_update => 0 >> in
285 the relationship attributes.
287 This is not a RDMS style cascade update - it purely means that when
288 an object has update called on it, all the related objects also
289 have update called. It will not change foreign keys automatically -
290 you must arrange to do this yourself.
292 =item on_delete / on_update
294 If you are using L<SQL::Translator> to create SQL for you, you can use these
295 attributes to explicitly set the desired C<ON DELETE> or C<ON UPDATE> constraint
296 type. If not supplied the SQLT parser will attempt to infer the constraint type by
297 interrogating the attributes of the B<opposite> relationship. For any 'multi'
298 relationship with C<< cascade_delete => 1 >>, the corresponding belongs_to
299 relationship will be created with an C<ON DELETE CASCADE> constraint. For any
300 relationship bearing C<< cascade_copy => 1 >> the resulting belongs_to constraint
301 will be C<ON UPDATE CASCADE>. If you wish to disable this autodetection, and just
302 use the RDBMS' default constraint type, pass C<< on_delete => undef >> or
303 C<< on_delete => '' >>, and the same for C<on_update> respectively.
307 Tells L<SQL::Translator> that the foreign key constraint it creates should be
308 deferrable. In other words, the user may request that the constraint be ignored
309 until the end of the transaction. Currently, only the PostgreSQL producer
310 actually supports this.
314 Tells L<SQL::Translator> to add an index for this constraint. Can also be
315 specified globally in the args to L<DBIx::Class::Schema/deploy> or
316 L<DBIx::Class::Schema/create_ddl_dir>. Default is on, set to 0 to disable.
320 =head2 register_relationship
324 =item Arguments: $relname, $rel_info
328 Registers a relationship on the class. This is called internally by
329 DBIx::Class::ResultSourceProxy to set up Accessors and Proxies.
333 sub register_relationship { }
335 =head2 related_resultset
339 =item Arguments: $relationship_name
341 =item Return Value: $related_resultset
345 $rs = $cd->related_resultset('artist');
347 Returns a L<DBIx::Class::ResultSet> for the relationship named
352 sub related_resultset {
354 $self->throw_exception("Can't call *_related as class methods")
357 my $rel_info = $self->relationship_info($rel);
358 $self->throw_exception( "No such relationship ${rel}" )
361 return $self->{related_resultsets}{$rel} ||= do {
362 my $attrs = (@_ > 1 && ref $_[$#_] eq 'HASH' ? pop(@_) : {});
363 $attrs = { %{$rel_info->{attrs} || {}}, %$attrs };
365 $self->throw_exception( "Invalid query: @_" )
366 if (@_ > 1 && (@_ % 2 == 1));
367 my $query = ((@_ > 1) ? {@_} : shift);
369 my $source = $self->result_source;
371 # condition resolution may fail if an incomplete master-object prefetch
372 # is encountered - that is ok during prefetch construction (not yet in_storage)
374 # if $rel_info->{cond} is a CODE, we might need to join from the
375 # current resultsource instead of just querying the target
376 # resultsource, in that case, the condition might provide an
377 # additional condition in order to avoid an unecessary join if
378 # that is at all possible.
379 my ($cond, $extended_cond) = try {
380 $source->_resolve_condition( $rel_info->{cond}, $rel, $self )
383 if ($self->in_storage) {
384 $self->throw_exception ($_);
387 $DBIx::Class::ResultSource::UNRESOLVABLE_CONDITION; # RV
390 if ($cond eq $DBIx::Class::ResultSource::UNRESOLVABLE_CONDITION) {
391 my $reverse = $source->reverse_relationship_info($rel);
392 foreach my $rev_rel (keys %$reverse) {
393 if ($reverse->{$rev_rel}{attrs}{accessor} && $reverse->{$rev_rel}{attrs}{accessor} eq 'multi') {
394 $attrs->{related_objects}{$rev_rel} = [ $self ];
395 weaken $attrs->{related_object}{$rev_rel}[0];
397 $attrs->{related_objects}{$rev_rel} = $self;
398 weaken $attrs->{related_object}{$rev_rel};
403 # this is where we're going to check if we have an extended
404 # rel. In that case, we need to: 1) If there's a second
405 # condition, we use that instead. 2) If there is only one
406 # condition, we need to join the current resultsource and have
407 # additional conditions.
408 if (ref $rel_info->{cond} eq 'CODE') {
409 # this is an extended relationship.
410 if ($extended_cond) {
411 $cond = $extended_cond;
415 # it's a bit hard to find out what to do with other joins
416 $self->throw_exception('Extended relationship '.$rel.' with additional join requires optimized declaration')
417 if exists $attrs->{join} && $attrs->{join};
419 # aliases get a bit more complicated, so we won't accept additional queries
420 $self->throw_exception('Extended relationship '.$rel.' with additional query requires optimized declaration')
424 [ { $rel => $self->result_source->from },
425 [ { 'me' => $self->result_source->related_source($rel)->from }, { 1 => 1 } ] ];
427 $cond->{"${rel}.${_}"} = $self->get_column($_) for $self->result_source->primary_columns;
431 if (ref $cond eq 'ARRAY') {
433 if (ref $_ eq 'HASH') {
435 foreach my $key (keys %$_) {
436 my $newkey = $key !~ /\./ ? "me.$key" : $key;
437 $hash->{$newkey} = $_->{$key};
444 } elsif (ref $cond eq 'HASH') {
445 foreach my $key (grep { ! /\./ } keys %$cond) {
446 $cond->{"me.$key"} = delete $cond->{$key};
450 $query = ($query ? { '-and' => [ $cond, $query ] } : $cond);
451 $self->result_source->related_source($rel)->resultset->search(
457 =head2 search_related
459 @objects = $rs->search_related('relname', $cond, $attrs);
460 $objects_rs = $rs->search_related('relname', $cond, $attrs);
462 Run a search on a related resultset. The search will be restricted to the
463 item or items represented by the L<DBIx::Class::ResultSet> it was called
464 upon. This method can be called on a ResultSet, a Row or a ResultSource class.
469 return shift->related_resultset(shift)->search(@_);
472 =head2 search_related_rs
474 ( $objects_rs ) = $rs->search_related_rs('relname', $cond, $attrs);
476 This method works exactly the same as search_related, except that
477 it guarantees a resultset, even in list context.
481 sub search_related_rs {
482 return shift->related_resultset(shift)->search_rs(@_);
487 $obj->count_related('relname', $cond, $attrs);
489 Returns the count of all the items in the related resultset, restricted by the
490 current item or where conditions. Can be called on a
491 L<DBIx::Class::Manual::Glossary/"ResultSet"> or a
492 L<DBIx::Class::Manual::Glossary/"Row"> object.
498 return $self->search_related(@_)->count;
503 my $new_obj = $obj->new_related('relname', \%col_data);
505 Create a new item of the related foreign class. If called on a
506 L<Row|DBIx::Class::Manual::Glossary/"Row"> object, it will magically
507 set any foreign key columns of the new object to the related primary
508 key columns of the source object for you. The newly created item will
509 not be saved into your storage until you call L<DBIx::Class::Row/insert>
515 my ($self, $rel, $values, $attrs) = @_;
516 return $self->search_related($rel)->new($values, $attrs);
519 =head2 create_related
521 my $new_obj = $obj->create_related('relname', \%col_data);
523 Creates a new item, similarly to new_related, and also inserts the item's data
524 into your storage medium. See the distinction between C<create> and C<new>
525 in L<DBIx::Class::ResultSet> for details.
532 my $obj = $self->search_related($rel)->create(@_);
533 delete $self->{related_resultsets}->{$rel};
539 my $found_item = $obj->find_related('relname', @pri_vals | \%pri_vals);
541 Attempt to find a related object using its primary key or unique constraints.
542 See L<DBIx::Class::ResultSet/find> for details.
549 return $self->search_related($rel)->find(@_);
552 =head2 find_or_new_related
554 my $new_obj = $obj->find_or_new_related('relname', \%col_data);
556 Find an item of a related class. If none exists, instantiate a new item of the
557 related class. The object will not be saved into your storage until you call
558 L<DBIx::Class::Row/insert> on it.
562 sub find_or_new_related {
564 my $obj = $self->find_related(@_);
565 return defined $obj ? $obj : $self->new_related(@_);
568 =head2 find_or_create_related
570 my $new_obj = $obj->find_or_create_related('relname', \%col_data);
572 Find or create an item of a related class. See
573 L<DBIx::Class::ResultSet/find_or_create> for details.
577 sub find_or_create_related {
579 my $obj = $self->find_related(@_);
580 return (defined($obj) ? $obj : $self->create_related(@_));
583 =head2 update_or_create_related
585 my $updated_item = $obj->update_or_create_related('relname', \%col_data, \%attrs?);
587 Update or create an item of a related class. See
588 L<DBIx::Class::ResultSet/update_or_create> for details.
592 sub update_or_create_related {
595 return $self->related_resultset($rel)->update_or_create(@_);
598 =head2 set_from_related
600 $book->set_from_related('author', $author_obj);
601 $book->author($author_obj); ## same thing
603 Set column values on the current object, using related values from the given
604 related object. This is used to associate previously separate objects, for
605 example, to set the correct author for a book, find the Author object, then
606 call set_from_related on the book.
608 This is called internally when you pass existing objects as values to
609 L<DBIx::Class::ResultSet/create>, or pass an object to a belongs_to accessor.
611 The columns are only set in the local copy of the object, call L</update> to
612 set them in the storage.
616 sub set_from_related {
617 my ($self, $rel, $f_obj) = @_;
618 my $rel_info = $self->relationship_info($rel);
619 $self->throw_exception( "No such relationship ${rel}" ) unless $rel_info;
620 my $cond = $rel_info->{cond};
621 $self->throw_exception(
622 "set_from_related can only handle a hash condition; the ".
623 "condition for $rel is of type ".
624 (ref $cond ? ref $cond : 'plain scalar')
625 ) unless ref $cond eq 'HASH';
626 if (defined $f_obj) {
627 my $f_class = $rel_info->{class};
628 $self->throw_exception( "Object $f_obj isn't a ".$f_class )
629 unless blessed $f_obj and $f_obj->isa($f_class);
632 # _resolve_condition might return two hashrefs, specially in the
633 # current case, since we know $f_object is an object.
634 my ($condref1, $condref2) = $self->result_source->_resolve_condition
635 ($rel_info->{cond}, $f_obj, $rel);
637 # if we get two condrefs, we need to use the second, otherwise we
639 $self->set_columns($condref2 ? $condref2 : $condref1);
644 =head2 update_from_related
646 $book->update_from_related('author', $author_obj);
648 The same as L</"set_from_related">, but the changes are immediately updated
653 sub update_from_related {
655 $self->set_from_related(@_);
659 =head2 delete_related
661 $obj->delete_related('relname', $cond, $attrs);
663 Delete any related item subject to the given conditions.
669 my $obj = $self->search_related(@_)->delete;
670 delete $self->{related_resultsets}->{$_[0]};
676 B<Currently only available for C<has_many>, C<many-to-many> and 'multi' type
681 =item Arguments: ($foreign_vals | $obj), $link_vals?
685 my $role = $schema->resultset('Role')->find(1);
686 $actor->add_to_roles($role);
687 # creates a My::DBIC::Schema::ActorRoles linking table row object
689 $actor->add_to_roles({ name => 'lead' }, { salary => 15_000_000 });
690 # creates a new My::DBIC::Schema::Role row object and the linking table
691 # object with an extra column in the link
693 Adds a linking table object for C<$obj> or C<$foreign_vals>. If the first
694 argument is a hash reference, the related object is created first with the
695 column values in the hash. If an object reference is given, just the linking
696 table object is created. In either case, any additional column values for the
697 linking table object can be specified in C<$link_vals>.
701 B<Currently only available for C<many-to-many> relationships.>
705 =item Arguments: (\@hashrefs | \@objs), $link_vals?
709 my $actor = $schema->resultset('Actor')->find(1);
710 my @roles = $schema->resultset('Role')->search({ role =>
711 { '-in' => ['Fred', 'Barney'] } } );
713 $actor->set_roles(\@roles);
714 # Replaces all of $actor's previous roles with the two named
716 $actor->set_roles(\@roles, { salary => 15_000_000 });
717 # Sets a column in the link table for all roles
720 Replace all the related objects with the given reference to a list of
721 objects. This does a C<delete> B<on the link table resultset> to remove the
722 association between the current object and all related objects, then calls
723 C<add_to_$rel> repeatedly to link all the new objects.
725 Note that this means that this method will B<not> delete any objects in the
726 table on the right side of the relation, merely that it will delete the link
729 Due to a mistake in the original implementation of this method, it will also
730 accept a list of objects or hash references. This is B<deprecated> and will be
731 removed in a future version.
733 =head2 remove_from_$rel
735 B<Currently only available for C<many-to-many> relationships.>
739 =item Arguments: $obj
743 my $role = $schema->resultset('Role')->find(1);
744 $actor->remove_from_roles($role);
745 # removes $role's My::DBIC::Schema::ActorRoles linking table row object
747 Removes the link between the current object and the related object. Note that
748 the related object itself won't be deleted unless you call ->delete() on
749 it. This method just removes the link between the two objects.
753 Matt S. Trout <mst@shadowcatsystems.co.uk>
757 You may distribute this code under the same terms as Perl itself.