Django - access foreign key data in an annotated query

Question

I'm using django-relationships and I'm filtering my users by number of followers like this:

Relationship.objects.values('to_user').annotate(num_followers=Count('to_user')).order_by('-num_followers')

which returns something like this

[{'to_user': 1, 'num_followers': 3}, {'to_user': 4, 'num_followers': 1}]

My problem is that I need access to my User object, not just their pk. Right now I'm doing something like this:

tu_list = []
for tu in top_users_set:
    tu_list.append({
        'top_user': User.objects.get(pk=tu['to_user']),
        'followers': tu['num_followers'],
    })

which is doing a query for every user. Since the queryset could end up having hundreds+ users, this could be really bad.

Any input to help improve this would be appreciated.

Thanks

Sergey Golovchenko · Accepted Answer · 2012-06-22 01:33:13Z

1

If you want the user you need to access it the other way around by querying the User model and joining Relationship. Here's the relevant documentation

should be something like this:

from django.db.models import Count

users = User.objects.annotate(num_followers=Count('to_users')).order_by('-num_followers')

this will give you the users and each of them will have an extra property num_followers

model.py

from django.contrib.auth.models import User
from django.db import models

class Relationship(models.Model):
    from_user = models.ForeignKey(User, related_name='from_users')
    to_user = models.ForeignKey(User, related_name='to_users')

test

>>> from so.models import *
>>> from django.contrib.auth.models import User
>>> u1 = User()
>>> u1.username='user1'
>>> u1.save()
>>> u2 = User()
>>> u2.username='user2'
>>> u2.save()
>>> u3=User()
>>> u3.username='user3'
>>> u3.save()
>>> # so we have 3 users now
>>> r1 = Relationship()
>>> r1.from_user=u1
>>> r1.to_user=u2
>>> r1.save()
>>> r2=Relationship()
>>> r2.from_user=u1
>>> r2.to_user=u3
>>> r2.save()
>>> r3=Relationship()
>>> r3.from_user=u2
>>> r3.to_user=u3
>>> r3.save()
>>> rels = Relationship.objects.all()
>>> rels.count()
3
>>> # we have 3 relationships: user1 follows user2, user1 follows user3, user2 follows user3
>>> users = User.objects.annotate(num_followers=Count('to_users')).order_by('-num_followers')
>>> for user in users:
>>>     print user.username, user.num_followers
user3 2
user2 1
user1 0

EDIT2 fixed the typos, added the test

edited Jun 22, 2012 at 1:33

answered Jun 21, 2012 at 15:11

Sergey Golovchenko

18.8k15 gold badges58 silver badges73 bronze badges

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

4 Comments

imns Over a year ago

With your example users is just a dict with {'num_followers': 5}

Sergey Golovchenko Over a year ago

Updated my answer to, see above

imns Over a year ago

I think you are missing a ) before .order_by, but when I add that, the queryset is not ordered correctly by the number of followers each user has.

Sergey Golovchenko Over a year ago

couldn't try it earlier, you are right there was a typo there. see edit2

Collectives™ on Stack Overflow

Django - access foreign key data in an annotated query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related