Join Log in
Score:0
MacSim avatar Drupal

Perform entityQuery with multiple fields conditions on referenced fields

um flag
MacSim

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?

62
0 + 0
9
Score:1
pmichelazzo avatar Drupal
in flag
pmichelazzo

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.

0 + 0
Score:1
sonfd avatar Drupal
in flag
sonfd

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);
0 + 0
MacSim avatar
um flag
MacSim
and since `andConditionGroup()` is the default behaviour I just needed to chain those conditions to the query. I will accept your answer cause it's more complete than @pmichelazzo answer but he was right as well. Thank you guys
1
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.