I've been trying to get the views data set up for a couple of custom entity types:
* Goals (table "goal")
* Goal Health Checks (table "goal_health_check")
I want to configure a GroupwiseMax relationship so that in a view of Goals, it's possible to pull in a Representative Goal Health Check —just as with Users, one can pull in a Representative Node. I went ahead and more or less copied the UserViewsData relationship definition into my own GoalHealthCheckViewsData class and changed the names of the tables accordingly:
$data['goal']['representative_health_check'] = [
'relationship' => [
'title' => $this->t('Representative health check'),
'label' => $this->t('Representative health check'),
'help' => $this->t('Obtains a single Goal Health Check for each Goal, according to a chosen sort criteria.'),
'id' => 'groupwise_max',
// If 'id' => 'standard, the following fields are correct....
'relationship field' => 'id',
'base' => 'goal_health_check',
'base field' => 'goal',
'relationship' => 'goal_health_check:goal',
// If 'id' => 'groupwise_max', the following fields SEEM correct based on UserViewsData....
'field' => 'goal',
'outer field' => 'goal.id',
'argument table' => 'goal',
'argument field' => 'id',
],
];
Unfortunately the subquery join this generates lacks any fields in the SELECT clause:
SELECT goal.id AS id, goal_health_check_goal.id AS goal_health_check_goal_id
FROM
{goal} goal
LEFT JOIN {goal_health_check} goal_health_check_goal ON (SELECT
FROM
{goal_health_check} goal_health_checkINNER
LEFT JOIN {goal} goal_goal_health_checkINNER ON "goal_health_checkINNER".goal = "goal_goal_health_checkINNER".id
WHERE (( ("goal_goal_health_checkINNER".id = goal.id ) ))
ORDER BY goal_health_checkINNER.created DESC
LIMIT 1 OFFSET 0) = goal_health_check_goal.goal
LIMIT 11 OFFSET 0
In order for that query to work properly, you'd have to change "(SELECT" to "(SELECT goal_health_checkINNER.goal". But by the time the subquery is built (see GroupwiseMax.php line 231), it doesn't contain any reference to the "goal" field at all. It only contains three separate references to its own "id" field.
I am unfortunately at a loss to how to get that field to appear in the subquery, and I suspect this points to a bug in the GroupwiseMax implementation —especially given that there's a (at least) four-year-old reference to an unresolved views issue in the code ("Workaround until
https://www.drupal.org/node/844910 →
is fixed"), immediately prior to all those id fields also being removed from the subquery.