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

score 4 · Answer 1 · answered Dec 19 '11 at 15:07

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

score 0 · Answer 2 · answered Oct 01 '19 at 16:20

We can use expression in Zend 3:

$join = new \Zend\Db\Sql\Expression('contractor_jobs.contractor_id = contractor_info.contractor_id AND 
                            contractor_jobs.job_trade_id = '.$variableName.' ');

    $select->join(
        'contractor_jobs',
        $join,
        array('job_trade_id'),
        $select::JOIN_LEFT
    );

It works for me. You can also check as per your conditions. Thanks for asking this question.

Multiple Conditions with Left Join in Zend Framework

2 Answers2