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