(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