Last active
November 23, 2017 04:28
-
-
Save aronkerr/6196724 to your computer and use it in GitHub Desktop.
ZF2 Sql Exists sub query using ZF2 query helpers
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * | |
FROM SOME_TABLE_ONE STO | |
WHERE EXISTS ( | |
SELECT * | |
FROM SOME_TABLE_TWO STT | |
WHERE STO.STT_ID = STT.ID | |
) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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