How to annotate on a Django model's M2M field and get a list of distinct instances?-CodePudding

I have two Django models Profile and Device with a ManyToMany relationship with one another like so:

class Profile(models.Model):
    devices = models.ManyToManyField(Device, related_name='profiles')

I am trying to use annotate() and Count() to query on all profiles that have 1 or more devices like this:

profiles = Profile.objects.annotate(dev_count=Count('devices')).filter(dev_count__gt=1)

This is great, it gives me a QuerySet with all the profiles (4500 ) with one or more devices, as expected.

Next, because of the M2M relationship, I would like to get a list of all the distinct devices among all the profiles from the previous queryset. All of my failed attempts below return an empty queryset. I have read the documentation on values, values_list, and annotate but I still can't figure out how to make the correct query here.

devices = profiles.values('devices').distinct()
devices = profiles.values_list('devices', flat=True).distinct()

I have also tried to do it in one go:

devices = (
    Profile.objects.values_list('devices', flat=True)
                .annotate(dev_count=Count('devices'))
                .filter(dev_count__gt=1)
                .distinct()
)

CodePudding user response：

You can not work with .values() since that that item appears both in the SELECT clause and the GROUP BY clause, so then you start mentioning the field, and hence the COUNT(devices) will return 1 for each group.

You can filter on the Devices that are linked to at least one of these Profiles with:

profiles = Profile.objects.annotate(
    dev_count=Count('devices')
).filter(dev_count__gt=1)

devices = Device.objects.filter(profile__in=profiles).distinct()

For some SQL dialects, usually MySQL it is better to first materialize the list of profiles and not work with a subquery, so:

profiles = Profile.objects.annotate(
    dev_count=Count('devices')
).filter(dev_count__gt=1)
profiles_list = list(profiles)

devices = Device.objects.filter(profile__in=profiles_list).distinct()