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',
				],
			];	
}
CommentFileSizeAuthor
groupwisemax_example.zip2.27 KBjlstrecker

Comments

jlstrecker created an issue. See original summary.

lendude’s picture

Version: 8.8.1 » 8.8.x-dev
Status: Active » Postponed (maintainer needs more info)
Issue tags: +Needs tests

Found 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.

jlstrecker’s picture

groupwise_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).

SELECT users_field_data.uid AS uid, node_field_data_users_field_data.nid AS node_field_data_users_field_data_nid
FROM
{users_field_data} users_field_data
LEFT JOIN {node_field_data} node_field_data_users_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER
FROM
{node_field_data} node_field_dataINNER
LEFT JOIN {users_field_data} users_field_data_node_field_dataINNER ON "node_field_dataINNER".uid = "users_field_data_node_field_dataINNER".uid
WHERE ("users_field_data_node_field_dataINNER".uid = users_field_data.uid)
ORDER BY node_field_dataINNER.title ASC
LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid
WHERE users_field_data.status = '1'
LIMIT 11 OFFSET 0

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.

jlstrecker’s picture

Status: Postponed (maintainer needs more info) » Closed (duplicate)

Welp, 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.