53

I am studying the new database layer, and I am glad it is changed to an OOP layer. I need to implement a query with db_select(). I found I can add a WHERE statement with $query->condition(), but by default the conditions are AND'ed together.

How can I OR the conditions together?

$query=db_select('users','u')->fields('u',array('uid','title','created','uid'));
$query->join('flag_content','fc' , 'u.uid = fc.content_id');
$query->condition('fc.fid', '5' , '=');
$query->condition('fc.uid', $uid , '=');
//…

8 Answers 8

86

That's not correct, the default is AND. To use or, you need to use:

$or = db_or();
$or->condition();
$or->condition();
$query->condition($or);

But the main conditions on the query are definitely joined with AND.

0
31

You can use this For making OR between 2 conditions ByDefault it is AND

$query=db_select('users','u')->fields('u',array('uid','title','created','uid'));
$query->join('flag_content','fc' , 'u.uid = fc.content_id');

$db_or = db_or();
$db_or->condition('fc.fid', '5' , '=');
$db_or->condition('fc.uid', $uid , '=');
$query->condition($db_or);

$result = $query->execute()->fetchAll();

It is possible to have AND condition within OR suppose you have 3 conditions and if you want it to be like con1 AND con2 OR con3 and even many other cases in this case you can use db_and like db_or because when you are trying to use db_or all the conditions within db_or takes now in case you want and between those you can use db_and that is little tedious but its possible to implement it. Here is the example below

 $query = db_select('work_details_table','wd');
$query->join('work_table','w','wd.request_id = w.request_id');
$query->fields('w',array('client_id'));
$query->fields('wd');
$query->condition('wd.request_id',$request_id);
if($status == 5 || $status == 6 || $status == 7){
  $query->condition('wd.status',$status);
}elseif($user_type == 'writer'){
  $query->condition('wd.writer_id',$user_id);  
  $db_or = db_or();
  $db_and = db_and();
    $db_and->condition('wd.status',2);
    $db_and->isNull('wd.editor_id');
  $db_or->condition('wd.status',4);
  $db_or->condition('wd.status',1);
  $db_or->condition($db_and);
  $query->condition($db_or);
}else if($user_type == 'editor'){
  $query->condition('wd.editor_id',$user_id);
  $db_or = db_or();
  $db_and = db_and();
    $db_and->condition('wd.status',2);
    $db_and->isNotNull('wd.editor_id');
  $db_or->condition('wd.status',3);
  $db_or->condition($db_and);
  $query->condition($db_or); 
}
//-----------------------------------------------------------------------------------------
$completed_sku = $query->execute()->fetchAll();

This will give you an idea how the complex and or condition can be handled in Drupal PDO queries .

24

According to drupal 7 Database documentation the default is AND condition, but if you want to use OR condition then use this;

$db_or = db_or();
$db_or->condition('n.type', 'event', '=');
$db_or->condition('n.type', 'article', '=');
$query->condition($db_or);
11

Simple example:

$nodes = db_select('node', 'n')
->fields('n')
->condition(
 db_or()
   ->condition('uid', 1)
   ->condition('status', 0)
 )
->execute()
->fetchAll();
0
1

By default there is AND condition, for OR condition need to add db_or() inside the condition.

$query = db_select('soccer_team"', 'n');
$query->fields('n',array('stid','title'))//SELECT the fields from node
        ->fields('n',array('title'))//SELECT the fields from user
            ->condition(
             db_or()
            ->condition('title','%ace%','LIKE')
            ->condition('title','%alb%','LIKE')
            )
        ->orderBy('title', 'ASC');
0

The db_or() solution won't work when you are building a complex query where you want most of the conditions to be conjoined with AND's and just one or some OR condition(s).

I ran into a case like this where I had conditions already, and I didn't want them to be OR conditions. I was able to use the where() method instead of condition to add another clause that included an OR. Here's an example similar to the query I used with the where method there near the end:

$query = db_select('users');
$query->join('user_join', NULL, 'users.uid = userid');
$query->join('field_data_account', NULL, 'appid = entity_id');
$query->leftJoin('users_roles', 'roles', 'users.uid = roles.uid');
$query->condition('account_target_id', $form['#account_id']);
$query->condition('userid', $lab_user->uid);
$query->where('appid <> :app_id OR rid = :rid', array(
    ':app_id' => $form['#app_id'],
    ':rid' => $role->rid,
))->fields('users')->countQuery();
$result = $query->execute()->fetchField();
4
  • This answer is not correct: db_or() works also when there are just two conditions that need to OR'ed out of 5 conditions that are AND'ed. There is not need to use $query->where(), for such case.
    – avpaderno
    Commented Aug 16, 2012 at 20:24
  • Thanks for the clarification, how would it be used in that case then? db_select()->condition()->condition()->db_or()->condition() ??
    – tcm5025
    Commented Aug 16, 2012 at 21:01
  • 1
    You need to use something similar to db_select()->condition($a)->condition($b)->condition(db_or()->condition($c)->condition($d))->condition($e); the resulting condition would be $a AND $b AND ($c OR $d) AND $e. I am using pseudo-code. db_or() is a function, not a method; db_select()->condition()->db_or() would return an error about a method not defined.
    – avpaderno
    Commented Aug 16, 2012 at 21:25
  • Ok, I get it. Thanks again for the explanation. I was under the impression that it was a method. So it would also have worked for my problem.
    – tcm5025
    Commented Aug 16, 2012 at 22:04
0

For Drupal 8 you can use e.g.:

// Use only records with ACCEPTED or REJECTED action.
$orCondition = $query->orConditionGroup();
$orCondition->condition('action', 'ACCEPTED');
$orCondition->condition('action', 'REJECTED');
$query->condition($orCondition);
0

db_or() is deprecated for Drupal 9: https://api.drupal.org/api/drupal/core%21includes%21database.inc/function/db_or/8.8.x

Following the deprecation notice, this code should be used:

use Drupal\Core\Database\Query\Condition;

$or = new Condition('OR');
$or->condition();
$or->condition();
$query->condition($or);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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