join() public method

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
See also: Cake\Database\Type
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
示例#1
0
 /**
  * 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);
     }
 }
示例#3
0
 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'));
 }
示例#4
-1
 /**
  * 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());
 }