JOIN tables using EntityQuery — Drupal

By using EntityQuery in Drupal, we can fetch data from database. Like

$query = $entity_query->condition('type', 'resource');
              ->condition('status', 1)
              ->range(0, 50)
              ->sort('changed', 'DESC')
              ->execute();

The above query will fetch 50 nodes of resource content type in most recent updated sorting order.

But sometime, we have a scenario where we need to add Join tables to get specified data. But there is no direct way to add JOIN operations in EntityQuery. There are two steps to achieve this:

1. addTag() to Entityquery
2. use hook_query_TAG_alter() to alter query

Let’s start

I was writing a query same as mentioned above. But I need all the nodes which have moderation state either in Draft or Published state. It can’t not be achieved with simple status condition 0 or 1. Because all other state apart from Published has 0 status value.

Since moderation state is not available in node object, we have to JOIN with content_moderation_state_field_revision table.

1. addTag()

To achieve this, I have added addTag() in EntityQuery:

$query = $entity_query->condition('type', 'resource');
              ->condition('status', 1)
              ->range(0, 50)
              ->sort('changed', 'DESC')
              ->addTag('example_tag')
              ->execute();

This addTag(‘example_tag’) part will add a tag in EntityQuery which can be used to alter the query using hook_query_TAG_alter().

2. hook_query_TAG_alter()

This hook perform alterations to a structured query for a given tag.

Now we have to use hook_query_TAG_alter() to alter the query by specifying the TAG name.

function hook_query_example_tag_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
  $query->addJoin('LEFT', 'content_moderation_state_field_revision', 'md', 'md.content_entity_revision_id = base_table.vid AND md.langcode = base_table.langcode');
  $or = $query->orConditionGroup();
  $or->condition('md.moderation_state', 'draft', '=');
  $or->condition('md.moderation_state', 'published', '=');
  $query->condition($or);
}

The above hook basically add LEFT join operation to query which is tagged with example_tag. The above code snippet will perform Left join with content_moderation_state_field_revision table and then we can add conditions according to the requirement. In our case, we have used orConditionGroup() to add condition of draft or published state.