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.

stett · Accepted Answer · 2015-06-22 16:23:11Z

2

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.

edited Jun 22, 2015 at 16:23

answered Jun 20, 2015 at 0:06

stett

1,4111 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chfumero Over a year ago

Ok, that is right. I already now that this solution works, but I have a doubt about it. The name of the owner is not unique, the database should contain 2 Richards being diferents persons. This solution can merge that two diferent persons results like one. Maybe grouping also by owner we can avoid this problem, but in this case we have the owner__name grouping just to take the field value. Look to the edition of the question please.

stett Over a year ago

Ah yes I see what you're saying. I think a solution to that might be to re-add the order_by, but with 'id' as an argument. I'll update my answer.

stett Over a year ago

Also I deleted my comment that "As a side note, you may as well also remove the order_by() clause - I don't think it does anything without arguments."... I realized that order_by does in fact do something when empty. Woo! I'm learning stuff.

chfumero Over a year ago

Ok, thanks for your help. I just use the version using owner in the values.

Collectives™ on Stack Overflow

Annotate a column field from relation on django

EDITION

1 Answer 1

EDIT

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDITION

1 Answer 1

EDIT

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related