How to create complex conditional clauses / queries?

Question

I've been reading the documentatoin on conditional clauses and also studied the api description for db_and() and db_or() but I fail to create a $query with 3 ->conditions:

• select all between 0 and 100
• but exclude 23, 48 and 97
• select also 107, 123, 126 and 128

That's what I have tried so far:

$query->db_or()
  ->condition('my_field', array(107, 123, 126, 128), 'IN')
  ->condition(db_and()->condition('my_field', array(0, 100), 'BETWEEN')
                      ->condition('my_field', array(23, 48, 97), 'NOT IN'));

But I get the following error:

PHP Fatal error:  Call to undefined method SelectQuery::db_or() in [... first line]

I've tried to play around with the syntax like that:

$query->condition(->db_or()
  ->condition('my_field', array(107, 123, 126, 128), 'IN')
  ->condition(db_and()->condition('my_field', array(0, 100), 'BETWEEN')
                      ->condition('my_field', array(23, 48, 97), 'NOT IN')));

But again getting errors like that:

PHP Parse error:  syntax error, unexpected T_OBJECT_OPERATOR, expecting ')' in [... first line]

I also tried working with variables:

$cond01 = array('my_field', array(107, 123, 126, 128), 'IN');
$cond02 = array('my_field', array(0, 100), 'BETWEEN');
$cond03 = array('my_field', array(23, 48, 97), 'NOT IN');
$db_and = db_and();
$db_and->condition($cond02);
$db_and->condition($cond03);
$db_or = db_or();
$db_or->condition($cond01);
$db_or->condition($db_and);
$query->condition($db_or);

But this again yields drupal errors like that:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause'

How to create a complex conditional database query with both AND and OR statements?

Answer 1

Actually your final method is a right way, however the condition function is expecting 2 or 3 arguments(else only one DatabaseCondition instance as Molot said) and if you're not passing DatabaseCondition instance then your first argument should be the table column name. But you're passing an Array as the first argument. That's why you got unknown column 'Array' error.

So you should revise it like below,

$db_and = db_and();
$db_and->condition('my_field', array(0, 100), 'BETWEEN');
$db_and->condition('my_field', array(23, 48, 97), 'NOT IN');
$db_or = db_or();
$db_or->condition('my_field', array(107, 123, 126, 128), 'IN');
$db_or->condition($db_and);
$query->condition($db_or);

Further note your second approach is also right, however you put a extra -> before calling the db_or() function. So you should write it in the following way,

$query->condition(db_or()
  ->condition('my_field', array(107, 123, 126, 128), 'IN')
  ->condition(db_and()->condition('my_field', array(0, 100), 'BETWEEN')
                      ->condition('my_field', array(23, 48, 97), 'NOT IN')));

Answer 2

Drupal is not cleanly OO, and is not cleanly procedural. db_or() is not a method. It's a function. This function returns DatabaseCondition object you can use to attach other conditions to it, like:

$or = db_or()->condition('tid1', 5)->condition('tid2', 6);
db_delete('term_relation')->condition($or)->execute();

Note that you cannot just put arrays into condition method. It accepts either DatabaseCondition instance, or 3 parameters. 3 element arrays are not equivalent of 3 parameters in PHP.