PHP Query::insert方法代码示例

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

 /**
  * Create an insert query.
  *
  * This changes the query type to be 'insert'.
  * Note calling this method will reset any data previously set
  * with Query::values()
  *
  * Can be combined with the where() method to create delete queries.
  *
  * @param array $columns The columns to insert into.
  * @param array $types A map between columns & their datatypes.
  * @return $this
  */
 public function insert(array $columns, array $types = [])
 {
     $table = $this->repository()->table();
     $this->into($table);
     return parent::insert($columns, $types);
 }

 /**
  * Quotes the table name and columns for an insert query
  *
  * @param Query $query
  * @return void
  */
 protected function _quoteInsert($query)
 {
     list($table, $columns) = $query->clause('insert');
     $table = $this->_driver->quoteIdentifier($table);
     foreach ($columns as &$column) {
         if (is_string($column)) {
             $column = $this->_driver->quoteIdentifier($column);
         }
     }
     $query->insert($columns)->into($table);
 }

 /**
  * Test that insert queries have results available to them.
  *
  * @return void
  */
 public function testInsertUsesOutput()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection'], [[]]);
     $connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
     $connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
     $query = new \Cake\Database\Query($connection);
     $query->insert(['title'])->into('articles')->values(['title' => 'A new article']);
     $expected = 'INSERT INTO articles (title) OUTPUT INSERTED.* VALUES (:c0)';
     $this->assertEquals($expected, $query->sql());
 }

 /**
  * 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 queries get a "RETURNING *" string at the end
  *
  * @return void
  */
 public function testInsertReturning()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Postgres', ['_connect', 'connection'], [[]]);
     $connection = $this->getMockBuilder('\\Cake\\Database\\Connection')->setMethods(['connect'])->disableOriginalConstructor()->getMock();
     $query = new \Cake\Database\Query($connection);
     $query->insert(['id', 'title'])->into('articles')->values([1, 'foo']);
     $translator = $driver->queryTranslator('insert');
     $query = $translator($query);
     $this->assertEquals('RETURNING *', $query->clause('epilog'));
     $query = new \Cake\Database\Query($connection);
     $query->insert(['id', 'title'])->into('articles')->values([1, 'foo'])->epilog('FOO');
     $query = $translator($query);
     $this->assertEquals('FOO', $query->clause('epilog'));
 }

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