Annotate a column field from relation on django

Question

I got two models defined Owner and Dog

class Dog(models.Model):
    name = models.CharField(max_length=255)
    owner = models.ForeignKey(Owner)
    adopted_date = models.DateTimeField()


class Owner(models.Model):
    name = models.CharField(max_length=255)

I want to make a list with all the owners with the amount of dogs adopted by date.

Example: OwnerName Date Amount Richard 15/11/24 2 Jow 15/11/24 2

I am making this query

Dog.objects.values('owner', 'adopted_date').annotate(Count('owner')).order_by()

The problem here is that this query return owner id not the name.

{
    'owner':1,
    'adopted_date': 'date',
    'owner_count': 2
}

I need to get on the owner the name of the owner, something like.

{
    'owner':'Richard,
    'adopted_date': 'date',
    'owner_count': 2
}

Any help will be appreciated.

---

EDITION

This solution works, But I'm having a few doubts about it, making this query Dog.objects.values('owner__name', 'owner', 'adopted_date').annotate(Count('owner'))

We can get a good result, but I am worry about performance, this will generate a group by for every column inside values, but I don't need to group by owner_name because that column is not unique.

I am looking for something like

Dog.objects.values('owner', 'adopted_date').annotate(Count('owner'), Column('owner__name')).order_by()

I know that the Column aggregator function don't exist, but maybe is a way to do something like this.

Thanks in advance.

Answer 1

If you change your query to

Dog.objects.values('owner__name', 'adopted_date').annotate(Count('owner'))

You will get a list of dictionaries of the form you want:

{
    'owner__name': 'Richard',
    'adopted_date': 'date',
    'owner__count': 2
}

I actually couldn't find this in the documentation for the values() clause, but it's common in the Django ORM syntax to refer to the members of a foreign key related field of a query (ie, "owner") by using double underscores (ie, "owner__name").

---

EDIT

With the above solution, there is still the problem that if two dogs have the same adoption date and two different owners with the same name, they will be grouped. If you want to group the output by dog (which will ensure one owner per group, since there is one owner per dog) then anywhere in the query you could add .order_by('pk').

This works, but it seems hackish since it utilizes order_by for a purpose other than ordering. I'll update again if I come across a better way of doing it.