代码之家 › 专栏 › 技术社区 › Juned Ansari

zf2-模型中的子查询问题

zend-framework2 zend-framework

Juned Ansari · 技术社区 · 8 年前

我在zf2子查询中遇到sql语法问题我不知道我的查询有什么问题。我的查询在mysql上运行得很好

在zend中,它显示语法错误

sqlQuery

SELECT ( SELECT role_name FROM roles WHERE role_id=(SELECT role_id FROM users WHERE id=a.user_id)) AS role,`a`.consultant_id,`a`.*, `c`.`name` AS `name`, `b`.`name` AS `branchname`, `u`.`name` AS `consultantname`, `u`.`role_id` AS `role_id`, `r`.`role_name` AS `rolename` 
            FROM `agents` AS `a` 
            LEFT JOIN `countries` AS `c` ON `c`.`code` = `a`.`country` 
            LEFT JOIN `branches` AS `b` ON `b`.`id` = `a`.`branch_id` 
            INNER JOIN `users` AS `u` ON `u`.`id` = `a`.`consultant_id` 
            INNER JOIN `roles` AS `r` ON `r`.`role_id` = `u`.`role_id` 
  WHERE  r.`role_id` IN(5,12,21,25,43,44) AND a.branch_id=63 AND a.consultant_id =2834 
  ORDER BY `a`.`id` ASC

Zend查询

        $rolesSelect = new Select();
        $rolesSelect->from(array('r1' => 'roles'));
        $rolesSelect->columns(array('ro_name'=>'role_name'));
        $rolesSelect->join(array('u1' =>'users'), 'u1.role_id = r1.role_id', array(''));
        $rolesSelect->join(array('a1' =>'agents'), 'u1.id = a1.user_id', array(''));

        $select = new Select();
        $select->from(array('a' => 'agents'));
        $select->columns(array('*','role_name1' => new Expression('?', array($rolesSelect))));
        $select->where($conditions);
        $select->join(array('c' =>'countries'), 'c.code = a.country', array('name'),'left');
        $select->join(array('b' =>'branches'), 'b.id = a.branch_id', array('branchname'=>'name'),'left');
        $select->join(array('u' =>'users'), 'u.id = a.consultant_id', array('consultantname'=>'name','role_id'=>'role_id'));
        $select->join(array('r' =>'roles'), 'r.role_id = u.role_id', array(''));
        $select->order('a.id ASC');
        $resultSet = $this->tableGateway->selectWith($select);
        echo $conditions."<pre>";
        print_r($resultSet);
        die;
        return $resultSet->buffer();

1 回复 | 直到 8 年前

Juned Ansari 8 年前

可以改用zend表达式,请参见下面的示例

$select = new Select();
            $select->from(array('a' => 'agents'));
            $select->columns(array('*','consultantname' => new \Zend\Db\Sql\Expression('(SELECT u.name FROM users u WHERE u.id = a.consultant_id)')));
            $select->where($conditions);
            $select->join(array('u' =>'users'), 'u.id = a.user_id', array());
            $select->join(array('c' =>'countries'), 'c.code = a.country', array('name'),'left');
            $select->join(array('b' =>'branches'), 'b.id = a.branch_id', array('branchname'=>'name'),'left');
            $select->join(array('r' =>'roles'), 'r.role_id = u.role_ref_id', array('role_name'));
            $select->order('a.id ASC');
            $resultSet = $this->tableGateway->selectWith($select);
            //echo "<pre>";print_r($resultSet);die;
            return $resultSet->buffer();