Multiple Conditions with Left Join in Zend Framework

Question

I am facing an issue while working with Zend Framework. Basically, I am trying to add an AND operator in a sub query using Left join. Please see the following query that my code is producing:

SELECT `d`.*, count(status) FROM `deal` AS `d` LEFT JOIN `payments` ON `payments`.deal_id = `d`.deal_id GROUP BY `d`.`deal_id` ORDER BY `created_date` desc  

Code is:

    $select = $this->_db->select()
      ->from(array('d'=>'deal'))                       
      ->joinLeftUsing('payments', 'deal_id', array('count(status)'))
      ->order("created_date $sortOrder")
       ->group("d.deal_id");

However, I want to add one more condition in my sub query i.e. Status = 1, please see the following output that I am willing to get.

SELECT `d`.*, count(status) FROM `deal` AS `d` LEFT JOIN `payments` ON `payments`.deal_id = `d`.deal_id AND status = 1 GROUP BY `d`.`deal_id` ORDER BY `created_date` desc  

Let me know if someone have an idea about how I can achieve the same.

Thanks, Gagz

Answer 1

I would recommend using joinLeft instead of joinLeftUsing

$select = $this->_db->select()
      ->from(array('d'=>'deal'))                       
      ->joinLeft('payments', 'payments.deal_id = d.deal_id and status = 1', 
                 array('count(status)'))
      ->order("created_date $sortOrder")
      ->group("d.deal_id");

gives me

SELECT `d`.*, count(status) FROM `deal` AS `d`
LEFT JOIN `payments` ON payments.deal_id = d.deal_id and status = 1 
GROUP BY `d`.`deal_id` ORDER BY `created_date ` ASC