2

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?

edigu
  • 9,878
  • 5
  • 57
  • 80
PMiller
  • 241
  • 1
  • 5
  • 15

1 Answers1

4

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();
Tim Burch
  • 1,088
  • 7
  • 9
  • The Zend example is for ZF1 even though the question clearly says it's about ZF2. I just asked a similar question about ZF2: http://stackoverflow.com/questions/25606544/zend-db-select-from-subquery – Lars Nyström Sep 01 '14 at 13:18
  • 1
    $select = $dbAdapter->select() ->from(array('t', $subQuery)) ->columns(array('*', 'num' => 'COUNT(*)')) -join('t2', 't2.id = t1.id' ->group('listingId') ->having('num = 3'); – mold Aug 30 '17 at 21:57
  • I didn't test your code, but naming $subQuery was what work for me using join, like this: $select = $dbAdapter->select() ->from(array('t', $subQuery)) ->columns(array('*', 'num' => 'COUNT(*)')) -join('t2', 't2.id = t1.id' ->group('listingId') ->having('num = 3'); – mold Aug 30 '17 at 22:02
  • I don't know if this ever worked (it probably did, at one point), but nowaways you have to specify the "from" source as an associative array, thus: `$select->from([ 't' => $subQuery ])` – bart Jan 13 '20 at 11:47