使用zend框架的多个子查询 [英] Multiple Sub Query with zend framework

问题描述

我正在寻找zend应用中面临的问题的解决方案，

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

我正在将zf 1.12和php 5.3与我的sql一起使用，

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

这是在我的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

可以提供理想的输出，但我想要在SQL中输入

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

现在我的问题是我必须在zend框架环境查询中转换该查询，

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

与我的sql格式有些不同

which is some what different from the my sql format,

我尝试了以下方法，

$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

推荐答案

尝试以下方法:

$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/zh/zend.db.select.html

这篇关于使用zend框架的多个子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！