Introduce 'any_null_means_no_value' option to eliminate wasteful queries. The option...
[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
c89815db 211By default, DBIC will attempt to query the related table for a row when the
212relationship accessor is called even if a foreign key member column IS NULL,
213which can be wasteful. To avoid this query from being performed, pass
214C<< any_null_means_no_value => 1 >> in the C<$attr> hashref. This only applies
215to accessors of type 'single' (when your accessor and foreign key have
216different names e.g. 'cd_id', and 'cd').
217
8091aa91 218NOTE: If you are used to L<Class::DBI> relationships, this is the equivalent
219of C<has_a>.
503536d5 220
9e64dfbf 221See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 222methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
223for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
224which can be assigned to relationships as well.
2535b501 225
bfab575a 226=head2 has_many
503536d5 227
2f3105ce 228=over 4
229
b0acf5d8 230=item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 231
232=back
233
e951858e 234Creates a one-to-many relationship, where the corresponding elements
235of the foreign class store the calling class's primary key in one (or
b0acf5d8 236more) of the foreign class columns. This relationship defaults to using
237the end of this classes namespace as the foreign key in C<$related_class>
238to resolve the join, unless C<$their_fk_column> specifies the foreign
e951858e 239key column in C<$related_class> or C<cond> specifies a reference to a
240join condition hash.
7a2c1380 241
242=over
243
244=item accessor_name
245
246This argument is the name of the method you can call on a
247L<DBIx::Class::Row> object to retrieve a resultset of the related
248class restricted to the ones related to the row object. In list
951ab5ab 249context it returns the row objects. This is often called the
250C<relation(ship) name>.
7a2c1380 251
951ab5ab 252Use this accessor_name in L<DBIx::Class::ResultSet/join>
7a2c1380 253or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
254indicated by this relationship.
255
256=item related_class
257
258This is the class name of the table which contains a foreign key
259column containing PK values of this class.
260
b0acf5d8 261=item their_fk_column
7a2c1380 262
263The column name on the related class that contains the foreign key.
264
265OR
266
267=item cond
268
b0acf5d8 269A hashref where the keys are C<foreign.$their_fk_column> and
951ab5ab 270the values are C<self.$matching_column>. This is useful for
7a2c1380 271relations that are across multiple columns.
272
951ab5ab 273OR
274
275An arrayref containing an SQL::Abstract-like condition. For example a
276link table where two columns link back to the same table. This is an
277OR condition.
278
279 My::Schema::Item->has_many('rels', 'My::Schema::Relationships',
280 [ { 'foreign.LItemID' => 'self.ID' },
281 { 'foreign.RItemID' => 'self.ID'} ]);
da2e2b76 282
7a2c1380 283=back
284
b8810cc5 285 # in an Author class (where Author has_many Books)
e951858e 286 # assuming related class is storing our PK in "author_id"
951ab5ab 287 My::DBIC::Schema::Author->has_many(
288 books =>
289 'My::DBIC::Schema::Book',
290 'author_id'
291 );
292
e951858e 293 # OR (same result)
951ab5ab 294 My::DBIC::Schema::Author->has_many(
295 books =>
296 'My::DBIC::Schema::Book',
e951858e 297 { 'foreign.author_id' => 'self.id' },
951ab5ab 298 );
e951858e 299
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)
951ab5ab 302 My::DBIC::Schema::Author->has_many(
303 books =>
304 'My::DBIC::Schema::Book',
951ab5ab 305 );
e951858e 306
2535b501 307
951ab5ab 308 # Usage
309 # resultset of Books belonging to author
310 my $booklist = $author->books;
311
312 # resultset of Books belonging to author, restricted by author name
313 my $booklist = $author->books({
bc0c9800 314 name => { LIKE => '%macaroni%' },
315 { prefetch => [qw/book/],
316 });
503536d5 317
951ab5ab 318 # array of Book objects belonging to author
319 my @book_objs = $author->books;
503536d5 320
951ab5ab 321 # force resultset even in list context
322 my $books_rs = $author->books;
323 ( $books_rs ) = $obj->books_rs;
324
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);
2535b501 329
2535b501 330
60a8fb95 331Three methods are created when you create a has_many relationship. The first
2f3105ce 332method is the expected accessor method, C<$accessor_name()>. The second is
333almost exactly the same as the accessor method but "_rs" is added to the end of
334the method name. This method works just like the normal accessor, except that
951ab5ab 335it always returns a resultset, even in list context. The third method,
2535b501 336named C<< add_to_$relname >>, will also be added to your Row items; this
2f3105ce 337allows you to insert new related items, using the same mechanism as in
5b89a768 338L<DBIx::Class::Relationship::Base/"create_related">.
d2113a68 339
8091aa91 340If you delete an object in a class with a C<has_many> relationship, all
b8810cc5 341the related objects will be deleted as well. To turn this behaviour off,
2a2ab6ab 342pass C<< cascade_delete => 0 >> in the C<$attr> hashref.
343
344The cascaded operations are performed after the requested delete or
345update, so if your database has a constraint on the relationship, it
346will have deleted/updated the related records or raised an exception
347before DBIx::Class gets to perform the cascaded operation.
503536d5 348
f4e92c39 349If you copy an object in a class with a C<has_many> relationship, all
350the related objects will be copied as well. To turn this behaviour off,
2fef093d 351pass C<< cascade_copy => 0 >> in the C<$attr> hashref. The behaviour
352defaults to C<< cascade_copy => 1 >>.
f4e92c39 353
9e64dfbf 354See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 355methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
356for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
357which can be assigned to relationships as well.
2535b501 358
bfab575a 359=head2 might_have
503536d5 360
2f3105ce 361=over 4
362
b0acf5d8 363=item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 364
365=back
366
7a2c1380 367Creates an optional one-to-one relationship with a class. This relationship
368defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
b0acf5d8 369resolve the join, unless C<$their_fk_column> specifies the foreign key
951ab5ab 370column in C<$related_class> or C<cond> specifies a reference to a join
7a2c1380 371condition hash.
372
373=over
374
375=item accessor_name
376
377This argument is the name of the method you can call on a
378L<DBIx::Class::Row> object to retrieve the instance of the foreign
951ab5ab 379class matching this relationship. This is often called the
380C<relation(ship) name>.
7a2c1380 381
951ab5ab 382Use this accessor_name in L<DBIx::Class::ResultSet/join>
7a2c1380 383or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
384indicated by this relationship.
385
386=item related_class
387
388This is the class name of the table which contains a foreign key
389column containing PK values of this class.
390
b0acf5d8 391=item their_fk_column
7a2c1380 392
393The column name on the related class that contains the foreign key.
394
395OR
396
397=item cond
398
b0acf5d8 399A hashref where the keys are C<foreign.$their_fk_column> and
b281b5e9 400the values are C<self.$matching_column>. This is useful for
7a2c1380 401relations that are across multiple columns.
402
403=back
404
951ab5ab 405 # Author may have an entry in the pseudonym table
406 My::DBIC::Schema::Author->might_have(
407 pseudonym =>
408 'My::DBIC::Schema::Pseudonym',
409 'author_id',
410 );
411
412 # OR (same result, assuming the related_class stores our PK)
413 My::DBIC::Schema::Author->might_have(
414 pseudonym =>
415 'My::DBIC::Schema::Pseudonym',
416 );
417
418 # OR (same result)
419 My::DBIC::Schema::Author->might_have(
420 pseudonym =>
421 'My::DBIC::Schema::Pseudonym',
422 { 'foreign.author_id' => 'self.id' },
423 );
424
425 # Usage
426 my $pname = $author->pseudonym; # to get the Pseudonym object
9e64dfbf 427
c99393ff 428If you update or delete an object in a class with a C<might_have>
b8810cc5 429relationship, the related object will be updated or deleted as well. To
430turn off this behavior, add C<< cascade_delete => 0 >> to the C<$attr>
2a2ab6ab 431hashref.
432
433The cascaded operations are performed after the requested delete or
434update, so if your database has a constraint on the relationship, it
435will have deleted/updated the related records or raised an exception
436before DBIx::Class gets to perform the cascaded operation.
503536d5 437
9e64dfbf 438See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 439methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
440for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
441which can be assigned to relationships as well.
2f3105ce 442
bfab575a 443=head2 has_one
444
2f3105ce 445=over 4
446
b0acf5d8 447=item Arguments: $accessor_name, $related_class, $their_fk_column|\%cond|\@cond?, \%attr?
2f3105ce 448
449=back
450
951ab5ab 451Creates a one-to-one relationship with a class. This relationship
452defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
b0acf5d8 453resolve the join, unless C<$their_fk_column> specifies the foreign key
951ab5ab 454column in C<$related_class> or C<cond> specifies a reference to a join
455condition hash.
2f3105ce 456
951ab5ab 457=over
458
459=item accessor_name
460
461This argument is the name of the method you can call on a
462L<DBIx::Class::Row> object to retrieve the instance of the foreign
463class matching this relationship. This is often called the
464C<relation(ship) name>.
465
466Use this accessor_name in L<DBIx::Class::ResultSet/join>
467or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
468indicated by this relationship.
469
470=item related_class
471
472This is the class name of the table which contains a foreign key
473column containing PK values of this class.
474
b0acf5d8 475=item their_fk_column
951ab5ab 476
477The column name on the related class that contains the foreign key.
478
479OR
480
481=item cond
482
b0acf5d8 483A hashref where the keys are C<foreign.$their_fk_column> and
b281b5e9 484the values are C<self.$matching_column>. This is useful for
951ab5ab 485relations that are across multiple columns.
486
487=back
bfab575a 488
951ab5ab 489 # Every book has exactly one ISBN
490 My::DBIC::Schema::Book->has_one(
491 isbn =>
492 'My::DBIC::Schema::ISBN',
493 'book_id',
494 );
495
496 # OR (same result, assuming related_class stores our PK)
497 My::DBIC::Schema::Book->has_one(
498 isbn =>
499 'My::DBIC::Schema::ISBN',
500 );
501
502 # OR (same result)
503 My::DBIC::Schema::Book->has_one(
504 isbn =>
505 'My::DBIC::Schema::ISBN',
506 { 'foreign.book_id' => 'self.id' },
507 );
508
509 # Usage
510 my $isbn_obj = $book->isbn; # to get the ISBN object
511
512Creates a one-to-one relationship with another class. This is just
513like C<might_have>, except the implication is that the other object is
514always present. The only difference between C<has_one> and
515C<might_have> is that C<has_one> uses an (ordinary) inner join,
516whereas C<might_have> defaults to a left join.
503536d5 517
2f3105ce 518The has_one relationship should be used when a row in the table has exactly one
519related row in another table. If the related row might not exist in the foreign
520table, use the L<DBIx::Class::Relationship/might_have> relationship.
521
522In the above example, each Book in the database is associated with exactly one
523ISBN object.
7411204b 524
9e64dfbf 525See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 526methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
527for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
528which can be assigned to relationships as well.
87c4e602 529
2535b501 530=head2 many_to_many
2f3105ce 531
532=over 4
533
951ab5ab 534=item Arguments: $accessor_name, $link_rel_name, $foreign_rel_name, \%attr?
303cf522 535
2f3105ce 536=back
537
7a2c1380 538C<many_to_many> is not strictly a relationship in its own right. Instead, it is
539a bridge between two resultsets which provide the same kind of convenience
540accessors as true relationships provide. Although the accessor will return a
541resultset or collection of objects just like has_many does, you cannot call
542C<related_resultset> and similar methods which operate on true relationships.
543
544=over
545
546=item accessor_name
547
548This argument is the name of the method you can call on a
549L<DBIx::Class::Row> object to retrieve the rows matching this
550relationship.
551
552On a many_to_many, unlike other relationships, this cannot be used in
553L<DBIx::Class::ResultSet/search> to join tables. Use the relations
554bridged across instead.
555
556=item link_rel_name
557
558This is the accessor_name from the has_many relationship we are
559bridging from.
560
561=item foreign_rel_name
562
563This is the accessor_name of the belongs_to relationship in the link
564table that we are bridging across (which gives us the table we are
565bridging to).
566
567=back
568
2f3105ce 569To create a many_to_many relationship from Actor to Role:
570
75d07914 571 My::DBIC::Schema::Actor->has_many( actor_roles =>
d2113a68 572 'My::DBIC::Schema::ActorRoles',
573 'actor' );
75d07914 574 My::DBIC::Schema::ActorRoles->belongs_to( role =>
d2113a68 575 'My::DBIC::Schema::Role' );
75d07914 576 My::DBIC::Schema::ActorRoles->belongs_to( actor =>
d2113a68 577 'My::DBIC::Schema::Actor' );
578
579 My::DBIC::Schema::Actor->many_to_many( roles => 'actor_roles',
71d5ed18 580 'role' );
bc0c9800 581
2f3105ce 582And, for the reverse relationship, from Role to Actor:
583
584 My::DBIC::Schema::Role->has_many( actor_roles =>
585 'My::DBIC::Schema::ActorRoles',
586 'role' );
587
588 My::DBIC::Schema::Role->many_to_many( actors => 'actor_roles', 'actor' );
589
787d6a29 590To add a role for your actor, and fill in the year of the role in the
591actor_roles table:
592
593 $actor->add_to_roles($role, { year => 1995 });
594
2535b501 595In the above example, ActorRoles is the link table class, and Role is the
596foreign class. The C<$link_rel_name> parameter is the name of the accessor for
597the has_many relationship from this table to the link table, and the
598C<$foreign_rel_name> parameter is the accessor for the belongs_to relationship
599from the link table to the foreign table.
600
d2113a68 601To use many_to_many, existing relationships from the original table to the link
75d07914 602table, and from the link table to the end table must already exist, these
d2113a68 603relation names are then used in the many_to_many call.
7411204b 604
2535b501 605In the above example, the Actor class will have 3 many_to_many accessor methods
951ab5ab 606set: C<roles>, C<add_to_roles>, C<set_roles>, and similarly named accessors
2535b501 607will be created for the Role class for the C<actors> many_to_many
608relationship.
609
9e64dfbf 610See L<DBIx::Class::Relationship::Base> for documentation on relationship
684af876 611methods and valid relationship attributes. Also see L<DBIx::Class::ResultSet>
612for a L<list of standard resultset attributes|DBIx::Class::ResultSet/ATTRIBUTES>
613which can be assigned to relationships as well.
2f3105ce 614
34d52be2 615=cut
616
b8e1e21f 6171;
34d52be2 618
34d52be2 619=head1 AUTHORS
620
951ab5ab 621see L<DBIx::Class>
34d52be2 622
623=head1 LICENSE
624
625You may distribute this code under the same terms as Perl itself.
626
627=cut
628