如何在zend框架中使用计数和区分？

Question

我试图用zend重写下面的查询

 SELECT COUNT(DISTINCT CPS.supplier_id, CPS.manufacturerid,CPS.categories_id)

            FROM suppliers_report AS CPS

            INNER JOIN  category_brand B ON B.categories_id = CPS.categories_id AND B.manufacturerid = CPS.manufacturerid

            INNER JOIN manufacturer m ON m.manufacturerid = CPS.manufacturerid

                          WHERE s.isactive=1 AND CPS.flg = 2 AND CPS.categories_id = c.parent_id

我在zend中尝试了上面的查询，如

 $this->select()     
      ->setIntegrityCheck(false) 
            ->from(array('CPS' => 'suppliers_report'), array('CPS.supplier_id', 'CPS.manufacturerid', 'CPS.categories_id'))
            ->join(array('B' => 'category_brand'), 'B.categories_id=CPS.categories_id' AND 'B.manufacturerid = CPS.manufacturerid')  
            ->join(array('m' => 'manufacturer'), 'm.manufacturerid = CPS.manufacturerid')
            ->where('s.isactive=1 AND CPS.flg = 2 AND CPS.categories_id = c.parent_id AND CPS.manufacturerid=ctb.manufacturerid');

我一直在讨论如何将count和DISTINCT包含在上面的case.Please帮助中

Answer 1

您需要对COUNT()这样的函数使用Zend_Db_Expr，尝试如下所示：

 $this->select()     
      ->setIntegrityCheck(false) 
      ->from(array('CPS' => 'suppliers_report'), array(new Zend_Db_Expr('COUNT(DISTINCT CPS.supplier_id, CPS.manufacturerid,CPS.categories_id)')))
      ->join(array('B' => 'category_brand'), 'B.categories_id=CPS.categories_id' AND 'B.manufacturerid = CPS.manufacturerid')  
      ->join(array('m' => 'manufacturer'), 'm.manufacturerid = CPS.manufacturerid')
      ->where('s.isactive=1 AND CPS.flg = 2 AND CPS.categories_id = c.parent_id AND CPS.manufacturerid=ctb.manufacturerid');