0

I am using following query

$this->tableGateway->getSql()
    ->select()
    ->join(array('pu'=>'project_users'), 'users.id = pu.user_id', array('project_id'), 'left')
    ->join(array('r'=>'roles'), 'pu.role_id=r.id', array('role'), 'left')
    ->where(array('company_id'=>$company_id,'users.status'=>'1',''));

What i need is:

To apply the condition as following

where  company_id=1 and users.status='1' and (project_id=null or project_id=1)

I am unable to get this

Please help

doydoy44
  • 5,720
  • 4
  • 29
  • 45
Pankaj Jarial
  • 71
  • 2
  • 8
  • see if it helps http://stackoverflow.com/questions/17242185/zend-framework-2-sql-select-with-or-and-and – Muhammad Zeeshan Apr 03 '14 at 04:59
  • It worked as below **`$where = new \Zend\Db\Sql\Where(); $where->equalTo('company_id',$company_id) ->and ->equalTo('users.status','1') ->nest() ->isNull('project_id') ->or ->equalTo('project_id',$project_id) ->unnest(); $resultSet = $this->tableGateway->getSql()->select() ->join(array('pu'=>'project_users'), 'users.id = pu.user_id', array('project_id'), 'left')->join(array('r'=>'roles'), 'pu.role_id=r.id', array('role'), 'left')->where($where);`** – Pankaj Jarial Apr 03 '14 at 05:29

2 Answers2

1

try this, Last line:

$this->tableGateway->getSql()->select()
        ->join(array('pu'=>'project_users'), 'users.id = pu.user_id', array('project_id'), 'left')
        ->join(array('r'=>'roles'), 'pu.role_id=r.id', array('role'), 'left')
        ->where(array('company_id'=>$company_id,'users.status'=>'1'))
        ->where(array('project_id'=>null,'project_id'=>'1'), Predicate\PredicateSet::OP_OR);
Allen Chak
  • 1,802
  • 1
  • 10
  • 21
0

I did it like

$where = new \Zend\Db\Sql\Where();
$where->equalTo('company_id',$company_id)->and
->equalTo('users.status','1')->nest()
->isNull('project_id')->or->equalTo('project_id',$project_id)
->unnest();         

$resultSet = $this->tableGateway->getSql()->select()
->join(array('pu'=>'project_users'), 'users.id = pu.user_id', 
array('project_id'), 'left')
->join(array('r'=>'roles'),'pu.role_id=r.id', array('role'),'left')
->where($where);
Pankaj Jarial
  • 71
  • 2
  • 8