Upgrade Your Drupal Skills

We trained 1,000+ Drupal Developers over the last decade.

See Advanced Courses NAH, I know Enough

'OR' Queries with EntityFieldQuery

Parent Feed: 

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

Original Post: 

About Drupal Sun

Drupal Sun is an Evolving Web project. It allows you to:

  • Do full-text search on all the articles in Drupal Planet (thanks to Apache Solr)
  • Facet based on tags, author, or feed
  • Flip through articles quickly (with j/k or arrow keys) to find what you're interested in
  • View the entire article text inline, or in the context of the site where it was created

See the blog post at Evolving Web

Evolving Web