CODEWITHSUNDEEP
phpmysqlzend-frameworkcountzend-framework2
Adrian Veidt
Adrian Veidt

Reputation: 280

How to use COUNT and DISTINCT together in zend framework?

I'm trying to rewrite the below query in 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

I tried the above query in zend as 

 $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');

I'm stuck on how to include count and DISTINCT in the above case.Please help

Upvotes: 1

Views: 555

Answers (1)

Kevin Sharp
Kevin Sharp

Reputation: 529

You'll need to use Zend_Db_Expr for functions like COUNT(), try something like the following:

 $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');

Upvotes: 1

Related Questions