How to create complex conditional clauses / queries?
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')));