.\" Automatically generated by Pod::Man 4.09 (Pod::Simple 3.35)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.if !\nF .nr F 0
.if \nF>0 \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    if !\nF==2 \{\
.        nr % 0
.        nr F 2
.    \}
.\}
.\" ========================================================================
.\"
.IX Title "DBIx::Class::Manual::Joining 3pm"
.TH DBIx::Class::Manual::Joining 3pm "2018-04-19" "perl v5.26.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBIx::Class::Manual::Joining \- Manual on joining tables with DBIx::Class
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This document should help you to use DBIx::Class if you are trying
to convert your normal \s-1SQL\s0 queries into DBIx::Class based queries, if
you use joins extensively (and also probably if you don't).
.SH "WHAT ARE JOINS"
.IX Header "WHAT ARE JOINS"
If you ended up here and you don't actually know what joins are yet,
then you should likely try the DBIx::Class::Manual::Intro
instead. Skip this part if you know what joins are..
.PP
But I'll explain anyway. Assuming you have created your database in a
more or less sensible way, you will end up with several tables that
contain \f(CW\*(C`related\*(C'\fR information. For example, you may have a table
containing information about \f(CW\*(C`CD\*(C'\fRs, containing the \s-1CD\s0 title and its
year of publication, and another table containing all the \f(CW\*(C`Track\*(C'\fRs
for the CDs, one track per row.
.PP
When you wish to extract information about a particular \s-1CD\s0 and all
its tracks, You can either fetch the \s-1CD\s0 row, then make another query
to fetch the tracks, or you can use a join. Compare:
.PP
.Vb 3
\&  SELECT ID, Title, Year FROM CD WHERE Title = \*(AqFunky CD\*(Aq;
\&  # .. Extract the ID, which is 10
\&  SELECT Name, Artist FROM Tracks WHERE CDID = 10;
\&
\&  SELECT cd.ID, cd.Title, cd.Year, tracks.Name, tracks.Artist FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = \*(AqFunky CD\*(Aq;
.Ve
.PP
So, joins are a way of extending simple select statements to include
fields from other, related, tables. There are various types of joins,
depending on which combination of the data you wish to retrieve, see
MySQL's doc on JOINs:
<http://dev.mysql.com/doc/refman/5.0/en/join.html>.
.SH "DEFINING JOINS AND RELATIONSHIPS"
.IX Header "DEFINING JOINS AND RELATIONSHIPS"
In DBIx::Class each relationship between two tables needs to first
be defined in the ResultSource for the
table. If the relationship needs to be accessed in both directions
(i.e. Fetch all tracks of a \s-1CD,\s0 and fetch the \s-1CD\s0 data for a Track),
then it needs to be defined for both tables.
.PP
For the CDs/Tracks example, that means writing, in \f(CW\*(C`MySchema::CD\*(C'\fR:
.PP
.Vb 1
\&  MySchema::CD\->has_many(\*(Aqtracks\*(Aq, \*(AqMySchema::Tracks\*(Aq);
.Ve
.PP
And in \f(CW\*(C`MySchema::Tracks\*(C'\fR:
.PP
.Vb 1
\&  MySchema::Tracks\->belongs_to(\*(Aqcd\*(Aq, \*(AqMySchema::CD\*(Aq, \*(AqCDID\*(Aq);
.Ve
.PP
There are several other types of relationships, they are more
comprehensively described in DBIx::Class::Relationship.
.SH "USING JOINS"
.IX Header "USING JOINS"
Once you have defined all your relationships, using them in actual
joins is fairly simple. The type of relationship that you chose
e.g. \f(CW\*(C`has_many\*(C'\fR, already indicates what sort of join will be
performed. \f(CW\*(C`has_many\*(C'\fR produces a \f(CW\*(C`LEFT JOIN\*(C'\fR for example, which will
fetch all the rows on the left side, whether there are matching rows
on the right (table being joined to), or not. You can force other
types of joins in your relationship, see the
DBIx::Class::Relationship docs.
.PP
When performing either a search or a
find operation, you can specify which
\&\f(CW\*(C`relations\*(C'\fR to also refine your results based on, using the
join attribute, like this:
.PP
.Vb 8
\&  $schema\->resultset(\*(AqCD\*(Aq)\->search(
\&    { \*(AqTitle\*(Aq => \*(AqFunky CD\*(Aq,
\&      \*(Aqtracks.Name\*(Aq => { like => \*(AqT%\*(Aq }
\&    },
\&    { join      => \*(Aqtracks\*(Aq,
\&      order_by  => [\*(Aqtracks.id\*(Aq],
\&    }
\&  );
.Ve
.PP
If you don't recognise most of this syntax, you should probably go
read \*(L"search\*(R" in DBIx::Class::ResultSet and
\&\*(L"\s-1ATTRIBUTES\*(R"\s0 in DBIx::Class::ResultSet, but here's a quick break down:
.PP
The first argument to search is a hashref of the \s-1WHERE\s0 attributes, in
this case a restriction on the Title column in the \s-1CD\s0 table, and a
restriction on the name of the track in the Tracks table, but \s-1ONLY\s0 for
tracks actually related to the chosen \s-1CD\s0(s). The second argument is a
hashref of attributes to the search, the results will be returned
sorted by the \f(CW\*(C`id\*(C'\fR of the related tracks.
.PP
The special 'join' attribute specifies which \f(CW\*(C`relationships\*(C'\fR to
include in the query. The distinction between \f(CW\*(C`relationships\*(C'\fR and
\&\f(CW\*(C`tables\*(C'\fR is important here, only the \f(CW\*(C`relationship\*(C'\fR names are valid.
.PP
This slightly nonsense example will produce \s-1SQL\s0 similar to:
.PP
.Vb 1
\&  SELECT cd.ID, cd.Title, cd.Year FROM CD cd JOIN Tracks tracks ON cd.ID = tracks.CDID WHERE cd.Title = \*(AqFunky CD\*(Aq AND tracks.Name LIKE \*(AqT%\*(Aq ORDER BY \*(Aqtracks.id\*(Aq;
.Ve
.SH "FETCHING RELATED DATA"
.IX Header "FETCHING RELATED DATA"
Another common use for joining to related tables, is to fetch the data
from both tables in one query, preventing extra round-trips to the
database. See the example above in \*(L"\s-1WHAT ARE JOINS\*(R"\s0.
.PP
Three techniques are described here. Of the three, only the
\&\f(CW\*(C`prefetch\*(C'\fR technique will deal sanely with fetching related objects
over a \f(CW\*(C`has_many\*(C'\fR relation. The others work fine for 1 to 1 type
relationships.
.SS "Whole related objects"
.IX Subsection "Whole related objects"
To fetch entire related objects, e.g. CDs and all Track data, use the
\&'prefetch' attribute:
.PP
.Vb 7
\&  $schema\->resultset(\*(AqCD\*(Aq)\->search(
\&    { \*(AqTitle\*(Aq => \*(AqFunky CD\*(Aq,
\&    },
\&    { prefetch      => \*(Aqtracks\*(Aq,
\&      order_by  => [\*(Aqtracks.id\*(Aq],
\&    }
\&  );
.Ve
.PP
This will produce \s-1SQL\s0 similar to the following:
.PP
.Vb 1
\&  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 = \*(AqFunky CD\*(Aq ORDER BY \*(Aqtracks.id\*(Aq;
.Ve
.PP
The syntax of 'prefetch' is the same as 'join' and implies the
joining, so there is no need to use both together.
.SS "Subset of related fields"
.IX Subsection "Subset of related fields"
To fetch a subset or the related fields, the '+select' and '+as'
attributes can be used. For example, if the \s-1CD\s0 data is required and
just the track name from the Tracks table:
.PP
.Vb 9
\&  $schema\->resultset(\*(AqCD\*(Aq)\->search(
\&    { \*(AqTitle\*(Aq => \*(AqFunky CD\*(Aq,
\&    },
\&    { join      => \*(Aqtracks\*(Aq,
\&      \*(Aq+select\*(Aq => [\*(Aqtracks.Name\*(Aq],
\&      \*(Aq+as\*(Aq     => [\*(Aqtrack_name\*(Aq],
\&      order_by  => [\*(Aqtracks.id\*(Aq],
\&    }
\&  );
.Ve
.PP
Which will produce the query:
.PP
.Vb 1
\&  SELECT cd.ID, cd.Title, cd.Year, tracks.Name FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = \*(AqFunky CD\*(Aq ORDER BY \*(Aqtracks.id\*(Aq;
.Ve
.PP
Note that the '+as' does not produce an \s-1SQL\s0 '\s-1AS\s0' keyword in the
output, see the DBIx::Class::Manual::FAQ for an explanation.
.PP
This type of column restriction has a downside, the returned \f(CW$result\fR
object will have no 'track_name' accessor:
.PP
.Vb 3
\&  while(my $result = $search_rs\->next) {
\&     print $result\->track_name; ## ERROR
\&  }
.Ve
.PP
Instead \f(CW\*(C`get_column\*(C'\fR must be used:
.PP
.Vb 3
\&  while(my $result = $search_rs\->next) {
\&     print $result\->get_column(\*(Aqtrack_name\*(Aq); ## WORKS
\&  }
.Ve
.SS "Incomplete related objects"
.IX Subsection "Incomplete related objects"
In rare circumstances, you may also wish to fetch related data as
incomplete objects. The usual reason to do is when the related table
has a very large field you don't need for the current data
output. This is better solved by storing that field in a separate
table which you only join to when needed.
.PP
To fetch an incomplete related object, supply the dotted notation to the '+as' attribute:
.PP
.Vb 9
\&  $schema\->resultset(\*(AqCD\*(Aq)\->search(
\&    { \*(AqTitle\*(Aq => \*(AqFunky CD\*(Aq,
\&    },
\&    { join      => \*(Aqtracks\*(Aq,
\&      \*(Aq+select\*(Aq => [\*(Aqtracks.Name\*(Aq],
\&      \*(Aq+as\*(Aq     => [\*(Aqtracks.Name\*(Aq],
\&      order_by  => [\*(Aqtracks.id\*(Aq],
\&    }
\&  );
.Ve
.PP
Which will produce same query as above;
.PP
.Vb 1
\&  SELECT cd.ID, cd.Title, cd.Year, tracks.Name FROM CD JOIN Tracks ON CD.ID = tracks.CDID WHERE cd.Title = \*(AqFunky CD\*(Aq ORDER BY \*(Aqtracks.id\*(Aq;
.Ve
.PP
Now you can access the result using the relationship accessor:
.PP
.Vb 3
\&  while(my $result = $search_rs\->next) {
\&     print $result\->tracks\->name; ## WORKS
\&  }
.Ve
.PP
However, this will produce broken objects. If the tracks id column is
not fetched, the object will not be usable for any operation other
than reading its data. Use the \*(L"Whole related objects\*(R" method as
much as possible to avoid confusion in your code later.
.PP
Broken means: Update will not work. Fetching other related objects
will not work. Deleting the object will not work.
.SH "COMPLEX JOINS AND STUFF"
.IX Header "COMPLEX JOINS AND STUFF"
.SS "Across multiple relations"
.IX Subsection "Across multiple relations"
For simplicity in the example above, the \f(CW\*(C`Artist\*(C'\fR was shown as a
simple text field in the \f(CW\*(C`Tracks\*(C'\fR table, in reality, you'll want to
have the artists in their own table as well, thus to fetch the
complete set of data we'll need to join to the Artist table too.
.PP
In \f(CW\*(C`MySchema::Tracks\*(C'\fR:
.PP
.Vb 1
\&  MySchema::Tracks\->belongs_to(\*(Aqartist\*(Aq, \*(AqMySchema::Artist\*(Aq, \*(AqArtistID\*(Aq);
.Ve
.PP
The search:
.PP
.Vb 5
\&  $schema\->resultset(\*(AqCD\*(Aq)\->search(
\&    { \*(AqTitle\*(Aq => \*(AqFunky CD\*(Aq },
\&    { join      => { \*(Aqtracks\*(Aq => \*(Aqartist\*(Aq },
\&    }
\&  );
.Ve
.PP
Which is:
.PP
.Vb 1
\&  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 = \*(AqFunky CD\*(Aq;
.Ve
.PP
To perform joins using relations of the tables you are joining to, use
a hashref to indicate the join depth. This can theoretically go as
deep as you like (warning: contrived examples!):
.PP
.Vb 1
\&  join => { room => { table => \*(Aqleg\*(Aq } }
.Ve
.PP
To join two relations at the same level, use an arrayref instead:
.PP
.Vb 1
\&  join => { room => [ \*(Aqchair\*(Aq, \*(Aqtable\*(Aq ] }
.Ve
.PP
Or combine the two:
.PP
.Vb 1
\&  join => { room => [ \*(Aqchair\*(Aq, { table => \*(Aqleg\*(Aq } ] }
.Ve
.SS "Table aliases"
.IX Subsection "Table aliases"
As an aside to all the discussion on joins, note that DBIx::Class
uses the \f(CW\*(C`relation names\*(C'\fR as table aliases. This is important when
you need to add grouping or ordering to your queries:
.PP
.Vb 6
\&  $schema\->resultset(\*(AqCD\*(Aq)\->search(
\&    { \*(AqTitle\*(Aq => \*(AqFunky CD\*(Aq },
\&    { join      => { \*(Aqtracks\*(Aq => \*(Aqartist\*(Aq },
\&      order_by  => [ \*(Aqtracks.Name\*(Aq, \*(Aqartist.Artist\*(Aq ],
\&    }
\&  );
\&
\&  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 = \*(AqFunky CD\*(Aq ORDER BY tracks.Name, artist.Artist;
.Ve
.PP
This is essential if any of your tables have columns with the same names.
.PP
Note that the table of the resultsource the search was performed on, is always aliased to \f(CW\*(C`me\*(C'\fR.
.SS "Joining to the same table twice"
.IX Subsection "Joining to the same table twice"
There is no magic to this, just do it. The table aliases will
automatically be numbered:
.PP
.Vb 1
\&  join => [ \*(Aqroom\*(Aq, \*(Aqroom\*(Aq ]
.Ve
.PP
The aliases are: \f(CW\*(C`room\*(C'\fR and \f(CW\*(C`room_2\*(C'\fR.
.SH "FURTHER QUESTIONS?"
.IX Header "FURTHER QUESTIONS?"
Check the list of additional \s-1DBIC\s0 resources.
.SH "COPYRIGHT AND LICENSE"
.IX Header "COPYRIGHT AND LICENSE"
This module is free software copyright
by the DBIx::Class (\s-1DBIC\s0) authors. You can
redistribute it and/or modify it under the same terms as the
DBIx::Class library.