51

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 , '=');
//…
82

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.

30

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 evene 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 heres 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 .

23

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);
10

Simple example:

$nodes = db_select('node', 'n')
->fields('n')
->condition(
 db_or()
   ->condition('uid', 1)
   ->condition('status', 0)
 )
->execute()
->fetchAll();
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();
  • 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. – kiamlaluno Aug 16 '12 at 20:24
  • Thanks for the clarification, how would it be used in that case then? db_select()->condition()->condition()->db_or()->condition() ?? – tcm5025 Aug 16 '12 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. – kiamlaluno Aug 16 '12 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 Aug 16 '12 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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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