Rewrite sql query to Zend_Db_Select

Question

I'm trying to build the following sql query as a Zend_Db_Select object

$sql = "
    SELECT
        u.id, 
        u.email,
        s.nic as nic,
        (SELECT COUNT(*) FROM event WHERE user_id = u.id AND event='login') as logins,
        (SELECT COUNT(*) FROM event WHERE user_id = u.id AND event='export') as exports,
        (SELECT MAX(time) FROM event WHERE user_id = u.id AND event='login') as lastlogin,
        (DATEDIFF(u.expire_date, NOW())) as daysleft
    FROM 
        user u,
        seller s
    WHERE 
        u.seller_id = s.id";

with no luck. I can't get the subquerys working in the Zend_Db_Select object.

Is it possible to achieve the same same result by joins instead of subquerys?

Any hints on how to get this working would be highly appreciated.

Answer 1

Try something like this:

$select->from(array('u'=>'user'),array('id','email'));
$select->join(array('s'=>'seller'),'s.id = u.seller_id', array('nic'));
$select->columns(array(
    'logins'    =>"(SELECT COUNT(*) FROM event WHERE user_id = u.id AND event='login')",
    'exports'   =>"(SELECT COUNT(*) FROM event WHERE user_id = u.id AND event='export')",
    'lastLogin' =>"(SELECT MAX(time) FROM event WHERE user_id = u.id AND event='login')",
    'daysLeft'  =>"(DATEDIFF(u.expire_date, NOW()))",));
$stmt = $select->query();
var_dump($stmt->fetchAll());