Multiple conditions on the same subfield in an entity query fails to join properly

Created on 11 October 2023, about 1 year ago

Problem/Motivation

Because of 🐛 Views relationships with multi-valued entity reference fields invalidate Distinct query option Needs work I have to implement some complex filtering in a custom list builder class for my entity type.

I have 2 bundles for this entity type, one that can reference a single node and one that can reference more than 1 node. These nodes are the same bundle (store) and I need to be able to filter by the same field on the Store entity in either bundle's field.

The code for this looks like so:

      $orCondition = $query->orConditionGroup();
      $like = '%123%';
      $orCondition->condition('store.entity.s_code.value', $like, 'LIKE');
      $orCondition->condition('stores.entity.s_code.value', $like, 'LIKE');
      $query->condition($orCondition);

Where the single cardinality field is store and the multi is stores

The field I'm filtering on is s_code

This produces the following SQL (dumped at the end of getEntityListQuery by casting the $query to a string).

SELECT "base_table"."revision_id" AS "revision_id", "base_table"."id" AS "id"
FROM
"proj_personalisation_criteria" "base_table"
LEFT JOIN "proj_pers_criteria__store" "proj_pers_criteria__store" ON "proj_pers_criteria__store"."entity_id" = "base_table"."id"
LEFT OUTER JOIN "node" "node" ON "node"."nid" = "proj_pers_criteria__store"."store_target_id"
LEFT JOIN "node__s_code" "node__s_code" ON "node__s_code"."entity_id" = "node"."nid"
LEFT JOIN "proj_pers_criteria__stores" "proj_pers_criteria__stores" ON "proj_pers_criteria__stores"."entity_id" = "base_table"."id"
LEFT OUTER JOIN "node" "node_2" ON "node_2"."nid" = "proj_pers_criteria__stores"."stores_target_id"
INNER JOIN "proj_personalisation_criteria_field_data" "proj_personalisation_criteria_field_data" ON "proj_personalisation_criteria_field_data"."id" = "base_table"."id"
WHERE (("node__s_code"."s_code_value" LIKE '%046%' ESCAPE '\\') or ("node__s_code"."s_code_value" LIKE '%046%' ESCAPE '\\'))
GROUP BY "base_table"."revision_id", "base_table"."id"
ORDER BY "base_table"."id" ASC
LIMIT 50 OFFSET 0

As you can see, the WHERE on the s_code uses the same table in both conditions. There is a second join on node (called node_2) but no join on node__s_code for THAT join.

If I change the 2nd or condition to some other random field then it works fine and joins twice correctly, in this case I changed stores.entity.s_code.value to stores.entity.subtitle.value where subtitle is another field on the store.

SELECT "base_table"."revision_id" AS "revision_id", "base_table"."id" AS "id"
FROM
"proj_personalisation_criteria" "base_table"
LEFT JOIN "proj_pers_criteria__store" "proj_pers_criteria__store" ON "proj_pers_criteria__store"."entity_id" = "base_table"."id"
LEFT OUTER JOIN "node" "node" ON "node"."nid" = "proj_pers_criteria__store"."store_target_id"
LEFT JOIN "node__s_code" "node__s_code" ON "node__s_code"."entity_id" = "node"."nid"
LEFT JOIN "proj_pers_criteria__stores" "proj_pers_criteria__stores" ON "proj_pers_criteria__stores"."entity_id" = "base_table"."id"
LEFT OUTER JOIN "node" "node_2" ON "node_2"."nid" = "proj_pers_criteria__stores"."stores_target_id"
LEFT JOIN "node__subtitle" "node__subtitle" ON "node__subtitle"."entity_id" = "node_2"."nid"
INNER JOIN "proj_personalisation_criteria_field_data" "proj_personalisation_criteria_field_data" ON "proj_personalisation_criteria_field_data"."id" = "base_table"."id"
WHERE (("node__s_code"."s_code_value" LIKE '%046%' ESCAPE '\\') or ("node__subtitle"."subtitle_value" LIKE '%046%' ESCAPE '\\')) 
GROUP BY "base_table"."revision_id", "base_table"."id"
ORDER BY "base_table"."id" ASC
LIMIT 50 OFFSET 0

In this case we correctly get another LEFT JOIN on the subtitle table and the condition is correct.

Steps to reproduce

Add 2 bundles with an ER field each on the same entity type/bundle (ER fields need to be different)
Test an entity query with an OR on both fields, filtering on the title of each, e.g

      $orCondition = $query->orConditionGroup();
      $like = '%foo%';
      $orCondition->condition('node.entity.foo.value', $like, 'LIKE');
      $orCondition->condition('nodes.entity.foo.value', $like, 'LIKE');
      $query->condition($orCondition);