Django-queryset get one object per field=foo

Question

I have a basic FK to user, call it owner

class Baz(models.Model):

    owner = models.ForeignKeyField(User)


    ....
    ....

Now, with a queryset of Baz's, is there something that I can chain that will give me only one Baz per owner?

Answer 1

EDIT: This has a higher chance of working:

CheckIn.objects.extra(where=['checkins_checkin.id = (SELECT MAX(checkins_checkin.id) FROM checkins_checkin temp, auth_user WHERE auth_user.id = temp.id AND temp.user_id = checkins_checkin.user_id)',]).count()

Answer 2

The function you are truly looking for is GROUP BY. However, Django does not typically support building querysets that do not directly output model instances. In this situation, you have two approachs:

Baz.objects.values('owner').distinct()

This will net you each distinct owner, but not the Baz object itself.

Baz.objects.filter(pk__in=Baz.objects.values('owner').distinct())

The above will perform a subquery (at least in MySQL) and should give the intended results, but isn't the most efficient way to retrieve it.

Lastly, since aggregates have been added, it may be possible for you to write a custom aggregate class which would work as a kind of "Distinct" and simply "GROUP BY ".

Answer 3

I believe the question is how to run the equivalent of this:

SELECT * FROM myapp_baz GROUP BY owner_id;

Which will return one row for each unique owner_id.

It looks like this does the trick:

qs = Baz.objects.all()
qs.query.group_by = ['owner_id']

# Seems to do the trick
print [item for item in qs]

Answer 4

This is probably not the best solution (would like to keep it out of memory and in querysets) but:

>>> d={}
>>> [d.setdefault(str(a.owner),a) for a in qs ]
>>> d.values()

does return a list of objects, the latest for each owner. I have real reservations about the scalability of this solution.