Re: [fw-general] help to build sql query via zend_db

[My Lists]

Actually i need to get SUM and COUNT in two child tables. My original
query was this:


$select=$this->getDbTable()->select()
               ->setIntegrityCheck(false)
               ->from(array('ms'=>'mailingSchedule'))
               ->joinleft(array('mi'=>'mailingItem'),
'mi.mailingSchedule__id__FK=ms.id',array("mailingItemCount"=>new
Zend_Db_Expr("COUNT(mi.id)")))
->group('id');


^-works perfectly.

So i had two add a new table  (mailingScheduleResul):

               $select=$this->getDbTable()->select()
               ->setIntegrityCheck(false)
               ->from(array('ms'=>'mailingSchedule'))
               ->joinleft(array('mi'=>'mailingItem'),
'mi.mailingSchedule__id__FK=ms.id',array("mailingItemCount"=>new
Zend_Db_Expr("COUNT(mi.id)")))
               ->joinleft(array('msr'=>'mailingScheduleResult'),
'msr.mailingSchedule__id__FK=ms.id',array("sent"=>new
Zend_Db_Expr("SUM(IF(msr.bounce=0,1,0))")))
               ->group('id');


The problem when i add the second join, messed up my count/sum
results. Maybe i missing something in my sql statement ( my example in
my first message works as expected)

Thanks for the help.