Joins

Joins

To join against another table, use the join(), innerJoin(), leftJoin(), or rightJoin() methods, like so:

$query = db_select('node', 'n');
$table_alias = $query->join('users', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));

The above directive will add an INNER JOIN (the default join type) against the "user" table, which will get an alias of "u". The join will be ON the condition " n.uid = u.uid AND u.uid = :uid", where :uid has a value of 5. Note the use of a prepared statement fragment. That allows for the addition of variable join statements in a secure fashion. Never put a literal value or variable directly into a query fragment, just as literals and variables should never be placed into a static query directly (they can lead to SQL injection vulnerabilities). The innerJoin(), leftJoin(), and rightJoin() methods operate identically for their respective join types.

The return value of a join method is the alias of the table that was assigned. If an alias is specified it will be used except in the rare case that alias is already in use by a different table. In that case, the system will assign a different alias.

Note that in place of a literal such as 'user' for the table name, all of the join methods will accept a select query as their first argument. Example:

$query = db_select('node', 'n');
$myselect = db_select('mytable')
->fields('mytable')
->condition('myfield', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');

Joins cannot be chained, so they have to be called separately (see Chaining). If you are chaining multiple functions together do it like this:

$query = db_select('node', 'n');
$query->join('field_data_body', 'b', 'n.nid = b.entity_id');
$query
->fields('n', array('nid', 'title'))
->condition('n.type', 'page')
->condition('n.status', '1')
->orderBy('n.created', 'DESC')
->addTag('node_access');