Zend\Db\Sql\Select multiple where (combination between AND and OR)

Question

I'm a little stuck at a query with multiple where. I want to 'translate' the query bellow in my Zend\Db\Sql\Select query:

SELECT `users`.*, `users_metas`.* 
FROM `users` INNER JOIN `users_metas` ON `users`.`ID` = `users_metas`.`parent_id` 
WHERE `role` = 'admin' 
AND ( 
         `users`.`username` like '%q_word%' 
      OR
         (`users_metas`.`meta_key` = 'name' 
              AND 
          `users_metas`.`meta_value` like '%q_word%')
    )
 ORDER BY `date_added` ASC

I tried something like this:

$select = new Select();
$select->where(array('role' => 'admin')); 
$select->join('users_metas', 'users.ID = users_metas.parent_id');

but I'm stuck to this where:

AND ( 
         `users`.`username` like '%q_word%' 
      OR
         (`users_metas`.`meta_key` = 'name' 
              AND 
          `users_metas`.`meta_value` like '%q_word%')
)

Thank you!

I solve the query using Expresion, like this $where = "users.username LIKE '%".$search."%' OR (users_metas.meta_key = 'name' AND users_metas.meta_value LIKE '%$search%'))"; $select->where(new Expression($where));. But if it's any other way to do it i would like to try different. Thanks! — Andreea
– Andreea, Commented Nov 6, 2015 at 12:40

Ed209 · Accepted Answer · 2015-11-06 20:53:10Z

2

$where = new Where();
$where
    ->equalTo('role', 'admin')
    ->nest()
    ->like('users.username', '%q_word%')
    ->or
    ->like('users_metas.meta_key', 'name')
    ->or
    ->like('users_metas.meta_value', '%q_word%')
    ->unnest();


$select = new Select();
$select
    ->from('users')
    ->join('users_metas', 'users.ID = users_metas.parent_id')
    ->where($where)
    ->order('date_added');

answered Nov 6, 2015 at 20:53

Ed209

8215 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Zend\Db\Sql\Select multiple where (combination between AND and OR)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related