0

I have a function that makes a db query using db_select. The problem I'm having is that in a function elsewhere, the query object is applied a couple of conditions, and I want to add an OR clause but the final query ends with an AND instead of an OR.

Simplified example:

function a() {
    $q = db_select('table', 't');
    $q->addField('t', 'field1');
    b($q);
    // Here I want to add an OR so that the query
    // Selects the conditions applied in b()
    // or something else
    $q->execute();
}

function b($q) {
    $q->condition('t.field1', 'some val', '=');
    $q->condition('t.field2', 'some val', '=');
}

The way this example is, it would compile to something like

SELECT field1 FROM table AS t WHERE t.field1 = 'some val' AND t.field2 = 'some val'

I want to make it so that it compiles to

SELECT field1 FROM table AS t WHERE t.field1 = 'some val' AND t.field2 = 'some val' OR t.field3 = 'some val';

And the OR should be added in place of my comment in a(). How would I go about that in a proper way?

Right now I'm solving it with

$q->where(' 1 = 1) OR (t.field3 = :v', array(':v' => 'some val'));

which compiles to:

SELECT field1 FROM table AS t WHERE t.field1 = 'some val' AND t.field2 = 'some val' AND (1 = 1) OR t.field3 = 'some val';

but I feel that is just ugly, not to mention potentially buggy.

Using db_or instead of the line above results in this:

SELECT field1 FROM table AS t WHERE t.field1 = 'some val' AND t.field2 = 'some val' AND (field1 = 'some val');

That because the db_or crease some sort of combo, and you have to add more than one condition:

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

Finally, it would be great to go a step further and create a query like

SELECT field1 FROM table AS t WHERE (... AND ...) OR ...

Edit: The example here is very simple, compared to the actual code, and I can't really modify the b function. I need to do the OR right where the comment is in a()

1

It is possible to nest db_and() and db_or() calls, and pass the result into a condition() call. See Conditional Clauses documentation page for more information.

The first parameter of condition() can also accept another conditional object. That inner conditional object will be incorporated into the outer conditional, surrounded by parentheses. The inner object may also use a different conjunction than the outer object. That way, one can build complex nested conditional structures by creating and building up conditional objects "bottom up".

The db_condition() helper function will return a new conditional object. It takes a single parameter that is the conjunction that object will use. In general, the helper methods db_and(), db_or(), and db_xor() will cover almost any expected case. That allows conditionals to be inserted inline in a query for a very compact syntax.

function a() {
  $q = db_select('table', 't');
  $q->addField('t', 'field1');
  $q->condition(db_or()->condition(b($q))->condition('t.field3', 'some val', '=');
  $q->execute();
}

function b($q) {
  return db_and()
    ->condition('t.field1', 'some val', '=');
    ->condition('t.field2', 'some val', '=');
}

This should produce the query SELECT field1 FROM table t WHERE ((t.field1 = 'some val' AND t.field2 = 'some val') OR (t.field3 = 'some val')).

  • Thanks, that is good to know, but not applicable in my case. The example I showed is a very simplified one. The actual code does more than that. The guy who did the initial implementation made a function that can be called to apply different changes to the query depending on a few parameters passed to the function. So, I can't really modify b(). I should have noted that in my question. – Buzu Sep 9 '16 at 23:04

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.