By googletorp


2011-11-01 09:55:38 8 Comments

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?

5 comments

@kiamlaluno 2011-11-01 10:30:57

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().

@jackrabbithanna 2015-07-24 19:43:06

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';
          }
    }
  }

}

@lmeurs 2013-07-03 07:37:58

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');

@Michael 2012-03-18 15:21:03

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.

@Tommi Forsström 2012-07-03 19:05:32

I'd propose selecting this as the right answer. The blog post provides a method for adding OR conditionality to EntityFieldQueries. The only problem is that you actually build SQL dependency with that method, which is kind of against the whole point of EFQs, but at least gets the job done. Thanks for the good link @Michael.

@D. Visser 2016-03-31 09:16:02

Since this is a community answer, and most of it consists of an external link, I feel like the code, or at least some of the contents of the article, should be included in this answer. Because links do die. Meta StackExchange discussion on this topic

@Michael 2016-04-02 19:11:49

The original article is rather long and the idea can be summarized as "use addTag() in query and implement hook_query_TAG_alter()". After that the question has been reduced to "How to use OR with SelectQuery object" which is known subject.

@Clive 2012-04-03 10:13:32

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.

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Select any entity created today

  • 2014-03-17 09:49:51
  • Felix Eve
  • 4729 View
  • 2 Score
  • 3 Answer
  • Tags:   7 database datetime

1 Answered Questions

1 Answered Questions

How to add field collection date field in scheduling component drupal 7?

  • 2013-02-03 15:18:09
  • Abubakar Shams
  • 579 View
  • -1 Score
  • 1 Answer
  • Tags:   entities rules

1 Answered Questions

[SOLVED] Indexing custom entity types

  • 2017-02-24 16:29:41
  • saramm1
  • 860 View
  • 1 Score
  • 1 Answer
  • Tags:   8 entities search

3 Answered Questions

[SOLVED] PDOException: SQLSTATE[42S02] - Base table not found

1 Answered Questions

[SOLVED] EntityFieldQuery with pager and db_or using addTag

  • 2015-04-29 12:04:24
  • DKvistgaard
  • 451 View
  • 2 Score
  • 1 Answer
  • Tags:   paging database

0 Answered Questions

Using addExpression and GROUP_CONCAT gives null result

  • 2014-07-25 04:59:28
  • Christian
  • 597 View
  • 0 Score
  • 0 Answer
  • Tags:   database migration

1 Answered Questions

[SOLVED] Select fields with group names in Drupal 7?

  • 2011-11-21 10:55:09
  • Rishi Kulshreshtha
  • 1868 View
  • 1 Score
  • 1 Answer
  • Tags:   entities

1 Answered Questions

[SOLVED] What is the specificity limit of EntityFieldQuery / fieldCondition?

  • 2012-05-31 13:21:26
  • beth
  • 1905 View
  • 2 Score
  • 1 Answer
  • Tags:   7 database

1 Answered Questions

[SOLVED] Getting info from an external database - Base table or view not found

  • 2011-08-23 14:50:02
  • user2615
  • 1620 View
  • 2 Score
  • 1 Answer
  • Tags:   7 database

Sponsored Content