The return value for each of those parts may vary. Some clauses use QueryExpression
to internally store their state, some use arrays and others may use booleans or
integers. This is summary of the return types for each clause.
- update: string The name of the table to update
- set: QueryExpression
- insert: array, will return an array containing the table + columns.
- values: ValuesExpression
- select: array, will return empty array when no fields are set
- distinct: boolean
- from: array of tables
- join: array
- set: array
- where: QueryExpression, returns null when not set
- group: array
- having: QueryExpression, returns null when not set
- order: OrderByExpression, returns null when not set
- limit: integer or QueryExpression, null when not set
- offset: integer or QueryExpression, null when not set
- union: array
/**
* Transforms an insert query that is meant to insert multiple rows at a time,
* otherwise it leaves the query untouched.
*
* The way Firebird works with multi insert is by having multiple select statements
* joined with UNION.
*
* @param \Cake\Database\Query $query The query to translate
* @return \Cake\Database\Query
*/
protected function _insertQueryTranslator($query)
{
$v = $query->clause('values');
if (count($v->values()) === 1 || $v->query()) {
return $query;
}
$newQuery = $query->connection()->newQuery();
$cols = $v->columns();
$placeholder = 0;
$replaceQuery = false;
foreach ($v->values() as $k => $val) {
$fillLength = count($cols) - count($val);
if ($fillLength > 0) {
$val = array_merge($val, array_fill(0, $fillLength, null));
}
foreach ($val as $col => $attr) {
if (!$attr instanceof ExpressionInterface) {
$val[$col] = sprintf(':c%d', $placeholder);
$placeholder++;
}
}
$select = array_combine($cols, $val);
if ($k === 0) {
$replaceQuery = true;
$newQuery->select($select);
continue;
}
$q = $newQuery->connection()->newQuery();
$newQuery->unionAll($q->select($select));
}
if ($replaceQuery) {
$v->query($newQuery);
}
return $query;
}
/**
* Modifies the original insert query to append a "RETURNING *" epilogue
* so that the latest insert id can be retrieved
*
* @param \Cake\Database\Query $query The query to translate.
* @return \Cake\Database\Query
*/
protected function _insertQueryTranslator($query)
{
if (!$query->clause('epilog')) {
$query->epilog('RETURNING *');
}
return $query;
}
/**
* Helper function used to build the string representation of a SELECT clause,
* it constructs the field list taking care of aliasing and
* converting expression objects to string. This function also constructs the
* DISTINCT clause for the query. This has been modified to allow it to support Salesforce
*
* @param array $parts list of fields to be transformed to string
* @param \Cake\Database\Query $query The query that is being compiled
* @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions
* @return string
*/
protected function _buildSelectPart($parts, $query, $generator)
{
$driver = $query->connection()->driver();
$select = 'SELECT %s%s%s';
if ($this->_orderedUnion && $query->clause('union')) {
$select = '(SELECT %s%s%s';
}
$distinct = $query->clause('distinct');
$modifiers = $query->clause('modifier') ?: null;
$normalized = [];
$parts = $this->_stringifyExpressions($parts, $generator);
foreach ($parts as $k => $p) {
if (!is_numeric($k)) {
$p = $p;
//Leave it alone
}
$normalized[] = $p;
}
if ($distinct === true) {
$distinct = 'DISTINCT ';
}
if (is_array($distinct)) {
$distinct = $this->_stringifyExpressions($distinct, $generator);
$distinct = sprintf('DISTINCT ON (%s) ', implode(', ', $distinct));
}
if ($modifiers !== null) {
$modifiers = $this->_stringifyExpressions($modifiers, $generator);
$modifiers = implode(' ', $modifiers) . ' ';
}
return sprintf($select, $distinct, $modifiers, implode(', ', $normalized));
}
/**
* Modify the limit/offset to TSQL
*
* @param \Cake\Database\Query $query The query to translate
* @return \Cake\Database\Query The modified query
*/
protected function _selectQueryTranslator($query)
{
$limit = $query->clause('limit');
$offset = $query->clause('offset');
if ($limit && $offset === null) {
$query->modifier(['_auto_top_' => sprintf('TOP %d', $limit)]);
}
if ($offset !== null && !$query->clause('order')) {
$query->order($query->newExpr()->add('SELECT NULL'));
}
if ($this->_version() < 11 && $offset !== null) {
return $this->_pagingSubquery($query, $limit, $offset);
}
return $query;
}
/**
* Generates the LIMIT part of a SQL query
*
* @param int $limit the limit clause
* @param \Cake\Database\Query $query The query that is being compiled
* @return string
*/
protected function _buildLimitPart($limit, $query)
{
if ($limit === null || $query->clause('offset') === null) {
return '';
}
return sprintf(' FETCH FIRST %d ROWS ONLY', $limit);
}
/**
* Test selecting rows using the page() method.
*
* @return void
*/
public function testSelectPage()
{
$query = new Query($this->connection);
$result = $query->select('id')->from('comments')->limit(1)->page(1)->execute();
$this->assertCount(1, $result);
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$query = new Query($this->connection);
$result = $query->select('id')->from('comments')->limit(1)->page(2)->order(['id' => 'asc'])->execute();
$this->assertCount(1, $result);
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$query = new Query($this->connection);
$query->select('id')->from('comments')->page(3, 10);
$this->assertEquals(10, $query->clause('limit'));
$this->assertEquals(20, $query->clause('offset'));
$query = new Query($this->connection);
$query->select('id')->from('comments')->page(1);
$this->assertEquals(25, $query->clause('limit'));
$this->assertEquals(0, $query->clause('offset'));
$query->select('id')->from('comments')->page(2);
$this->assertEquals(25, $query->clause('limit'));
$this->assertEquals(25, $query->clause('offset'));
}
/**
* 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;
}
/**
* Quotes the table name and columns for an insert query
*
* @param Query $query
* @return void
*/
protected function _quoteInsert($query)
{
list($table, $columns) = $query->clause('insert');
$table = $this->_driver->quoteIdentifier($table);
foreach ($columns as &$column) {
if (is_string($column)) {
$column = $this->_driver->quoteIdentifier($column);
}
}
$query->insert($columns)->into($table);
}
/**
* Returns the passed query after rewriting the DISTINCT clause, so that drivers
* that do not support the "ON" part can provide the actual way it should be done
*
* @param \Cake\Database\Query $original The query to be transformed
* @return \Cake\Database\Query
*/
protected function _transformDistinct($original)
{
if (!is_array($original->clause('distinct'))) {
return $original;
}
$query = clone $original;
$distinct = $query->clause('distinct');
$query->distinct(false);
$order = new OrderByExpression($distinct);
$query->select(function ($q) use($distinct, $order) {
$over = $q->newExpr('ROW_NUMBER() OVER')->add('(PARTITION BY')->add($q->newExpr()->add($distinct)->tieWith(','))->add($order)->add(')')->tieWith(' ');
return ['_cake_distinct_pivot_' => $over];
})->limit(null)->offset(null)->order([], true);
$outer = new Query($query->connection());
$outer->select('*')->from(['_cake_distinct_' => $query])->where(['_cake_distinct_pivot_' => 1]);
// Decorate the original query as that is what the
// end developer will be calling execute() on originally.
$original->decorateResults(function ($row) {
if (isset($row['_cake_distinct_pivot_'])) {
unset($row['_cake_distinct_pivot_']);
}
return $row;
});
return $outer;
}
/**
* Test that cloning goes deep.
*
* @return void
*/
public function testDeepClone()
{
$query = new Query($this->connection);
$query->select(['id', 'title' => $query->func()->concat(['title' => 'literal', 'test'])])->from('articles')->where(['Articles.id' => 1])->offset(10)->limit(1)->order(['Articles.id' => 'DESC']);
$dupe = clone $query;
$this->assertEquals($query->clause('where'), $dupe->clause('where'));
$this->assertNotSame($query->clause('where'), $dupe->clause('where'));
$dupe->where(['Articles.title' => 'thinger']);
$this->assertNotEquals($query->clause('where'), $dupe->clause('where'));
$this->assertNotSame($query->clause('select')['title'], $dupe->clause('select')['title']);
$this->assertEquals($query->clause('order'), $dupe->clause('order'));
$this->assertNotSame($query->clause('order'), $dupe->clause('order'));
$query->order(['Articles.title' => 'ASC']);
$this->assertNotEquals($query->clause('order'), $dupe->clause('order'));
}
/**
* Quotes the table name for an update query
*
* @param \Cake\Database\Query $query The update query to quote.
* @return void
*/
protected function _quoteUpdate($query)
{
$table = $query->clause('update')[0];
if (is_string($table)) {
$query->update($this->_driver->quoteIdentifier($table));
}
}
/**
* Generates the EPILOG part of a SQL query, including special handling
* if we added offset and/or limit wrappers earlier
*
* @param mixed $epilog the epilog clause
* @param \Cake\Database\Query $query The query that is being compiled
* @param \Cake\Database\ValueBinder $generator The placeholder and value binder object
* @return string
*/
protected function _buildEpilogPart($epilog, $query, $generator)
{
if (!is_array($epilog) || !array_key_exists('snelgOracleOrigEpilog', $epilog)) {
$origEpilog = $epilog;
} else {
//We wrapped the original epilog, which might have been an
//ExpressionInterface instead of a simple string
$origEpilog = $epilog['snelgOracleOrigEpilog'];
}
//Duplicate original _sqlCompiler functionality...
if ($origEpilog instanceof ExpressionInterface) {
$origEpilog = [$origEpilog->sql($generator)];
}
$origEpilog = $this->_stringifyExpressions((array) $origEpilog, $generator);
$epilogSql = sprintf(' %s', implode(', ', $origEpilog));
//...and then add our own wrappers.
/*
* We need to double-check that "limit" and/or "offset"
* are actually set because calls to $query->count() (e.g. in Paginator
* component) reset the Query "limit" and "offset" to null
*/
if (is_array($epilog)) {
if (!empty($epilog['snelgOracleLimitEndWrap']) && intval($query->clause('limit') > 0)) {
$epilogSql .= $epilog['snelgOracleLimitEndWrap'];
}
if (!empty($epilog['snelgOracleOffsetEndWrap']) && intval($query->clause('offset') > 0)) {
$epilogSql .= $epilog['snelgOracleOffsetEndWrap'];
}
}
return $epilogSql;
}
/**
* Transforms an insert query that is meant to insert multiple rows at a time,
* otherwise it leaves the query untouched.
*
* The way SQLite works with multi insert is by having multiple select statements
* joined with UNION.
*
* @param \Cake\Database\Query $query The query to translate
* @return Query
*/
protected function _insertQueryTranslator($query)
{
$v = $query->clause('values');
if (count($v->values()) === 1 || $v->query()) {
return $query;
}
$newQuery = $query->connection()->newQuery();
$cols = $v->columns();
foreach ($v->values() as $k => $val) {
$fillLength = count($cols) - count($val);
if ($fillLength > 0) {
$val = array_merge($val, array_fill(0, $fillLength, null));
}
$val = array_map(function ($val) {
return $val instanceof ExpressionInterface ? $val : '?';
}, $val);
$select = array_combine($cols, $val);
if ($k === 0) {
$newQuery->select($select);
continue;
}
$q = $newQuery->connection()->newQuery();
$newQuery->unionAll($q->select($select));
}
if ($newQuery->type()) {
$v->query($newQuery);
}
return $query;
}
/**
* Available options are:
*
* - for: The id of the record to read.
* - direct: Boolean, whether to return only the direct (true), or all (false) children,
* defaults to false (all children).
* - order : The order to apply on found nodes. Default on 'model_sort_fieldname' config
*
* If the direct option is set to true, only the direct children are returned (based upon the parent_id field)
*
* @param \Cake\ORM\Query $query
* @param array $options Array of options as described above
* @return \Cake\ORM\Query
*/
public function findChildren(Query $query, array $options)
{
$default_options = ['direct' => false, 'sort' => []];
$options = array_merge($default_options, $options);
$for = isset($options['for']) ? $options['for'] : null;
if (empty($for)) {
throw new \InvalidArgumentException("The 'for' key is required for find('children')");
}
if ($options['direct']) {
/*
* Add order clause if not already set
*/
if ($query->clause('order') === null) {
$sort = !empty($options['sort']) ? $options['sort'] : [$this->config('model_sort_fieldname') => 'asc'];
$query->order($sort);
}
$query->where([$this->config('model_parent_id_fieldname') => $for]);
return $query;
} else {
/*
SELECT nodes.*, t2.max_level as level
FROM nodes
INNER JOIN
(
SELECT nac.node_id, MAX(level) as max_level
FROM nodes_ancestors nac
INNER JOIN
(
SELECT node_id
FROM nodes_ancestors
WHERE ancestor_id = 1
) t ON t.node_id = nac.node_id
GROUP BY node_id
) t2 ON nodes.id = t2.node_id
ORDER BY max_level ASC, sort ASC
*/
$ancestorTable = $this->getAncestorTable($this->_table);
$subquery2 = $ancestorTable->find()->select(['nac_node_id' => 'node_id'])->where(['ancestor_id' => $for]);
$subquery1 = $ancestorTable->find()->select(['node_id' => 'nac_node_id', 'max_level' => $subquery2->func()->max('level')])->join(['table' => $subquery2, 'alias' => 't', 'type' => 'INNER', 'conditions' => 't.nac_node_id = Ancestors.node_id'])->group(['node_id']);
$selected_fields = $this->_table->schema()->columns();
$selected_fields['level'] = 't2.max_level';
$query->select($selected_fields)->join(['table' => $subquery1, 'alias' => 't2', 'type' => 'INNER', 'conditions' => $this->_table->alias() . '.id = t2.node_id'])->order(['max_level' => 'ASC', 'sort' => 'ASC']);
return $query;
// /*
// SELECT n2.*
// FROM nodes n1
// INNER JOIN nodes_ancestors ON ancestor_id = n1.id
// INNER JOIN nodes n2 ON node_id = n2.id
// WHERE ancestor_id = 1
// ORDER BY level ASC, n1.sort ASC
// */
// /*
// * 1) Find all nodes linked to the ancestors that are under the searched item
// * 2) Create a new collection based on the items as we don't want a Collection of ancestors
// * 3) if $options['multilevel'] is true -> organize items as a multilevel array
// */
// $ancestor_table = $this->getAncestorTable($this->_table);
// $model_alias = $this->_table->alias();
// $ancestor_table->belongsTo($model_alias, [
// 'className' => $model_alias,
// 'foreignKey' => 'node_id',
// 'propertyName' => 'linked_node'
// ]);
// $order = [];
// $order['level'] = 'ASC';
// if(isset($options['sort']))
// {
// $order = $order + $options['sort'];
// }
// $query = $ancestor_table->find();
// $query->contain([$model_alias]);
// $query->order($order);
// $query->where(['ancestor_id' => $for]);
// $nodes = [];
// foreach($query as $ancestor_entity){
// $nodes[] = $ancestor_entity->linked_node;
// }
// return new \Cake\Collection\Collection($nodes);
}
}