makes search_related on extended rels without the optimized version work. involves...
[dbsrgits/DBIx-Class-Historic.git] / lib / DBIx / Class / Manual / Joining.pod
CommitLineData
b8b1b969 1=head1 NAME
2
3DBIx::Class::Manual::Joining - Manual on joining tables with DBIx::Class
4
5=head1 DESCRIPTION
6
7This document should help you to use L<DBIx::Class> if you are trying
8to convert your normal SQL queries into DBIx::Class based queries, if
9you use joins extensively (and also probably if you don't).
10
11=head1 WHAT ARE JOINS
12
13If you ended up here and you don't actually know what joins are yet,
14then you should likely try the L<DBIx::Class::Manual::Intro>
15instead. Skip this part if you know what joins are..
16
17But I'll explain anyway. Assuming you have created your database in a
18more or less sensible way, you will end up with several tables that
19contain C<related> information. For example, you may have a table
b01eba93 20containing information about C<CD>s, containing the CD title and it's
b8b1b969 21year of publication, and another table containing all the C<Track>s
22for the CDs, one track per row.
23
24When you wish to extract information about a particular CD and all
25it's tracks, You can either fetch the CD row, then make another query
26to fetch the tracks, or you can use a join. Compare:
27
28 SELECT ID, Title, Year FROM CD WHERE Title = 'Funky CD';
29 # .. Extract the ID, which is 10
30 SELECT Name, Artist FROM Tracks WHERE CDID = 10;
31
32 SELECT cd.ID, cd.Title, cd.Year, tracks.Name, tracks.Artist FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = 'Funky CD';
33
34So, joins are a way of extending simple select statements to include
35fields from other, related, tables. There are various types of joins,
36depending on which combination of the data you wish to retrieve, see
b01eba93 37MySQL's doc on JOINs:
38L<http://dev.mysql.com/doc/refman/5.0/en/join.html>.
b8b1b969 39
40=head1 DEFINING JOINS AND RELATIONSHIPS
41
42In L<DBIx::Class> each relationship between two tables needs to first
43be defined in the L<ResultSource|DBIx::Class::Manual::Glossary/ResultSource> for the
44table. If the relationship needs to be accessed in both directions
45(i.e. Fetch all tracks of a CD, and fetch the CD data for a Track),
b01eba93 46then it needs to be defined for both tables.
b8b1b969 47
48For the CDs/Tracks example, that means writing, in C<MySchema::CD>:
49
50 MySchema::CD->has_many('tracks', 'MySchema::Tracks');
51
52And in C<MySchema::Tracks>:
53
54 MySchema::Tracks->belongs_to('cd', 'MySchema::CD', 'CDID');
55
56There are several other types of relationships, they are more
57comprehensively described in L<DBIx::Class::Relationship>.
58
59=head1 USING JOINS
60
61Once you have defined all your relationships, using them in actual
62joins is fairly simple. The type of relationship that you chose
63e.g. C<has_many>, already indicates what sort of join will be
64performed. C<has_many> produces a C<LEFT JOIN> for example, which will
65fetch all the rows on the left side, whether there are matching rows
66on the right (table being joined to), or not. You can force other
67types of joins in your relationship, see the
68L<DBIx::Class::Relationship> docs.
69
70When performing either a L<search|DBIx::Class::ResultSet/search> or a
71L<find|DBIx::Class::ResultSet/find> operation, you can specify which
b01eba93 72C<relations> to also refine your results based on, using the
b8b1b969 73L<join|DBIx::Class::ResultSet/join> attribute, like this:
74
75 $schema->resultset('CD')->search(
b01eba93 76 { 'Title' => 'Funky CD',
77 'tracks.Name' => { like => 'T%' }
78 },
b8b1b969 79 { join => 'tracks',
b01eba93 80 order_by => ['tracks.id'],
b8b1b969 81 }
82 );
83
84If you don't recognise most of this syntax, you should probably go
85read L<DBIx::Class::ResultSet/search> and
86L<DBIx::Class::ResultSet/ATTRIBUTES>, but here's a quick break down:
87
88The first argument to search is a hashref of the WHERE attributes, in
b01eba93 89this case a restriction on the Title column in the CD table, and a
90restriction on the name of the track in the Tracks table, but ONLY for
91tracks actually related to the chosen CD(s). The second argument is a
92hashref of attributes to the search, the results will be returned
93sorted by the C<id> of the related tracks.
94
95The special 'join' attribute specifies which C<relationships> to
96include in the query. The distinction between C<relationships> and
97C<tables> is important here, only the C<relationship> names are valid.
98
99This slightly nonsense example will produce SQL similar to:
100
101 SELECT cd.ID, cd.Title, cd.Year FROM CD cd JOIN Tracks tracks ON cd.ID = tracks.CDID WHERE cd.Title = 'Funky CD' AND tracks.Name LIKE 'T%' ORDER BY 'tracks.id';
102
103=head1 FETCHING RELATED DATA
104
105Another common use for joining to related tables, is to fetch the data
106from both tables in one query, preventing extra round-trips to the
107database. See the example above in L</WHAT ARE JOINS>.
108
4c4964c1 109Three techniques are described here. Of the three, only the
110C<prefetch> technique will deal sanely with fetching related objects
111over a C<has_many> relation. The others work fine for 1 to 1 type
112relationships.
113
b01eba93 114=head2 Whole related objects
115
48580715 116To fetch entire related objects, e.g. CDs and all Track data, use the
b01eba93 117'prefetch' attribute:
118
119 $schema->resultset('CD')->search(
120 { 'Title' => 'Funky CD',
121 },
122 { prefetch => 'tracks',
123 order_by => ['tracks.id'],
124 }
125 );
126
127This will produce SQL similar to the following:
128
129 SELECT cd.ID, cd.Title, cd.Year, tracks.id, tracks.Name, tracks.Artist FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = 'Funky CD' ORDER BY 'tracks.id';
130
131The syntax of 'prefetch' is the same as 'join' and implies the
48580715 132joining, so there is no need to use both together.
b01eba93 133
134=head2 Subset of related fields
135
136To fetch a subset or the related fields, the '+select' and '+as'
137attributes can be used. For example, if the CD data is required and
138just the track name from the Tracks table:
139
140 $schema->resultset('CD')->search(
141 { 'Title' => 'Funky CD',
142 },
143 { join => 'tracks',
144 '+select' => ['tracks.Name'],
145 '+as' => ['track_name'],
146 order_by => ['tracks.id'],
147 }
148 );
149
150Which will produce the query:
151
152 SELECT cd.ID, cd.Title, cd.Year, tracks.Name FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = 'Funky CD' ORDER BY 'tracks.id';
153
154Note that the '+as' does not produce an SQL 'AS' keyword in the
155output, see the L<DBIx::Class::Manual::FAQ> for an explanation.
156
157This type of column restriction has a downside, the resulting $row
158object will have no 'track_name' accessor:
159
160 while(my $row = $search_rs->next) {
161 print $row->track_name; ## ERROR
162 }
163
164Instead C<get_column> must be used:
165
166 while(my $row = $search_rs->next) {
167 print $row->get_colum('track_name'); ## WORKS
168 }
169
170=head2 Incomplete related objects
171
172In rare circumstances, you may also wish to fetch related data as
173incomplete objects. The usual reason to do is when the related table
174has a very large field you don't need for the current data
175output. This is better solved by storing that field in a separate
176table which you only join to when needed.
177
178To fetch an incomplete related object, supply the dotted notation to the '+as' attribute:
179
180 $schema->resultset('CD')->search(
181 { 'Title' => 'Funky CD',
182 },
183 { join => 'tracks',
184 '+select' => ['tracks.Name'],
185 '+as' => ['tracks.Name'],
186 order_by => ['tracks.id'],
187 }
188 );
189
190Which will produce same query as above;
191
192 SELECT cd.ID, cd.Title, cd.Year, tracks.Name FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = 'Funky CD' ORDER BY 'tracks.id';
193
194Now you can access the result using the relationship accessor:
195
196 while(my $row = $search_rs->next) {
197 print $row->tracks->name; ## WORKS
198 }
b8b1b969 199
b01eba93 200However, this will produce broken objects. If the tracks id column is
201not fetched, the object will not be usable for any operation other
202than reading its data. Use the L</Whole related objects> method as
203much as possible to avoid confusion in your code later.
b8b1b969 204
b01eba93 205Broken means: Update will not work. Fetching other related objects
206will not work. Deleting the object will not work.
b8b1b969 207
208=head1 COMPLEX JOINS AND STUFF
209
210=head2 Across multiple relations
211
212For simplicity in the example above, the C<Artist> was shown as a
bc7e7eac 213simple text field in the C<Tracks> table, in reality, you'll want to
b8b1b969 214have the artists in their own table as well, thus to fetch the
215complete set of data we'll need to join to the Artist table too.
216
217In C<MySchema::Tracks>:
218
219 MySchema::Tracks->belongs_to('artist', 'MySchema::Artist', 'ArtistID');
220
221The search:
222
223 $schema->resultset('CD')->search(
224 { 'Title' => 'Funky CD' },
225 { join => { 'tracks' => 'artist' },
b8b1b969 226 }
227 );
228
229Which is:
230
b01eba93 231 SELECT me.ID, me.Title, me.Year FROM CD me JOIN Tracks tracks ON CD.ID = tracks.CDID JOIN Artists artist ON tracks.ArtistID = artist.ID WHERE me.Title = 'Funky CD';
b8b1b969 232
233To perform joins using relations of the tables you are joining to, use
234a hashref to indicate the join depth. This can theoretically go as
48580715 235deep as you like (warning: contrived examples!):
b8b1b969 236
237 join => { room => { table => 'leg' } }
238
239To join two relations at the same level, use an arrayref instead:
240
241 join => { room => [ 'chair', 'table' ] }
242
243Or combine the two:
244
245 join => { room => [ 'chair', { table => 'leg' } ]
246
247=head2 Table aliases
248
249As an aside to all the discussion on joins, note that L<DBIx::Class>
250uses the C<relation names> as table aliases. This is important when
251you need to add grouping or ordering to your queries:
252
253 $schema->resultset('CD')->search(
254 { 'Title' => 'Funky CD' },
255 { join => { 'tracks' => 'artist' },
256 order_by => [ 'tracks.Name', 'artist.Artist' ],
b8b1b969 257 }
258 );
259
b01eba93 260 SELECT me.ID, me.Title, me.Year FROM CD me JOIN Tracks tracks ON CD.ID = tracks.CDID JOIN Artists artist ON tracks.ArtistID = artist.ID WHERE me.Title = 'Funky CD' ORDER BY tracks.Name, artist.Artist;
b8b1b969 261
262This is essential if any of your tables have columns with the same names.
263
264Note that the table of the resultsource the search was performed on, is always aliased to C<me>.
265
266=head2 Joining to the same table twice
267
268There is no magic to this, just do it. The table aliases will
269automatically be numbered:
270
271 join => [ 'room', 'room' ]
272
3bad9fe1 273The aliases are: C<room> and C<room_2>.
b8b1b969 274
275=cut
276