### Example:
$query->func()->count('*');
$query->func()->dateDiff(['2012-01-05', '2012-01-02'])
| public func ( ) : Cake\Database\FunctionsBuilder | ||
| return | Cake\Database\FunctionsBuilder | |
/**
* 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']);
}
/**
* Tests that functions are correctly transformed and their parameters are bound
*
* @group FunctionExpression
* @return void
*/
public function testSQLFunctions()
{
$query = new Query($this->connection);
$result = $query->select(function ($q) {
return ['total' => $q->func()->count('*')];
})->from('comments')->execute();
$expected = [['total' => 6]];
$this->assertEquals($expected, $result->fetchAll('assoc'));
$query = new Query($this->connection);
$result = $query->select(['c' => $query->func()->concat([$query->func()->to_char([new IdentifierExpression('comment')]), ' is appended'])])->from('comments')->order(['c' => 'ASC'])->limit(1)->execute();
$expected = [['c' => 'First Comment for First Article is appended']];
$this->assertEquals($expected, $result->fetchAll('assoc'));
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->dateDiff(['2012-01-05', '2012-01-02'])])->from('DUAL')->execute()->fetchAll('assoc');
$this->assertEquals(3, abs($result[0]['d']));
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->now('date')])->from('DUAL')->execute();
$date = $result->fetchAll('assoc');
$this->assertEquals([['d' => date('Y-m-d')]], $date);
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->now('time')])->from('DUAL')->execute();
$d = $result->fetchAll('assoc')[0]['d'];
$date1 = date('U');
$this->assertWithinRange($date1, (new \DateTime($d))->format('U'), 1);
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->now()])->from('DUAL')->execute();
$this->assertWithinRange(date('U'), (new \DateTime($result->fetchAll('assoc')[0]['d']))->format('U'), 1);
$query = new Query($this->connection);
$created = new IdentifierExpression('created');
$result = $query->select(['d' => $query->func()->datePart('day', $created), 'm' => $query->func()->datePart('month', $created), 'y' => $query->func()->datePart('year', $created), 'de' => $query->func()->extract('day', $created), 'me' => $query->func()->extract('month', $created), 'ye' => $query->func()->extract('year', $created), 'wd' => $query->func()->weekday($created), 'dow' => $query->func()->dayOfWeek($created), 'addDays' => $query->func()->dateAdd($created, 2, 'day'), 'substractYears' => $query->func()->dateAdd($created, -2, 'year')])->from('comments')->where(['created' => FunctionsBuilder::toDate(['2007-03-18 10:45:23'])])->execute()->fetchAll('assoc');
$result[0]['m'] = ltrim($result[0]['m'], '0');
$result[0]['me'] = ltrim($result[0]['me'], '0');
$result[0]['addDays'] = substr($result[0]['addDays'], 0, 10);
$result[0]['substractYears'] = substr($result[0]['substractYears'], 0, 10);
$expected = ['d' => '18', 'm' => '3', 'y' => '2007', 'de' => '18', 'me' => '3', 'ye' => '2007', 'wd' => '1', 'dow' => '1', 'addDays' => '2007-03-20', 'substractYears' => '2005-03-18'];
$this->assertEquals($expected, $result[0]);
}
/**
* 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'));
}
/**
* Tests that functions are correctly transformed and their parameters are bound
*
* @group FunctionExpression
* @return void
*/
public function testSQLFunctions()
{
$query = new Query($this->connection);
$result = $query->select(function ($q) {
return ['total' => $q->func()->count('*')];
})->from('articles')->execute();
$expected = [['total' => 3]];
$this->assertEquals($expected, $result->fetchAll('assoc'));
$query = new Query($this->connection);
$result = $query->select(['c' => $query->func()->concat(['title' => 'literal', ' is appended'])])->from('articles')->order(['c' => 'ASC'])->execute();
$expected = [['c' => 'First Article is appended'], ['c' => 'Second Article is appended'], ['c' => 'Third Article is appended']];
$this->assertEquals($expected, $result->fetchAll('assoc'));
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->dateDiff(['2012-01-05', '2012-01-02'])])->execute()->fetchAll('assoc');
$this->assertEquals(3, abs($result[0]['d']));
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->now('date')])->execute();
$this->assertEquals([['d' => date('Y-m-d')]], $result->fetchAll('assoc'));
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->now('time')])->execute();
$this->assertWithinMargin(date('U'), (new \DateTime($result->fetchAll('assoc')[0]['d']))->format('U'), 1);
$query = new Query($this->connection);
$result = $query->select(['d' => $query->func()->now()])->execute();
$this->assertWithinMargin(date('U'), (new \DateTime($result->fetchAll('assoc')[0]['d']))->format('U'), 1);
}
