SQL queries with AND/OR conditions

Question

I'm transferring SQL statements from a Drupal 5 installation to Drupal 8, and struggling with And/Or condtions. eg given the original SQL:

 SELECT ..... WHERE ..... AND classes.description = "Photographer" AND (classoptions.description = "Name of photographer here" OR classoptions.description = "Not assigned").

I've translated it to:

$query = $this->dbConnection->select('classes'); ....etc.... $query->condition('classes.description', 'Photographer');

So far, so good. Then I need an OR condition for

$query->condition('classoptions.description', 'Name of photographer here'); $query->condition('classoptions.description', 'Not assigned');

Looking at documentation on-line, using db_or() is suggested, as in :

$query->condition(db_or() ->condition('classoptions.description', 'Name of photographer here'); ->condition('classoptions.description', 'Not assigned'));

..but this is a) deprecated, b) gives error 'db_or function not found' The replacement for db_or() is to obtain a new Condition object, but what am I supposed to do with it? The function

 $query->orConditionGroup();

Will return a new Condition object, and the code

$query->condition(orConditionGroup() ->condition('classoptions.description', 'Name of photographer here'); ->condition('classoptions.description', 'Not assigned'));

Formats a Condition object with the conditions to be ORed just fine.But again, how do I incorporate this into the query?

Maouna · Accepted Answer · 2016-06-14 19:32:02Z

Have a look how orConditionGroup() is used in core. For example, in ConfigEntityQueryTest it is used this way:

$query = $this->factory->get('config_query_test', 'AND'); $and_condition_1 = $query->orConditionGroup() ->condition('id', '2') ->condition('label', $this->entities[0]->label); $and_condition_2 = $query->orConditionGroup() ->condition('id', 1) ->condition('label', $this->entities[3]->label); $this->queryResults = $query ->condition($and_condition_1) ->condition($and_condition_2) ->execute();

I am sure you can apply this to your problem.

Thanks. That works ( I thought I'd already tried that!) I ended up coding $query->condition('classes.description','Photographer'); $query->condition($query->orConditionGroup() ->condition('classoptions.description', $photographer) ->condition('classoptions.description', 'Not assigned')); You can also use ->where('sql snippet', args) to perform the SQL conditions in full, although this seem to go against the grain of most D8 SQL methods. — davem
– davem, Commented Jun 15, 2016 at 5:23

miiimooo · Accepted Answer · 2017-05-22 14:08:36Z

For the sake of completeness, this worked for me (thanks to the original poster):

$query = \Drupal::entityQuery('taxonomy_term') ->condition('vid', 'surface'); $group = $query->orConditionGroup() ->condition('field_surface_exclude', NULL, 'IS NULL') ->condition('field_surface_exclude', 0); $tids = $query->condition($group)->execute();

Stack Exchange Network

SQL queries with AND/OR conditions

2 Answers 2

Hot Network Questions

SQL queries with AND/OR conditions

2 Answers 2

Related

Hot Network Questions