Using OR with EntityFieldQuery

Question

I have never had the need to do this before today, but it doesn't seem like you can make OR queries with EntityFieldQuery, since db_or is used for select queries.

An example would get to all entities that has a date field where the value is null or after today.

Am I missing something or some trick or is this simply not supported?

Answer 1

I have seen a solution of this problem. The idea is to use addTag() in query and implement hook_query_TAG_alter(), where you have good old SelectQuery object.

Answer 2

You can sublass EntityFieldQuery and override some methods.

The conditions that are added to an object of class EntityFieldQuery (e.g. a property condition) are added to an array.

  public function propertyCondition($column, $value, $operator = NULL) {
    // The '!=' operator is deprecated in favour of the '<>' operator since the
    // latter is ANSI SQL compatible.
    if ($operator == '!=') {
      $operator = '<>';
    }
    $this->propertyConditions[] = array(
      'column' => $column, 
      'value' => $value, 
      'operator' => $operator,
    );
    return $this;
  }

When the query is built, that array is then used in a loop similar to the following one (the code is present in EntityFieldQuery::propertyQuery()):

foreach ($this->propertyConditions as $property_condition) {
  $this->addCondition($select_query, "$base_table." . $property_condition['column'], $property_condition);
}

$select_query contains the value returned from a call to db_select().

Answer 3

You can't I'm afraid, ORs are not natively supported by the EntityFieldQuery class.

One way round it might be to add a tag to the query with with ->addTag(), then implement hook_query_TAG_alter() to change the internal structure of the query manually for queries containing that tag.

Doing this you will be able to loop through the existing conditions and make the necessary alterations to add your OR logic. It's not a pretty way to do it though; you can find an example here.

Answer 4

No need to split queries into 2 and merge or anything like that. Just need to alter the query

Consider the scenario: I had 2 entity types with machine names: tincan statements, and tincan_agents

5 entity reference fields on the entity

4 of them are regular entity reference fields and the 5th (tincan_object) is a multi-entity-type reference field, each reference field references a entities of type 'Agent'.

The tincan_object reference field can reference Agents and Activities (a third entity type). An Agent has a property object_type, which can either be Agent or Group.

I want to find any Statement that references one of several possible Agents, in any of the reference fields. We need an OR operator between the fieldConditions, but we also need to check the object_type of the multi-entity type reference field, and make sure it is one of two possibilities.

The code below represents the simplest possible , in our solution the query had many other conditions, fields, etc... so the code needed to not count on the order of conditions, or even if all of these fields were being queried.

    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'tincan_statement');

    $all_agents = array(4,10); //entity_ids to search for
    $query->addTag('tincan_statement_get_agents');
    $query->fieldCondition('tincan_actor', 'target_id', $all_agents, 'IN'); 
    //need OR between fields conditions
    $query->fieldCondition('tincan_authority', 'target_id', $all_agents, 'IN');
//need OR between fields conditions
    $query->fieldCondition('tincan_instructor', 'target_id', $all_agents, 'IN');
//need OR between fields conditions
    $query->fieldCondition('tincan_team', 'target_id', $all_agents, 'IN');
//need OR between fields conditions
//but then nested in the OR structure we need an AND for two columns of the multientity type reference field tincan_object
    $query->fieldCondition('tincan_object', 'target_id', $all_agents, 'IN');
    $query->fieldCondition('tincan_object', 'object_type', array('Agent', 'Group'), 'IN');
    $results = $query->$execute();

Solution: Notice in the above EntityFieldQuery

 $query->addTag('tincan_statement_get_agents');

This tags the query, allowing implementation of hook_query_TAG_alter()

/**
 * Implements hook_query_TAG_alter()
 * alters the query for finding agents with or without the related_agents flag
 * used for Statement API Get processor EntityFieldQuery
 */
function tincan_lrs_query_tincan_statement_get_agents_alter(QueryAlterableInterface $query) {
  //need to or the search for all the fields (actor, object, authority, instructor, team)
  // the object_type of the object field needs to be Agent OR Group

  $conditions =& $query->conditions();
  // dsm($conditions);  //dsm() is your friend! comes with devel module
  $agent_grouping_condition = db_or(); 
  $object_parameters = array();
  $x = 0;
  foreach ($conditions as $key => $condition) {
    if (is_numeric($key) && isset($condition['field']) && is_scalar($condition['field'])) {
      if ( (strpos($condition['field'], 'tincan_object_object_type') !== FALSE  ||
          strpos($condition['field'], 'tincan_object_target_id') !== FALSE ) && $condition['operator'] == 'IN') {
  //u
            unset($conditions[$key]);
            $object_parameters[$x]['field'] = $condition['field'];
            $object_parameters[$x]['value'] = $condition['value'];
            $object_parameters[$x]['operator'] = $condition['operator'];
            $x += 1;
          }

       if(strpos($condition['field'], 'tincan_actor_target_id') !== FALSE ||
          strpos($condition['field'], 'tincan_instructor_target_id') !== FALSE ||
          strpos($condition['field'], 'tincan_team_target_id') !== FALSE ||
          strpos($condition['field'], 'tincan_authority_target_id') !== FALSE ) {
            unset($conditions[$key]);
            $agent_grouping_condition->condition($condition['field'], $condition['value'], $condition['operator']);

      } 
    }
  }

  // create new AND condition to nest in our OR condition set for the object parameters
  $object_condition = db_and();
  foreach($object_parameters as $key => $param) {
    $object_condition->condition($param['field'], $param['value'], $param['operator']);
  }

  $agent_grouping_condition->condition($object_condition);

  $query->condition($agent_grouping_condition);

  //By default EntityFieldQuery uses inner joins, change to left
  $tables =& $query->getTables();

  foreach($tables as $key => $table) {
    if (strpos($key, 'field_data_tincan_object') !== FALSE ||
        strpos($key, 'field_data_tincan_actor') !== FALSE ||
        strpos($key, 'field_data_tincan_authority') !== FALSE ||
        strpos($key, 'field_data_tincan_instructor') !== FALSE ||
        strpos($key, 'field_data_tincan_team') !== FALSE ) {
          if(!is_null($table['join type'])) {
            $tables[$key]['join type'] = 'LEFT';
          }
    }
  }

}

Answer 5

The OP wants to query for entities with date null OR bigger than x, I wanted to query for nodes with no language defined OR the user's language. addTag() is the best solution for adding an actual OR statement, but would be overkill in my case. My very simple OR can be accomplished by looking up the language property in an array using:

$query->propertyCondition('language', array($GLOBALS['language']->language, LANGUAGE_NONE), 'IN');