Tiny POD formatting fix
[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
20containing information about C<CDs>, containing the CD title and it's
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
37L<MySQL's doc on JOINs|http://dev.mysql.com/doc/refman/5.0/en/join.html>.
38
39=head1 DEFINING JOINS AND RELATIONSHIPS
40
41In L<DBIx::Class> each relationship between two tables needs to first
42be defined in the L<ResultSource|DBIx::Class::Manual::Glossary/ResultSource> for the
43table. If the relationship needs to be accessed in both directions
44(i.e. Fetch all tracks of a CD, and fetch the CD data for a Track),
45then it needs to be defined in both tables.
46
47For the CDs/Tracks example, that means writing, in C<MySchema::CD>:
48
49 MySchema::CD->has_many('tracks', 'MySchema::Tracks');
50
51And in C<MySchema::Tracks>:
52
53 MySchema::Tracks->belongs_to('cd', 'MySchema::CD', 'CDID');
54
55There are several other types of relationships, they are more
56comprehensively described in L<DBIx::Class::Relationship>.
57
58=head1 USING JOINS
59
60Once you have defined all your relationships, using them in actual
61joins is fairly simple. The type of relationship that you chose
62e.g. C<has_many>, already indicates what sort of join will be
63performed. C<has_many> produces a C<LEFT JOIN> for example, which will
64fetch all the rows on the left side, whether there are matching rows
65on the right (table being joined to), or not. You can force other
66types of joins in your relationship, see the
67L<DBIx::Class::Relationship> docs.
68
69When performing either a L<search|DBIx::Class::ResultSet/search> or a
70L<find|DBIx::Class::ResultSet/find> operation, you can specify which
71C<relations> to also fetch data from (or sort by), using the
72L<join|DBIx::Class::ResultSet/join> attribute, like this:
73
74 $schema->resultset('CD')->search(
75 { 'Title' => 'Funky CD' },
76 { join => 'tracks',
77 '+select' => [ 'tracks.Name', 'tracks.Artist' ],
78 '+as' => [ 'TrackName', 'ArtistName' ]
79 }
80 );
81
82If you don't recognise most of this syntax, you should probably go
83read L<DBIx::Class::ResultSet/search> and
84L<DBIx::Class::ResultSet/ATTRIBUTES>, but here's a quick break down:
85
86The first argument to search is a hashref of the WHERE attributes, in
87this case a simple restriction on the Title column. The second
88argument is a hashref of attributes to the search, '+select' adds
89extra columns to the select (from the joined table(s) or from
90calculations), and '+as' gives aliases to those fields.
91
92'join' specifies which C<relationships> to include in the query. The
93distinction between C<relationships> and C<tables> is important here,
94only the C<relationship> names are valid.
95
96This example should magically produce SQL like the second select in
97L</WHAT ARE JOINS> above.
98
99=head1 COMPLEX JOINS AND STUFF
100
101=head2 Across multiple relations
102
103For simplicity in the example above, the C<Artist> was shown as a
bc7e7eac 104simple text field in the C<Tracks> table, in reality, you'll want to
b8b1b969 105have the artists in their own table as well, thus to fetch the
106complete set of data we'll need to join to the Artist table too.
107
108In C<MySchema::Tracks>:
109
110 MySchema::Tracks->belongs_to('artist', 'MySchema::Artist', 'ArtistID');
111
112The search:
113
114 $schema->resultset('CD')->search(
115 { 'Title' => 'Funky CD' },
116 { join => { 'tracks' => 'artist' },
117 '+select' => [ 'tracks.Name', 'artist.Artist' ],
118 '+as' => [ 'TrackName', 'ArtistName' ]
119 }
120 );
121
122Which is:
123
124 SELECT me.ID, me.Title, me.Year, tracks.Name, artist.Artist FROM CD me JOIN Tracks tracks ON CD.ID = tracks.CDID JOIN Artists artist ON tracks.ArtistID = artist.ID WHERE me.Title = 'Funky CD';
125
126To perform joins using relations of the tables you are joining to, use
127a hashref to indicate the join depth. This can theoretically go as
128deep as you like (warning, contrived examples!):
129
130 join => { room => { table => 'leg' } }
131
132To join two relations at the same level, use an arrayref instead:
133
134 join => { room => [ 'chair', 'table' ] }
135
136Or combine the two:
137
138 join => { room => [ 'chair', { table => 'leg' } ]
139
140=head2 Table aliases
141
142As an aside to all the discussion on joins, note that L<DBIx::Class>
143uses the C<relation names> as table aliases. This is important when
144you need to add grouping or ordering to your queries:
145
146 $schema->resultset('CD')->search(
147 { 'Title' => 'Funky CD' },
148 { join => { 'tracks' => 'artist' },
149 order_by => [ 'tracks.Name', 'artist.Artist' ],
150 '+select' => [ 'tracks.Name', 'artist.Artist' ],
151 '+as' => [ 'TrackName', 'ArtistName' ]
152 }
153 );
154
155 SELECT me.ID, me.Title, me.Year, tracks.Name, artist.Artist 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;
156
157This is essential if any of your tables have columns with the same names.
158
159Note that the table of the resultsource the search was performed on, is always aliased to C<me>.
160
161=head2 Joining to the same table twice
162
163There is no magic to this, just do it. The table aliases will
164automatically be numbered:
165
166 join => [ 'room', 'room' ]
167
3bad9fe1 168The aliases are: C<room> and C<room_2>.
b8b1b969 169
170=cut
171