Tables can be passed as an array of strings, array of expression
objects, a single expression or a single string.
If an array is passed, keys will be used to alias tables using the value as the
real field to be aliased. It is possible to alias strings, ExpressionInterface objects or
even other Query objects.
By default this function will append any passed argument to the list of tables
to be selected from, unless the second argument is set to true.
This method can be used for select, update and delete statements.
### Examples:
$query->from(['p' => 'posts']); // Produces FROM posts p
$query->from('authors'); // Appends authors: FROM posts p, authors
$query->from(['products'], true); // Resets the list: FROM products
$query->from(['sub' => $countQuery]); // FROM (SELECT ...) sub
| public from ( array | string $tables = [], boolean $overwrite = false ) | ||
| $tables | array | string | tables to be added to the list. This argument, can be passed as an array of strings, array of expression objects, or a single string. See the examples above for the valid call types. |
| $overwrite | boolean | whether to reset tables with passed list or not |
/**
* Apply translation steps to delete queries.
*
* Chops out aliases on delete query conditions as most database dialects do not
* support aliases in delete queries. This also removes aliases
* in table names as they frequently don't work either.
*
* We are intentionally not supporting deletes with joins as they have even poorer support.
*
* @param \Cake\Database\Query $query The query to translate
* @return \Cake\Database\Query The modified query
*/
protected function _deleteQueryTranslator($query)
{
$hadAlias = false;
$tables = [];
foreach ($query->clause('from') as $alias => $table) {
if (is_string($alias)) {
$hadAlias = true;
}
$tables[] = $table;
}
if ($hadAlias) {
$query->from($tables, true);
}
if (!$hadAlias) {
return $query;
}
$conditions = $query->clause('where');
if ($conditions) {
$conditions->traverse(function ($condition) {
if (!$condition instanceof Comparison) {
return $condition;
}
$field = $condition->getField();
if ($field instanceof ExpressionInterface || strpos($field, '.') === false) {
return $condition;
}
list(, $field) = explode('.', $field);
$condition->setField($field);
return $condition;
});
}
return $query;
}
/**
* Tests it is possible to select aliased fields
*
* @return void
*/
public function testSelectAliasedFieldsFromTable()
{
$query = new Query($this->connection);
$result = $query->select(['text' => 'body', 'author_id'])->from('articles')->execute();
$this->assertEquals(['text' => 'First Article Body', 'author_id' => 1], $result->fetch('assoc'));
$this->assertEquals(['text' => 'Second Article Body', 'author_id' => 3], $result->fetch('assoc'));
$query = new Query($this->connection);
$result = $query->select(['text' => 'body', 'author' => 'author_id'])->from('articles')->execute();
$this->assertEquals(['text' => 'First Article Body', 'author' => 1], $result->fetch('assoc'));
$this->assertEquals(['text' => 'Second Article Body', 'author' => 3], $result->fetch('assoc'));
$query = new Query($this->connection);
$query->select(['text' => 'body'])->select(['author_id', 'foo' => 'body']);
$result = $query->from('articles')->execute();
$this->assertEquals(['foo' => 'First Article Body', 'text' => 'First Article Body', 'author_id' => 1], $result->fetch('assoc'));
$this->assertEquals(['foo' => 'Second Article Body', 'text' => 'Second Article Body', 'author_id' => 3], $result->fetch('assoc'));
$query = new Query($this->connection);
$exp = $query->newExpr('1 + 1');
$comp = $query->newExpr(['author_id +' => 2]);
$result = $query->select(['text' => 'body', 'two' => $exp, 'three' => $comp])->from('articles')->execute();
$this->assertEquals(['text' => 'First Article Body', 'two' => 2, 'three' => 3], $result->fetch('assoc'));
$this->assertEquals(['text' => 'Second Article Body', 'two' => 2, 'three' => 5], $result->fetch('assoc'));
}
/**
* Tests that empty values don't set where clauses.
*
* @return void
*/
public function testWhereEmptyValues()
{
$query = new Query($this->connection);
$query->from('comments')->where('');
$this->assertCount(0, $query->clause('where'));
$query->where([]);
$this->assertCount(0, $query->clause('where'));
}