Zend DB Select nested Joins

Question

I'm trying to realize the following query with zend db select:

SELECT `uac`.`uid`, `u`.`uid`, `g`.`groupid`, `g`.`packageid` 
FROM `user_has_data` AS `uac` 
INNER JOIN `users` AS `u` ON u.uid = uac.uid 
LEFT JOIN (`user_in_group` AS `uig` 
  INNER JOIN `groups` AS `ag` ON (ag.groupid = uig.groupid) AND (ag.packageid = 2) 
 ) AS `g` ON uac.uid = g.uid
WHERE (uac.dataid = '3') AND (u.uname LIKE 'test%')
GROUP BY `u`.`uid`

I got the following, but got stuck when trying to convert the nested join to zend structure:

$select = $db->select()->from(array('uac' => 'user_has_data'), array('uac.uid'))
->join(array('u' => 'users'), 'u.uid = uac.uid', array('uid', 'uname'))
->joinLeft(array('uig' => 'user_in_groups'), 'uig.uid = uid', array('agid' => 'uig.groupid'))
->join(array('ag' => 'groups'), '(ag.agid = uig.groupid) AND ( ag.packageid = '.$packageid.')', array('packageid'))
->where('uac.dataid = ?', $dataid)
->where('(u.uname LIKE ?)', $value)
->group('u.uid');

Is it possible to get the given sql query into a suitable structure for zend db select? I need a select object for further handling in a paginator, so if this is not possible I have to make a straight forward sql query.

Thanks in advantage!

Answer 1

I don't believe you can do a nested join like that with Zend_Db_Select. Your best bet is to create your own paginator adapter (it's easier than you might think, look at the Select one) and manage the LIMIT part of the SQL yourself.