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;
}