3 DBIx::Class::Manual::Joining - Manual on joining tables with DBIx::Class
7 This document should help you to use L<DBIx::Class> if you are trying
8 to convert your normal SQL queries into DBIx::Class based queries, if
9 you use joins extensively (and also probably if you don't).
13 If you ended up here and you don't actually know what joins are yet,
14 then you should likely try the L<DBIx::Class::Manual::Intro>
15 instead. Skip this part if you know what joins are..
17 But I'll explain anyway. Assuming you have created your database in a
18 more or less sensible way, you will end up with several tables that
19 contain C<related> information. For example, you may have a table
20 containing information about C<CD>s, containing the CD title and it's
21 year of publication, and another table containing all the C<Track>s
22 for the CDs, one track per row.
24 When you wish to extract information about a particular CD and all
25 it's tracks, You can either fetch the CD row, then make another query
26 to fetch the tracks, or you can use a join. Compare:
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;
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';
34 So, joins are a way of extending simple select statements to include
35 fields from other, related, tables. There are various types of joins,
36 depending on which combination of the data you wish to retrieve, see
38 L<http://dev.mysql.com/doc/refman/5.0/en/join.html>.
40 =head1 DEFINING JOINS AND RELATIONSHIPS
42 In L<DBIx::Class> each relationship between two tables needs to first
43 be defined in the L<ResultSource|DBIx::Class::Manual::Glossary/ResultSource> for the
44 table. 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),
46 then it needs to be defined for both tables.
48 For the CDs/Tracks example, that means writing, in C<MySchema::CD>:
50 MySchema::CD->has_many('tracks', 'MySchema::Tracks');
52 And in C<MySchema::Tracks>:
54 MySchema::Tracks->belongs_to('cd', 'MySchema::CD', 'CDID');
56 There are several other types of relationships, they are more
57 comprehensively described in L<DBIx::Class::Relationship>.
61 Once you have defined all your relationships, using them in actual
62 joins is fairly simple. The type of relationship that you chose
63 e.g. C<has_many>, already indicates what sort of join will be
64 performed. C<has_many> produces a C<LEFT JOIN> for example, which will
65 fetch all the rows on the left side, whether there are matching rows
66 on the right (table being joined to), or not. You can force other
67 types of joins in your relationship, see the
68 L<DBIx::Class::Relationship> docs.
70 When performing either a L<search|DBIx::Class::ResultSet/search> or a
71 L<find|DBIx::Class::ResultSet/find> operation, you can specify which
72 C<relations> to also refine your results based on, using the
73 L<join|DBIx::Class::ResultSet/join> attribute, like this:
75 $schema->resultset('CD')->search(
76 { 'Title' => 'Funky CD',
77 'tracks.Name' => { like => 'T%' }
80 order_by => ['tracks.id'],
84 If you don't recognise most of this syntax, you should probably go
85 read L<DBIx::Class::ResultSet/search> and
86 L<DBIx::Class::ResultSet/ATTRIBUTES>, but here's a quick break down:
88 The first argument to search is a hashref of the WHERE attributes, in
89 this case a restriction on the Title column in the CD table, and a
90 restriction on the name of the track in the Tracks table, but ONLY for
91 tracks actually related to the chosen CD(s). The second argument is a
92 hashref of attributes to the search, the results will be returned
93 sorted by the C<id> of the related tracks.
95 The special 'join' attribute specifies which C<relationships> to
96 include in the query. The distinction between C<relationships> and
97 C<tables> is important here, only the C<relationship> names are valid.
99 This slightly nonsense example will produce SQL similar to:
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';
103 =head1 FETCHING RELATED DATA
105 Another common use for joining to related tables, is to fetch the data
106 from both tables in one query, preventing extra round-trips to the
107 database. See the example above in L</WHAT ARE JOINS>.
109 =head2 Whole related objects
111 To fetch entire related objects, eg CDs and all Track data, use the
112 'prefetch' attribute:
114 $schema->resultset('CD')->search(
115 { 'Title' => 'Funky CD',
117 { prefetch => 'tracks',
118 order_by => ['tracks.id'],
122 This will produce SQL similar to the following:
124 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';
126 The syntax of 'prefetch' is the same as 'join' and implies the
127 joining, so no need to use both together.
129 =head2 Subset of related fields
131 To fetch a subset or the related fields, the '+select' and '+as'
132 attributes can be used. For example, if the CD data is required and
133 just the track name from the Tracks table:
135 $schema->resultset('CD')->search(
136 { 'Title' => 'Funky CD',
139 '+select' => ['tracks.Name'],
140 '+as' => ['track_name'],
141 order_by => ['tracks.id'],
145 Which will produce the query:
147 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';
149 Note that the '+as' does not produce an SQL 'AS' keyword in the
150 output, see the L<DBIx::Class::Manual::FAQ> for an explanation.
152 This type of column restriction has a downside, the resulting $row
153 object will have no 'track_name' accessor:
155 while(my $row = $search_rs->next) {
156 print $row->track_name; ## ERROR
159 Instead C<get_column> must be used:
161 while(my $row = $search_rs->next) {
162 print $row->get_colum('track_name'); ## WORKS
165 =head2 Incomplete related objects
167 In rare circumstances, you may also wish to fetch related data as
168 incomplete objects. The usual reason to do is when the related table
169 has a very large field you don't need for the current data
170 output. This is better solved by storing that field in a separate
171 table which you only join to when needed.
173 To fetch an incomplete related object, supply the dotted notation to the '+as' attribute:
175 $schema->resultset('CD')->search(
176 { 'Title' => 'Funky CD',
179 '+select' => ['tracks.Name'],
180 '+as' => ['tracks.Name'],
181 order_by => ['tracks.id'],
185 Which will produce same query as above;
187 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';
189 Now you can access the result using the relationship accessor:
191 while(my $row = $search_rs->next) {
192 print $row->tracks->name; ## WORKS
195 However, this will produce broken objects. If the tracks id column is
196 not fetched, the object will not be usable for any operation other
197 than reading its data. Use the L</Whole related objects> method as
198 much as possible to avoid confusion in your code later.
200 Broken means: Update will not work. Fetching other related objects
201 will not work. Deleting the object will not work.
203 =head1 COMPLEX JOINS AND STUFF
205 =head2 Across multiple relations
207 For simplicity in the example above, the C<Artist> was shown as a
208 simple text field in the C<Tracks> table, in reality, you'll want to
209 have the artists in their own table as well, thus to fetch the
210 complete set of data we'll need to join to the Artist table too.
212 In C<MySchema::Tracks>:
214 MySchema::Tracks->belongs_to('artist', 'MySchema::Artist', 'ArtistID');
218 $schema->resultset('CD')->search(
219 { 'Title' => 'Funky CD' },
220 { join => { 'tracks' => 'artist' },
226 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';
228 To perform joins using relations of the tables you are joining to, use
229 a hashref to indicate the join depth. This can theoretically go as
230 deep as you like (warning, contrived examples!):
232 join => { room => { table => 'leg' } }
234 To join two relations at the same level, use an arrayref instead:
236 join => { room => [ 'chair', 'table' ] }
240 join => { room => [ 'chair', { table => 'leg' } ]
244 As an aside to all the discussion on joins, note that L<DBIx::Class>
245 uses the C<relation names> as table aliases. This is important when
246 you need to add grouping or ordering to your queries:
248 $schema->resultset('CD')->search(
249 { 'Title' => 'Funky CD' },
250 { join => { 'tracks' => 'artist' },
251 order_by => [ 'tracks.Name', 'artist.Artist' ],
255 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;
257 This is essential if any of your tables have columns with the same names.
259 Note that the table of the resultsource the search was performed on, is always aliased to C<me>.
261 =head2 Joining to the same table twice
263 There is no magic to this, just do it. The table aliases will
264 automatically be numbered:
266 join => [ 'room', 'room' ]
268 The aliases are: C<room> and C<room_2>.