Zend Framework 2 Sql Select with OR and

时间:2022-12-07 20:13:05

I want to make this query using Zend\Db\Sql\Select:

我想用Zend\Db\Sql\选择来查询:

SELECT table1.* FROM table1 
    INNER JOIN table2 ON table1.columnA = table2.columnB 
    INNER JOIN table3 ON table1.columnC = table3.columnD

WHERE (table2.column2 = 2 or table3.column3 = 3) and table1.column1 = 1

ORDER BY table1.columnE ASC LIMIT 1

I have this code so far:

到目前为止,我有这个代码:

/*@var $db Adapter */
$db = $this->getServiceLocator()->get('db');
$sql = new Sql($db);
$select = $sql->select();

$select->from('table1');
$select->join('table2','table1.columnA = table2.columnB',array());
$select->join('table3','table1.columnC = table3.columnD',array());

$select->where(array('table2.column2' => 2, 'table2.column3' => 3), Predicate\PredicateSet::OP_OR);

$select->where(array('table1.column1' => 1),Predicate\PredicateSet::OP_AND);

$select->order('table1.columnE ASC');
$select->limit(1);

$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();

But doesn't works, because produce this one (without the "(" and ")" for the OR):

但它不起作用,因为产生这个(没有“(”和“)”):

SELECT table1.* FROM table1 
    INNER JOIN table2 ON table1.columnA = table2.columnB 
    INNER JOIN table3 ON table1.columnC = table3.columnD

WHERE table2.column2 = 2 or table3.column3 = 3 and table1.column1 = 1

ORDER BY table1.columnE ASC LIMIT 1

What can I do?

我能做什么?

2 个解决方案

#1


36  

from the top of the head using Where fluent interface:

使用流畅界面从头顶开始:

$select->where
       ->nest
           ->equalTo('table2.column2', 2)
           ->or
           ->equalTo('table2.column3', 3)
       ->unnest
       ->and
       ->equalTo('table1.column1', 1);

#2


7  

I would do something like:

我会这样做:

$where = new \Zend\Db\Sql\Where();

$where
    ->nest()
    ->equalTo('table2.column2', 2)
    ->or
    ->equalTo('table2.column3', 3)
    ->unnest()
    ->and
    ->equalTo('table1.column1', 1);
$select->where($where)

Just because this way your $select keep being an implementation of Zend\Db\Sql\SqlInterface while doing

因为这样,您的$select将继续作为Zend\Db\Sql\SqlInterface的实现

$select->where
   ->nest

will return an instance of a Zend Sql operator. Which is not bad but then you can't just do

将返回Zend Sql操作符的一个实例。这不是坏事,但你不能这么做

$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();

#1


36  

from the top of the head using Where fluent interface:

使用流畅界面从头顶开始:

$select->where
       ->nest
           ->equalTo('table2.column2', 2)
           ->or
           ->equalTo('table2.column3', 3)
       ->unnest
       ->and
       ->equalTo('table1.column1', 1);

#2


7  

I would do something like:

我会这样做:

$where = new \Zend\Db\Sql\Where();

$where
    ->nest()
    ->equalTo('table2.column2', 2)
    ->or
    ->equalTo('table2.column3', 3)
    ->unnest()
    ->and
    ->equalTo('table1.column1', 1);
$select->where($where)

Just because this way your $select keep being an implementation of Zend\Db\Sql\SqlInterface while doing

因为这样,您的$select将继续作为Zend\Db\Sql\SqlInterface的实现

$select->where
   ->nest

will return an instance of a Zend Sql operator. Which is not bad but then you can't just do

将返回Zend Sql操作符的一个实例。这不是坏事,但你不能这么做

$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();