1DBIx::Class::RelationshUispe:r:BCaosnet(r3i)buted Perl DDoBcIuxm:e:nCtlaatsiso:n:Relationship::Base(3)
2
6 DBIx::Class::Relationship::Base - Inter-table relationships
7
9 __PACKAGE__->add_relationship(
10 spiders => 'My::DB::Result::Creatures',
11 sub {
12 my $args = shift;
13 return {
14 "$args->{foreign_alias}.id" => { -ident => "$args->{self_alias}.id" },
15 "$args->{foreign_alias}.type" => 'arachnid'
16 };
17 },
18 );
19
21 This class provides methods to describe the relationships between the
22 tables in your database model. These are the "bare bones" relationships
23 methods, for predefined ones, look in DBIx::Class::Relationship.
24
26 add_relationship
27 Arguments: $rel_name, $foreign_class, $condition, $attrs
28 __PACKAGE__->add_relationship('rel_name',
30 'Foreign::Class',
31 $condition, $attrs);
32 Create a custom relationship between one result source and another
34 source, indicated by its class name.
35 condition
37 The condition argument describes the "ON" clause of the "JOIN"
39 expression used to connect the two sources when creating SQL queries.
40 Simple equality
42 To create simple equality joins, supply a hashref containing the remote
44 table column name as the key(s) prefixed by 'foreign.', and the
45 corresponding local table column name as the value(s) prefixed by
46 'self.'. Both "foreign" and "self" are pseudo aliases and must be
47 entered literally. They will be replaced with the actual correct table
48 alias when the SQL is produced.
49 For example given:
51 My::Schema::Author->has_many(
53 books => 'My::Schema::Book',
54 { 'foreign.author_id' => 'self.id' }
55 );
56 A query like:
58 $author_rs->search_related('books')->next
60 will result in the following "JOIN" clause:
62 ... FROM author me LEFT JOIN book books ON books.author_id = me.id ...
64 This describes a relationship between the "Author" table and the "Book"
66 table where the "Book" table has a column "author_id" containing the ID
67 value of the "Author".
68 Similarly:
70 My::Schema::Book->has_many(
72 editions => 'My::Schema::Edition',
73 {
74 'foreign.publisher_id' => 'self.publisher_id',
75 'foreign.type_id' => 'self.type_id',
76 }
77 );
78 ...
80 $book_rs->search_related('editions')->next
82 will result in the "JOIN" clause:
84 ... FROM book me
86 LEFT JOIN edition editions ON
87 editions.publisher_id = me.publisher_id
88 AND editions.type_id = me.type_id ...
89 This describes the relationship from "Book" to "Edition", where the
91 "Edition" table refers to a publisher and a type (e.g. "paperback"):
92 Multiple groups of simple equality conditions
94 As is the default in SQL::Abstract::Classic, the key-value pairs will
96 be "AND"ed in the resulting "JOIN" clause. An "OR" can be achieved with
97 an arrayref. For example a condition like:
98 My::Schema::Item->has_many(
100 related_item_links => My::Schema::Item::Links,
101 [
102 { 'foreign.left_itemid' => 'self.id' },
103 { 'foreign.right_itemid' => 'self.id' },
104 ],
105 );
106 will translate to the following "JOIN" clause:
108 ... FROM item me JOIN item_relations related_item_links ON
110 related_item_links.left_itemid = me.id
111 OR related_item_links.right_itemid = me.id ...
112 This describes the relationship from "Item" to "Item::Links", where
114 "Item::Links" is a many-to-many linking table, linking items back to
115 themselves in a peer fashion (without a "parent-child" designation)
116 Custom join conditions
118 NOTE: The custom join condition specification mechanism is capable of
120 generating JOIN clauses of virtually unlimited complexity. This may limit
121 your ability to traverse some of the more involved relationship chains the
122 way you expect, *and* may bring your RDBMS to its knees. Exercise care
123 when declaring relationships as described here.
124 To specify joins which describe more than a simple equality of column
126 values, the custom join condition coderef syntax can be used. For
127 example:
128 My::Schema::Artist->has_many(
130 cds_80s => 'My::Schema::CD',
131 sub {
132 my $args = shift;
133 return {
135 "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
136 "$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
137 };
138 }
139 );
140 ...
142 $artist_rs->search_related('cds_80s')->next;
144 will result in the "JOIN" clause:
146 ... FROM artist me LEFT JOIN cd cds_80s ON
148 cds_80s.artist = me.artistid
149 AND cds_80s.year < ?
150 AND cds_80s.year > ?
151 with the bind values:
153 '1990', '1979'
155 "$args->{foreign_alias}" and "$args->{self_alias}" are supplied the
157 same values that would be otherwise substituted for "foreign" and
158 "self" in the simple hashref syntax case.
159 The coderef is expected to return a valid SQL::Abstract::Classic query-
161 structure, just like what one would supply as the first argument to
162 "search" in DBIx::Class::ResultSet. The return value will be passed
163 directly to DBIx::Class::SQLMaker and the resulting SQL will be used
164 verbatim as the "ON" clause of the "JOIN" statement associated with
165 this relationship.
166 While every coderef-based condition must return a valid "ON" clause, it
168 may elect to additionally return a simplified optional join-free
169 condition consisting of a hashref with all keys being fully qualified
170 names of columns declared on the corresponding result source. This
171 boils down to two scenarios:
172 • When relationship resolution is invoked after "$result->$rel_name",
174 as opposed to "$rs->related_resultset($rel_name)", the $result
175 object is passed to the coderef as "$args->{self_result_object}".
176 • Alternatively when the user-space invokes resolution via
178 "$result->set_from_related( $rel_name => $foreign_values_or_object
179 )", the corresponding data is passed to the coderef as
180 "$args->{foreign_values}", always in the form of a hashref. If a
181 foreign result object is supplied (which is valid usage of
182 "set_from_related"), its values will be extracted into hashref form
183 by calling get_columns.
184 Note that the above scenarios are mutually exclusive, that is you will
186 be supplied none or only one of "self_result_object" and
187 "foreign_values". In other words if you define your condition coderef
188 as:
189 sub {
191 my $args = shift;
192 return (
194 {
195 "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
196 "$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
197 },
198 ! $args->{self_result_object} ? () : {
199 "$args->{foreign_alias}.artist" => $args->{self_result_object}->artistid,
200 "$args->{foreign_alias}.year" => { '>', "1979", '<', "1990" },
201 },
202 ! $args->{foreign_values} ? () : {
203 "$args->{self_alias}.artistid" => $args->{foreign_values}{artist},
204 }
205 );
206 }
207 Then this code:
209 my $artist = $schema->resultset("Artist")->find({ id => 4 });
211 $artist->cds_80s->all;
212 Can skip a "JOIN" altogether and instead produce:
214 SELECT cds_80s.cdid, cds_80s.artist, cds_80s.title, cds_80s.year, cds_80s.genreid, cds_80s.single_track
216 FROM cd cds_80s
217 WHERE cds_80s.artist = ?
218 AND cds_80s.year < ?
219 AND cds_80s.year > ?
220 With the bind values:
222 '4', '1990', '1979'
224 While this code:
226 my $cd = $schema->resultset("CD")->search({ artist => 1 }, { rows => 1 })->single;
228 my $artist = $schema->resultset("Artist")->new({});
229 $artist->set_from_related('cds_80s');
230 Will properly set the "$artist->artistid" field of this new object to 1
232 Note that in order to be able to use "set_from_related" (and by
234 extension $result->create_related), the returned join free condition
235 must contain only plain values/deflatable objects. For instance the
236 "year" constraint in the above example prevents the relationship from
237 being used to create related objects using "$artst->create_related(
238 cds_80s => { title => 'blah' } )" (an exception will be thrown).
239 In order to allow the user to go truly crazy when generating a custom
241 "ON" clause, the $args hashref passed to the subroutine contains some
242 extra metadata. Currently the supplied coderef is executed as:
243 $relationship_info->{cond}->({
245 self_resultsource => The resultsource instance on which rel_name is registered
246 rel_name => The relationship name (does *NOT* always match foreign_alias)
247 self_alias => The alias of the invoking resultset
249 foreign_alias => The alias of the to-be-joined resultset (does *NOT* always match rel_name)
250 # only one of these (or none at all) will ever be supplied to aid in the
252 # construction of a join-free condition
253 self_result_object => The invocant *object* itself in case of a call like
255 $result_object->$rel_name( ... )
256 foreign_values => A *hashref* of related data: may be passed in directly or
258 derived via ->get_columns() from a related object in case of
259 $result_object->set_from_related( $rel_name, $foreign_result_object )
260 # deprecated inconsistent names, will be forever available for legacy code
262 self_rowobj => Old deprecated slot for self_result_object
263 foreign_relname => Old deprecated slot for rel_name
264 });
265 attributes
267 The standard ResultSet attributes may be used as relationship
269 attributes. In particular, the 'where' attribute is useful for
270 filtering relationships:
271 __PACKAGE__->has_many( 'valid_users', 'MyApp::Schema::User',
273 { 'foreign.user_id' => 'self.user_id' },
274 { where => { valid => 1 } }
275 );
276 The following attributes are also valid:
278 join_type
280 Explicitly specifies the type of join to use in the relationship.
281 Any SQL join type is valid, e.g. "LEFT" or "RIGHT". It will be
282 placed in the SQL command immediately before "JOIN".
283 proxy => $column | \@columns | \%column
285 The 'proxy' attribute can be used to retrieve values, and to
286 perform updates if the relationship has 'cascade_update' set. The
287 'might_have' and 'has_one' relationships have this set by default;
288 if you want a proxy to update across a 'belongs_to' relationship,
289 you must set the attribute yourself.
290 \@columns
292 An arrayref containing a list of accessors in the foreign class
293 to create in the main class. If, for example, you do the
294 following:
295 MyApp::Schema::CD->might_have(liner_notes => 'MyApp::Schema::LinerNotes',
297 undef, {
298 proxy => [ qw/notes/ ],
299 });
300 Then, assuming MyApp::Schema::LinerNotes has an accessor named
302 notes, you can do:
303 my $cd = MyApp::Schema::CD->find(1);
305 $cd->notes('Notes go here'); # set notes -- LinerNotes object is
306 # created if it doesn't exist
307 For a 'belongs_to relationship, note the 'cascade_update':
309 MyApp::Schema::Track->belongs_to( cd => 'MyApp::Schema::CD', 'cd,
311 { proxy => ['title'], cascade_update => 1 }
312 );
313 $track->title('New Title');
314 $track->update; # updates title in CD
315 \%column
317 A hashref where each key is the accessor you want installed in
318 the main class, and its value is the name of the original in
319 the foreign class.
320 MyApp::Schema::Track->belongs_to( cd => 'MyApp::Schema::CD', 'cd', {
322 proxy => { cd_title => 'title' },
323 });
324 This will create an accessor named "cd_title" on the $track
326 result object.
327 NOTE: you can pass a nested struct too, for example:
329 MyApp::Schema::Track->belongs_to( cd => 'MyApp::Schema::CD', 'cd', {
331 proxy => [ 'year', { cd_title => 'title' } ],
332 });
333 accessor
335 Specifies the type of accessor that should be created for the
336 relationship. Valid values are "single" (for when there is only a
337 single related object), "multi" (when there can be many), and
338 "filter" (for when there is a single related object, but you also
339 want the relationship accessor to double as a column accessor). For
340 "multi" accessors, an add_to_* method is also created, which calls
341 "create_related" for the relationship.
342 is_foreign_key_constraint
344 If you are using SQL::Translator to create SQL for you and you find
345 that it is creating constraints where it shouldn't, or not creating
346 them where it should, set this attribute to a true or false value
347 to override the detection of when to create constraints.
348 cascade_copy
350 If "cascade_copy" is true on a "has_many" relationship for an
351 object, then when you copy the object all the related objects will
352 be copied too. To turn this behaviour off, pass "cascade_copy => 0"
353 in the $attr hashref.
354 The behaviour defaults to "cascade_copy => 1" for "has_many"
356 relationships.
357 cascade_delete
359 By default, DBIx::Class cascades deletes across "has_many",
360 "has_one" and "might_have" relationships. You can disable this
361 behaviour on a per-relationship basis by supplying "cascade_delete
362 => 0" in the relationship attributes.
363 The cascaded operations are performed after the requested delete,
365 so if your database has a constraint on the relationship, it will
366 have deleted/updated the related records or raised an exception
367 before DBIx::Class gets to perform the cascaded operation.
368 cascade_update
370 By default, DBIx::Class cascades updates across "has_one" and
371 "might_have" relationships. You can disable this behaviour on a
372 per-relationship basis by supplying "cascade_update => 0" in the
373 relationship attributes.
374 The "belongs_to" relationship does not update across relationships
376 by default, so if you have a 'proxy' attribute on a belongs_to and
377 want to use 'update' on it, you must set "cascade_update => 1".
378 This is not a RDMS style cascade update - it purely means that when
380 an object has update called on it, all the related objects also
381 have update called. It will not change foreign keys automatically -
382 you must arrange to do this yourself.
383 on_delete / on_update
385 If you are using SQL::Translator to create SQL for you, you can use
386 these attributes to explicitly set the desired "ON DELETE" or "ON
387 UPDATE" constraint type. If not supplied the SQLT parser will
388 attempt to infer the constraint type by interrogating the
389 attributes of the opposite relationship. For any 'multi'
390 relationship with "cascade_delete => 1", the corresponding
391 belongs_to relationship will be created with an "ON DELETE CASCADE"
392 constraint. For any relationship bearing "cascade_copy => 1" the
393 resulting belongs_to constraint will be "ON UPDATE CASCADE". If you
394 wish to disable this autodetection, and just use the RDBMS' default
395 constraint type, pass "on_delete => undef" or "on_delete => ''",
396 and the same for "on_update" respectively.
397 is_deferrable
399 Tells SQL::Translator that the foreign key constraint it creates
400 should be deferrable. In other words, the user may request that the
401 constraint be ignored until the end of the transaction. Currently,
402 only the PostgreSQL producer actually supports this.
403 add_fk_index
405 Tells SQL::Translator to add an index for this constraint. Can also
406 be specified globally in the args to "deploy" in
407 DBIx::Class::Schema or "create_ddl_dir" in DBIx::Class::Schema.
408 Default is on, set to 0 to disable.
409 register_relationship
411 Arguments: $rel_name, $rel_info
412 Registers a relationship on the class. This is called internally by
414 DBIx::Class::ResultSourceProxy to set up Accessors and Proxies.
415 related_resultset
417 Arguments: $rel_name
418 Return Value: $related_resultset
419 $rs = $cd->related_resultset('artist');
421 Returns a DBIx::Class::ResultSet for the relationship named $rel_name.
423 $relationship_accessor
425 Arguments: none
426 Return Value: $result | $related_resultset | undef
427 # These pairs do the same thing
429 $result = $cd->related_resultset('artist')->single; # has_one relationship
430 $result = $cd->artist;
431 $rs = $cd->related_resultset('tracks'); # has_many relationship
432 $rs = $cd->tracks;
433 This is the recommended way to traverse through relationships, based on
435 the "accessor" name given in the relationship definition.
436 This will return either a Result or a ResultSet, depending on if the
438 relationship is "single" (returns only one row) or "multi" (returns
439 many rows). The method may also return "undef" if the relationship
440 doesn't exist for this instance (like in the case of "might_have"
441 relationships).
442 search_related
444 Arguments: $rel_name, $cond?, \%attrs?
445 Return Value: $resultset (scalar context) | @result_objs (list context)
446 Run a search on a related resultset. The search will be restricted to
448 the results represented by the DBIx::Class::ResultSet it was called
449 upon.
450 See "search_related" in DBIx::Class::ResultSet for more information.
452 search_related_rs
454 This method works exactly the same as search_related, except that it
455 guarantees a resultset, even in list context.
456 count_related
458 Arguments: $rel_name, $cond?, \%attrs?
459 Return Value: $count
460 Returns the count of all the rows in the related resultset, restricted
462 by the current result or where conditions.
463 new_related
465 Arguments: $rel_name, \%col_data
466 Return Value: $result
467 Create a new result object of the related foreign class. It will
469 magically set any foreign key columns of the new object to the related
470 primary key columns of the source object for you. The newly created
471 result will not be saved into your storage until you call "insert" in
472 DBIx::Class::Row on it.
473 create_related
475 Arguments: $rel_name, \%col_data
476 Return Value: $result
477 my $result = $obj->create_related($rel_name, \%col_data);
479 Creates a new result object, similarly to new_related, and also inserts
481 the result's data into your storage medium. See the distinction between
482 "create" and "new" in DBIx::Class::ResultSet for details.
483 find_related
485 Arguments: $rel_name, \%col_data | @pk_values, { key =>
486 $unique_constraint, %attrs }?
487 Return Value: $result | undef
488 my $result = $obj->find_related($rel_name, \%col_data);
490 Attempt to find a related object using its primary key or unique
492 constraints. See "find" in DBIx::Class::ResultSet for details.
493 find_or_new_related
495 Arguments: $rel_name, \%col_data, { key => $unique_constraint, %attrs
496 }?
497 Return Value: $result
498 Find a result object of a related class. See "find_or_new" in
500 DBIx::Class::ResultSet for details.
501 find_or_create_related
503 Arguments: $rel_name, \%col_data, { key => $unique_constraint, %attrs
504 }?
505 Return Value: $result
506 Find or create a result object of a related class. See "find_or_create"
508 in DBIx::Class::ResultSet for details.
509 update_or_create_related
511 Arguments: $rel_name, \%col_data, { key => $unique_constraint, %attrs
512 }?
513 Return Value: $result
514 Update or create a result object of a related class. See
516 "update_or_create" in DBIx::Class::ResultSet for details.
517 set_from_related
519 Arguments: $rel_name, $result
520 Return Value: not defined
521 $book->set_from_related('author', $author_obj);
523 $book->author($author_obj); ## same thing
524 Set column values on the current object, using related values from the
526 given related object. This is used to associate previously separate
527 objects, for example, to set the correct author for a book, find the
528 Author object, then call set_from_related on the book.
529 This is called internally when you pass existing objects as values to
531 "create" in DBIx::Class::ResultSet, or pass an object to a belongs_to
532 accessor.
533 The columns are only set in the local copy of the object, call update
535 to update them in the storage.
536 update_from_related
538 Arguments: $rel_name, $result
539 Return Value: not defined
540 $book->update_from_related('author', $author_obj);
542 The same as "set_from_related", but the changes are immediately updated
544 in storage.
545 delete_related
547 Arguments: $rel_name, $cond?, \%attrs?
548 Return Value: $underlying_storage_rv
549 Delete any related row, subject to the given conditions. Internally,
551 this calls:
552 $self->search_related(@_)->delete
554 And returns the result of that.
556 add_to_$rel
558 Currently only available for "has_many", "many_to_many" and 'multi'
559 type relationships.
560 has_many / multi
562 Arguments: \%col_data
564 Return Value: $result
565 Creates/inserts a new result object. Internally, this calls:
567 $self->create_related($rel, @_)
569 And returns the result of that.
571 many_to_many
573 Arguments: (\%col_data | $result), \%link_col_data?
575 Return Value: $result
576 my $role = $schema->resultset('Role')->find(1);
578 $actor->add_to_roles($role);
579 # creates a My::DBIC::Schema::ActorRoles linking table result object
580 $actor->add_to_roles({ name => 'lead' }, { salary => 15_000_000 });
582 # creates a new My::DBIC::Schema::Role result object and the linking table
583 # object with an extra column in the link
584 Adds a linking table object. If the first argument is a hash reference,
586 the related object is created first with the column values in the hash.
587 If an object reference is given, just the linking table object is
588 created. In either case, any additional column values for the linking
589 table object can be specified in "\%link_col_data".
590 See "many_to_many" in DBIx::Class::Relationship for additional details.
592 set_$rel
594 Currently only available for "many_to_many" relationships.
595 Arguments: (\@hashrefs_of_col_data | \@result_objs), $link_vals?
597 Return Value: not defined
598 my $actor = $schema->resultset('Actor')->find(1);
600 my @roles = $schema->resultset('Role')->search({ role =>
601 { '-in' => ['Fred', 'Barney'] } } );
602 $actor->set_roles(\@roles);
604 # Replaces all of $actor's previous roles with the two named
605 $actor->set_roles(\@roles, { salary => 15_000_000 });
607 # Sets a column in the link table for all roles
608 Replace all the related objects with the given reference to a list of
610 objects. This does a "delete" on the link table resultset to remove the
611 association between the current object and all related objects, then
612 calls "add_to_$rel" repeatedly to link all the new objects.
613 Note that this means that this method will not delete any objects in
615 the table on the right side of the relation, merely that it will delete
616 the link between them.
617 Due to a mistake in the original implementation of this method, it will
619 also accept a list of objects or hash references. This is deprecated
620 and will be removed in a future version.
621 remove_from_$rel
623 Currently only available for "many_to_many" relationships.
624 Arguments: $result
626 Return Value: not defined
627 my $role = $schema->resultset('Role')->find(1);
629 $actor->remove_from_roles($role);
630 # removes $role's My::DBIC::Schema::ActorRoles linking table result object
631 Removes the link between the current object and the related object.
633 Note that the related object itself won't be deleted unless you call
634 ->delete() on it. This method just removes the link between the two
635 objects.
636
638 Check the list of additional DBIC resources.
639
641 This module is free software copyright by the DBIx::Class (DBIC)
642 authors. You can redistribute it and/or modify it under the same terms
643 as the DBIx::Class library.
644perl v5.34.0 2021-07-22DBIx::Class::Relationship::Base(3)