Tables can be passed as an array of strings, an array describing the
join parts, an array with multiple join descriptions, or a single string.
By default this function will append any passed argument to the list of tables
to be joined, unless the third argument is set to true.
When no join type is specified an INNER JOIN is used by default:
$query->join(['authors']) will produce INNER JOIN authors ON 1 = 1
It is also possible to alias joins using the array key:
$query->join(['a' => 'authors']) will produce INNER JOIN authors a ON 1 = 1
A join can be fully described and aliased using the array notation:
$query->join([
'a' => [
'table' => 'authors',
'type' => 'LEFT',
'conditions' => 'a.id = b.author_id'
]
]);
Produces LEFT JOIN authors a ON a.id = b.author_id
You can even specify multiple joins in an array, including the full description:
$query->join([
'a' => [
'table' => 'authors',
'type' => 'LEFT',
'conditions' => 'a.id = b.author_id'
],
'p' => [
'table' => 'publishers',
'type' => 'INNER',
'conditions' => 'p.id = b.publisher_id AND p.name = "Cake Software Foundation"'
]
]);
LEFT JOIN authors a ON a.id = b.author_id
INNER JOIN publishers p ON p.id = b.publisher_id AND p.name = "Cake Software Foundation"
### Using conditions and types
Conditions can be expressed, as in the examples above, using a string for comparing
columns, or string with already quoted literal values. Additionally it is
possible to use conditions expressed in arrays or expression objects.
When using arrays for expressing conditions, it is often desirable to convert
the literal values to the correct database representation. This is achieved
using the second parameter of this function.
$query->join(['a' => [
'table' => 'articles',
'conditions' => [
'a.posted >=' => new DateTime('-3 days'),
'a.published' => true,
'a.author_id = authors.id'
]
]], ['a.posted' => 'datetime', 'a.published' => 'boolean'])
### Overwriting joins
When creating aliased joins using the array notation, you can override
previous join definitions by using the same alias in consequent
calls to this function or you can replace all previously defined joins
with another list if the third parameter for this function is set to true.
$query->join(['alias' => 'table']); // joins table with as alias
$query->join(['alias' => 'another_table']); // joins another_table with as alias
$query->join(['something' => 'different_table'], [], true); // resets joins list
| public join ( array | string | null $tables = null, array $types = [], boolean $overwrite = false ) | ||
| $tables | array | string | null | list of tables to be joined in the query |
| $types | array | associative array of type names used to bind values to query |
| $overwrite | boolean | whether to reset joins with passed list or not |
/**
* Tests that it is possible to use a subquery in a join clause
*
* @return void
*/
public function testSubqueyInJoin()
{
$subquery = (new Query($this->connection))->select('*')->from('authors');
$query = new Query($this->connection);
$result = $query->select(['title', 'name'])->from('articles')->join(['b' => $subquery])->execute();
$this->assertCount(self::ARTICLE_COUNT * self::AUTHOR_COUNT, $result, 'Cross join causes multiplication');
$subquery->where(['id' => 1]);
$result = $query->execute();
$this->assertCount(3, $result);
$query->join(['b' => ['table' => $subquery, 'conditions' => ['b.id = articles.id']]], [], true);
$result = $query->execute();
$this->assertCount(1, $result);
}
/**
* Quotes all identifiers in each of the clauses of a query
*
* @param \Cake\Database\Query $query The query to quote.
* @return void
*/
protected function _quoteParts($query)
{
foreach (['distinct', 'select', 'from', 'group'] as $part) {
$contents = $query->clause($part);
if (!is_array($contents)) {
continue;
}
$result = $this->_basicQuoter($contents);
if (!empty($result)) {
$query->{$part}($result, true);
}
}
$joins = $query->clause('join');
if ($joins) {
$joins = $this->_quoteJoins($joins);
$query->join($joins, [], true);
}
}
public function beforeFind(Event $event, Query $query, ArrayObject $options)
{
$query->join(['table' => 'pessoas_associacoes', 'alias' => 'PessoasAssociacoes', 'type' => 'INNER', 'conditions' => ['PessoasAssociacoes.pessoa_id = ' . $this->aliasField('pessoa_id'), 'PessoasAssociacoes.tipo_associacao' => 7, 'PessoasAssociacoes.status !=' => 9]]);
$query->group($this->aliasField('pessoa_id'));
}
/**
* Test removeJoin().
*
* @return void
*/
public function testRemoveJoin()
{
$query = new Query($this->connection);
$query->select(['id', 'title'])->from('articles')->join(['authors' => ['type' => 'INNER', 'conditions' => ['articles.author_id = authors.id']]]);
$this->assertArrayHasKey('authors', $query->join());
$this->assertSame($query, $query->removeJoin('authors'));
$this->assertArrayNotHasKey('authors', $query->join());
}