I have a node with a list field of text values. I want to write a query that gets all nodes that don't have one of the selected values.

How do I write this query?

$query->condition('field_states_excluded', $state, 'DOES NOT CONTAIN');
  • Slightly sleep-deprived so I might be missing something - isn't that a NOT IN? Assuming $state is an array – Clive Feb 19 '18 at 21:07
  • 1
    Sounds like a NOT IN to me as well assuming $state is an array of multiple states.. It only gets complicated when you only want to filter out nodes that match/don't match on all of multiple values. Or a partial string match, aka the opposite of "CONTAIN", that would be tricky. But that seems unlikely when talking about states... – Berdir Feb 19 '18 at 22:28

You can make use of NOT REGEXP if we assume that $state is an array of multiple states.

I will explain with example:
for example if want to get all nodes with the field title DOES NOT CONTAIN page, hello, and test.

Sql statement should be something like:

SELECT * FROM `node_field_data` WHERE `type` = 'page' AND `title`  NOT REGEXP '^.*page.*$|^.*hello.*$|^.*test.*$';

Drupal Code:

  $excluded_strings = array('page', 'hello', 'test');
  //- Add regex prefix and suffix
  $excluded_strings_with_regex = array_map(function ($val) {
    return '^.*' . $val . '.*$';
  }, $excluded_strings);
  //- Join array elements with a string | glue 
  //- result $regex_p_string = "^.*page.*$|^.*hello.*$|^.*test.*$"
  $regex_p_string = implode("|", $excluded_strings_with_regex);
  //-Build Query
  $query = \Drupal::entityQuery('node');
  $query->condition('status', 1);
  $query->condition('type', 'page');
  //- Add regex Condition.
  $query->condition('title', $regex_p_string, 'NOT REGEXP');
  $nids = $query->execute();
| improve this answer | |

The only operators supported by QueryInterface::condition() are the following:

  • =, <>, >, >=, <, <=, STARTS_WITH, CONTAINS, ENDS_WITH
  • IN, NOT IN

You cannot use NOT REGEXP, since Drupal 8 doesn't support it, but you can use NOT IN, as @Clive and @Berdir suggested, if you have an array of values the field should not have.
Since you are talking of states, I agree with @Berdir it's more probable that is your case. I think more difficult somebody would exclude states (which are a limited set of values) using regular expressions.

| improve this answer | |

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.