### Example:
$query->func()->count('*');
$query->func()->dateDiff(['2012-01-05', '2012-01-02'])
public func ( ) : Cake\Database\FunctionsBuilder | ||
Результат | 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); }