1 package DBIx::Class::Relationship;
6 use base qw/DBIx::Class/;
8 __PACKAGE__->load_own_components(qw/
18 DBIx::Class::Relationship - Inter-table relationships
22 ## Creating relationships
23 MyDB::Schema::Actor->has_many('actorroles' => 'MyDB::Schema::ActorRole',
25 MyDB::Schema::Role->has_many('actorroles' => 'MyDB::Schema::ActorRole',
27 MyDB::Schema::ActorRole->belongs_to('role' => 'MyDB::Schema::Role');
28 MyDB::Schema::ActorRole->belongs_to('actor' => 'MyDB::Schema::Actor');
30 MyDB::Schema::Role->many_to_many('actors' => 'actorroles', 'actor');
31 MyDB::Schema::Actor->many_to_many('roles' => 'actorroles', 'role');
33 ## Using relationships
34 $schema->resultset('Actor')->find({ id => 1})->roles();
35 $schema->resultset('Role')->find({ id => 1 })->actorroles->search_related('actor', { Name => 'Fred' });
36 $schema->resultset('Actor')->add_to_roles({ Name => 'Sherlock Holmes'});
38 See L<DBIx::Class::Manual::Cookbook> for more.
42 This class provides methods to set up relationships between the tables
43 in your database model. Relationships are the most useful and powerful
44 technique that L<DBIx::Class> provides. To create efficient database queries,
45 create relationships between any and all tables that have something in
46 common, for example if you have a table Authors:
57 1 | 1 | Rulers of the universe
58 2 | 1 | Rulers of the galaxy
60 Then without relationships, the method of getting all books by Fred goes like
63 my $fred = $schema->resultset('Author')->find({ Name => 'Fred' });
64 my $fredsbooks = $schema->resultset('Book')->search({ Author => $fred->ID });
66 With a has_many relationship called "books" on Author (see below for details),
67 we can do this instead:
69 my $fredsbooks = $schema->resultset('Author')->find({ Name => 'Fred' })->books;
71 Each relationship sets up an accessor method on the
72 L<DBIx::Class::Manual::Glossary/"Row"> objects that represent the items
73 of your table. From L<DBIx::Class::Manual::Glossary/"ResultSet"> objects,
74 the relationships can be searched using the "search_related" method.
75 In list context, each returns a list of Row objects for the related class,
76 in scalar context, a new ResultSet representing the joined tables is
77 returned. Thus, the calls can be chained to produce complex queries.
78 Since the database is not actually queried until you attempt to retrieve
79 the data for an actual item, no time is wasted producing them.
81 my $cheapfredbooks = $schema->resultset('Author')->find({
83 })->books->search_related('prices', {
84 Price => { '<=' => '5.00' },
87 will produce a query something like:
89 SELECT * FROM Author me
90 LEFT JOIN Books books ON books.author = me.id
91 LEFT JOIN Prices prices ON prices.book = books.id
92 WHERE prices.Price <= 5.00
94 all without needing multiple fetches.
96 Only the helper methods for setting up standard relationship types
97 are documented here. For the basic, lower-level methods, and a description
98 of all the useful *_related methods that you get for free, see
99 L<DBIx::Class::Relationship::Base>.
103 All helper methods are called similar to the following template:
105 __PACKAGE__->$method_name('relname', 'Foreign::Class', $cond, $attrs);
107 Both C<$cond> and C<$attrs> are optional. Pass C<undef> for C<$cond> if
108 you want to use the default value for it, but still want to set C<$attrs>.
110 See L<DBIx::Class::Relationship::Base> for documentation on the
111 attrubutes that are allowed in the C<$attrs> argument.
118 =item Arguments: $accessor_name, $related_class, $our_fk_column|\%cond|\@cond?, \%attr?
122 Creates a relationship where the calling class stores the foreign
123 class's primary key in one (or more) of the calling class columns.
124 This relationship defaults to using C<$accessor_name> as the column
125 name in this class to resolve the join against the primary key from
126 C<$related_class>, unless C<$our_fk_column> specifies the foreign key column
127 in this class or C<cond> specifies a reference to a join condition hash.
133 This argument is the name of the method you can call on a
134 L<DBIx::Class::Row> object to retrieve the instance of the foreign
135 class matching this relationship. This is often called the
136 C<relation(ship) name>.
138 Use this accessor_name in L<DBIx::Class::ResultSet/join>
139 or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
140 indicated by this relationship.
144 This is the class name of the table referenced by the foreign key in
149 The column name on this class that contains the foreign key.
155 A hashref where the keys are C<foreign.$column_on_related_table> and
156 the values are C<self.$our_fk_column>. This is useful for
157 relations that are across multiple columns.
162 # in a Book class (where Author has many Books)
163 My::DBIC::Schema::Book->belongs_to(
165 'My::DBIC::Schema::Author',
170 My::DBIC::Schema::Book->belongs_to(
172 'My::DBIC::Schema::Author',
173 { 'foreign.author_id' => 'self.author_id' }
176 # OR (similar result but uglier accessor name)
177 My::DBIC::Schema::Book->belongs_to(
179 'My::DBIC::Schema::Author'
183 my $author_obj = $book->author; # get author object
184 $book->author( $new_author_obj ); # set author object
185 $book->author_id(); # get the plain id
187 # To retrieve the plain id if you used the ugly version:
188 $book->get_column('author_id');
191 If the relationship is optional -- i.e. the column containing the foreign key
192 can be NULL -- then the belongs_to relationship does the right thing. Thus, in
193 the example above C<$obj-E<gt>author> would return C<undef>. However in this
194 case you would probably want to set the C<join_type> attribute so that a C<LEFT
195 JOIN> is done, which makes complex resultsets involving C<join> or C<prefetch>
196 operations work correctly. The modified declaration is shown below:
198 # in a Book class (where Author has_many Books)
199 __PACKAGE__->belongs_to(
201 'My::DBIC::Schema::Author',
203 { join_type => 'left' }
207 Cascading deletes are off by default on a C<belongs_to>
208 relationship. To turn them on, pass C<< cascade_delete => 1 >>
209 in the $attr hashref.
211 By default, DBIC will attempt to query the related table for a row when the
212 relationship accessor is called even if a foreign key member column IS NULL,
213 which can be wasteful. To avoid this query from being performed, pass
214 C<< any_null_means_no_value => 1 >> in the C<$attr> hashref. This only applies
215 to accessors of type 'single' (when your accessor and foreign key have
216 different names e.g. 'cd_id', and 'cd').
218 NOTE: If you are used to L<Class::DBI> relationships, this is the equivalent
221 See L<DBIx::Class::Relationship::Base> for documentation on relationship
222 methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
223 for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
224 which can be assigned to relationships as well.
230 =item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
234 Creates a one-to-many relationship, where the corresponding elements
235 of the foreign class store the calling class's primary key in one (or
236 more) of the foreign class columns. This relationship defaults to using
237 the end of this classes namespace as the foreign key in C<$related_class>
238 to resolve the join, unless C<$their_fk_column> specifies the foreign
239 key column in C<$related_class> or C<cond> specifies a reference to a
246 This argument is the name of the method you can call on a
247 L<DBIx::Class::Row> object to retrieve a resultset of the related
248 class restricted to the ones related to the row object. In list
249 context it returns the row objects. This is often called the
250 C<relation(ship) name>.
252 Use this accessor_name in L<DBIx::Class::ResultSet/join>
253 or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
254 indicated by this relationship.
258 This is the class name of the table which contains a foreign key
259 column containing PK values of this class.
261 =item their_fk_column
263 The column name on the related class that contains the foreign key.
269 A hashref where the keys are C<foreign.$their_fk_column> and
270 the values are C<self.$matching_column>. This is useful for
271 relations that are across multiple columns.
275 An arrayref containing an SQL::Abstract-like condition. For example a
276 link table where two columns link back to the same table. This is an
279 My::Schema::Item->has_many('rels', 'My::Schema::Relationships',
280 [ { 'foreign.LItemID' => 'self.ID' },
281 { 'foreign.RItemID' => 'self.ID'} ]);
285 # in an Author class (where Author has_many Books)
286 # assuming related class is storing our PK in "author_id"
287 My::DBIC::Schema::Author->has_many(
289 'My::DBIC::Schema::Book',
294 My::DBIC::Schema::Author->has_many(
296 'My::DBIC::Schema::Book',
297 { 'foreign.author_id' => 'self.id' },
300 # OR (similar result, assuming related_class is storing our PK, in "author")
301 # (the "author" is guessed at from "Author" in the class namespace)
302 My::DBIC::Schema::Author->has_many(
304 'My::DBIC::Schema::Book',
309 # resultset of Books belonging to author
310 my $booklist = $author->books;
312 # resultset of Books belonging to author, restricted by author name
313 my $booklist = $author->books({
314 name => { LIKE => '%macaroni%' },
315 { prefetch => [qw/book/],
318 # array of Book objects belonging to author
319 my @book_objs = $author->books;
321 # force resultset even in list context
322 my $books_rs = $author->books;
323 ( $books_rs ) = $obj->books_rs;
325 # create a new book for this author, the relation fields are auto-filled
326 $author->create_related('books', \%col_data);
327 # alternative method for the above
328 $author->add_to_books(\%col_data);
331 Three methods are created when you create a has_many relationship. The first
332 method is the expected accessor method, C<$accessor_name()>. The second is
333 almost exactly the same as the accessor method but "_rs" is added to the end of
334 the method name. This method works just like the normal accessor, except that
335 it always returns a resultset, even in list context. The third method,
336 named C<< add_to_$relname >>, will also be added to your Row items; this
337 allows you to insert new related items, using the same mechanism as in
338 L<DBIx::Class::Relationship::Base/"create_related">.
340 If you delete an object in a class with a C<has_many> relationship, all
341 the related objects will be deleted as well. To turn this behaviour off,
342 pass C<< cascade_delete => 0 >> in the C<$attr> hashref.
344 The cascaded operations are performed after the requested delete or
345 update, so if your database has a constraint on the relationship, it
346 will have deleted/updated the related records or raised an exception
347 before DBIx::Class gets to perform the cascaded operation.
349 If you copy an object in a class with a C<has_many> relationship, all
350 the related objects will be copied as well. To turn this behaviour off,
351 pass C<< cascade_copy => 0 >> in the C<$attr> hashref. The behaviour
352 defaults to C<< cascade_copy => 1 >>.
354 See L<DBIx::Class::Relationship::Base> for documentation on relationship
355 methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
356 for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
357 which can be assigned to relationships as well.
363 =item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
367 Creates an optional one-to-one relationship with a class. This relationship
368 defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
369 resolve the join, unless C<$their_fk_column> specifies the foreign key
370 column in C<$related_class> or C<cond> specifies a reference to a join
377 This argument is the name of the method you can call on a
378 L<DBIx::Class::Row> object to retrieve the instance of the foreign
379 class matching this relationship. This is often called the
380 C<relation(ship) name>.
382 Use this accessor_name in L<DBIx::Class::ResultSet/join>
383 or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
384 indicated by this relationship.
388 This is the class name of the table which contains a foreign key
389 column containing PK values of this class.
391 =item their_fk_column
393 The column name on the related class that contains the foreign key.
399 A hashref where the keys are C<foreign.$their_fk_column> and
400 the values are C<self.$matching_column>. This is useful for
401 relations that are across multiple columns.
405 # Author may have an entry in the pseudonym table
406 My::DBIC::Schema::Author->might_have(
408 'My::DBIC::Schema::Pseudonym',
412 # OR (same result, assuming the related_class stores our PK)
413 My::DBIC::Schema::Author->might_have(
415 'My::DBIC::Schema::Pseudonym',
419 My::DBIC::Schema::Author->might_have(
421 'My::DBIC::Schema::Pseudonym',
422 { 'foreign.author_id' => 'self.id' },
426 my $pname = $author->pseudonym; # to get the Pseudonym object
428 If you update or delete an object in a class with a C<might_have>
429 relationship, the related object will be updated or deleted as well. To
430 turn off this behavior, add C<< cascade_delete => 0 >> to the C<$attr>
433 The cascaded operations are performed after the requested delete or
434 update, so if your database has a constraint on the relationship, it
435 will have deleted/updated the related records or raised an exception
436 before DBIx::Class gets to perform the cascaded operation.
438 See L<DBIx::Class::Relationship::Base> for documentation on relationship
439 methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
440 for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
441 which can be assigned to relationships as well.
447 =item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
451 Creates a one-to-one relationship with a class. This relationship
452 defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
453 resolve the join, unless C<$their_fk_column> specifies the foreign key
454 column in C<$related_class> or C<cond> specifies a reference to a join
461 This argument is the name of the method you can call on a
462 L<DBIx::Class::Row> object to retrieve the instance of the foreign
463 class matching this relationship. This is often called the
464 C<relation(ship) name>.
466 Use this accessor_name in L<DBIx::Class::ResultSet/join>
467 or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
468 indicated by this relationship.
472 This is the class name of the table which contains a foreign key
473 column containing PK values of this class.
475 =item their_fk_column
477 The column name on the related class that contains the foreign key.
483 A hashref where the keys are C<foreign.$their_fk_column> and
484 the values are C<self.$matching_column>. This is useful for
485 relations that are across multiple columns.
489 # Every book has exactly one ISBN
490 My::DBIC::Schema::Book->has_one(
492 'My::DBIC::Schema::ISBN',
496 # OR (same result, assuming related_class stores our PK)
497 My::DBIC::Schema::Book->has_one(
499 'My::DBIC::Schema::ISBN',
503 My::DBIC::Schema::Book->has_one(
505 'My::DBIC::Schema::ISBN',
506 { 'foreign.book_id' => 'self.id' },
510 my $isbn_obj = $book->isbn; # to get the ISBN object
512 Creates a one-to-one relationship with another class. This is just
513 like C<might_have>, except the implication is that the other object is
514 always present. The only difference between C<has_one> and
515 C<might_have> is that C<has_one> uses an (ordinary) inner join,
516 whereas C<might_have> defaults to a left join.
518 The has_one relationship should be used when a row in the table has exactly one
519 related row in another table. If the related row might not exist in the foreign
520 table, use the L<DBIx::Class::Relationship/might_have> relationship.
522 In the above example, each Book in the database is associated with exactly one
525 See L<DBIx::Class::Relationship::Base> for documentation on relationship
526 methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
527 for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
528 which can be assigned to relationships as well.
534 =item Arguments: $accessor_name, $link_rel_name, $foreign_rel_name, \%attr?
538 C<many_to_many> is not strictly a relationship in its own right. Instead, it is
539 a bridge between two resultsets which provide the same kind of convenience
540 accessors as true relationships provide. Although the accessor will return a
541 resultset or collection of objects just like has_many does, you cannot call
542 C<related_resultset> and similar methods which operate on true relationships.
548 This argument is the name of the method you can call on a
549 L<DBIx::Class::Row> object to retrieve the rows matching this
552 On a many_to_many, unlike other relationships, this cannot be used in
553 L<DBIx::Class::ResultSet/search> to join tables. Use the relations
554 bridged across instead.
558 This is the accessor_name from the has_many relationship we are
561 =item foreign_rel_name
563 This is the accessor_name of the belongs_to relationship in the link
564 table that we are bridging across (which gives us the table we are
569 To create a many_to_many relationship from Actor to Role:
571 My::DBIC::Schema::Actor->has_many( actor_roles =>
572 'My::DBIC::Schema::ActorRoles',
574 My::DBIC::Schema::ActorRoles->belongs_to( role =>
575 'My::DBIC::Schema::Role' );
576 My::DBIC::Schema::ActorRoles->belongs_to( actor =>
577 'My::DBIC::Schema::Actor' );
579 My::DBIC::Schema::Actor->many_to_many( roles => 'actor_roles',
582 And, for the reverse relationship, from Role to Actor:
584 My::DBIC::Schema::Role->has_many( actor_roles =>
585 'My::DBIC::Schema::ActorRoles',
588 My::DBIC::Schema::Role->many_to_many( actors => 'actor_roles', 'actor' );
590 To add a role for your actor, and fill in the year of the role in the
593 $actor->add_to_roles($role, { year => 1995 });
595 In the above example, ActorRoles is the link table class, and Role is the
596 foreign class. The C<$link_rel_name> parameter is the name of the accessor for
597 the has_many relationship from this table to the link table, and the
598 C<$foreign_rel_name> parameter is the accessor for the belongs_to relationship
599 from the link table to the foreign table.
601 To use many_to_many, existing relationships from the original table to the link
602 table, and from the link table to the end table must already exist, these
603 relation names are then used in the many_to_many call.
605 In the above example, the Actor class will have 3 many_to_many accessor methods
606 set: C<roles>, C<add_to_roles>, C<set_roles>, and similarly named accessors
607 will be created for the Role class for the C<actors> many_to_many
610 See L<DBIx::Class::Relationship::Base> for documentation on relationship
611 methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
612 for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
613 which can be assigned to relationships as well.
625 You may distribute this code under the same terms as Perl itself.