0

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?

2 Answers 2

2

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);  
1
  • 1
    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
    – MacSim
    Mar 7, 2022 at 1:56
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.