zend left join count not working [关闭]

Question

ia have a problem with count in multi joins

$query = $this->select()
        ->setIntegrityCheck(false)
        ->from(array('u' => 'users'))
        ->join(array('c' => 'clients'), 'u.id = c.user_id', 'COUNT(c.user_id) as clientsCount')
        ->join(array('emails' => 'u_emails'), 'u.id = emails.user_id', 'COUNT(emails.user_id) as emailsCount')
        ->join(array('sms' => 'u_sms'), 'u.id = sms.user_id', 'COUNT(sms.user_id) as smsCount')
        ->where('u.id=?', (int) USER_ID)->group('u.id');

Answer 1

I think this can help you out of this problem:

$this->select()
     ->from(array('u' => 'users'), array(
            'clientsCount' => new Zend_Db_Expr("(SELECT COUNT(*) FROM clients WHERE clients.user_id = u.id)"),
            'emailsCount' => new Zend_Db_Expr("(SELECT COUNT(*) FROM emails WHERE emails.user_id = u.id)"),
            'smsCount' => new Zend_Db_Expr("(SELECT COUNT(*) FROM sms WHERE sms.user_id = u.id)"),
     )
     ->where('u.id=?', (int) USER_ID)
     ->group('u.id');

I can't test the query so feel free to ask if you have any problems.

Edited my answer, please try it again