1DBIx::Class::Manual::JoUisneirngC(o3n)tributed Perl DocuDmBeInxt:a:tCiloanss::Manual::Joining(3)
2
3
4

NAME

6       DBIx::Class::Manual::Joining - Manual on joining tables with
7       DBIx::Class
8

DESCRIPTION

10       This document should help you to use DBIx::Class if you are trying to
11       convert your normal SQL queries into DBIx::Class based queries, if you
12       use joins extensively (and also probably if you don't).
13

WHAT ARE JOINS

15       If you ended up here and you don't actually know what joins are yet,
16       then you should likely try the DBIx::Class::Manual::Intro instead. Skip
17       this part if you know what joins are..
18
19       But I'll explain anyway. Assuming you have created your database in a
20       more or less sensible way, you will end up with several tables that
21       contain "related" information. For example, you may have a table
22       containing information about "CD"s, containing the CD title and its
23       year of publication, and another table containing all the "Track"s for
24       the CDs, one track per row.
25
26       When you wish to extract information about a particular CD and all its
27       tracks, You can either fetch the CD row, then make another query to
28       fetch the tracks, or you can use a join. Compare:
29
30         SELECT ID, Title, Year FROM CD WHERE Title = 'Funky CD';
31         # .. Extract the ID, which is 10
32         SELECT Name, Artist FROM Tracks WHERE CDID = 10;
33
34         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';
35
36       So, joins are a way of extending simple select statements to include
37       fields from other, related, tables. There are various types of joins,
38       depending on which combination of the data you wish to retrieve, see
39       MySQL's doc on JOINs:
40       <http://dev.mysql.com/doc/refman/5.0/en/join.html>.
41

DEFINING JOINS AND RELATIONSHIPS

43       In DBIx::Class each relationship between two tables needs to first be
44       defined in the ResultSource for the table. If the relationship needs to
45       be accessed in both directions (i.e. Fetch all tracks of a CD, and
46       fetch the CD data for a Track), then it needs to be defined for both
47       tables.
48
49       For the CDs/Tracks example, that means writing, in "MySchema::CD":
50
51         MySchema::CD->has_many('tracks', 'MySchema::Tracks');
52
53       And in "MySchema::Tracks":
54
55         MySchema::Tracks->belongs_to('cd', 'MySchema::CD', 'CDID');
56
57       There are several other types of relationships, they are more
58       comprehensively described in DBIx::Class::Relationship.
59

USING JOINS

61       Once you have defined all your relationships, using them in actual
62       joins is fairly simple. The type of relationship that you chose e.g.
63       "has_many", already indicates what sort of join will be performed.
64       "has_many" produces a "LEFT JOIN" for example, which will fetch all the
65       rows on the left side, whether there are matching rows on the right
66       (table being joined to), or not. You can force other types of joins in
67       your relationship, see the DBIx::Class::Relationship docs.
68
69       When performing either a search or a find operation, you can specify
70       which "relations" to also refine your results based on, using the join
71       attribute, like this:
72
73         $schema->resultset('CD')->search(
74           { 'Title' => 'Funky CD',
75             'tracks.Name' => { like => 'T%' }
76           },
77           { join      => 'tracks',
78             order_by  => ['tracks.id'],
79           }
80         );
81
82       If you don't recognise most of this syntax, you should probably go read
83       "search" in DBIx::Class::ResultSet and "ATTRIBUTES" in
84       DBIx::Class::ResultSet, but here's a quick break down:
85
86       The first argument to search is a hashref of the WHERE attributes, in
87       this case a restriction on the Title column in the CD table, and a
88       restriction on the name of the track in the Tracks table, but ONLY for
89       tracks actually related to the chosen CD(s). The second argument is a
90       hashref of attributes to the search, the results will be returned
91       sorted by the "id" of the related tracks.
92
93       The special 'join' attribute specifies which "relationships" to include
94       in the query. The distinction between "relationships" and "tables" is
95       important here, only the "relationship" names are valid.
96
97       This slightly nonsense example will produce SQL similar to:
98
99         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';
100
102       Another common use for joining to related tables, is to fetch the data
103       from both tables in one query, preventing extra round-trips to the
104       database. See the example above in "WHAT ARE JOINS".
105
106       Three techniques are described here. Of the three, only the "prefetch"
107       technique will deal sanely with fetching related objects over a
108       "has_many" relation. The others work fine for 1 to 1 type
109       relationships.
110
111   Whole related objects
112       To fetch entire related objects, e.g. CDs and all Track data, use the
113       'prefetch' attribute:
114
115         $schema->resultset('CD')->search(
116           { 'Title' => 'Funky CD',
117           },
118           { prefetch      => 'tracks',
119             order_by  => ['tracks.id'],
120           }
121         );
122
123       This will produce SQL similar to the following:
124
125         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
127       The syntax of 'prefetch' is the same as 'join' and implies the joining,
128       so there is no need to use both together.
129
130   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:
134
135         $schema->resultset('CD')->search(
136           { 'Title' => 'Funky CD',
137           },
138           { join      => 'tracks',
139             '+select' => ['tracks.Name'],
140             '+as'     => ['track_name'],
141             order_by  => ['tracks.id'],
142           }
143         );
144
145       Which will produce the query:
146
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';
148
149       Note that the '+as' does not produce an SQL 'AS' keyword in the output,
150       see the DBIx::Class::Manual::FAQ for an explanation.
151
152       This type of column restriction has a downside, the returned $result
153       object will have no 'track_name' accessor:
154
155         while(my $result = $search_rs->next) {
156            print $result->track_name; ## ERROR
157         }
158
159       Instead "get_column" must be used:
160
161         while(my $result = $search_rs->next) {
162            print $result->get_column('track_name'); ## WORKS
163         }
164
165   Incomplete related objects
166       In rare circumstances, you may also wish to fetch related data as
167       incomplete objects. The usual reason to do is when the related table
168       has a very large field you don't need for the current data output. This
169       is better solved by storing that field in a separate table which you
170       only join to when needed.
171
172       To fetch an incomplete related object, supply the dotted notation to
173       the '+as' attribute:
174
175         $schema->resultset('CD')->search(
176           { 'Title' => 'Funky CD',
177           },
178           { join      => 'tracks',
179             '+select' => ['tracks.Name'],
180             '+as'     => ['tracks.Name'],
181             order_by  => ['tracks.id'],
182           }
183         );
184
185       Which will produce same query as above;
186
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';
188
189       Now you can access the result using the relationship accessor:
190
191         while(my $result = $search_rs->next) {
192            print $result->tracks->name; ## WORKS
193         }
194
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 than
197       reading its data. Use the "Whole related objects" method as much as
198       possible to avoid confusion in your code later.
199
200       Broken means: Update will not work. Fetching other related objects will
201       not work. Deleting the object will not work.
202

COMPLEX JOINS AND STUFF

204   Across multiple relations
205       For simplicity in the example above, the "Artist" was shown as a simple
206       text field in the "Tracks" table, in reality, you'll want to have the
207       artists in their own table as well, thus to fetch the complete set of
208       data we'll need to join to the Artist table too.
209
210       In "MySchema::Tracks":
211
212         MySchema::Tracks->belongs_to('artist', 'MySchema::Artist', 'ArtistID');
213
214       The search:
215
216         $schema->resultset('CD')->search(
217           { 'Title' => 'Funky CD' },
218           { join      => { 'tracks' => 'artist' },
219           }
220         );
221
222       Which is:
223
224         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';
225
226       To perform joins using relations of the tables you are joining to, use
227       a hashref to indicate the join depth. This can theoretically go as deep
228       as you like (warning: contrived examples!):
229
230         join => { room => { table => 'leg' } }
231
232       To join two relations at the same level, use an arrayref instead:
233
234         join => { room => [ 'chair', 'table' ] }
235
236       Or combine the two:
237
238         join => { room => [ 'chair', { table => 'leg' } ] }
239
240   Table aliases
241       As an aside to all the discussion on joins, note that DBIx::Class uses
242       the "relation names" as table aliases. This is important when you need
243       to add grouping or ordering to your queries:
244
245         $schema->resultset('CD')->search(
246           { 'Title' => 'Funky CD' },
247           { join      => { 'tracks' => 'artist' },
248             order_by  => [ 'tracks.Name', 'artist.Artist' ],
249           }
250         );
251
252         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;
253
254       This is essential if any of your tables have columns with the same
255       names.
256
257       Note that the table of the resultsource the search was performed on, is
258       always aliased to "me".
259
260   Joining to the same table twice
261       There is no magic to this, just do it. The table aliases will
262       automatically be numbered:
263
264         join => [ 'room', 'room' ]
265
266       The aliases are: "room" and "room_2".
267

FURTHER QUESTIONS?

269       Check the list of additional DBIC resources.
270
272       This module is free software copyright by the DBIx::Class (DBIC)
273       authors. You can redistribute it and/or modify it under the same terms
274       as the DBIx::Class library.
275
276
277
278perl v5.32.0                      2020-07-28   DBIx::Class::Manual::Joining(3)
Impressum