Steps to reproduce:
- Install and enable the attached module.
- Create a view showing content.
- In the view, add a "Representative revision" relationship (provided by above module).
The view displays an error like this, complaining about a missing quotation mark:
SQLSTATE[HY000]: General error: 1 near "".langcode = "": syntax error: SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid, node_field_revision_node_field_data.vid AS node_field_revision_node_field_data_vid FROM {node_field_data} node_field_data LEFT JOIN {node_field_revision} node_field_revision_node_field_data ON (SELECT node_field_revisionINNER.vid AS vidINNER FROM {node_field_revision} node_field_revisionINNER LEFT JOIN {node_field_data} node_field_data_node_field_revisionINNER ON "node_field_revisionINNER".nid = "node_field_data_node_field_revisionINNER".nid AND node_field_data_node_field_revisionINNER".langcode = "node_field_revisionINNER".langcode WHERE ("node_field_data_node_field_revisionINNER".nid = node_field_data.nid) ORDER BY node_field_revisionINNER.vid DESC LIMIT 1 OFFSET 0) = node_field_revision_node_field_data.vid WHERE node_field_data.status = :db_condition_placeholder_0 ORDER BY node_field_data_created DESC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 )
I tested with the Drupal demo application (tried both the Umami and the standard install profile), no other contrib or custom modules installed.
The interesting part of the attached module is:
// groupwisemax_example.views.inc
<?php
/**
* Implements hook_views_data_alter().
*/
function groupwisemax_example_views_data_alter(array &$data) {
$data['node_field_data']['nid_representative'] = [
'relationship' => [
'title' => t('Representative revision'),
'label' => t('Representative revision'),
'help' => t('Obtains a single representative revision for each node, according to a chosen sort criterion.'),
'id' => 'groupwise_max',
'relationship field' => 'nid',
'outer field' => 'node_field_data.nid',
'argument table' => 'node_field_data',
'argument field' => 'nid',
'base' => 'node_field_revision',
'field' => 'vid',
'relationship' => 'node_field_revision:nid',
],
];
}
Comments
Comment #2
lendudeFound this rather useless @todo in
\Drupal\Tests\views\Functional\Handler\HandlerAllTest::testHandlers// @todo The groupwise relationship is currently broken.So I guess this is a known issue? Didn't check the issue queue for duplicates.
We really need some steps (or better yet an automated test) to test this with just core.
Comment #3
jlstreckergroupwise_max does apparently work in some cases. It's used in the UserViewsData and TermViewsData classes in core. I did a quick check of one (created view of users, added "representative node" relationship) and it seemed to work. It generated a valid query and displayed the expected results.
Below is the valid query. Notably, it only has
"node_field_dataINNER".uid = "users_field_data_node_field_dataINNER".uid(1 field) where the invalid query has"node_field_revisionINNER".nid = "node_field_data_node_field_revisionINNER".nid AND node_field_data_node_field_revisionINNER".langcode = "node_field_revisionINNER".langcode(2 fields).Other issues where invalid SQL is generated for a groupwise_max relationship:
- Representative Node Views fails due to invalid SQL
- Views GroupwiseMax relationship fails when 'field' isn't the base table's usual base field
My error looks at least superficially similar to comments #18/#24 on the first of those issues.
Comment #4
jlstreckerWelp, I guess it was the same underlying issue as Representative Node Views fails due to invalid SQL. With the latest patch on that issue, I no longer get the error.