Sets the connection instance to be used for executing and transforming this query
When called with a null argument, it will return the current connection instance.
public connection ( Cake\Datasource\ConnectionInterface | null $connection = null ) | ||
$connection | Cake\Datasource\ConnectionInterface | null | instance |
/** * 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; }
/** * Builds the SQL fragment for INSERT INTO. * * @param array $parts The insert parts. * @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 SQL fragment. */ protected function _buildInsertPart($parts, $query, $generator) { $driver = $query->connection()->driver(); $table = $driver->quoteIfAutoQuote($parts[0]); $columns = $this->_stringifyExpressions($parts[1], $generator); return sprintf('INSERT INTO %s (%s)', $table, implode(', ', $columns)); }
/** * Receives a TupleExpression and changes it so that it conforms to this * SQL dialect. * * It transforms expressions looking like '(a, b) IN ((c, d), (e, f)' into an * equivalent expression of the form '((a = c) AND (b = d)) OR ((a = e) AND (b = f))'. * * It can also transform transform expressions where the right hand side is a query * selecting the same amount of columns as the elements in the left hand side of * the expression: * * (a, b) IN (SELECT c, d FROM a_table) is transformed into * * 1 = (SELECT 1 FROM a_table WHERE (a = c) AND (b = d)) * * @param \Cake\Database\Expression\TupleComparison $expression The expression to transform * @param \Cake\Database\Query $query The query to update. * @return void */ protected function _transformTupleComparison(TupleComparison $expression, $query) { $fields = $expression->getField(); if (!is_array($fields)) { return; } $value = $expression->getValue(); $op = $expression->getOperator(); $true = new QueryExpression('1'); if ($value instanceof Query) { $selected = array_values($value->clause('select')); foreach ($fields as $i => $field) { $value->andWhere([$field . " {$op}" => new IdentifierExpression($selected[$i])]); } $value->select($true, true); $expression->setField($true); $expression->setOperator('='); return; } $surrogate = $query->connection()->newQuery()->select($true); if (!is_array(current($value))) { $value = [$value]; } foreach ($value as $tuple) { $surrogate->orWhere(function ($exp) use($fields, $tuple) { foreach (array_values($tuple) as $i => $value) { $exp->add([$fields[$i] => $value]); } return $exp; }); } $expression->setField($true); $expression->setValue($surrogate); $expression->setOperator('='); }
/** * 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)); }
/** * Tests parameter binding * * @return void */ public function testBind() { $query = new Query($this->connection); $driver = $query->connection()->driver(); $createdField = $driver->quoteIfAutoQuote('created'); $results = $query->select(['id', 'comment'])->from('comments')->where(["{$createdField} BETWEEN :foo AND :bar"])->bind(':foo', new \DateTime('2007-03-18 10:50:00'), 'datetime')->bind(':bar', new \DateTime('2007-03-18 10:52:00'), 'datetime')->execute(); $expected = [['id' => '4', 'comment' => 'Fourth Comment for First Article']]; $this->assertEquals($expected, $results->fetchAll('assoc')); $query = new Query($this->connection); $results = $query->select(['id', 'comment'])->from('comments')->where(["{$createdField} BETWEEN :foo AND :bar"])->bind(':foo', '2007-03-18 10:50:00')->bind(':bar', '2007-03-18 10:52:00')->execute(); $this->assertEquals($expected, $results->fetchAll('assoc')); }
/** * 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; }