CODEWITHSUNDEEP
mysqldatabasezend-framework2
PMiller
PMiller

Reputation: 271

Zend Framework 2 - sql subquery

I'm trying to run a special SQL query in ZF 2.

SELECT listingId, COUNT(*) as num 
FROM
(SELECT DISTINCT listingId, locationId
 FROM l_f_locations
 WHERE locationId IN ( 7, 9, 10)) AS foo
GROUP by listingId, HAVING num = 3

I tried creating the subquery first as it's a complete MySQL query but then fail to integrate it into the main query at all. I can't alias the subquery e.g. "AS foo" as this is a requirement for the complete SQL squery to work.

Any ideas?

Upvotes: 2

Views: 2337

Answers (1)

Tim Burch
Tim Burch

Reputation: 1103

First of all, you can do this without a sub-query:

SELECT listingId, COUNT(DISTINCT locationId) AS num
FROM l_f_locations
WHERE listingId IN(7,9,10)
GROUP BY listingId
HAVING num = 3;

For future reference, however, you could do the query you mention using a pair of Zend_Db_Select objects, one for the sub-query and another for the main:

$subQuery = $dbAdapter->select()
    ->from('l_f_locations', array('listingId', 'locationId'))
    ->where('locationId IN(7,9,10)')
    ->group('listingId')
    ->group('locationId');

$select = $dbAdapter->select()
    ->from($subQuery, array('*', 'num' => 'COUNT(*)'))
    ->group('listingId')
    ->having('num = 3');

$result = $select->query()->fetchAll();

Upvotes: 4

Related Questions