You can optionally pass a single raw SQL string or an array or expressions in
any format accepted by \Cake\Database\Expression\QueryExpression:
$expression = $query->newExpr(); // Returns an empty expression object
$expression = $query->newExpr('Table.column = Table2.column'); // Return a raw SQL expression
/**
* 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 $this->_transformDistinct($query);
}
/**
* Tests that case statements work correctly for various use-cases.
*
* @return void
*/
public function testSqlCaseStatement()
{
$query = new Query($this->connection);
$publishedCase = $query->newExpr()->addCase($query->newExpr()->add(['published' => 'Y']), 1, 'integer');
$notPublishedCase = $query->newExpr()->addCase($query->newExpr()->add(['published' => 'N']), 1, 'integer');
//Postgres requires the case statement to be cast to a integer
if ($this->connection->driver() instanceof \Cake\Database\Driver\Postgres) {
$publishedCase = $query->func()->cast([$publishedCase, 'integer' => 'literal'])->type(' AS ');
$notPublishedCase = $query->func()->cast([$notPublishedCase, 'integer' => 'literal'])->type(' AS ');
}
$results = $query->select(['published' => $query->func()->sum($publishedCase), 'not_published' => $query->func()->sum($notPublishedCase)])->from(['comments'])->execute()->fetchAll('assoc');
$this->assertEquals(5, $results[0]['published']);
$this->assertEquals(1, $results[0]['not_published']);
$query = new Query($this->connection);
$query->insert(['article_id', 'user_id', 'comment', 'published'])->into('comments')->values(['article_id' => 2, 'user_id' => 1, 'comment' => 'In limbo', 'published' => 'L'])->execute()->closeCursor();
$query = new Query($this->connection);
$conditions = [$query->newExpr()->add(['published' => 'Y']), $query->newExpr()->add(['published' => 'N'])];
$values = ['Published', 'Not published', 'None'];
$results = $query->select(['id', 'comment', 'status' => $query->newExpr()->addCase($conditions, $values)])->from(['comments'])->execute()->fetchAll('assoc');
$this->assertEquals('Published', $results[2]['status']);
$this->assertEquals('Not published', $results[3]['status']);
$this->assertEquals('None', $results[6]['status']);
}
/**
* Test that insert can use expression objects as values.
*
* @return void
*/
public function testInsertExpressionValues()
{
$query = new Query($this->connection);
$query->insert(['title', 'author_id'])->into('articles')->values(['title' => $query->newExpr("SELECT 'jose' FROM DUAL"), 'author_id' => 99]);
$result = $query->execute();
$result->closeCursor();
//PDO_SQLSRV returns -1 for successful inserts when using INSERT ... OUTPUT
if (!$this->connection->driver() instanceof \Cake\Database\Driver\Sqlserver) {
$this->assertCount(1, $result);
}
$result = (new Query($this->connection))->select('*')->from('articles')->where(['author_id' => 99])->execute();
$this->assertCount(1, $result);
$expected = ['id' => 4, 'title' => 'jose', 'body' => null, 'author_id' => '99', 'published' => 'N'];
$this->assertEquals($expected, $result->fetch('assoc'));
$subquery = new Query($this->connection);
$subquery->select(['name'])->from('authors')->where(['id' => 1]);
$query = new Query($this->connection);
$query->insert(['title', 'author_id'])->into('articles')->values(['title' => $subquery, 'author_id' => 100]);
$result = $query->execute();
$result->closeCursor();
//PDO_SQLSRV returns -1 for successful inserts when using INSERT ... OUTPUT
if (!$this->connection->driver() instanceof \Cake\Database\Driver\Sqlserver) {
$this->assertCount(1, $result);
}
$result = (new Query($this->connection))->select('*')->from('articles')->where(['author_id' => 100])->execute();
$this->assertCount(1, $result);
$expected = ['id' => 5, 'title' => 'mariano', 'body' => null, 'author_id' => '100', 'published' => 'N'];
$this->assertEquals($expected, $result->fetch('assoc'));
}
/**
* Tests that insert query parts get quoted automatically
*
* @return void
*/
public function testQuotingInsert()
{
$this->connection->driver()->autoQuoting(true);
$query = new Query($this->connection);
$sql = $query->insert(['bar', 'baz'])->into('foo')->where(['something' => 'value'])->sql();
$this->assertQuotedQuery('INSERT INTO <foo> \\(<bar>, <baz>\\)', $sql);
$query = new Query($this->connection);
$sql = $query->insert([$query->newExpr()->add('bar')])->into('foo')->where(['something' => 'value'])->sql();
$this->assertQuotedQuery('INSERT INTO <foo> \\(\\(bar\\)\\)', $sql);
}