Limit a View with a relationship to show only the first (by sort) relationship of each of the original entities

Question

(note - I've tried aggregation and Distinct, see final paragraph. This is different to questions like this one about Views duplicates because I want to remove the "duplicate" relationship-derived items with the first "original" determined by sort criteria, not ID)

To give a simple use-case, imagine a shop with products (italics) and a taxonomy vocabulary 'colour' (bold) something like this. Using products here simply for an easy-to-understand use case.

Each product has a field relating to sales:

• Red - Weight: -5
  • Fire truck. Sales: 12
  • Telephone. Sales: 128
• Blue - Weight: 0
  • Whale. Sales: 0
  • Bottle. Sales: 914
• Yellow - Weight: 5
  • Banana. Sales: 1,941
  • Submarine. Sales: 1
• more...

I want something like a view which, for each term (sorted by weight, limited to the top three), shows the term name with the image of the top-selling item, as an example of that category. For example:

• Red. picture of a red telephone
• Blue. picture of a blue bottle
• Yellow. picture of a yellow banana

I could do this using Views Field View module, with each item in a Taxonomy Term view contains a nested view of products restricted to one item, receiving a contextual filter that is the taxonomy term ID, then sorts by sales to show only the top seller. This is my "last resort" option, since Views Field View can create very complex queries. I'd prefer to avoid views within views for somethig that seems so simple.

What I've tried to make work is, making a view of terms, with a relationship to products, then sort it first by product sales then by term weight. This works, but it gives me every product with the term, and I can't find a way to restrict it. With results limited to three the above example would show:

• Red. picture of a red telephone
• Red. picture of a red firetruck
• Blue. picture of a blue bottle

Based on an old similar question on SO, turning on Use aggregation under Advanced view settings and ticking Distinct under Query settings under Advanced view settings, I've managed to get something that removes the "duplicates" from the original list. However, because of the timing of when they're removed, the sort isn't able to apply, and it seems to simply get the first by ID, not by my sort criteria:

• Red. picture of a red fire truck
• Blue. picture of a blue whale
• Yellow. picture of a yellow banana

Answer 1

I believe your issue is a good example of needing additional meta information on your Product (Node) to do (simple) views searches. The approach you've take, by embedding the information into a Field of the node is typically difficult to use. I recommend 2 possible solutions:

• This may not have a solution for you but I suggest looking at https://www.drupal.org/project/views_aggregator for complicated aggregated views.
• Another option is that instead of using the field with nested values. To use something like the Relation module to store your heuristics (sales data). Relations be linked together to get information like you requested from your products.

Answer 2

I found a module that does almost exactly what I'm looking for: Views Grouping Row Limit.

The bad news is, it's unmaintained and never got a stable release, but it seems to work from my limited testing. I set the original field to be a grouping field, and then set the Views Grouping Row Limit to 1 item, and it shows the correct one based on my sort criteria.

---

From my testing so far, it seems to work as expected against up-to-date Views and other common modules in April 2016, and appears to meet all the criteria of my answer, if the patch on this issue is applied: https://www.drupal.org/node/1315888.

It gives incorrect results when mixed with the normal Views pager/item limit (in my example, it'd show the red and blue items only, then nothing because the in-built Views three item limit counts the not-shown other red item), but with this patch, you can limit the number of groups as well as the number of items within each group. It also adds the ability to add a group offset (i.e. skip X groups then show the next Y).