1DBIx::Class::RelationshUispe(r3)Contributed Perl DocumenDtBaItxi:o:nClass::Relationship(3)
2
6 DBIx::Class::Relationship - Inter-table relationships
7
9 ## Creating relationships
10 MyApp::Schema::Actor->has_many('actorroles' => 'MyApp::Schema::ActorRole',
11 'actor');
12 MyApp::Schema::Role->has_many('actorroles' => 'MyApp::Schema::ActorRole',
13 'role');
14 MyApp::Schema::ActorRole->belongs_to('role' => 'MyApp::Schema::Role');
15 MyApp::Schema::ActorRole->belongs_to('actor' => 'MyApp::Schema::Actor');
16 MyApp::Schema::Role->many_to_many('actors' => 'actorroles', 'actor');
18 MyApp::Schema::Actor->many_to_many('roles' => 'actorroles', 'role');
19 ## Using relationships
21 $schema->resultset('Actor')->find({ id => 1})->roles();
22 $schema->resultset('Role')->find({ id => 1 })->actorroles->search_related('actor', { Name => 'Fred' });
23 $schema->resultset('Actor')->add_to_roles({ Name => 'Sherlock Holmes'});
24 See DBIx::Class::Manual::Cookbook for more.
26
28 The word Relationship has a specific meaning in DBIx::Class, see the
29 definition in the Glossary.
30 This class provides methods to set up relationships between the tables
32 in your database model. Relationships are the most useful and powerful
33 technique that DBIx::Class provides. To create efficient database
34 queries, create relationships between any and all tables that have
35 something in common, for example if you have a table Authors:
36 ID | Name | Age
38 ------------------
39 1 | Fred | 30
40 2 | Joe | 32
41 and a table Books:
43 ID | Author | Name
45 --------------------
46 1 | 1 | Rulers of the universe
47 2 | 1 | Rulers of the galaxy
48 Then without relationships, the method of getting all books by Fred
50 goes like this:
51 my $fred = $schema->resultset('Author')->find({ Name => 'Fred' });
53 my $fredsbooks = $schema->resultset('Book')->search({ Author => $fred->ID });
54 With a has_many relationship called "books" on Author (see below for
56 details), we can do this instead:
57 my $fredsbooks = $schema->resultset('Author')->find({ Name => 'Fred' })->books;
59 Each relationship sets up an accessor method on the Result objects that
61 represent the items of your table. From ResultSet objects, the
62 relationships can be searched using the "search_related" method. In
63 list context, each returns a list of Result objects for the related
64 class, in scalar context, a new ResultSet representing the joined
65 tables is returned. Thus, the calls can be chained to produce complex
66 queries. Since the database is not actually queried until you attempt
67 to retrieve the data for an actual item, no time is wasted producing
68 them.
69 my $cheapfredbooks = $schema->resultset('Author')->find({
71 Name => 'Fred',
72 })->books->search_related('prices', {
73 Price => { '<=' => '5.00' },
74 });
75 will produce a query something like:
77 SELECT * FROM Author me
79 LEFT JOIN Books books ON books.author = me.id
80 LEFT JOIN Prices prices ON prices.book = books.id
81 WHERE prices.Price <= 5.00
82 all without needing multiple fetches.
84 Only the helper methods for setting up standard relationship types are
86 documented here. For the basic, lower-level methods, and a description
87 of all the useful *_related methods that you get for free, see
88 DBIx::Class::Relationship::Base.
89
91 All helper methods are called similar to the following template:
92 __PACKAGE__->$method_name('rel_name', 'Foreign::Class', \%cond|\@cond|\&cond?, \%attrs?);
94 Both "cond" and "attrs" are optional. Pass "undef" for "cond" if you
96 want to use the default value for it, but still want to set "attrs".
97 See "condition" in DBIx::Class::Relationship::Base for full
99 documentation on definition of the "cond" argument.
100 See "attributes" in DBIx::Class::Relationship::Base for documentation
102 on the attributes that are allowed in the "attrs" argument.
103 belongs_to
105 Arguments: $accessor_name, $related_class,
106 $our_fk_column|\%cond|\@cond|\$cond?, \%attrs?
107 Creates a relationship where the calling class stores the foreign
109 class's primary key in one (or more) of the calling class columns.
110 This relationship defaults to using $accessor_name as the column name
111 in this class to resolve the join against the primary key from
112 $related_class, unless $our_fk_column specifies the foreign key column
113 in this class or "cond" specifies a reference to a join condition.
114 accessor_name
116 This argument is the name of the method you can call on a Result
117 object to retrieve the instance of the foreign class matching this
118 relationship. This is often called the "relation(ship) name".
119 Use this accessor_name in "join" in DBIx::Class::ResultSet or
121 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
122 indicated by this relationship.
123 related_class
125 This is the class name of the table referenced by the foreign key
126 in this class.
127 our_fk_column
129 The column name on this class that contains the foreign key.
130 OR
132 cond
134 A hashref, arrayref or coderef specifying a custom join expression.
135 For more info see "condition" in DBIx::Class::Relationship::Base.
136 # in a Book class (where Author has many Books)
138 My::DBIC::Schema::Book->belongs_to(
139 author =>
140 'My::DBIC::Schema::Author',
141 'author_id'
142 );
143 # OR (same result)
145 My::DBIC::Schema::Book->belongs_to(
146 author =>
147 'My::DBIC::Schema::Author',
148 { 'foreign.author_id' => 'self.author_id' }
149 );
150 # OR (similar result but uglier accessor name)
152 My::DBIC::Schema::Book->belongs_to(
153 author_id =>
154 'My::DBIC::Schema::Author'
155 );
156 # Usage
158 my $author_obj = $book->author; # get author object
159 $book->author( $new_author_obj ); # set author object
160 $book->author_id(); # get the plain id
161 # To retrieve the plain id if you used the ugly version:
163 $book->get_column('author_id');
164 If some of the foreign key columns are nullable you probably want to
166 set the join_type attribute to "left" explicitly so that SQL expressing
167 this relation is composed with a "LEFT JOIN" (as opposed to "INNER
168 JOIN" which is default for "belongs_to" relationships). This ensures
169 that relationship traversal works consistently in all situations. (i.e.
170 resultsets involving join or prefetch). The modified declaration is
171 shown below:
172 # in a Book class (where Author has_many Books)
174 __PACKAGE__->belongs_to(
175 author =>
176 'My::DBIC::Schema::Author',
177 'author',
178 { join_type => 'left' }
179 );
180 Cascading deletes are off by default on a "belongs_to" relationship. To
182 turn them on, pass "cascade_delete => 1" in the $attr hashref.
183 By default, DBIC will return undef and avoid querying the database if a
185 "belongs_to" accessor is called when any part of the foreign key IS
186 NULL. To disable this behavior, pass "undef_on_null_fk => 0" in the
187 "\%attrs" hashref.
188 NOTE: If you are used to Class::DBI relationships, this is the
190 equivalent of "has_a".
191 See "attributes" in DBIx::Class::Relationship::Base for documentation
193 on relationship methods and valid relationship attributes. Also see
194 DBIx::Class::ResultSet for a list of standard resultset attributes
195 which can be assigned to relationships as well.
196 has_many
198 Arguments: $accessor_name, $related_class,
199 $their_fk_column|\%cond|\@cond|\&cond?, \%attrs?
200 Creates a one-to-many relationship where the foreign class refers to
202 this class's primary key. This relationship refers to zero or more
203 records in the foreign table (e.g. a "LEFT JOIN"). This relationship
204 defaults to using the end of this classes namespace as the foreign key
205 in $related_class to resolve the join, unless $their_fk_column
206 specifies the foreign key column in $related_class or "cond" specifies
207 a reference to a join condition.
208 accessor_name
210 This argument is the name of the method you can call on a Result
211 object to retrieve a resultset of the related class restricted to
212 the ones related to the result object. In list context it returns
213 the result objects. This is often called the "relation(ship) name".
214 Use this accessor_name in "join" in DBIx::Class::ResultSet or
216 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
217 indicated by this relationship.
218 related_class
220 This is the class name of the table which contains a foreign key
221 column containing PK values of this class.
222 their_fk_column
224 The column name on the related class that contains the foreign key.
225 OR
227 cond
229 A hashref, arrayref or coderef specifying a custom join
230 expression. For more info see "condition" in
231 DBIx::Class::Relationship::Base.
232 # in an Author class (where Author has_many Books)
234 # assuming related class is storing our PK in "author_id"
235 My::DBIC::Schema::Author->has_many(
236 books =>
237 'My::DBIC::Schema::Book',
238 'author_id'
239 );
240 # OR (same result)
242 My::DBIC::Schema::Author->has_many(
243 books =>
244 'My::DBIC::Schema::Book',
245 { 'foreign.author_id' => 'self.id' },
246 );
247 # OR (similar result, assuming related_class is storing our PK, in "author")
249 # (the "author" is guessed at from "Author" in the class namespace)
250 My::DBIC::Schema::Author->has_many(
251 books =>
252 'My::DBIC::Schema::Book',
253 );
254 # Usage
257 # resultset of Books belonging to author
258 my $booklist = $author->books;
259 # resultset of Books belonging to author, restricted by author name
261 my $booklist = $author->books({
262 name => { LIKE => '%macaroni%' },
263 { prefetch => [qw/book/],
264 });
265 # array of Book objects belonging to author
267 my @book_objs = $author->books;
268 # force resultset even in list context
270 my $books_rs = $author->books;
271 ( $books_rs ) = $obj->books_rs;
272 # create a new book for this author, the relation fields are auto-filled
274 $author->create_related('books', \%col_data);
275 # alternative method for the above
276 $author->add_to_books(\%col_data);
277 Three methods are created when you create a has_many relationship. The
279 first method is the expected accessor method, "$accessor_name()". The
280 second is almost exactly the same as the accessor method but "_rs" is
281 added to the end of the method name, eg "$accessor_name_rs()". This
282 method works just like the normal accessor, except that it always
283 returns a resultset, even in list context. The third method, named
284 "add_to_$rel_name", will also be added to your Row items; this allows
285 you to insert new related items, using the same mechanism as in
286 "create_related" in DBIx::Class::Relationship::Base.
287 If you delete an object in a class with a "has_many" relationship, all
289 the related objects will be deleted as well. To turn this behaviour
290 off, pass "cascade_delete => 0" in the $attr hashref.
291 The cascaded operations are performed after the requested delete or
293 update, so if your database has a constraint on the relationship, it
294 will have deleted/updated the related records or raised an exception
295 before DBIx::Class gets to perform the cascaded operation.
296 If you copy an object in a class with a "has_many" relationship, all
298 the related objects will be copied as well. To turn this behaviour off,
299 pass "cascade_copy => 0" in the $attr hashref. The behaviour defaults
300 to "cascade_copy => 1".
301 See "attributes" in DBIx::Class::Relationship::Base for documentation
303 on relationship methods and valid relationship attributes. Also see
304 DBIx::Class::ResultSet for a list of standard resultset attributes
305 which can be assigned to relationships as well.
306 might_have
308 Arguments: $accessor_name, $related_class,
309 $their_fk_column|\%cond|\@cond|\&cond?, \%attrs?
310 Creates an optional one-to-one relationship with a class. This
312 relationship defaults to using $accessor_name as the foreign key in
313 $related_class to resolve the join, unless $their_fk_column specifies
314 the foreign key column in $related_class or "cond" specifies a
315 reference to a join condition.
316 accessor_name
318 This argument is the name of the method you can call on a Result
319 object to retrieve the instance of the foreign class matching this
320 relationship. This is often called the "relation(ship) name".
321 Use this accessor_name in "join" in DBIx::Class::ResultSet or
323 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
324 indicated by this relationship.
325 related_class
327 This is the class name of the table which contains a foreign key
328 column containing PK values of this class.
329 their_fk_column
331 The column name on the related class that contains the foreign key.
332 OR
334 cond
336 A hashref, arrayref or coderef specifying a custom join
337 expression. For more info see "condition" in
338 DBIx::Class::Relationship::Base.
339 # Author may have an entry in the pseudonym table
341 My::DBIC::Schema::Author->might_have(
342 pseudonym =>
343 'My::DBIC::Schema::Pseudonym',
344 'author_id',
345 );
346 # OR (same result, assuming the related_class stores our PK)
348 My::DBIC::Schema::Author->might_have(
349 pseudonym =>
350 'My::DBIC::Schema::Pseudonym',
351 );
352 # OR (same result)
354 My::DBIC::Schema::Author->might_have(
355 pseudonym =>
356 'My::DBIC::Schema::Pseudonym',
357 { 'foreign.author_id' => 'self.id' },
358 );
359 # Usage
361 my $pname = $author->pseudonym; # to get the Pseudonym object
362 If you update or delete an object in a class with a "might_have"
364 relationship, the related object will be updated or deleted as well. To
365 turn off this behavior, add "cascade_delete => 0" to the $attr hashref.
366 The cascaded operations are performed after the requested delete or
368 update, so if your database has a constraint on the relationship, it
369 will have deleted/updated the related records or raised an exception
370 before DBIx::Class gets to perform the cascaded operation.
371 See "attributes" in DBIx::Class::Relationship::Base for documentation
373 on relationship methods and valid relationship attributes. Also see
374 DBIx::Class::ResultSet for a list of standard resultset attributes
375 which can be assigned to relationships as well.
376 Note that if you supply a condition on which to join, and the column in
378 the current table allows nulls (i.e., has the "is_nullable" attribute
379 set to a true value), than "might_have" will warn about this because
380 it's naughty and you shouldn't do that. The warning will look something
381 like:
382 "might_have/has_one" must not be on columns with is_nullable set to true (MySchema::SomeClass/key)
384 If you must be naughty, you can suppress the warning by setting
386 "DBIC_DONT_VALIDATE_RELS" environment variable to a true value.
387 Otherwise, you probably just meant to use
388 "DBIx::Class::Relationship/belongs_to".
389 has_one
391 Arguments: $accessor_name, $related_class,
392 $their_fk_column|\%cond|\@cond|\&cond?, \%attrs?
393 Creates a one-to-one relationship with a class. This relationship
395 defaults to using $accessor_name as the foreign key in $related_class
396 to resolve the join, unless $their_fk_column specifies the foreign key
397 column in $related_class or "cond" specifies a reference to a join
398 condition.
399 accessor_name
401 This argument is the name of the method you can call on a Result
402 object to retrieve the instance of the foreign class matching this
403 relationship. This is often called the "relation(ship) name".
404 Use this accessor_name in "join" in DBIx::Class::ResultSet or
406 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
407 indicated by this relationship.
408 related_class
410 This is the class name of the table which contains a foreign key
411 column containing PK values of this class.
412 their_fk_column
414 The column name on the related class that contains the foreign key.
415 OR
417 cond
419 A hashref, arrayref or coderef specifying a custom join
420 expression. For more info see "condition" in
421 DBIx::Class::Relationship::Base.
422 # Every book has exactly one ISBN
424 My::DBIC::Schema::Book->has_one(
425 isbn =>
426 'My::DBIC::Schema::ISBN',
427 'book_id',
428 );
429 # OR (same result, assuming related_class stores our PK)
431 My::DBIC::Schema::Book->has_one(
432 isbn =>
433 'My::DBIC::Schema::ISBN',
434 );
435 # OR (same result)
437 My::DBIC::Schema::Book->has_one(
438 isbn =>
439 'My::DBIC::Schema::ISBN',
440 { 'foreign.book_id' => 'self.id' },
441 );
442 # Usage
444 my $isbn_obj = $book->isbn; # to get the ISBN object
445 Creates a one-to-one relationship with another class. This is just like
447 "might_have", except the implication is that the other object is always
448 present. The only difference between "has_one" and "might_have" is that
449 "has_one" uses an (ordinary) inner join, whereas "might_have" defaults
450 to a left join.
451 The has_one relationship should be used when a row in the table must
453 have exactly one related row in another table. If the related row might
454 not exist in the foreign table, use the "might_have" in
455 DBIx::Class::Relationship relationship.
456 In the above example, each Book in the database is associated with
458 exactly one ISBN object.
459 See "attributes" in DBIx::Class::Relationship::Base for documentation
461 on relationship methods and valid relationship attributes. Also see
462 DBIx::Class::ResultSet for a list of standard resultset attributes
463 which can be assigned to relationships as well.
464 Note that if you supply a condition on which to join, if the column in
466 the current table allows nulls (i.e., has the "is_nullable" attribute
467 set to a true value), than warnings might apply just as with
468 "might_have" in DBIx::Class::Relationship.
469 many_to_many
471 Arguments: $accessor_name, $link_rel_name, $foreign_rel_name, \%attrs?
472 "many_to_many" is a Relationship bridge which has a specific meaning in
474 DBIx::Class, see the definition in the Glossary.
475 "many_to_many" is not strictly a relationship in its own right.
477 Instead, it is a bridge between two resultsets which provide the same
478 kind of convenience accessors as true relationships provide. Although
479 the accessor will return a resultset or collection of objects just like
480 has_many does, you cannot call "related_resultset" and similar methods
481 which operate on true relationships.
482 accessor_name
484 This argument is the name of the method you can call on a Result
485 object to retrieve the rows matching this relationship.
486 On a many_to_many, unlike other relationships, this cannot be used
488 in "search" in DBIx::Class::ResultSet to join tables. Use the
489 relations bridged across instead.
490 link_rel_name
492 This is the accessor_name from the has_many relationship we are
493 bridging from.
494 foreign_rel_name
496 This is the accessor_name of the belongs_to relationship in the
497 link table that we are bridging across (which gives us the table we
498 are bridging to).
499 To create a many_to_many relationship from Actor to Role:
501 My::DBIC::Schema::Actor->has_many( actor_roles =>
503 'My::DBIC::Schema::ActorRoles',
504 'actor' );
505 My::DBIC::Schema::ActorRoles->belongs_to( role =>
506 'My::DBIC::Schema::Role' );
507 My::DBIC::Schema::ActorRoles->belongs_to( actor =>
508 'My::DBIC::Schema::Actor' );
509 My::DBIC::Schema::Actor->many_to_many( roles => 'actor_roles',
511 'role' );
512 And, for the reverse relationship, from Role to Actor:
514 My::DBIC::Schema::Role->has_many( actor_roles =>
516 'My::DBIC::Schema::ActorRoles',
517 'role' );
518 My::DBIC::Schema::Role->many_to_many( actors => 'actor_roles', 'actor' );
520 To add a role for your actor, and fill in the year of the role in the
522 actor_roles table:
523 $actor->add_to_roles($role, { year => 1995 });
525 In the above example, ActorRoles is the link table class, and Role is
527 the foreign class. The $link_rel_name parameter is the name of the
528 accessor for the has_many relationship from this table to the link
529 table, and the $foreign_rel_name parameter is the accessor for the
530 belongs_to relationship from the link table to the foreign table.
531 To use many_to_many, existing relationships from the original table to
533 the link table, and from the link table to the end table must already
534 exist, these relation names are then used in the many_to_many call.
535 In the above example, the Actor class will have 3 many_to_many accessor
537 methods set: "roles", "add_to_roles", "set_roles", and similarly named
538 accessors will be created for the Role class for the "actors"
539 many_to_many relationship.
540 See "attributes" in DBIx::Class::Relationship::Base for documentation
542 on relationship methods and valid relationship attributes. Also see
543 DBIx::Class::ResultSet for a list of standard resultset attributes
544 which can be assigned to relationships as well.
545
547 Check the list of additional DBIC resources.
548
550 This module is free software copyright by the DBIx::Class (DBIC)
551 authors. You can redistribute it and/or modify it under the same terms
552 as the DBIx::Class library.
553perl v5.30.1 2020-01-29 DBIx::Class::Relationship(3)