Representative Node Views fails due to invalid SQL

blind 10.1 reroll. conflict was just a block in the GroupWiseMax and and it was breaking with the str_contains changes so the patch doesn't have an interdiff because the patch uses preg instead of the str_contains logic.

Carlos Romero → made their first commit to this issue’s fork.

Not entirely sure where the problem stems from, but the latest patch has these lines (line#303 in the file after patching):

+      // Replace the **CORRELATED** placeholder with the outer field name.
+      if ($val === '**CORRELATED**') {
+        $quoted[$key] = $this->definition['outer field'];
+      }

It appears that the outer field definition is just a plain table name with column, and not a properly aliased reference. This essentially results in an error such as the column is not found. Example (see the line I singled out):

SELECT "section_association"."id"                            AS "id",
       "users_field_data_section_association__user_id"."uid" AS "users_field_data_section_association__user_id_uid",
       "node_field_data_users_field_data"."nid"              AS "node_field_data_users_field_data_nid"
FROM section_association "section_association"
LEFT JOIN section_association__user_id "section_association__user_id"
ON section_association.id = section_association__user_id.entity_id AND section_association__user_id.deleted = '0'
    INNER JOIN users_field_data "users_field_data_section_association__user_id" ON section_association__user_id.user_id_target_id = users_field_data_section_association__user_id.uid
    INNER JOIN node_field_data "node_field_data_users_field_data" ON (SELECT "node_field_data_reps"."nid" AS "nid_reps"
    FROM
    node_field_data "node_field_data_reps"
    LEFT JOIN users_field_data "users_field_data_node_field_data_reps" ON "node_field_data_reps".uid = "users_field_data_node_field_data_reps".uid

    WHERE ("users_field_data_node_field_data_reps".uid = users_field_data.uid)      
                                                            # ^^^, should be "node_field_data_users_field_data"."uid"

    ORDER BY "node_field_data_reps"."changed" DESC
    LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid
WHERE ("section_association"."access_scheme" IN ('section'))
  AND ("users_field_data_section_association__user_id"."uid"
    > '1')

In the code, replacing $quoted[$key] = $this->definition['outer field']; with $quoted[$key] = $this->relationship . "." . $this->definition['relationship field']; solves the issue.

Frankly I don't know if that is the proper way of generating the correct aliased reference, and am not sure if there are any implications to this approach. But it solves my issue.

Adding a patch with the correction from the previous comment

The patch in #71 (as well as the diff from MR 6352) both result in a WSOD error for me (Drupal 10.2.6 PHP 8.2.18):

Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'tid' in where clause is ambiguous: SELECT "node_field_data_taxonomy_term_field_data"."title" AS "node_field_data_taxonomy_term_field_data_title", "node_field_data_taxonomy_term_field_data"."created" AS "node_field_data_taxonomy_term_field_data_created", "node_field_data_taxonomy_term_field_data"."uid" AS "node_field_data_taxonomy_term_field_data_uid", "taxonomy_term_field_data"."weight" AS "taxonomy_term_field_data_weight", COUNT(field_discussion_taxonomy_term_field_data.nid) AS "field_discussion_taxonomy_term_field_data_nid", SUM(field_discussion_taxonomy_term_field_data__comment_entity_statistics.comment_count) AS "field_discussion_taxonomy_term_field_data__comment_entity_st", MIN(taxonomy_term_field_data.tid) AS "tid", MIN(field_discussion_taxonomy_term_field_data.nid) AS "field_discussion_taxonomy_term_field_data_nid_1", MIN(node_field_data_taxonomy_term_field_data.nid) AS "node_field_data_taxonomy_term_field_data_nid" FROM "taxonomy_term_field_data" "taxonomy_term_field_data" LEFT JOIN "node__field_discussion" "node__field_discussion" ON taxonomy_term_field_data.tid = node__field_discussion.field_discussion_target_id AND node__field_discussion.deleted = :views_join_condition_0 LEFT JOIN "node_field_data" "field_discussion_taxonomy_term_field_data" ON node__field_discussion.entity_id = field_discussion_taxonomy_term_field_data.nid LEFT JOIN "node_field_data" "node_field_data_taxonomy_term_field_data" ON (SELECT "node_field_dataINNER"."nid" AS "nidINNER" FROM "node_field_data" "node_field_dataINNER" LEFT JOIN "taxonomy_index" "taxonomy_indexINNER" ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN "taxonomy_term_field_data" "taxonomy_term_field_data_node_field_dataINNER" ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = "".tid) ORDER BY "node_field_dataINNER"."created" DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid INNER JOIN (SELECT "tn"."nid" AS "nid" FROM "taxonomy_index" "tn" WHERE "tn"."tid" = :db_condition_placeholder_6 UNION SELECT "tn"."nid" AS "nid" FROM "taxonomy_index" "tn" INNER JOIN "taxonomy_term__parent" "th" ON "tn"."tid" = "th"."entity_id" INNER JOIN "taxonomy_term__parent" "th1" ON "th"."parent_target_id" = "th1"."entity_id" WHERE "th1"."entity_id" = :db_condition_placeholder_7) "taxonomy_index_depth" ON field_discussion_taxonomy_term_field_data.nid = taxonomy_index_depth.nid LEFT JOIN "taxonomy_term__field_path_prefix" "taxonomy_term__field_path_prefix" ON taxonomy_term_field_data.tid = taxonomy_term__field_path_prefix.entity_id AND taxonomy_term__field_path_prefix.deleted = :views_join_condition_1 AND (taxonomy_term__field_path_prefix.langcode = taxonomy_term_field_data.langcode OR taxonomy_term__field_path_prefix.bundle = :views_join_condition_3) LEFT JOIN "comment_entity_statistics" "field_discussion_taxonomy_term_field_data__comment_entity_statistics" ON field_discussion_taxonomy_term_field_data.nid = field_discussion_taxonomy_term_field_data__comment_entity_statistics.entity_id AND field_discussion_taxonomy_term_field_data__comment_entity_statistics.entity_type = :views_join_condition_4 WHERE ((field_discussion_taxonomy_term_field_data.status = 1 OR (field_discussion_taxonomy_term_field_data.uid = 20 AND 20 <> 0 AND 1 = 1) OR 1 = 1 OR 1 = 1)) AND ("field_discussion_taxonomy_term_field_data"."type" IN (:db_condition_placeholder_5)) GROUP BY "node_field_data_taxonomy_term_field_data_title", "node_field_data_taxonomy_term_field_data_created", "node_field_data_taxonomy_term_field_data_uid", "taxonomy_term_field_data_weight" ORDER BY "taxonomy_term_field_data_weight" ASC; Array ( [:db_condition_placeholder_5] => post [:views_join_condition_0] => 0 [:db_condition_placeholder_6] => 1083 [:db_condition_placeholder_7] => 1083 [:views_join_condition_1] => 0 [:views_join_condition_3] => brand [:views_join_condition_4] => node ) in Drupal\mysql\Driver\Database\mysql\ExceptionHandler->handleExecutionException() (line 45 of core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php).

The patch in #66 works for me (doesn't result in a WSOD anyway) but gives this warning:
Deprecated function: Creation of dynamic property Drupal\views\Plugin\views\relationship\GroupwiseMax::$table_aliases is deprecated in Drupal\views\Plugin\views\relationship\GroupwiseMax->leftQuery() (line 253 of core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php).

This patch is just the one in #66 with this bit added:

/**
* The table aliases used by the subquery.
*/
// phpcs:ignore Drupal.NamingConventions.ValidVariableName.LowerCamelName
protected array $table_aliases;

$tableAliases (as in the patch from #72) also works and meets the camelCase requirement, but for simplicity's sake I wanted to stay as close as possible to the patch that seems to work for us.

neclimdul → changed the visibility of the branch 2379423-representative-node-views to hidden.

Doing a reroll I tried to understand the subquery changes Charly was trying to do. They weren't working for me and the previous code is pretty obtuses and admits its fixing confusing edge cases so I made a branch without it since I believe it was working for us as is. Split his changes into a separate branch if anyone wants to view and compare.

Sorry, about the branch noise. I forgot to request access and before you do the branch names get mangled and I didn't notice until after I pushed them.

Moving to NW for the test coverage.

Also tagging for issue summary update to use the standard template please

Thanks.