ResultSetColumn::func() now returns all results if called in list context
[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
75d07914 22 MyDB::Schema::Actor->has_many('actorroles' => 'MyDB::Schema::ActorRole',
d2113a68 23 'actor');
75d07914 24 MyDB::Schema::Role->has_many('actorroles' => 'MyDB::Schema::ActorRole',
d2113a68 25 'role');
26 MyDB::Schema::ActorRole->belongs_to('role' => 'MyDB::Schema::Role');
27 MyDB::Schema::ActorRole->belongs_to('actor' => 'MyDB::Schema::Actor');
28
29 MyDB::Schema::Role->many_to_many('actors' => 'actorroles', 'actor');
30 MyDB::Schema::Actor->many_to_many('roles' => 'actorroles', 'role');
31
32 $schema->resultset('Actor')->roles();
33 $schema->resultset('Role')->search_related('actors', { Name => 'Fred' });
fca27358 34 $schema->resultset('ActorRole')->add_to_roles({ Name => 'Sherlock Holmes'});
d2113a68 35
36See L<DBIx::Class::Manual::Cookbook> for more.
37
34d52be2 38=head1 DESCRIPTION
39
bc1171c3 40This class provides methods to set up relationships between the tables
41in your database model. Relationships are the most useful and powerful
42technique that L<DBIx::Class> provides. To create efficient database queries,
43create relationships between any and all tables that have something in
44common, for example if you have a table Authors:
45
46 ID | Name | Age
47 ------------------
48 1 | Fred | 30
49 2 | Joe | 32
50
51and a table Books:
52
53 ID | Author | Name
54 --------------------
55 1 | 1 | Rulers of the universe
56 2 | 1 | Rulers of the galaxy
57
58Then without relationships, the method of getting all books by Fred goes like
59this:
60
61 my $fred = $schema->resultset('Author')->find({ Name => 'Fred' });
62 my $fredsbooks = $schema->resultset('Book')->search({ Author => $fred->ID });
63With a has_many relationship called "books" on Author (see below for details),
64we can do this instead:
65
66 my $fredsbooks = $schema->resultset('Author')->find({ Name => 'Fred' })->books;
67
75d07914 68Each relationship sets up an accessor method on the
bc1171c3 69L<DBIx::Class::Manual::Glossary/"Row"> objects that represent the items
70of your table. From L<DBIx::Class::Manual::Glossary/"ResultSet"> objects,
75d07914 71the relationships can be searched using the "search_related" method.
bc1171c3 72In list context, each returns a list of Row objects for the related class,
73in scalar context, a new ResultSet representing the joined tables is
74returned. Thus, the calls can be chained to produce complex queries.
75Since the database is not actually queried until you attempt to retrieve
76the data for an actual item, no time is wasted producing them.
77
bc0c9800 78 my $cheapfredbooks = $schema->resultset('Author')->find({
79 Name => 'Fred',
80 })->books->search_related('prices', {
81 Price => { '<=' => '5.00' },
82 });
bc1171c3 83
84will produce a query something like:
85
75d07914 86 SELECT * FROM Author me
bc1171c3 87 LEFT JOIN Books books ON books.author = me.id
88 LEFT JOIN Prices prices ON prices.book = books.id
89 WHERE prices.Price <= 5.00
90
91all without needing multiple fetches.
34d52be2 92
bfab575a 93Only the helper methods for setting up standard relationship types
d2113a68 94are documented here. For the basic, lower-level methods, and a description
95of all the useful *_related methods that you get for free, see
bfab575a 96L<DBIx::Class::Relationship::Base>.
503536d5 97
34d52be2 98=head1 METHODS
99
bfab575a 100All helper methods take the following arguments:
503536d5 101
8091aa91 102 __PACKAGE__>$method_name('relname', 'Foreign::Class', $cond, $attrs);
bfab575a 103
104Both C<$cond> and C<$attrs> are optional. Pass C<undef> for C<$cond> if
105you want to use the default value for it, but still want to set C<$attrs>.
2535b501 106
9e64dfbf 107See L<DBIx::Class::Relationship::Base> for a list of valid attributes and valid
108relationship attributes.
503536d5 109
bfab575a 110=head2 belongs_to
503536d5 111
2f3105ce 112=over 4
113
9e64dfbf 114=item Arguments: $accessor_name, $related_class, $foreign_key_column|$cond?, $attr?
2f3105ce 115
116=back
117
7a2c1380 118Creates a relationship where the calling class stores the foreign class's
119primary key in one (or more) of its columns. This relationship defaults to
120using C<$accessor_name> as the foreign key in C<$related_class> to resolve the
121join, unless C<$foreign_key_column> specifies the foreign key column in
122C<$related_class> or C<$cond> specifies a reference to a join condition hash.
123
124=over
125
126=item accessor_name
127
128This argument is the name of the method you can call on a
129L<DBIx::Class::Row> object to retrieve the instance of the foreign
130class matching this relationship.
131
132Use this accessor_name (relation name) in L<DBIx::Class::ResultSet/join>
133or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
134indicated by this relationship.
135
136=item related_class
137
138This is the class name of the table referenced by the foreign key in
139this class.
140
141=item foreign_key_column
142
143The column name on this class that contains the foreign key.
144
145OR
146
147=item cond
148
149A hashref where the keys are C<foreign.$column_on_related_table> and
150the values are C<self.$foreign_key_column>. This is useful for
151relations that are across multiple columns.
152
153=back
154
155
c99393ff 156 # in a Book class (where Author has many Books)
9e64dfbf 157 My::DBIC::Schema::Book->belongs_to( author => 'My::DBIC::Schema::Author' );
2535b501 158
159 my $author_obj = $obj->author; # get author object
9e64dfbf 160 $obj->author( $new_author_obj ); # set author object
161
162The above belongs_to relationship could also have been specified as,
2535b501 163
9e64dfbf 164 My::DBIC::Schema::Book->belongs_to( author,
165 'My::DBIC::Schema::Author',
826daaae 166 { 'foreign.author' => 'self.author' } );
503536d5 167
2f3105ce 168If the relationship is optional -- i.e. the column containing the foreign key
169can be NULL -- then the belongs_to relationship does the right thing. Thus, in
170the example above C<$obj-E<gt>author> would return C<undef>. However in this
171case you would probably want to set the C<join_type> attribute so that a C<LEFT
172JOIN> is done, which makes complex resultsets involving C<join> or C<prefetch>
173operations work correctly. The modified declaration is shown below:
2c3ad870 174
b8810cc5 175 # in a Book class (where Author has_many Books)
2c3ad870 176 __PACKAGE__->belongs_to(author => 'My::DBIC::Schema::Author',
177 'author', {join_type => 'left'});
178
179
b8810cc5 180Cascading deletes are off by default on a C<belongs_to>
181relationship. To turn them on, pass C<< cascade_delete => 1 >>
182in the $attr hashref.
e8e9e5c7 183
8091aa91 184NOTE: If you are used to L<Class::DBI> relationships, this is the equivalent
185of C<has_a>.
503536d5 186
9e64dfbf 187See L<DBIx::Class::Relationship::Base> for documentation on relationship
188methods and valid relationship attributes.
2535b501 189
bfab575a 190=head2 has_many
503536d5 191
2f3105ce 192=over 4
193
2535b501 194=item Arguments: $accessor_name, $related_class, $foreign_key_column|$cond?, $attr?
2f3105ce 195
196=back
197
7a2c1380 198Creates a one-to-many relationship, where the corresponding elements of the
199foreign class store the calling class's primary key in one (or more) of its
200columns. This relationship defaults to using C<$accessor_name> as the foreign
201key in C<$related_class> to resolve the join, unless C<$foreign_key_column>
202specifies the foreign key column in C<$related_class> or C<$cond> specifies a
203reference to a join condition hash.
204
205=over
206
207=item accessor_name
208
209This argument is the name of the method you can call on a
210L<DBIx::Class::Row> object to retrieve a resultset of the related
211class restricted to the ones related to the row object. In list
212context it returns the row objects.
213
214Use this accessor_name (relation name) in L<DBIx::Class::ResultSet/join>
215or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
216indicated by this relationship.
217
218=item related_class
219
220This is the class name of the table which contains a foreign key
221column containing PK values of this class.
222
223=item foreign_key_column
224
225The column name on the related class that contains the foreign key.
226
227OR
228
229=item cond
230
231A hashref where the keys are C<foreign.$column_on_related_table> and
232the values are C<self.$foreign_key_column>. This is useful for
233relations that are across multiple columns.
234
235=back
236
b8810cc5 237 # in an Author class (where Author has_many Books)
d2113a68 238 My::DBIC::Schema::Author->has_many(books => 'My::DBIC::Schema::Book', 'author');
2535b501 239
c99393ff 240 my $booklist = $obj->books;
bc0c9800 241 my $booklist = $obj->books({
242 name => { LIKE => '%macaroni%' },
243 { prefetch => [qw/book/],
244 });
c99393ff 245 my @book_objs = $obj->books;
5b89a768 246 my $books_rs = $obj->books;
247 ( $books_rs ) = $obj->books_rs;
503536d5 248
c99393ff 249 $obj->add_to_books(\%col_data);
503536d5 250
2535b501 251The above C<has_many> relationship could also have been specified with an
252explicit join condition:
253
254 My::DBIC::Schema::Author->has_many( books => 'My::DBIC::Schema::Book', {
255 'foreign.author' => 'self.author',
256 });
257
60a8fb95 258Three methods are created when you create a has_many relationship. The first
2f3105ce 259method is the expected accessor method, C<$accessor_name()>. The second is
260almost exactly the same as the accessor method but "_rs" is added to the end of
261the method name. This method works just like the normal accessor, except that
262it returns a resultset no matter what, even in list context. The third method,
2535b501 263named C<< add_to_$relname >>, will also be added to your Row items; this
2f3105ce 264allows you to insert new related items, using the same mechanism as in
5b89a768 265L<DBIx::Class::Relationship::Base/"create_related">.
d2113a68 266
8091aa91 267If you delete an object in a class with a C<has_many> relationship, all
b8810cc5 268the related objects will be deleted as well. To turn this behaviour off,
269pass C<< cascade_delete => 0 >> in the C<$attr> hashref. However, any
270database-level cascade or restrict will take precedence over a
271DBIx-Class-based cascading delete.
503536d5 272
9e64dfbf 273See L<DBIx::Class::Relationship::Base> for documentation on relationship
274methods and valid relationship attributes.
2535b501 275
bfab575a 276=head2 might_have
503536d5 277
2f3105ce 278=over 4
279
9e64dfbf 280=item Arguments: $accessor_name, $related_class, $foreign_key_column|$cond?, $attr?
2f3105ce 281
282=back
283
7a2c1380 284Creates an optional one-to-one relationship with a class. This relationship
285defaults to using C<$accessor_name> as the foreign key in C<$related_class> to
286resolve the join, unless C<$foreign_key_column> specifies the foreign key
287column in C<$related_class> or C<$cond> specifies a reference to a join
288condition hash.
289
290=over
291
292=item accessor_name
293
294This argument is the name of the method you can call on a
295L<DBIx::Class::Row> object to retrieve the instance of the foreign
296class matching this relationship.
297
298Use this accessor_name (relation name) in L<DBIx::Class::ResultSet/join>
299or L<DBIx::Class::ResultSet/prefetch> to join to the foreign table
300indicated by this relationship.
301
302=item related_class
303
304This is the class name of the table which contains a foreign key
305column containing PK values of this class.
306
307=item foreign_key_column
308
309The column name on the related class that contains the foreign key.
310
311OR
312
313=item cond
314
315A hashref where the keys are C<foreign.$column_on_related_table> and
316the values are C<self.$foreign_key_column>. This is useful for
317relations that are across multiple columns.
318
319=back
320
9e64dfbf 321 My::DBIC::Schema::Author->might_have( pseudonym =>
322 'My::DBIC::Schema::Pseudonym' );
2f3105ce 323
880a1a0c 324 my $pname = $obj->pseudonym; # to get the Pseudonym object
8091aa91 325
9e64dfbf 326The above might_have relationship could have been specified as:
327
328 My::DBIC::Schema::Author->might_have( pseudonym =>
329 'My::DBIC::Schema::Pseudonym',
330 'author' );
331
332Or even:
333
334 My::DBIC::Schema::Author->might_have( pseudonym =>
335 'My::DBIC::Schema::Pseudonym',
336 { 'foreign.author' => 'self.author' } );
337
c99393ff 338If you update or delete an object in a class with a C<might_have>
b8810cc5 339relationship, the related object will be updated or deleted as well. To
340turn off this behavior, add C<< cascade_delete => 0 >> to the C<$attr>
341hashref. Any database-level update or delete constraints will override
342this behavior.
503536d5 343
9e64dfbf 344See L<DBIx::Class::Relationship::Base> for documentation on relationship
345methods and valid relationship attributes.
2f3105ce 346
bfab575a 347=head2 has_one
348
2f3105ce 349=over 4
350
351=item Arguments: $accessor_name, $related_class_name, $join_condition?, $attr?
352
353=back
354
d2113a68 355 My::DBIC::Schema::Book->has_one(isbn => 'My::DBIC::Schema::ISBN');
2f3105ce 356
357 my $isbn_obj = $obj->isbn; # to get the ISBN object
bfab575a 358
c99393ff 359Creates a one-to-one relationship with another class. This is just like
360C<might_have>, except the implication is that the other object is always
361present. The only difference between C<has_one> and C<might_have> is that
362C<has_one> uses an (ordinary) inner join, whereas C<might_have> uses a
363left join.
503536d5 364
2f3105ce 365The has_one relationship should be used when a row in the table has exactly one
366related row in another table. If the related row might not exist in the foreign
367table, use the L<DBIx::Class::Relationship/might_have> relationship.
368
369In the above example, each Book in the database is associated with exactly one
370ISBN object.
7411204b 371
9e64dfbf 372See L<DBIx::Class::Relationship::Base> for documentation on relationship
373methods and valid relationship attributes.
87c4e602 374
2535b501 375=head2 many_to_many
2f3105ce 376
377=over 4
378
2535b501 379=item Arguments: $accessor_name, $link_rel_name, $foreign_rel_name, $attr?
303cf522 380
2f3105ce 381=back
382
7a2c1380 383C<many_to_many> is not strictly a relationship in its own right. Instead, it is
384a bridge between two resultsets which provide the same kind of convenience
385accessors as true relationships provide. Although the accessor will return a
386resultset or collection of objects just like has_many does, you cannot call
387C<related_resultset> and similar methods which operate on true relationships.
388
389=over
390
391=item accessor_name
392
393This argument is the name of the method you can call on a
394L<DBIx::Class::Row> object to retrieve the rows matching this
395relationship.
396
397On a many_to_many, unlike other relationships, this cannot be used in
398L<DBIx::Class::ResultSet/search> to join tables. Use the relations
399bridged across instead.
400
401=item link_rel_name
402
403This is the accessor_name from the has_many relationship we are
404bridging from.
405
406=item foreign_rel_name
407
408This is the accessor_name of the belongs_to relationship in the link
409table that we are bridging across (which gives us the table we are
410bridging to).
411
412=back
413
2f3105ce 414To create a many_to_many relationship from Actor to Role:
415
75d07914 416 My::DBIC::Schema::Actor->has_many( actor_roles =>
d2113a68 417 'My::DBIC::Schema::ActorRoles',
418 'actor' );
75d07914 419 My::DBIC::Schema::ActorRoles->belongs_to( role =>
d2113a68 420 'My::DBIC::Schema::Role' );
75d07914 421 My::DBIC::Schema::ActorRoles->belongs_to( actor =>
d2113a68 422 'My::DBIC::Schema::Actor' );
423
424 My::DBIC::Schema::Actor->many_to_many( roles => 'actor_roles',
71d5ed18 425 'role' );
bc0c9800 426
2f3105ce 427And, for the reverse relationship, from Role to Actor:
428
429 My::DBIC::Schema::Role->has_many( actor_roles =>
430 'My::DBIC::Schema::ActorRoles',
431 'role' );
432
433 My::DBIC::Schema::Role->many_to_many( actors => 'actor_roles', 'actor' );
434
787d6a29 435To add a role for your actor, and fill in the year of the role in the
436actor_roles table:
437
438 $actor->add_to_roles($role, { year => 1995 });
439
2535b501 440In the above example, ActorRoles is the link table class, and Role is the
441foreign class. The C<$link_rel_name> parameter is the name of the accessor for
442the has_many relationship from this table to the link table, and the
443C<$foreign_rel_name> parameter is the accessor for the belongs_to relationship
444from the link table to the foreign table.
445
d2113a68 446To use many_to_many, existing relationships from the original table to the link
75d07914 447table, and from the link table to the end table must already exist, these
d2113a68 448relation names are then used in the many_to_many call.
7411204b 449
2535b501 450In the above example, the Actor class will have 3 many_to_many accessor methods
2f3105ce 451set: C<$roles>, C<$add_to_roles>, C<$set_roles>, and similarly named accessors
2535b501 452will be created for the Role class for the C<actors> many_to_many
453relationship.
454
9e64dfbf 455See L<DBIx::Class::Relationship::Base> for documentation on relationship
456methods and valid relationship attributes.
2f3105ce 457
34d52be2 458=cut
459
b8e1e21f 4601;
34d52be2 461
34d52be2 462=head1 AUTHORS
463
daec44b8 464Matt S. Trout <mst@shadowcatsystems.co.uk>
34d52be2 465
466=head1 LICENSE
467
468You may distribute this code under the same terms as Perl itself.
469
470=cut
471