Zend Framework 2 - sql subquery

Question

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?

You can find your answer here. tarunlinux.blogspot.in/2013/11/… — Tarun Singhal
– Tarun Singhal, Commented Jan 9, 2014 at 10:36

Tim Burch · Accepted Answer · 2013-11-06 15:26:05Z

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();

edited Nov 6, 2013 at 15:26

answered Oct 25, 2013 at 23:44

Tim Burch

1,0937 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lars Nyström Over a year ago

The Zend example is for ZF1 even though the question clearly says it's about ZF2. I just asked a similar question about ZF2: stackoverflow.com/questions/25606544/…

mirapole Over a year ago

$select = $dbAdapter->select() ->from(array('t', $subQuery)) ->columns(array('', 'num' => 'COUNT()')) -join('t2', 't2.id = t1.id' ->group('listingId') ->having('num = 3');

mirapole Over a year ago

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');

bart Over a year ago

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 ])

Collectives™ on Stack Overflow

Zend Framework 2 - sql subquery

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related