Altering Entity Field Querys for JOINS ORs and Profit
One of my favorite features from Drupal 7 is the EntityFieldQuery. The power of the EntityFieldQuery is a well known thing, and I a have written about extending EntityFieldQueries with subqueries before. This time I will go into extending the query as a query object, using Drupal's hook and alter architecture.
Simple EntityFieldQuery
For our example we will start with a simple EntityFieldQuery for getting a list of nodes.
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
->entityCondition('bundle', 'activity_set');
$result = $query->execute();
if (isset($result['node'])) {
$activity_sets = node_load_multiple(array_keys($result['node']));
$activity_sets = node_view_multiple($activity_sets);
}
Now lets say that we want this list to be a keyword filtered list of title or taxonomy. But, we also want to filter this to exclusively show nodes with another taxonomy field's values. The one taxonomy field is an easy addition.
$query->fieldCondition('field_related_curriculum', 'tid', $structure_taxonomy, 'IN');
How EntityFieldQueries can start to break down
But how do we add an OR
statement to also allow for the title keyword filter?
EntityFieldQuery doesn't allow for OR
out of the box. We could extend the EntityFieldQuery class and add some interface to it to try and handle this case. OR, we could just use Drupal's hook and alter system to alter the query before we execute the query.
Before we can alter the query, we must know what query we want to alter. We will do this by tagging the query when we created the EntityFieldQuery.
$query->addTag('keyword');
While we are at it we will have to add our filter criteria as well.
$query->addMetaData('options', $options);
There now we have what we will need for our alter hook. To alter this query we will need to implement hookqueryTAG_alter().
/**
* Implements hook_query_TAG_alter().
*/
function module_name_query_keyword_alter(QueryAlterableInterface $query) {
// @TODO: make this do things.
}
What we want is for any single word in the title to come back with a like. I did it this way. I realize that this can be done in a variety of different ways.
$keywords = explode(' ', $options['keywords']);
$or = db_or();
if (count($keywords)) {
foreach ($keywords as $keyword) {
$or->condition('node.title', '%' . $keyword . '%', 'LIKE');
}
}
$query->condition($or);
There, we are done, right? Nope. We may think that we are, however, if we ran that code we would get a PDO exception. Why? Because we are not doing any propertyConditions on the original EntityFieldQuery. Drupal is smart enough to know that if it isn't using the node table in the EFQ that it doesn't need to JOIN
it.
To fix this, go back up to the original EntityFieldQuery and add this line $query->propertyCondition('status', 1);
. Awesome, now our code will execute and everything is sun shine and rainbows.