zf2 - subquery issue in model

i am getting sql syntax problem in zf2 subquery i dont knonw whats wrong with my query. my query is perfectly working on mysql

in zend it shows syntax error

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 Query

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

Answers


you can use zend expression instead see below example

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

Need Your Help

Ping to subnet on IPsec Host only works with -I parameter

routing routes openvpn ipsec

I have the following situation. Two hosts connect two subnets over the Internet using Strongswan IpSec

Why my cross compiler toolchain not static link?

gcc toolchain cross-compiling linux-from-scratch clfs

I want to build my cross compile tool chain, I build it successful.