PHP Query::func方法代码示例

 /**
  * 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);
 }