Executing a query internally executes several steps, the first one is
letting the connection transform this object to fit its particular dialect,
this might result in generating a different Query object that will be the one
to actually be executed. Immediately after, literal values are passed to the
connection so they are bound to the query in a safe way. Finally, the resulting
statement is decorated with custom objects to execute callbacks for each row
retrieved if necessary.
Resulting statement is traversable, so it can be used in any loop as you would
with an array.
This method can be overridden in query subclasses to decorate behavior
around query execution.
| public execute ( ) : Cake\Database\StatementInterface | ||
| return | Cake\Database\StatementInterface | |
/**
* 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'"), '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'));
}
/**
* {@inheritDoc}
*/
public function execute()
{
$this->_transformQuery();
return parent::execute();
}
/**
* Tests that UNION ALL can be built by setting the second param of union() to true
*
* @return void
*/
public function testUnionAll()
{
$union = (new Query($this->connection))->select(['id', 'title'])->from(['a' => 'articles']);
$query = new Query($this->connection);
$result = $query->select(['id', FunctionsBuilder::toChar(new IdentifierExpression('comment'))])->from(['c' => 'comments'])->union($union)->execute();
$rows = $result->fetchAll();
$this->assertCount(self::ARTICLE_COUNT + self::COMMENT_COUNT, $rows);
$union->select(['foo' => 'id', 'bar' => 'title']);
$union = (new Query($this->connection))->select(['id', 'name', 'other' => 'id', 'nameish' => 'name'])->from(['b' => 'authors'])->where(['id ' => 1]);
$query->select(['foo' => 'id', 'bar' => FunctionsBuilder::toChar(new IdentifierExpression('comment'))])->unionAll($union);
$result = $query->execute();
$rows2 = $result->fetchAll();
$this->assertCount(1 + self::COMMENT_COUNT + self::ARTICLE_COUNT, $rows2);
$this->assertNotEquals($rows, $rows2);
}
/**
* Tests that types in the type map are used in the
* specific comparison functions when using a callable
*
* @return void
*/
public function testBetweenExpressionAndTypeMap()
{
$this->loadFixtures('Comments');
$query = new Query($this->connection);
$query->select('id')->from('comments')->defaultTypes(['created' => 'datetime'])->where(function ($expr) {
$from = new \DateTime('2007-03-18 10:45:00');
$to = new \DateTime('2007-03-18 10:48:00');
return $expr->between('created', $from, $to);
});
$this->assertCount(2, $query->execute()->fetchAll());
}
/**
* Test that INSERT INTO ... SELECT works.
*
* @return void
*/
public function testInsertFromSelect()
{
$select = (new Query($this->connection))->select(['name', "'some text'", 99])->from('authors')->where(['id' => 1]);
$query = new Query($this->connection);
$query->insert(['title', 'body', 'author_id'], ['title' => 'string', 'body' => 'string', 'author_id' => 'integer'])->into('articles')->values($select);
$result = $query->sql();
$this->assertQuotedQuery('INSERT INTO <articles> \\(<title>, <body>, <author_id>\\) SELECT', $result, true);
$this->assertQuotedQuery('SELECT <name>, \'some text\', 99 FROM <authors>', $result, true);
$result = $query->execute();
$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' => 'mariano', 'body' => 'some text', 'author_id' => 99, 'published' => 'N'];
$this->assertEquals($expected, $result->fetch('assoc'));
}
/**
* Test that insert can use expression objects as values.
*
* @return void
*/
public function testInsertExpressionValues()
{
$query = new Query($this->connection);
$query->insert(['title'])->into('articles')->values(['title' => $query->newExpr("SELECT 'jose'")]);
$result = $query->execute();
$this->assertCount(1, $result);
$subquery = new Query($this->connection);
$subquery->select(['name'])->from('authors')->where(['id' => 1]);
$query = new Query($this->connection);
$query->insert(['title'])->into('articles')->values(['title' => $subquery]);
$result = $query->execute();
$this->assertCount(1, $result);
}
