Multiple Sub Query with zend framework

Question

there i am looking for a solution of problem that i am facing in zend apllication,

i am using zf 1.12 and php 5.3 with my sql,

Here is My Query Which runs perfectly in My SQL

SELECT usermaster.*, (select count(projecttouser.u_id) from projecttouser where 
usermaster.id=projecttouser.u_id  ) as proj,

(select count(tasktotarget.assigned_to) from tasktotarget where    
usermaster.id=tasktotarget.assigned_to  ) as target,

(select count(tasktotarget.assigned_to) from tasktotarget where 
usermaster.id=tasktotarget.assigned_to AND tasktotarget.is_active = 1  ) as active


from usermaster  group by usermaster.id

That Gives , Perfect Output Which i wanted but in my sql

now my problem is i have to convert that query in zend frame environment query,

which is some what different from the my sql format,

i have tried somethings as follow,

$psub=$this->select()
            ->setIntegrityCheck(false)
            ->from(array('p'=>'projecttouser'),array('count(p.u_id) as count')) 
            ->join(array('i'=>'usermaster'),'p.u_id=i.id') 
            ->where('i.id=p.u_id');

        $tsub=$this->select()
            ->setIntegrityCheck(false)
            ->from(array('t'=>'tasktotarget'),array('count(t.assigned_to) as tcount'))                     
            ->where('usermaster.id=tasktotarget.assigned_to');

        $tasub=$this->select()
            ->setIntegrityCheck(false)
            ->from(array('ta'=>'tasktotarget'),array('count(ta.assigned_to) as tacount'))                     
            ->where('usermaster.id=tasktotarget.assigned_to AND tasktotarget.is_active = 1 ');

        $sql=$this->select()
                ->setIntegrityCheck(false)                   
                ->from(array('u'=>'usermaster',$psub,$tsub,$tasub))
                 ->group('u.id')   
                 ->order($order_by . ' ' . $order)
                 ->where('u.is_delete=false');                     

        $resultSet = $this->fetchAll($sql);
        return $resultSet;

so , if anyone can help me create and format the query that will be very helpfull

I have created the zend query for a single sub query with zend before, but this has three sub queries and i can't figure out a way to do it.. — Ronak K
– Ronak K, Commented Aug 15, 2013 at 10:23

Fouad Fodail · Accepted Answer · 2013-08-15 12:05:10Z

3

Try this one :

$psub=$this->db->select()
    ->setIntegrityCheck(false)
    ->from(array('p'=>'projecttouser'),array('count(p.u_id) as count'))
    //->join(array('i'=>'usermaster'),'p.u_id=i.id') // no need for this join
    ->where('usermaster.id=projecttouser.u_id');

$tsub=$this->db->select()
    ->setIntegrityCheck(false)
    ->from(array('t'=>'tasktotarget'),array('count(t.assigned_to) as tcount'))
    ->where('usermaster.id=tasktotarget.assigned_to');

$tasub=$this->db->select()
    ->setIntegrityCheck(false)
    ->from(array('ta'=>'tasktotarget'),array('count(ta.assigned_to) as tacount'))
    ->where('usermaster.id=tasktotarget.assigned_to AND tasktotarget.is_active = 1 ');

$sql=$this->db->select()
    ->setIntegrityCheck(false)
    ->from(array('u'=>'usermaster'), array('usermaster.*', 
            'proj' => new Zend_Db_Expr('(' . $psub . ')'),
            'target' => new Zend_Db_Expr('(' . $tsub . ')'),
            'active' => new Zend_Db_Expr('(' . $tasub . ')')))
    ->group('u.id')
    //->order($order_by . ' ' . $order)
    ->where('u.is_delete=false');

$resultSet = $this->fetchAll($sql);
return $resultSet;

Doc : http://framework.zend.com/manual/1.12/en/zend.db.select.html

answered Aug 15, 2013 at 12:05

Fouad Fodail

2,6431 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ronak K Over a year ago

Thanks a Lot , That was indeed very helpful, almost complete answer, with little changes it worked perfectly for me , thanks a ton mate .

Collectives™ on Stack Overflow

Multiple Sub Query with zend framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related