Merge 'trunk' into 'sqla_1.50_compat'
[dbsrgits/DBIx-Class-Historic.git] / lib / DBIx / Class / Relationship.pm
CommitLineData
b8e1e21f 1package DBIx::Class::Relationship;
2
3use strict;
4use warnings;
5
1edd1722 6use base qw/DBIx::Class/;
55e2d745 7
07037f89 8__PACKAGE__->load_own_components(qw/
7411204b 9 Helpers
07037f89 10 Accessor
11 CascadeActions
12 ProxyMethods
13 Base
14/);
b8e1e21f 15
75d07914 16=head1 NAME
34d52be2 17
18DBIx::Class::Relationship - Inter-table relationships
19
20=head1 SYNOPSIS
21
951ab5ab 22 ## Creating relationships
75d07914 23 MyDB::Schema::Actor->has_many('actorroles' => 'MyDB::Schema::ActorRole',
d2113a68 24 'actor');
75d07914 25 MyDB::Schema::Role->has_many('actorroles' => 'MyDB::Schema::ActorRole',
d2113a68 26 'role');
27 MyDB::Schema::ActorRole->belongs_to('role' => 'MyDB::Schema::Role');
28 MyDB::Schema::ActorRole->belongs_to('actor' => 'MyDB::Schema::Actor');
29
30 MyDB::Schema::Role->many_to_many('actors' => 'actorroles', 'actor');
31 MyDB::Schema::Actor->many_to_many('roles' => 'actorroles', 'role');
32
951ab5ab 33 ## Using relationships
c9070342 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'});
d2113a68 37
38See L<DBIx::Class::Manual::Cookbook> for more.
39
34d52be2 40=head1 DESCRIPTION
41
bc1171c3 42This class provides methods to set up relationships between the tables
43in your database model. Relationships are the most useful and powerful
44technique that L<DBIx::Class> provides. To create efficient database queries,
45create relationships between any and all tables that have something in
46common, for example if you have a table Authors:
47
48 ID | Name | Age
49 ------------------
50 1 | Fred | 30
51 2 | Joe | 32
52
53and a table Books:
54
55 ID | Author | Name
56 --------------------
57 1 | 1 | Rulers of the universe
58 2 | 1 | Rulers of the galaxy
59
60Then without relationships, the method of getting all books by Fred goes like
61this:
62
63 my $fred = $schema->resultset('Author')->find({ Name => 'Fred' });
64 my $fredsbooks = $schema->resultset('Book')->search({ Author => $fred->ID });
2f0790c4 65
bc1171c3 66With a has_many relationship called "books" on Author (see below for details),
67we can do this instead:
68
69 my $fredsbooks = $schema->resultset('Author')->find({ Name => 'Fred' })->books;
70
75d07914 71Each relationship sets up an accessor method on the
bc1171c3 72L<DBIx::Class::Manual::Glossary/"Row"> objects that represent the items
73of your table. From L<DBIx::Class::Manual::Glossary/"ResultSet"> objects,
75d07914 74the relationships can be searched using the "search_related" method.
bc1171c3 75In list context, each returns a list of Row objects for the related class,
76in scalar context, a new ResultSet representing the joined tables is
77returned. Thus, the calls can be chained to produce complex queries.
78Since the database is not actually queried until you attempt to retrieve
79the data for an actual item, no time is wasted producing them.
80
bc0c9800 81 my $cheapfredbooks = $schema->resultset('Author')->find({
82 Name => 'Fred',
83 })->books->search_related('prices', {
84 Price => { '<=' => '5.00' },
85 });
bc1171c3 86
87will produce a query something like:
88
75d07914 89 SELECT * FROM Author me
bc1171c3 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
93
94all without needing multiple fetches.
34d52be2 95
bfab575a 96Only the helper methods for setting up standard relationship types
d2113a68 97are documented here. For the basic, lower-level methods, and a description
98of all the useful *_related methods that you get for free, see
bfab575a 99L<DBIx::Class::Relationship::Base>.
503536d5 100
34d52be2 101=head1 METHODS
102
8457faf7 103All helper methods are called similar to the following template:
503536d5 104
8457faf7 105 __PACKAGE__->$method_name('relname', 'Foreign::Class', $cond, $attrs);
bfab575a 106
107Both C<$cond> and C<$attrs> are optional. Pass C<undef> for C<$cond> if
108you want to use the default value for it, but still want to set C<$attrs>.
2535b501 109
8457faf7 110See L<DBIx::Class::Relationship::Base> for documentation on the
111attrubutes that are allowed in the C<$attrs> argument.
112
503536d5 113
bfab575a 114=head2 belongs_to
503536d5 115
2f3105ce 116=over 4
117
b0acf5d8 118=item Arguments: $accessor_name, $related_class, $our_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 119
120=back
121
951ab5ab 122Creates a relationship where the calling class stores the foreign
b0acf5d8 123class's primary key in one (or more) of the calling class columns.
124This relationship defaults to using C<$accessor_name> as the column
125name in this class to resolve the join against the primary key from
126C<$related_class>, unless C<$our_fk_column> specifies the foreign key column
127in this class or C<cond> specifies a reference to a join condition hash.
7a2c1380 128
129=over
130
131=item accessor_name
132
133This argument is the name of the method you can call on a
134L<DBIx::Class::Row> object to retrieve the instance of the foreign
8457faf7 135class matching this relationship. This is often called the
136C<relation(ship) name>.
7a2c1380 137
8457faf7 138Use this accessor_name in L<DBIx::Class::ResultSet/join>
7a2c1380 139or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
140indicated by this relationship.
141
142=item related_class
143
144This is the class name of the table referenced by the foreign key in
145this class.
146
b0acf5d8 147=item our_fk_column
7a2c1380 148
149The column name on this class that contains the foreign key.
150
151OR
152
153=item cond
154
155A hashref where the keys are C<foreign.$column_on_related_table> and
b0acf5d8 156the values are C<self.$our_fk_column>. This is useful for
7a2c1380 157relations that are across multiple columns.
158
159=back
160
161
c99393ff 162 # in a Book class (where Author has many Books)
951ab5ab 163 My::DBIC::Schema::Book->belongs_to(
164 author =>
165 'My::DBIC::Schema::Author',
166 'author_id'
167 );
168
169 # OR (same result)
170 My::DBIC::Schema::Book->belongs_to(
171 author =>
172 'My::DBIC::Schema::Author',
173 { 'foreign.author_id' => 'self.author_id' }
174 );
175
176 # OR (similar result but uglier accessor name)
177 My::DBIC::Schema::Book->belongs_to(
178 author_id =>
179 'My::DBIC::Schema::Author'
180 );
181
182 # Usage
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
186
187 # To retrieve the plain id if you used the ugly version:
188 $book->get_column('author_id');
2535b501 189
503536d5 190
2f3105ce 191If the relationship is optional -- i.e. the column containing the foreign key
192can be NULL -- then the belongs_to relationship does the right thing. Thus, in
193the example above C<$obj-E<gt>author> would return C<undef>. However in this
194case you would probably want to set the C<join_type> attribute so that a C<LEFT
195JOIN> is done, which makes complex resultsets involving C<join> or C<prefetch>
196operations work correctly. The modified declaration is shown below:
2c3ad870 197
b8810cc5 198 # in a Book class (where Author has_many Books)
951ab5ab 199 __PACKAGE__->belongs_to(
200 author =>
201 'My::DBIC::Schema::Author',
202 'author',
203 { join_type => 'left' }
204 );
2c3ad870 205
206
b8810cc5 207Cascading deletes are off by default on a C<belongs_to>
208relationship. To turn them on, pass C<< cascade_delete => 1 >>
209in the $attr hashref.
e8e9e5c7 210
cef1bdda 211By default, DBIC will return undef and avoid querying the database if a
212C<belongs_to> accessor is called when any part of the foreign key IS NULL. To
213disable this behavior, pass C<< undef_on_null_fk => 0 >> in the C<$attr>
214hashref.
c89815db 215
8091aa91 216NOTE: If you are used to L<Class::DBI> relationships, this is the equivalent
217of C<has_a>.
503536d5 218
9e64dfbf 219See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 220methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
221for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
222which can be assigned to relationships as well.
2535b501 223
bfab575a 224=head2 has_many
503536d5 225
2f3105ce 226=over 4
227
b0acf5d8 228=item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 229
230=back
231
e951858e 232Creates a one-to-many relationship, where the corresponding elements
233of the foreign class store the calling class's primary key in one (or
b0acf5d8 234more) of the foreign class columns. This relationship defaults to using
235the end of this classes namespace as the foreign key in C<$related_class>
236to resolve the join, unless C<$their_fk_column> specifies the foreign
e951858e 237key column in C<$related_class> or C<cond> specifies a reference to a
238join condition hash.
7a2c1380 239
240=over
241
242=item accessor_name
243
244This argument is the name of the method you can call on a
245L<DBIx::Class::Row> object to retrieve a resultset of the related
246class restricted to the ones related to the row object. In list
951ab5ab 247context it returns the row objects. This is often called the
248C<relation(ship) name>.
7a2c1380 249
951ab5ab 250Use this accessor_name in L<DBIx::Class::ResultSet/join>
7a2c1380 251or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
252indicated by this relationship.
253
254=item related_class
255
256This is the class name of the table which contains a foreign key
257column containing PK values of this class.
258
b0acf5d8 259=item their_fk_column
7a2c1380 260
261The column name on the related class that contains the foreign key.
262
263OR
264
265=item cond
266
b0acf5d8 267A hashref where the keys are C<foreign.$their_fk_column> and
951ab5ab 268the values are C<self.$matching_column>. This is useful for
7a2c1380 269relations that are across multiple columns.
270
951ab5ab 271OR
272
273An arrayref containing an SQL::Abstract-like condition. For example a
274link table where two columns link back to the same table. This is an
275OR condition.
276
277 My::Schema::Item->has_many('rels', 'My::Schema::Relationships',
278 [ { 'foreign.LItemID' => 'self.ID' },
279 { 'foreign.RItemID' => 'self.ID'} ]);
da2e2b76 280
7a2c1380 281=back
282
b8810cc5 283 # in an Author class (where Author has_many Books)
e951858e 284 # assuming related class is storing our PK in "author_id"
951ab5ab 285 My::DBIC::Schema::Author->has_many(
286 books =>
287 'My::DBIC::Schema::Book',
288 'author_id'
289 );
290
e951858e 291 # OR (same result)
951ab5ab 292 My::DBIC::Schema::Author->has_many(
293 books =>
294 'My::DBIC::Schema::Book',
e951858e 295 { 'foreign.author_id' => 'self.id' },
951ab5ab 296 );
e951858e 297
298 # OR (similar result, assuming related_class is storing our PK, in "author")
299 # (the "author" is guessed at from "Author" in the class namespace)
951ab5ab 300 My::DBIC::Schema::Author->has_many(
301 books =>
302 'My::DBIC::Schema::Book',
951ab5ab 303 );
e951858e 304
2535b501 305
951ab5ab 306 # Usage
307 # resultset of Books belonging to author
308 my $booklist = $author->books;
309
310 # resultset of Books belonging to author, restricted by author name
311 my $booklist = $author->books({
bc0c9800 312 name => { LIKE => '%macaroni%' },
313 { prefetch => [qw/book/],
314 });
503536d5 315
951ab5ab 316 # array of Book objects belonging to author
317 my @book_objs = $author->books;
503536d5 318
951ab5ab 319 # force resultset even in list context
320 my $books_rs = $author->books;
321 ( $books_rs ) = $obj->books_rs;
322
323 # create a new book for this author, the relation fields are auto-filled
324 $author->create_related('books', \%col_data);
325 # alternative method for the above
326 $author->add_to_books(\%col_data);
2535b501 327
2535b501 328
60a8fb95 329Three methods are created when you create a has_many relationship. The first
2f3105ce 330method is the expected accessor method, C<$accessor_name()>. The second is
331almost exactly the same as the accessor method but "_rs" is added to the end of
332the method name. This method works just like the normal accessor, except that
951ab5ab 333it always returns a resultset, even in list context. The third method,
2535b501 334named C<< add_to_$relname >>, will also be added to your Row items; this
2f3105ce 335allows you to insert new related items, using the same mechanism as in
5b89a768 336L<DBIx::Class::Relationship::Base/"create_related">.
d2113a68 337
8091aa91 338If you delete an object in a class with a C<has_many> relationship, all
b8810cc5 339the related objects will be deleted as well. To turn this behaviour off,
2a2ab6ab 340pass C<< cascade_delete => 0 >> in the C<$attr> hashref.
341
342The cascaded operations are performed after the requested delete or
343update, so if your database has a constraint on the relationship, it
344will have deleted/updated the related records or raised an exception
345before DBIx::Class gets to perform the cascaded operation.
503536d5 346
f4e92c39 347If you copy an object in a class with a C<has_many> relationship, all
348the related objects will be copied as well. To turn this behaviour off,
2fef093d 349pass C<< cascade_copy => 0 >> in the C<$attr> hashref. The behaviour
350defaults to C<< cascade_copy => 1 >>.
f4e92c39 351
9e64dfbf 352See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 353methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
354for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
355which can be assigned to relationships as well.
2535b501 356
bfab575a 357=head2 might_have
503536d5 358
2f3105ce 359=over 4
360
b0acf5d8 361=item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 362
363=back
364
7a2c1380 365Creates an optional one-to-one relationship with a class. This relationship
366defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
b0acf5d8 367resolve the join, unless C<$their_fk_column> specifies the foreign key
951ab5ab 368column in C<$related_class> or C<cond> specifies a reference to a join
7a2c1380 369condition hash.
370
371=over
372
373=item accessor_name
374
375This argument is the name of the method you can call on a
376L<DBIx::Class::Row> object to retrieve the instance of the foreign
951ab5ab 377class matching this relationship. This is often called the
378C<relation(ship) name>.
7a2c1380 379
951ab5ab 380Use this accessor_name in L<DBIx::Class::ResultSet/join>
7a2c1380 381or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
382indicated by this relationship.
383
384=item related_class
385
386This is the class name of the table which contains a foreign key
387column containing PK values of this class.
388
b0acf5d8 389=item their_fk_column
7a2c1380 390
391The column name on the related class that contains the foreign key.
392
393OR
394
395=item cond
396
b0acf5d8 397A hashref where the keys are C<foreign.$their_fk_column> and
b281b5e9 398the values are C<self.$matching_column>. This is useful for
7a2c1380 399relations that are across multiple columns.
400
401=back
402
951ab5ab 403 # Author may have an entry in the pseudonym table
404 My::DBIC::Schema::Author->might_have(
405 pseudonym =>
406 'My::DBIC::Schema::Pseudonym',
407 'author_id',
408 );
409
410 # OR (same result, assuming the related_class stores our PK)
411 My::DBIC::Schema::Author->might_have(
412 pseudonym =>
413 'My::DBIC::Schema::Pseudonym',
414 );
415
416 # OR (same result)
417 My::DBIC::Schema::Author->might_have(
418 pseudonym =>
419 'My::DBIC::Schema::Pseudonym',
420 { 'foreign.author_id' => 'self.id' },
421 );
422
423 # Usage
424 my $pname = $author->pseudonym; # to get the Pseudonym object
9e64dfbf 425
c99393ff 426If you update or delete an object in a class with a C<might_have>
b8810cc5 427relationship, the related object will be updated or deleted as well. To
428turn off this behavior, add C<< cascade_delete => 0 >> to the C<$attr>
2a2ab6ab 429hashref.
430
431The cascaded operations are performed after the requested delete or
432update, so if your database has a constraint on the relationship, it
433will have deleted/updated the related records or raised an exception
434before DBIx::Class gets to perform the cascaded operation.
503536d5 435
9e64dfbf 436See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 437methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
438for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
439which can be assigned to relationships as well.
2f3105ce 440
bfab575a 441=head2 has_one
442
2f3105ce 443=over 4
444
b0acf5d8 445=item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 446
447=back
448
951ab5ab 449Creates a one-to-one relationship with a class. This relationship
450defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
b0acf5d8 451resolve the join, unless C<$their_fk_column> specifies the foreign key
951ab5ab 452column in C<$related_class> or C<cond> specifies a reference to a join
453condition hash.
2f3105ce 454
951ab5ab 455=over
456
457=item accessor_name
458
459This argument is the name of the method you can call on a
460L<DBIx::Class::Row> object to retrieve the instance of the foreign
461class matching this relationship. This is often called the
462C<relation(ship) name>.
463
464Use this accessor_name in L<DBIx::Class::ResultSet/join>
465or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
466indicated by this relationship.
467
468=item related_class
469
470This is the class name of the table which contains a foreign key
471column containing PK values of this class.
472
b0acf5d8 473=item their_fk_column
951ab5ab 474
475The column name on the related class that contains the foreign key.
476
477OR
478
479=item cond
480
b0acf5d8 481A hashref where the keys are C<foreign.$their_fk_column> and
b281b5e9 482the values are C<self.$matching_column>. This is useful for
951ab5ab 483relations that are across multiple columns.
484
485=back
bfab575a 486
951ab5ab 487 # Every book has exactly one ISBN
488 My::DBIC::Schema::Book->has_one(
489 isbn =>
490 'My::DBIC::Schema::ISBN',
491 'book_id',
492 );
493
494 # OR (same result, assuming related_class stores our PK)
495 My::DBIC::Schema::Book->has_one(
496 isbn =>
497 'My::DBIC::Schema::ISBN',
498 );
499
500 # OR (same result)
501 My::DBIC::Schema::Book->has_one(
502 isbn =>
503 'My::DBIC::Schema::ISBN',
504 { 'foreign.book_id' => 'self.id' },
505 );
506
507 # Usage
508 my $isbn_obj = $book->isbn; # to get the ISBN object
509
510Creates a one-to-one relationship with another class. This is just
511like C<might_have>, except the implication is that the other object is
512always present. The only difference between C<has_one> and
513C<might_have> is that C<has_one> uses an (ordinary) inner join,
514whereas C<might_have> defaults to a left join.
503536d5 515
2f3105ce 516The has_one relationship should be used when a row in the table has exactly one
517related row in another table. If the related row might not exist in the foreign
518table, use the L<DBIx::Class::Relationship/might_have> relationship.
519
520In the above example, each Book in the database is associated with exactly one
521ISBN object.
7411204b 522
9e64dfbf 523See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 524methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
525for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
526which can be assigned to relationships as well.
87c4e602 527
2535b501 528=head2 many_to_many
2f3105ce 529
530=over 4
531
951ab5ab 532=item Arguments: $accessor_name, $link_rel_name, $foreign_rel_name, \%attr?
303cf522 533
2f3105ce 534=back
535
7a2c1380 536C<many_to_many> is not strictly a relationship in its own right. Instead, it is
537a bridge between two resultsets which provide the same kind of convenience
538accessors as true relationships provide. Although the accessor will return a
539resultset or collection of objects just like has_many does, you cannot call
540C<related_resultset> and similar methods which operate on true relationships.
541
542=over
543
544=item accessor_name
545
546This argument is the name of the method you can call on a
547L<DBIx::Class::Row> object to retrieve the rows matching this
548relationship.
549
550On a many_to_many, unlike other relationships, this cannot be used in
551L<DBIx::Class::ResultSet/search> to join tables. Use the relations
552bridged across instead.
553
554=item link_rel_name
555
556This is the accessor_name from the has_many relationship we are
557bridging from.
558
559=item foreign_rel_name
560
561This is the accessor_name of the belongs_to relationship in the link
562table that we are bridging across (which gives us the table we are
563bridging to).
564
565=back
566
2f3105ce 567To create a many_to_many relationship from Actor to Role:
568
75d07914 569 My::DBIC::Schema::Actor->has_many( actor_roles =>
d2113a68 570 'My::DBIC::Schema::ActorRoles',
571 'actor' );
75d07914 572 My::DBIC::Schema::ActorRoles->belongs_to( role =>
d2113a68 573 'My::DBIC::Schema::Role' );
75d07914 574 My::DBIC::Schema::ActorRoles->belongs_to( actor =>
d2113a68 575 'My::DBIC::Schema::Actor' );
576
577 My::DBIC::Schema::Actor->many_to_many( roles => 'actor_roles',
71d5ed18 578 'role' );
bc0c9800 579
2f3105ce 580And, for the reverse relationship, from Role to Actor:
581
582 My::DBIC::Schema::Role->has_many( actor_roles =>
583 'My::DBIC::Schema::ActorRoles',
584 'role' );
585
586 My::DBIC::Schema::Role->many_to_many( actors => 'actor_roles', 'actor' );
587
787d6a29 588To add a role for your actor, and fill in the year of the role in the
589actor_roles table:
590
591 $actor->add_to_roles($role, { year => 1995 });
592
2535b501 593In the above example, ActorRoles is the link table class, and Role is the
594foreign class. The C<$link_rel_name> parameter is the name of the accessor for
595the has_many relationship from this table to the link table, and the
596C<$foreign_rel_name> parameter is the accessor for the belongs_to relationship
597from the link table to the foreign table.
598
d2113a68 599To use many_to_many, existing relationships from the original table to the link
75d07914 600table, and from the link table to the end table must already exist, these
d2113a68 601relation names are then used in the many_to_many call.
7411204b 602
2535b501 603In the above example, the Actor class will have 3 many_to_many accessor methods
951ab5ab 604set: C<roles>, C<add_to_roles>, C<set_roles>, and similarly named accessors
2535b501 605will be created for the Role class for the C<actors> many_to_many
606relationship.
607
9e64dfbf 608See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 609methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
610for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
611which can be assigned to relationships as well.
2f3105ce 612
34d52be2 613=cut
614
b8e1e21f 6151;
34d52be2 616
34d52be2 617=head1 AUTHORS
618
951ab5ab 619see L<DBIx::Class>
34d52be2 620
621=head1 LICENSE
622
623You may distribute this code under the same terms as Perl itself.
624
625=cut
626