Skip to content

Instantly share code, notes, and snippets.

@aronkerr
Last active November 23, 2017 04:28
Show Gist options
  • Save aronkerr/6196724 to your computer and use it in GitHub Desktop.
Save aronkerr/6196724 to your computer and use it in GitHub Desktop.
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
)
<?PHP
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();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment