Drupal 8.x

I have a view returning:

  • UID: 1, NID: 1, VALUE: 9
  • UID: 1, NID: 3, VALUE: 8
  • UID: 1, NID: 2, VALUE: 7
  • UID: 2, NID: 9, VALUE: 5
  • UID: 2, NID: 5, VALUE: 4
  • UID: 2, NID: 7, VALUE: 3

I would like to only return:

  • UID: 1, NID: 1, VALUE: 9
  • UID: 2, NID: 9, VALUE: 5

My goal is to show only one row per UID which contains the highest VALUE. For this view, NID does not matter, but it is a part of the Database table and query being sent to views with hook_views_data() and is used elsewhere.

Can the Views module accomplish this?

  • 2
    It seems that drupal is really bad at aggregation and subqueries. You will want a query that is like: SELECT uid, nid, value FROM table INNER JOIN (SELECT uid, max(value) as mvalue FROM table GROUP BY uid) as maxvalue ON maxvalue.uid=uid AND maxvalue.mvalue=value; I don’t know how to make drupal create this SQL statement. Any time I’ve needed this, it’s been a part of a custom module, so Inwould just code it in by hand. Commented Dec 30, 2018 at 14:40
  • 2
    Try searching the web for “Drupal Views Subqueries” and you might find something. The Relation module might be what you need, but it is under early development. I wish there was a way to create a view, and then use that view as a subquery in another view. Commented Dec 30, 2018 at 14:52
  • I would be curious if it's possible to use hook_views_query_alter() and hook_views_data() to create different views groups/providers from one database table and one MYMODULE.views.inc? Even better if you could do it without knowing what the view name or display ID would be... Commented Dec 31, 2018 at 4:00
  • 1
    If I were to do it right, I would modify the Views relationship handler and field handler. The relationship handler currently displays a list of entities which are connected to the base table (or any other relationships) by an entity reference. This would be changed to also display all views. The user would select a view, the join type (the existing “require this relationship”) and the existing field to join ON. Then the field handler would have to be modified to allow the user to select fields from the other view to include in the view. The views query could then be altered. Commented Dec 31, 2018 at 11:05
  • 1
    If you would like drupal to allow one view to join on another view, I have started a project in my sandbox. (drupal.org/sandbox/beakerboy/3024293). Let me know if you want git push access. Once it actually does something I can promote it to a full project. Commented Jan 8, 2019 at 14:21

1 Answer 1


In Drupal 7 there was the Views Reference Filter.

Basically, what you're after is filtering one view with another (if you have to go through the interface and not code a custom solution). Those database queries can be quite slow so you're advised to use caching.

Views Field View has the D8 version. The strategy is to create two Views displays, one listing Users, the other Views listing only one node sorted by Value descending and with a contextual filter for UID. Then you insert the second view into the first one (as an additional field).

This way you get the one node that has the highest value for the particular user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.