2

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.

Tor Inge Schulstad
  • 78
  • 5
  • Why do not the subqueries work? How did you assemble them and how did you fit them in your final select object? – bububaba Mar 21 '12 at 15:24

1 Answers1

0

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());
H Hatfield
  • 881
  • 5
  • 9
  • Thanks a lot @just-h Putting the sub querys in $select->columns() solved it. I had tried several ways to put them in the from() array, but all of then returned sql errors. – Tor Inge Schulstad Mar 21 '12 at 21:25