current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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);
share|improve this answer
 
Thanks, I used the creation of "$where = new \Zend\Db\Sql\Where();" but this works fine too. –  leticia Jun 24 '13 at 12:42
 
@gsc-leticia that example will override any conditions already inplace, as $select->where($where) completely replaces Where object. Also unnecessary creation of extra object is a tiniest bit but perf hit. –  Xerkus Jun 24 '13 at 14:24
 
thanks, I will check and confirm you –  leticia Jun 24 '13 at 14:35
 
You are right, all works fine and this solution is more straightforward –  leticia Jun 24 '13 at 17:40
add comment
up vote 1 down vote

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->where ->nest

will return an instance of a Zend Sql operator. Which is not bad but then you can't just do $statement = $sql->prepareStatementForSqlObject($select); $resultSet = $statement->execute();

share|improve this answer
 
Thanks, works fine –  leticia Jun 24 '13 at 12:41
 
BTW using the other solution I could use: $sql->prepareStatementForSqlObject($select); and $resultSet = $statement->execute(); without problem. –  leticia Jun 24 '13 at 20:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.