Zend框架中具有左连接的多个条件 [英] Multiple Conditions with Left Join in Zend Framework

问题描述

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

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  

代码是:

    $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，请参见以下我愿意获得的输出.

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.

谢谢， 加兹(Gagz)

Thanks, Gagz

推荐答案

我建议使用joinLeft而不是joinLeftUsing

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");

给我

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

这篇关于Zend框架中具有左连接的多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！