View with relationship - column displaying count of related nodes

Question

I have a content type ("Interviewee") and a Media entity using the new media stuff in core (e.g. Remote Video) which have entity references back to the Interviewee entity.

I then have a view of the Interviewee content type, with a relationship to Media.

Rather than displaying multiple rows per person for each related media file, I'd just like display the total number of audio/video files linked to that content type in a column – including, this is important, a 0 for anyone who doesn't have media files - also ideally the column would be sortable.

Is this kind of basic counting of relationships possible in core / with a widely used module, without having to code a custom views field or a views sort plugin?

I've written similar plugins before, however I kind of assumed I could do it using aggregation - but I'm not getting anywhere.

Answer 1

You can do this with Aggregation.

First create your normal Views listing Interviewee nodes. Add the Media reference field and set to show all values in one row.

Turn Aggregation on, and change the Aggregation settings for the Media reference field to Count or Count DISTINCT.

Sorting criteria can sometimes mess up Aggregation counting so it's best not to have any while you're configuring the Aggregation, then add later once you know Aggregation is working and check each step that it's working as expected.

You can even add the Sort criteria for the reference field and use Aggregation on it (again Count or Count DISTINCT), Expose it if you want to allow users to change the order Ascending/Descending.

Sorting also works in a table Format if you turn on sorting for that field.