zend framework select object and complex joins

Question

I am trying to learn to use the select object which is really comfortable to use, at least with less complex queries. Is there any limitations using this instead of manual sql queries?

I would prefer to use it everywhere in my application but really don't know how to use it for more complex queries like this one:

SELECT yt.rowid, yt.concat, added_latest, yt.volume, (yt.risk*yt.volume)
FROM obsoletes.batches yt
INNER JOIN(
       SELECT rowid, batch, concat, volume as latest_vol, MAX(added) AS added_latest
       FROM obsoletes.batches
       GROUP BY concat) ss 
       ON yt.added = ss.added_latest AND yt.batch = ss.batch
WHERE yt.concat = 'something'
GROUP BY yt.batch

Is this possible? And how? The documentation is not that great. Could I also use WHERE x IN ( ... ) with the select object?

Thanks in advance!

Answer 1

Not sure, but something like that:

$inner = $db->select ()
    ->from ('obsoletes.batches', array (
    'rowid',
    'batch',
    'concat',
    new Zend_Db_Expr ('volume AS latest_vol'),
    new Zend_Db_Expr ('MAX(added) AS added_latest')
))
    ->group ('concat');


$query = $db->select ()
    ->from ('obsoletes.batches', array (
    'rowid',
    'concat',
    'volume',
    new Zend_Db_Expr ('risk*volume')
))
    ->joinInner ($inner, 'yt.added = added_latest AND yt.batch = ss.batch')
    ->where ('concat = ?', 'something')
    ->group ('batch');