sql - Zend Framework 中左连接的多个条件

Question

我在使用 Zend Framework 时遇到问题。基本上，我正在尝试使用左连接在子查询中添加一个 AND 运算符。请查看我的代码生成的以下查询:

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  

代码是:

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

但是，我想在我的子查询中再添加一个条件，即 Status = 1，请查看我愿意获得的以下输出。

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  

如果有人对我如何实现相同目标有任何想法，请告诉我。

谢谢，嘎子

Answer 1

我建议使用 joinLeft 而不是 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");

给我

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