Perform entityQuery with multiple fields conditions on referenced fields

Question

I have an article node type with a field referencing related articles. If an article has less than 4 references for that field, I need to search for other articles which

• are not the current node
• have been updated less than 2 years ago
• have a checkbox 'favorite' checked
• share at least one culture id (taxonomy reference) AND at least one theme id (taxonomy reference)

And I am actually wondering how to perform that last need... Based on Perform a query with an entity field condition with multiple values, I wrote the following code:

<?php

namespace Drupal\my_module\Preprocess;

use Drupal\Core\DependencyInjection\ContainerInjectionInterface;
use Drupal\Core\Entity\EntityTypeManagerInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Preprocess Node type Article.
 */
class MyArticle implements ContainerInjectionInterface {

  const MAX_RELATED_ARTICLES = 4;

  /**
   * Taxonomy term storage.
   *
   * @var \Drupal\node\NodeStorage
   */
  protected $nodeStorage;

  /**
   * Class constructor.
   *
   * @param \Drupal\Core\Entity\EntityTypeManagerInterface $entity_type_manager
   *   The entity type manager.
   */
  public function __construct(EntityTypeManagerInterface $entity_type_manager) {
    $this->nodeStorage = $entity_type_manager->getStorage('node');
  }

  /**
   * {@inheritdoc}
   */
  public static function create(ContainerInterface $container) {
    return new static(
      $container->get('entity_type.manager')
    );
  }

  /**
   * Preprocess node type article.
   *
   * @param array $variables
   *   An array of variables used to render the node.
   */
  public function preprocess(array &$variables) {
    if ('full' === $variables['view_mode']) {
      $this->preprocessFull($variables);
    }
  }

  /**
   * Preprocess node type article in view mode full.
   *
   * @param array $variables
   *   An array of variables used to render the node.
   */
  protected function preprocessFull(array &$variables) {
    $node = $variables['node'];

    $related_articles = $node->get('related_articles')->referencedEntities();
    $themes = $node->get('themes')->referencedEntities();
    $cultures = $node->get('cultures')->referencedEntities();
    $themes_ids = $cultures_ids = [];
    foreach ($themes as $term) {
      $themes_ids[] = $term->id();
    }
    foreach ($cultures as $term) {
      $cultures_ids[] = $term->id();
    }
    $nb_related_articles = count($related_articles);

    if ($nb_related_articles < self::MAX_RELATED_ARTICLES) {
      $limit = self::MAX_RELATED_ARTICLES - $nb_related_articles;
      $changed_boundary = strtotime('-2 years');
      $query = $this->nodeStorage->getQuery()
        ->condition('type', 'article')
        ->condition('status', 1)
        ->condition('nid', $node->id(), '<>')
        ->condition('favorite', 1)
        ->condition('changed', $changed_boundary, '>=')
        ->range(0, $limit);
      $or_themes = $or_cultures = $query->orConditionGroup();
      foreach ($themes_ids as $tid) {
        $or_themes->condition('cultures', $tid);
      }
      foreach ($cultures_ids as $tid) {
        $or_cultures->condition('themes', $tid);
      }
      $and = $query->andConditionGroup()
        ->condition($or_themes);
      $query->condition($and);
      $and = $query->andConditionGroup()
        ->condition($or_cultures);
      $query->condition($and);
      $node_ids = $query->execute();
    }
  }

}

which gives me the following query:

SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid"
FROM "node" "base_table"
INNER JOIN "node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid"
INNER JOIN "node__favorite" "node__favorite" ON "node__favorite"."entity_id" = "base_table"."nid"
LEFT JOIN "node__cultures" "node__cultures" ON "node__cultures"."entity_id" = "base_table"."nid"
LEFT JOIN "node__themes" "node__themes" ON "node__themes"."entity_id" = "base_table"."nid"
LEFT JOIN "node__cultures" "node__cultures_2" ON "node__cultures_2"."entity_id" = "base_table"."nid"
LEFT JOIN "node__themes" "node__themes_2" ON "node__themes_2"."entity_id" = "base_table"."nid"
WHERE ("node_field_data"."type" = 'article')
AND ("node_field_data"."status" = 'NODE_PUBLISHED')
AND ("node_field_data"."nid" <> '19533')
AND ("node__favorite"."favorite_value" = '1')
AND ("node_field_data"."changed" >= '1583280325')
AND (("node__cultures"."cultures_target_id" = '5077') or ("node__themes"."themes_target_id" = '42') or ("node__themes"."themes_target_id" = '38'))
AND (("node__cultures_2"."cultures_target_id" = '5077') or ("node__themes_2"."themes_target_id" = '42') or ("node__themes_2"."themes_target_id" = '38'))
GROUP BY base_table.vid, base_table.nid
LIMIT 4 OFFSET 0

And it's not what I expected since I need a AND condition between culture ids and theme ids. I would need something like :

AND (("node__cultures"."cultures_target_id" = '5077') AND (("node__themes"."themes_target_id" = '42') or ("node__themes"."themes_target_id" = '38')))

instead of

AND (("node__cultures"."cultures_target_id" = '5077') or ("node__themes"."themes_target_id" = '42') or ("node__themes"."themes_target_id" = '38'))

So my question is: is there a way to aciheve this with the And/OrConditionGroup or should I use subqueries to match some specific ids for both themes and cultures?

Answer 1

One small thing is you're mismatching your ids and your fields here:

foreach ($themes_ids as $tid) {
  $or_themes->condition('cultures', $tid); // should use field 'themes'
}
foreach ($cultures_ids as $tid) {
  $or_cultures->condition('themes', $tid); // should use field 'cultures'
}

But the major issue is here:

$and = $query->andConditionGroup()
  ->condition($or_themes);
$query->condition($and);
$and = $query->andConditionGroup()
  ->condition($or_cultures);
$query->condition($and);

You're wrapping each OR condition in its own AND condition.

But what you want is one AND condition wrapping your two OR conditions:

$and = $query->andConditionGroup()
  ->condition($or_themes)
  ->condition($or_cultures);
$query->condition($and);

Additionally, as mentioned by @pmichelazzo, you can (should? I would) use IN instead of your OR condition groups, e.g.

$cultures_and_themes = $query->andConditionGroup()
  ->condition('cultures', $cultures_ids, 'IN')
  ->condition('themes', $themes_ids, 'IN');
$query->condition($cultures_and_themes);  

Answer 2

Using the snippet that you provide (and expect to have), probably you could use the operator IN instead of AND:

AND (("node__cultures"."cultures_target_id" = '5077') AND (("node__themes"."themes_target_id" IN ('42', '38'))

On this way, you get the cultures with target ID 5077 (since it's the same for both), and the themes with target ID 42 OR 38.