ZF2 has a number of great classes available to make building secure, database agnostic queries a piece of cake. One issue that I have run into is how to go about creating SQL statements such as the following.
ZF2 SQL Exists Sub Query Using ZF2 Query Helpers
SELECT *
FROM SOME_TABLE_ONE STO
WHERE EXISTS (
SELECT *
FROM SOME_TABLE_TWO STT
WHERE STO.STT_ID = STT.ID
)If we want to create this in ZF2 we need to create a secondary query. We can do this like this.
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Predicate\Expression;
public function getData() {
// Get the DB adapter
$adapter = $this->getAdapter()
// Build the main query
$sql = new Sql($adapter);
$select = $sql->select();
$select->from(array('STO' => 'SOME_TABLE_ONE'));
// Create subquery
$subSelect = $sql->select();
$subSelect->from(array('STT' => 'SOME_TABLE_TWO'));
$subSelect->where('STO.STT_ID = STT.ID');
// Add the subquery as a predicate to the main query
/*
* Notice the @ symbol to suppress errors on the getSqlString() method.
* See: http://framework.zend.com/security/advisory/ZF2013-03
*/
$select->where->addPredicate(
new Expression(
"EXISTS(" . @$subSelect->getSqlString($adapter->getPlatform()) . ")"
)
);
// Run the query
$statement = $sql->prepareStatementForSqlObject($select);
$data = $statement->execute();
}Notice the @ symbol use in the getSqlString() call. This is because in response to ZF2013-03, a security warning of potential sql injection, ZF2 now throws a notice when the quoteValues() method is called from the platform object. They have made a quoteTrustedValues() method available to avoid the notice but it is not possible to use it here since the getSqlString() method automatically calls the quoteValues() method.
Get In Touch.
ContactRecommended content:
