EntityFieldQuery, LEFT JOINs and IS NULL conditions

Adding support for LEFT JOINs and IS NULL conditions to Drupal's EntityFieldQuery has been asked for in d.o. issue queues and discussed over and over again.

Basic approach

The basic example could be found in many places and is always more or less the same. Say we have a commerce_store field created on commerce_order entity type, and we want to get all commerce_order entities owned by a specific user, for which this field's value is empty:

1) Start with EntityFieldQuery() in its standard form:

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'commerce_order', '=')
  ->propertyCondition('uid', $uid)
  ->propertyOrderBy('order_id', 'DESC');

2) tag the query:

$query->addTag('empty_store');

3) and finally alter it in custom hook_query_TAG_alter() implementation adding relevant LEFT JOIN and isNull() clause:

/**
 * Implements hook_query_TAG_alter() for empty_store.
 */
function MYMODULE_query_empty_store_alter(QueryAlterableInterface $query) {
  $query->leftJoin("field_data_commerce_store", 
    "field_data_commerce_store", 
    "commerce_order.order_id = field_data_commerce_store.entity_id");
  $query->isNull("field_data_commerce_store.commerce_store_target_id");
}

Nothing fancy, nothing new.

$query->leftJoin("field_data_commerce_store",
  "field_data_commerce_store",
  "commerce_order.order_id = field_data_commerce_store.entity_id 
    AND field_data_commerce_store.entity_type = 'commerce_order' 
    AND field_data_commerce_store.deleted = '0'");

Going further

Now, it gets much more interesting if the empty field should be dynamic / parametrisable.

In such a case we don't know in advance which table should be used for join, what alias, what condition etc. To achieve the required effect we would need to borrow some concepts from field_sql_storage_field_storage_query() function.

1) Again, let's start with standard EntityFieldQuery() as in the first example.

2) Assuming we know the name of the field which should be empty ($field_name), we want to gather some data we will need for the LEFT JOIN and IS NULL condition later on:

$field = field_info_field($field_name);
$tablename_function = ($query->age == FIELD_LOAD_CURRENT) 
  ? '_field_sql_storage_tablename'
  : '_field_sql_storage_revision_tablename';
$db_table_name = $tablename_function($field);
$db_table_alias = _field_sql_storage_tablealias($db_table_name, '_null', $query);
$db_field_name = _field_sql_storage_columnname($field_name, $column);

// Add it to the array which we'll attach to the query's metadata.
$empty_fields[$db_table_name] = array(
  'table' => $db_table_name,
  'alias' => $db_table_alias,
  'conditions' => "commerce_order.order_id = $db_table_alias.entity_id",
  'field_name' => $db_field_name,
);

Here we assume there might be more that one field with NULL values, that's why we use $null_fields as array of arrays.

3) Now, besides tagging the query, we also add $null_fields array to its metadata, so we have all that information available in the hook_query_TAG_alter() implementation:

$query->addTag('empty_fields');
$query->addMetaData('empty_fields', $empty_fields);

4) Finally, in mentioned hook_query_TAG_alter() implementation, for each field from query's empty_fields metadata add relevant LEFT JOIN and isNull() condition:

/**
 * Implements hook_query_TAG_alter() for empty_fields.
 */
function MYMODULE_query_empty_fields_alter(QueryAlterableInterface $query) {
  foreach ($query->getMetaData('empty_fields') as $empty_field) {
    $query->leftJoin($empty_field['table'], $empty_field['alias'], $empty_field['conditions']);
    $query->isNull($empty_field['alias'] . '.' . $empty_field['field_name']);
  }
}

And that's it!

An example of this could be found in commerce_marketplace_cart_order_ids() function in commerce_marketplace_cart submodule of Commerce Marketplace module suite.