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 Drupalmy_modulePreprocess;  use DrupalCoreDependencyInjectionContainerInjectionInterface; use DrupalCoreEntityEntityTypeManagerInterface; use SymfonyComponentDependencyInjectionContainerInterface;  /**  * Preprocess Node type Article.  */ class MyArticle implements ContainerInjectionInterface {    const MAX_RELATED_ARTICLES = 4;    /**    * Taxonomy term storage.    *    * @var DrupalnodeNodeStorage    */   protected $nodeStorage;    /**    * Class constructor.    *    * @param DrupalCoreEntityEntityTypeManagerInterface $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?