entityQuery to get Nodes referenced by other nodes like an SQL JOIN

Question

I have an entity query to get content of type article that has a field that matches a given value, which works as expected.

$query = \Drupal::entityQuery('node');
$query->condition('status', 1);
$query->condition('type', 'article');
$query->condition('field_some_field', 'some value');

Now I have a second content type detail_page that has an entity reference to a node of type article. I would like to get content of type article, same as before, but only those that have been referenced in the field_detail_page_article_ref field of a detail_page content type.

Is that possible with Entity Queries? Playing around with the raw SQL, what I basically need to do is an INNER JOIN or LEFT JOIN with the field field_detail_page_article_ref to see if the nid exists. But it doesn't seem like there is any sort of join() function available for entity queries.

Answer 1

I just had exactly the same problem with a Drupal 8 instance, and while I wasn't able to solve it with a single query as I'd've liked to, I did succeed with the following:

My goal was to get all the nodes of type A or B which meet certain criteria, one of which is that they are referenced by type C. (I've removed that other stuff here to focus on the issue.) Type has an entity reference field which allows multiple entries to either type A or B.

First we get all the nids that are referenced:

$connection = \Drupal::database();
$query = $connection->query("SELECT DISTINCT field_name_target_id  FROM {node__field_name}");
$referenced_nids = $query->fetchCol();

Note that you'll need to adjust your code to match the name of your reference field. See https://www.drupal.org/docs/8/api/database-api/static-queries, which is where I ripped that bit from.

Then my query to get the actual nodes/nids:

$storage = \Drupal::entityTypeManager()->getStorage('node');

$query = $storage->getQuery();

$nids = $query->condition('type', ['TypeA', 'TypeB'], 'IN')
  ->condition('status', 1)
  ->condition('nid', $referenced_nids, 'IN')
  ->sort('title')
  ->execute();

UPDATE: I learned today that the first step of my procedure is not considered proper by Drupal standards, as you shouldn't make direct queries on Drupal tables (see https://www.drupal.org/core/d8-bc-policy#schema). The following method is more brute force unfortunately, but caching the results should help.

Instead I refactored the first part using an EntityQuery.

// Get all the entities of type C, the referencing entity:
$query = \Drupal::entityQuery('node')
  ->condition('type', 'TypeC')
  ->condition('status', 1);
$typeC_nids = $query->execute();
$typeC_nodes = $storage->loadMultiple($typec_nids);

// Loop through the nodes and get all the references:
$referenced_nids = [];
foreach ($typeC_nodes as $node) {
 $references = array_column($node->field_name->getValue(), 'target_id');
 foreach ($references as $reference) {
   if (!in_array($reference, $referenced_nids)) {
     $referenced_nids[] = $reference;
   }
 }

}

Then use that list of the referenced NIDs in the second query.

Answer 2

You can use 'entity' field query

$query = \Drupal::entityQuery('node');
$query->condition('status', 1);
$query->condition('type', 'article');
$query->condition('field_some_field.entity:node.title', 'Referenced node title');

Answer 3

An example using \Drupal::entityTypeManager():

$entityTypeManager = \Drupal::entityTypeManager();
$nodeStorage = $entityTypeManager->getStorage('node');
$nodeStorage->loadByProperties([
  'type' => 'mycontenttype',
  'field_entity_reference_field.entity:node.nid' => 3786,
]);