Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBIx::Class::Manual::JUsernContributed Perl DocDBIx::Class::Manual::Joining(3)

       DBIx::Class::Manual::Joining - Manual on	joining	tables with

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

       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..

       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 "related" information. For example, you may have	a table
       containing information about "CD"s, containing the CD title and its
       year of publication, and	another	table containing all the "Track"s for
       the CDs,	one track per row.

       When you	wish to	extract	information about a particular CD and all its
       tracks, You can either fetch the	CD row,	then make another query	to
       fetch the tracks, or you	can use	a join.	Compare:

	 SELECT	ID, Title, Year	FROM CD	WHERE Title = 'Funky CD';
	 # .. 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 = 'Funky	CD';

       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:

       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 CD, and
       fetch the CD data for a Track), then it needs to	be defined for both

       For the CDs/Tracks example, that	means writing, in "MySchema::CD":

	 MySchema::CD->has_many('tracks', 'MySchema::Tracks');

       And in "MySchema::Tracks":

	 MySchema::Tracks->belongs_to('cd', 'MySchema::CD', 'CDID');

       There are several other types of	relationships, they are	more
       comprehensively described in DBIx::Class::Relationship.

       Once you	have defined all your relationships, using them	in actual
       joins is	fairly simple. The type	of relationship	that you chose e.g.
       "has_many", already indicates what sort of join will be performed.
       "has_many" produces a "LEFT JOIN" 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.

       When performing either a	search or a find operation, you	can specify
       which "relations" to also refine	your results based on, using the join
       attribute, like this:

	   { 'Title' =>	'Funky CD',
	     'tracks.Name' => {	like =>	'T%' }
	   { join      => 'tracks',
	     order_by  => [''],

       If you don't recognise most of this syntax, you should probably go read
       "search"	in DBIx::Class::ResultSet and "ATTRIBUTES" in
       DBIx::Class::ResultSet, but here's a quick break	down:

       The first argument to search is a hashref of the	WHERE attributes, in
       this case a restriction on the Title column in the CD table, and	a
       restriction on the name of the track in the Tracks table, but ONLY for
       tracks actually related to the chosen CD(s). The	second argument	is a
       hashref of attributes to	the search, the	results	will be	returned
       sorted by the "id" of the related tracks.

       The special 'join' attribute specifies which "relationships" to include
       in the query. The distinction between "relationships" and "tables" is
       important here, only the	"relationship" names are valid.

       This slightly nonsense example will produce SQL similar to:

	 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 '';

       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 "WHAT	ARE JOINS".

       Three techniques	are described here. Of the three, only the "prefetch"
       technique will deal sanely with fetching	related	objects	over a
       "has_many" relation. The	others work fine for 1 to 1 type

   Whole related objects
       To fetch	entire related objects,	e.g. CDs and all Track data, use the
       'prefetch' attribute:

	   { 'Title' =>	'Funky CD',
	   { prefetch	   => 'tracks',
	     order_by  => [''],

       This will produce SQL similar to	the following:

	 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' ORDER BY	'';

       The syntax of 'prefetch'	is the same as 'join' and implies the joining,
       so there	is no need to use both together.

   Subset of related fields
       To fetch	a subset or the	related	fields,	the '+select' and '+as'
       attributes can be used. For example, if the CD data is required and
       just the	track name from	the Tracks table:

	   { 'Title' =>	'Funky CD',
	   { join      => 'tracks',
	     '+select' => ['tracks.Name'],
	     '+as'     => ['track_name'],
	     order_by  => [''],

       Which will produce the query:

	 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 '';

       Note that the '+as' does	not produce an SQL 'AS'	keyword	in the output,
       see the DBIx::Class::Manual::FAQ	for an explanation.

       This type of column restriction has a downside, the returned $result
       object will have	no 'track_name'	accessor:

	 while(my $result = $search_rs->next) {
	    print $result->track_name; ## ERROR

       Instead "get_column" must be used:

	 while(my $result = $search_rs->next) {
	    print $result->get_column('track_name'); ##	WORKS

   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.

       To fetch	an incomplete related object, supply the dotted	notation to
       the '+as' attribute:

	   { 'Title' =>	'Funky CD',
	   { join      => 'tracks',
	     '+select' => ['tracks.Name'],
	     '+as'     => ['tracks.Name'],
	     order_by  => [''],

       Which will produce same query as	above;

	 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 '';

       Now you can access the result using the relationship accessor:

	 while(my $result = $search_rs->next) {
	    print $result->tracks->name; ## WORKS

       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 "Whole	related	objects" method	as much	as
       possible	to avoid confusion in your code	later.

       Broken means: Update will not work. Fetching other related objects will
       not work. Deleting the object will not work.

   Across multiple relations
       For simplicity in the example above, the	"Artist" was shown as a	simple
       text field in the "Tracks" 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.

       In "MySchema::Tracks":

	 MySchema::Tracks->belongs_to('artist',	'MySchema::Artist', 'ArtistID');

       The search:

	   { 'Title' =>	'Funky CD' },
	   { join      => { 'tracks' =>	'artist' },

       Which is:

	 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';

       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!):

	 join => { room	=> { table => 'leg' } }

       To join two relations at	the same level,	use an arrayref	instead:

	 join => { room	=> [ 'chair', 'table' ]	}

       Or combine the two:

	 join => { room	=> [ 'chair', {	table => 'leg' } ] }

   Table aliases
       As an aside to all the discussion on joins, note	that DBIx::Class uses
       the "relation names" as table aliases. This is important	when you need
       to add grouping or ordering to your queries:

	   { 'Title' =>	'Funky CD' },
	   { join      => { 'tracks' =>	'artist' },
	     order_by  => [ 'tracks.Name', 'artist.Artist' ],

	 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;

       This is essential if any	of your	tables have columns with the same

       Note that the table of the resultsource the search was performed	on, is
       always aliased to "me".

   Joining to the same table twice
       There is	no magic to this, just do it. The table	aliases	will
       automatically be	numbered:

	 join => [ 'room', 'room' ]

       The aliases are:	"room" and "room_2".

       Check the list of additional DBIC resources.

       This module is free software copyright by the DBIx::Class (DBIC)
       authors.	You can	redistribute it	and/or modify it under the same	terms
       as the DBIx::Class library.

perl v5.24.1			  2016-06-17   DBIx::Class::Manual::Joining(3)


Want to link to this manual page? Use this URL:

home | help