'OR' Queries with EntityFieldQuery

As we outlined in a previous post about building the Energy.gov platform without Views, extending EntityFieldQuery (EFQ) is essential to our development philosophy.

One hurdle with using EFQ, however, is adding new fields to existing content. Querying for nodes on an existing platform when a new field is added conflicts with some of the assumptions made in EFQ. Archiving content among various organic groups on an existing site is a great example scenario to illustrate this point.

Let’s say that in certain cases, you want users to be able to archive a story from appearing on the front page for one group, but still want the story to be published in another. In this case, the list of stories on the front page are controlled by EFQ.

For the user, you could simply add a new field that allows them to choose which group the story should be archived.

For the developer, EFQ presents a problem because existing nodes will not contain a value for the new archive field in the archive field table. Because EFQ uses INNER JOINs, it assumes the existence of values in all relevant tables, meaning content without an archive value will be excluded altogether.

The solution* is to use an ‘OR’ query.

Our goal is to either show content that is not in list of groups selected for the archive field OR to show content that does not have a value for the archive field.

But EFQ, as a class, does not work with ‘OR’ queries, right?

Well, technically ‘no’, but it does break down into the SelectQuery class upon execution, so our answer is to use an alter.

Altering by Tag

Since we’re already extending EFQ, we create a method to tag the query so we can utilize hook_query_tag_alter in the module.

/**
 * @file
 * extends EntityFieldQuery, providing some useful added methods and some defaults
 */

class EnergyEntityFieldQuery extends EntityFieldQuery {
  /**
   * define some defaults for the class
   */

  public function __construct() {
    $this
      ->entityCondition(‘entity_type’, ‘node’)
      ->propertyCondition(‘status’, 1)
      ->propertyOrderBy(‘created’, ‘DESC’);

    //archive a node for display
    $this->archive();
  }

  /* Other methods we discussed earlier are here */

  /**
   * Set archive tag to be manipulated by an alter later. Checking for null
   * values doesn’t work in EFQ because of INNER JOIN
   *
   */

  public function archive() {
    $this->addTag(‘archive’);
    return $this;
  }

  /**
   * Unset archive tag
   */

  public function clearArchive() {
    unset($this->tags[‘archive’]);
  }
}

Within the EnergyEntityFieldQuery (EEFQ) class, we also like to add an ‘undo’ method as this allows us to remove an assumption in our extension where needed.

Without going into too much detail, part of the execution of EEFQ involves invoking the appropriate alter hooks.

We then define the alter in our module where we can apply a LEFT JOIN and OR query.

/**
 * Implements hook_query_TAG_alter
 *
 * Check if archive taxonomy is set for a group, but include entities
 * that may not have a value
 *
 * @param QueryAlterableInterface $query
 */

function energy_content_query_archive_alter(QueryAlterableInterface $query) {
  //grab the taxonomy by the existing og relationship
  $group = og_context_determine_context();
  $tid = $group->etid;
  $query
    ->leftJoin(‘field_data_field_archive’, ‘a’, ‘node.nid = a.entity_id’);
  if ($tid) {
    $or = db_or()
          ->condition(‘a.field_archive_tid’, array($tid), ‘NOT IN’)
          ->isNull(‘a.field_archive_tid’);
    $query
      ->condition($or);
  }
}

The organic groups code is specific to the Energy.gov platform, so it can be ignored. It merely communicates how we grab the taxonomy id for comparison.

The key aspects are to LEFT JOIN the appropriate field table, then use db_or() to set your conditions.*

In this case, we’re not only going to exclude nodes that may have the value of the ‘archive’ tid I’ve set, but we also want those nodes who may have a NULL value in that field table.

Hopefully this technique provides you with some ideas of how to extend EFQ even further. Additionally, it should provide a solution to those who wondered how to perform OR queries with EFQ in Drupal 7.

*Assuming you are using the SQL storage engine

  • Joshua Turton

    An interesting adjunct to this is that the addMetadata() and getMetadata functions allow you to pass data back and forth alongside your query tag. This can be useful if, for example, you are adding a WHERE to your query that isn’t as easy to access as the current NID.

  • Justin Geeslin

    Thanks for writing what appears to be the only information on how to do this.
    If I have to extend a class and use a hook alter just to do an OR query, I think I’ll stick with Views.

    • joseph_cheek

      You don’t really need to create a subclass to do it… this example just made a bunch of stuff (initializing some query defaults, adding the tag, etc.) automatic when instantiating a type of this class. You can, instead, just add the tag and write the hook_form_TAG_alter.

      I haven’t found a way around using a global variable for any parameters needed by the alter, though. Ideas welcome.

      for debugging, i add:

      dpm((string) $query, ‘query before alter’);

      and

      dpm((string) $query, ‘query after alter’);

      to the beginning and end of the hook function and can see what the hook gets and what it does.

      • D34dMan

        To add parameters that could be used by alter query do something like below

        $query->addMetaData(‘node’, $node);

        ref: https://drupal.org/node/310077