EntityFieldQuery with "NOT IN" FieldCondition and "=" FieldCondition on the same multi-value field is only processing one FieldCondition

Question

The EFQ is:

$query->entityCondition('entity_type', 'node')
    ->fieldCondition('field_terms_wb_access', 'tid', array_keys($children), 'NOT IN')
    ->fieldCondition('field_terms_wb_access', 'tid', $tid, '=')
    ->propertyOrderBy('title');

and the query being generated is

SELECT DISTINCT 
  field_data_field_terms_wb_access0.entity_type AS entity_type, 
  field_data_field_terms_wb_access0.entity_id AS entity_id, 
  field_data_field_terms_wb_access0.revision_id AS revision_id, 
  field_data_field_terms_wb_access0.bundle AS bundle
FROM {field_data_field_terms_wb_access} 
  field_data_field_terms_wb_access0
INNER JOIN {field_data_field_terms_wb_access} 
  field_data_field_terms_wb_access1 
  ON field_data_field_terms_wb_access1.entity_type = field_data_field_terms_wb_access0.entity_type 
  AND field_data_field_terms_wb_access1.entity_id = field_data_field_terms_wb_access0.entity_id
INNER JOIN {node} node 
  ON node.nid = field_data_field_terms_wb_access0.entity_id
  WHERE  (
    field_data_field_terms_wb_access0.field_terms_wb_access_tid 
    NOT IN  (
      :db_condition_placeholder_0, 
      :db_condition_placeholder_1, 
      :db_condition_placeholder_2, 
      [...]
      :db_condition_placeholder_22
    )
  ) 
  AND (
    field_data_field_terms_wb_access1.field_terms_wb_access_tid = :db_condition_placeholder_23
  ) 
  AND (
    field_data_field_terms_wb_access0.deleted = :db_condition_placeholder_24
  ) 
  AND (
    field_data_field_terms_wb_access0.entity_type = :db_condition_placeholder_25
  ) 
  ORDER BY node.title ASC

However, why I include both fieldCondition('field_terms_wb_access', 'tid', $tid, '=') AND fieldCondition('field_terms_wb_access', 'tid', array_keys($children), 'NOT IN') clauses, it seems to only honor the first one and ignore the second. If I comment out the first one, however, then the second one is honored. I get no errors in the php error log or on the screen.

Answer 1

This happens because EntityFieldQuery::addFieldCondition() is written like so:

protected function addFieldCondition(&$conditions, $field, $column = NULL, $value = NULL, $operator = NULL, $delta_group = NULL, $language_group = NULL) {
  // The '!=' operator is deprecated in favour of the '<>' operator since the
  // latter is ANSI SQL compatible.
  if ($operator == '!=') {
    $operator = '<>';
  }
  if (is_scalar($field)) {
    $field_definition = field_info_field($field);
    if (empty($field_definition)) {
      throw new EntityFieldQueryException(t('Unknown field: @field_name', array('@field_name' => $field)));
    }
    $field = $field_definition;
  }
  // Ensure the same index is used for field conditions as for fields.
  $index = count($this->fields);
  $this->fields[$index] = $field;
  if (isset($column)) {
    $conditions[$index] = array(
      'field' => $field,
      'column' => $column,
      'value' => $value,
      'operator' => $operator,
      'delta_group' => $delta_group,
      'language_group' => $language_group,
    );
  }
  return $this;
}

Note that a field condition is stored as an array in the $conditions variable, indexed by a a value that represents the field. When you add a new fieldCondition on the same field, it just overwrites the array.

I don't know if this was intentionally designed this way, or if it's a bug... but the behaviour you are seeing is exactly what the code says it should do!