Data Driven Design icon

ZF2 SQL Exists Sub Query Using ZF2 Query Helpers

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.

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.
Contact

We proudly serve
the Pacific Northwest