How to create complex conditional clauses / queries? 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 -&gt;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-&gt;db_or()
  -&gt;condition('my_field', array(107, 123, 126, 128), 'IN')
  -&gt;condition(db_and()-&gt;condition('my_field', array(0, 100), 'BETWEEN')
                      -&gt;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-&gt;condition(-&gt;db_or()
  -&gt;condition('my_field', array(107, 123, 126, 128), 'IN')
  -&gt;condition(db_and()-&gt;condition('my_field', array(0, 100), 'BETWEEN')
                      -&gt;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-&gt;condition($cond02);
$db_and-&gt;condition($cond03);
$db_or = db_or();
$db_or-&gt;condition($cond01);
$db_or-&gt;condition($db_and);
$query-&gt;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?
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-&gt;condition('my_field', array(0, 100), 'BETWEEN');
$db_and-&gt;condition('my_field', array(23, 48, 97), 'NOT IN');
$db_or = db_or();
$db_or-&gt;condition('my_field', array(107, 123, 126, 128), 'IN');
$db_or-&gt;condition($db_and);
$query-&gt;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-&gt;condition(db_or()
  -&gt;condition('my_field', array(107, 123, 126, 128), 'IN')
  -&gt;condition(db_and()-&gt;condition('my_field', array(0, 100), 'BETWEEN')
                      -&gt;condition('my_field', array(23, 48, 97), 'NOT IN')));