1DBIx::Class::RelationshUispe(r3)Contributed Perl DocumenDtBaItxi:o:nClass::Relationship(3)
2
6 DBIx::Class::Relationship - Inter-table relationships
7
9 ## Creating relationships
10 MyDB::Schema::Actor->has_many('actorroles' => 'MyDB::Schema::ActorRole',
11 'actor');
12 MyDB::Schema::Role->has_many('actorroles' => 'MyDB::Schema::ActorRole',
13 'role');
14 MyDB::Schema::ActorRole->belongs_to('role' => 'MyDB::Schema::Role');
15 MyDB::Schema::ActorRole->belongs_to('actor' => 'MyDB::Schema::Actor');
16 MyDB::Schema::Role->many_to_many('actors' => 'actorroles', 'actor');
18 MyDB::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 "Row" in
61 DBIx::Class::Manual::Glossary objects that represent the items of your
62 table. From "ResultSet" in DBIx::Class::Manual::Glossary objects, the
63 relationships can be searched using the "search_related" method. In
64 list context, each returns a list of Row objects for the related class,
65 in scalar context, a new ResultSet representing the joined tables is
66 returned. Thus, the calls can be chained to produce complex queries.
67 Since the database is not actually queried until you attempt to
68 retrieve the data for an actual item, no time is wasted producing 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('relname', 'Foreign::Class', \%cond | \@cond, \%attrs);
94 Both $cond and $attrs are optional. Pass "undef" for $cond if you want
96 to use the default value for it, but still want to set "\%attrs".
97 See DBIx::Class::Relationship::Base for documentation on the attributes
99 that are allowed in the "\%attrs" argument.
100 belongs_to
102 Arguments: $accessor_name, $related_class,
103 $our_fk_column|\%cond|\@cond?, \%attrs?
104 Creates a relationship where the calling class stores the foreign
106 class's primary key in one (or more) of the calling class columns.
107 This relationship defaults to using $accessor_name as the column name
108 in this class to resolve the join against the primary key from
109 $related_class, unless $our_fk_column specifies the foreign key column
110 in this class or "cond" specifies a reference to a join condition hash.
111 accessor_name
113 This argument is the name of the method you can call on a
114 DBIx::Class::Row object to retrieve the instance of the foreign
115 class matching this relationship. This is often called the
116 "relation(ship) name".
117 Use this accessor_name in "join" in DBIx::Class::ResultSet or
119 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
120 indicated by this relationship.
121 related_class
123 This is the class name of the table referenced by the foreign key
124 in this class.
125 our_fk_column
127 The column name on this class that contains the foreign key.
128 OR
130 cond
132 A hashref where the keys are "foreign.$column_on_related_table" and
133 the values are "self.$our_fk_column". This is useful for relations
134 that are across multiple columns.
135 # in a Book class (where Author has many Books)
137 My::DBIC::Schema::Book->belongs_to(
138 author =>
139 'My::DBIC::Schema::Author',
140 'author_id'
141 );
142 # OR (same result)
144 My::DBIC::Schema::Book->belongs_to(
145 author =>
146 'My::DBIC::Schema::Author',
147 { 'foreign.author_id' => 'self.author_id' }
148 );
149 # OR (similar result but uglier accessor name)
151 My::DBIC::Schema::Book->belongs_to(
152 author_id =>
153 'My::DBIC::Schema::Author'
154 );
155 # Usage
157 my $author_obj = $book->author; # get author object
158 $book->author( $new_author_obj ); # set author object
159 $book->author_id(); # get the plain id
160 # To retrieve the plain id if you used the ugly version:
162 $book->get_column('author_id');
163 If the relationship is optional -- i.e. the column containing the
165 foreign key can be NULL -- then the belongs_to relationship does the
166 right thing. Thus, in the example above "$obj->author" would return
167 "undef". However in this case you would probably want to set the
168 "join_type" attribute so that a "LEFT JOIN" is done, which makes
169 complex resultsets involving "join" or "prefetch" operations work
170 correctly. The modified declaration is shown below:
171 # in a Book class (where Author has_many Books)
173 __PACKAGE__->belongs_to(
174 author =>
175 'My::DBIC::Schema::Author',
176 'author',
177 { join_type => 'left' }
178 );
179 Cascading deletes are off by default on a "belongs_to" relationship. To
181 turn them on, pass "cascade_delete => 1" in the $attr hashref.
182 By default, DBIC will return undef and avoid querying the database if a
184 "belongs_to" accessor is called when any part of the foreign key IS
185 NULL. To disable this behavior, pass "undef_on_null_fk => 0" in the
186 $attr hashref.
187 NOTE: If you are used to Class::DBI relationships, this is the
189 equivalent of "has_a".
190 See DBIx::Class::Relationship::Base for documentation on relationship
192 methods and valid relationship attributes. Also see
193 DBIx::Class::ResultSet for a list of standard resultset attributes
194 which can be assigned to relationships as well.
195 has_many
197 Arguments: $accessor_name, $related_class,
198 $their_fk_column|\%cond|\@cond?, \%attrs?
199 Creates a one-to-many relationship where the foreign class refers to
201 this class's primary key. This relationship refers to zero or more
202 records in the foreign table (e.g. a "LEFT JOIN"). This relationship
203 defaults to using the end of this classes namespace as the foreign key
204 in $related_class to resolve the join, unless $their_fk_column
205 specifies the foreign key column in $related_class or "cond" specifies
206 a reference to a join condition hash.
207 accessor_name
209 This argument is the name of the method you can call on a
210 DBIx::Class::Row object to retrieve a resultset of the related
211 class restricted to the ones related to the row object. In list
212 context it returns the row objects. This is often called the
213 "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 where the keys are "foreign.$their_fk_column" and the
230 values are "self.$matching_column". This is useful for relations
231 that are across multiple columns.
232 OR
234 An arrayref containing an SQL::Abstract-like condition. For example
236 a link table where two columns link back to the same table. This is
237 an OR condition.
238 My::Schema::Item->has_many('rels', 'My::Schema::Relationships',
240 [ { 'foreign.LItemID' => 'self.ID' },
241 { 'foreign.RItemID' => 'self.ID'} ]);
242 # in an Author class (where Author has_many Books)
244 # assuming related class is storing our PK in "author_id"
245 My::DBIC::Schema::Author->has_many(
246 books =>
247 'My::DBIC::Schema::Book',
248 'author_id'
249 );
250 # OR (same result)
252 My::DBIC::Schema::Author->has_many(
253 books =>
254 'My::DBIC::Schema::Book',
255 { 'foreign.author_id' => 'self.id' },
256 );
257 # OR (similar result, assuming related_class is storing our PK, in "author")
259 # (the "author" is guessed at from "Author" in the class namespace)
260 My::DBIC::Schema::Author->has_many(
261 books =>
262 'My::DBIC::Schema::Book',
263 );
264 # Usage
267 # resultset of Books belonging to author
268 my $booklist = $author->books;
269 # resultset of Books belonging to author, restricted by author name
271 my $booklist = $author->books({
272 name => { LIKE => '%macaroni%' },
273 { prefetch => [qw/book/],
274 });
275 # array of Book objects belonging to author
277 my @book_objs = $author->books;
278 # force resultset even in list context
280 my $books_rs = $author->books;
281 ( $books_rs ) = $obj->books_rs;
282 # create a new book for this author, the relation fields are auto-filled
284 $author->create_related('books', \%col_data);
285 # alternative method for the above
286 $author->add_to_books(\%col_data);
287 Three methods are created when you create a has_many relationship. The
289 first method is the expected accessor method, "$accessor_name()". The
290 second is almost exactly the same as the accessor method but "_rs" is
291 added to the end of the method name. This method works just like the
292 normal accessor, except that it always returns a resultset, even in
293 list context. The third method, named "add_to_$relname", will also be
294 added to your Row items; this allows you to insert new related items,
295 using the same mechanism as in "create_related" in
296 DBIx::Class::Relationship::Base.
297 If you delete an object in a class with a "has_many" relationship, all
299 the related objects will be deleted as well. To turn this behaviour
300 off, pass "cascade_delete => 0" in the $attr hashref.
301 The cascaded operations are performed after the requested delete or
303 update, so if your database has a constraint on the relationship, it
304 will have deleted/updated the related records or raised an exception
305 before DBIx::Class gets to perform the cascaded operation.
306 If you copy an object in a class with a "has_many" relationship, all
308 the related objects will be copied as well. To turn this behaviour off,
309 pass "cascade_copy => 0" in the $attr hashref. The behaviour defaults
310 to "cascade_copy => 1".
311 See DBIx::Class::Relationship::Base for documentation on relationship
313 methods and valid relationship attributes. Also see
314 DBIx::Class::ResultSet for a list of standard resultset attributes
315 which can be assigned to relationships as well.
316 might_have
318 Arguments: $accessor_name, $related_class,
319 $their_fk_column|\%cond|\@cond?, \%attrs?
320 Creates an optional one-to-one relationship with a class. This
322 relationship defaults to using $accessor_name as the foreign key in
323 $related_class to resolve the join, unless $their_fk_column specifies
324 the foreign key column in $related_class or "cond" specifies a
325 reference to a join condition hash.
326 accessor_name
328 This argument is the name of the method you can call on a
329 DBIx::Class::Row object to retrieve the instance of the foreign
330 class matching this relationship. This is often called the
331 "relation(ship) name".
332 Use this accessor_name in "join" in DBIx::Class::ResultSet or
334 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
335 indicated by this relationship.
336 related_class
338 This is the class name of the table which contains a foreign key
339 column containing PK values of this class.
340 their_fk_column
342 The column name on the related class that contains the foreign key.
343 OR
345 cond
347 A hashref where the keys are "foreign.$their_fk_column" and the
348 values are "self.$matching_column". This is useful for relations
349 that are across multiple columns.
350 # Author may have an entry in the pseudonym table
352 My::DBIC::Schema::Author->might_have(
353 pseudonym =>
354 'My::DBIC::Schema::Pseudonym',
355 'author_id',
356 );
357 # OR (same result, assuming the related_class stores our PK)
359 My::DBIC::Schema::Author->might_have(
360 pseudonym =>
361 'My::DBIC::Schema::Pseudonym',
362 );
363 # OR (same result)
365 My::DBIC::Schema::Author->might_have(
366 pseudonym =>
367 'My::DBIC::Schema::Pseudonym',
368 { 'foreign.author_id' => 'self.id' },
369 );
370 # Usage
372 my $pname = $author->pseudonym; # to get the Pseudonym object
373 If you update or delete an object in a class with a "might_have"
375 relationship, the related object will be updated or deleted as well. To
376 turn off this behavior, add "cascade_delete => 0" to the $attr hashref.
377 The cascaded operations are performed after the requested delete or
379 update, so if your database has a constraint on the relationship, it
380 will have deleted/updated the related records or raised an exception
381 before DBIx::Class gets to perform the cascaded operation.
382 See DBIx::Class::Relationship::Base for documentation on relationship
384 methods and valid relationship attributes. Also see
385 DBIx::Class::ResultSet for a list of standard resultset attributes
386 which can be assigned to relationships as well.
387 Note that if you supply a condition on which to join, if the column in
389 the current table allows nulls (i.e., has the "is_nullable" attribute
390 set to a true value), than "might_have" will warn about this because
391 it's naughty and you shouldn't do that.
392 "might_have/has_one" must not be on columns with is_nullable set to true (MySchema::SomeClass/key)
394 If you must be naughty, you can suppress the warning by setting
396 "DBIC_DONT_VALIDATE_RELS" environment variable to a true value.
397 Otherwise, you probably just want to use
398 "DBIx::Class::Relationship/belongs_to".
399 has_one
401 Arguments: $accessor_name, $related_class,
402 $their_fk_column|\%cond|\@cond?, \%attrs?
403 Creates a one-to-one relationship with a class. This relationship
405 defaults to using $accessor_name as the foreign key in $related_class
406 to resolve the join, unless $their_fk_column specifies the foreign key
407 column in $related_class or "cond" specifies a reference to a join
408 condition hash.
409 accessor_name
411 This argument is the name of the method you can call on a
412 DBIx::Class::Row object to retrieve the instance of the foreign
413 class matching this relationship. This is often called the
414 "relation(ship) name".
415 Use this accessor_name in "join" in DBIx::Class::ResultSet or
417 "prefetch" in DBIx::Class::ResultSet to join to the foreign table
418 indicated by this relationship.
419 related_class
421 This is the class name of the table which contains a foreign key
422 column containing PK values of this class.
423 their_fk_column
425 The column name on the related class that contains the foreign key.
426 OR
428 cond
430 A hashref where the keys are "foreign.$their_fk_column" and the
431 values are "self.$matching_column". This is useful for relations
432 that are across multiple columns.
433 # Every book has exactly one ISBN
435 My::DBIC::Schema::Book->has_one(
436 isbn =>
437 'My::DBIC::Schema::ISBN',
438 'book_id',
439 );
440 # OR (same result, assuming related_class stores our PK)
442 My::DBIC::Schema::Book->has_one(
443 isbn =>
444 'My::DBIC::Schema::ISBN',
445 );
446 # OR (same result)
448 My::DBIC::Schema::Book->has_one(
449 isbn =>
450 'My::DBIC::Schema::ISBN',
451 { 'foreign.book_id' => 'self.id' },
452 );
453 # Usage
455 my $isbn_obj = $book->isbn; # to get the ISBN object
456 Creates a one-to-one relationship with another class. This is just like
458 "might_have", except the implication is that the other object is always
459 present. The only difference between "has_one" and "might_have" is that
460 "has_one" uses an (ordinary) inner join, whereas "might_have" defaults
461 to a left join.
462 The has_one relationship should be used when a row in the table has
464 exactly one related row in another table. If the related row might not
465 exist in the foreign table, use the "might_have" in
466 DBIx::Class::Relationship relationship.
467 In the above example, each Book in the database is associated with
469 exactly one ISBN object.
470 See DBIx::Class::Relationship::Base for documentation on relationship
472 methods and valid relationship attributes. Also see
473 DBIx::Class::ResultSet for a list of standard resultset attributes
474 which can be assigned to relationships as well.
475 Note that if you supply a condition on which to join, if the column in
477 the current table allows nulls (i.e., has the "is_nullable" attribute
478 set to a true value), than warnings might apply just as with
479 "might_have" in DBIx::Class::Relationship.
480 many_to_many
482 Arguments: $accessor_name, $link_rel_name, $foreign_rel_name, \%attrs?
483 "many_to_many" is a Relationship bridge which has a specific meaning in
485 DBIx::Class, see the definition in the Glossary.
486 "many_to_many" is not strictly a relationship in its own right.
488 Instead, it is a bridge between two resultsets which provide the same
489 kind of convenience accessors as true relationships provide. Although
490 the accessor will return a resultset or collection of objects just like
491 has_many does, you cannot call "related_resultset" and similar methods
492 which operate on true relationships.
493 accessor_name
495 This argument is the name of the method you can call on a
496 DBIx::Class::Row object to retrieve the rows matching this
497 relationship.
498 On a many_to_many, unlike other relationships, this cannot be used
500 in "search" in DBIx::Class::ResultSet to join tables. Use the
501 relations bridged across instead.
502 link_rel_name
504 This is the accessor_name from the has_many relationship we are
505 bridging from.
506 foreign_rel_name
508 This is the accessor_name of the belongs_to relationship in the
509 link table that we are bridging across (which gives us the table we
510 are bridging to).
511 To create a many_to_many relationship from Actor to Role:
513 My::DBIC::Schema::Actor->has_many( actor_roles =>
515 'My::DBIC::Schema::ActorRoles',
516 'actor' );
517 My::DBIC::Schema::ActorRoles->belongs_to( role =>
518 'My::DBIC::Schema::Role' );
519 My::DBIC::Schema::ActorRoles->belongs_to( actor =>
520 'My::DBIC::Schema::Actor' );
521 My::DBIC::Schema::Actor->many_to_many( roles => 'actor_roles',
523 'role' );
524 And, for the reverse relationship, from Role to Actor:
526 My::DBIC::Schema::Role->has_many( actor_roles =>
528 'My::DBIC::Schema::ActorRoles',
529 'role' );
530 My::DBIC::Schema::Role->many_to_many( actors => 'actor_roles', 'actor' );
532 To add a role for your actor, and fill in the year of the role in the
534 actor_roles table:
535 $actor->add_to_roles($role, { year => 1995 });
537 In the above example, ActorRoles is the link table class, and Role is
539 the foreign class. The $link_rel_name parameter is the name of the
540 accessor for the has_many relationship from this table to the link
541 table, and the $foreign_rel_name parameter is the accessor for the
542 belongs_to relationship from the link table to the foreign table.
543 To use many_to_many, existing relationships from the original table to
545 the link table, and from the link table to the end table must already
546 exist, these relation names are then used in the many_to_many call.
547 In the above example, the Actor class will have 3 many_to_many accessor
549 methods set: "roles", "add_to_roles", "set_roles", and similarly named
550 accessors will be created for the Role class for the "actors"
551 many_to_many relationship.
552 See DBIx::Class::Relationship::Base for documentation on relationship
554 methods and valid relationship attributes. Also see
555 DBIx::Class::ResultSet for a list of standard resultset attributes
556 which can be assigned to relationships as well.
557
559 see DBIx::Class
560
562 You may distribute this code under the same terms as Perl itself.
563perl v5.12.0 2010-05-12 DBIx::Class::Relationship(3)