Django Aggregation Across Reverse Relationship
- by Tom
Given these two models:
class Profile(models.Model):
user = models.ForeignKey(User, unique=True, verbose_name=_('user'))
about = models.TextField(_('about'), blank=True)
zip = models.CharField(max_length=10, verbose_name='zip code', blank=True)
website = models.URLField(_('website'), blank=True, verify_exists=False)
class ProfileView(models.Model):
profile = models.ForeignKey(Profile)
viewer = models.ForeignKey(User, blank=True, null=True)
created = models.DateTimeField(auto_now_add=True)
I want to get all profiles sorted by total views. I can get a list of profile ids sorted by total views with:
ProfileView.objects.values('profile').annotate(Count('profile')).order_by('-profile__count')
But that's just a dictionary of profile ids, which means I then have to loop over it and put together a list of profile objects. Which is a number of additional queries and still doesn't result in a QuerySet. At that point, I might as well drop to raw SQL. Before I do, is there a way to do this from the Profile model? ProfileViews are related via a ForeignKey field, but it's not as though the Profile model knows that, so I'm not sure how to tie the two together.
As an aside, I realize I could just store views as a property on the Profile model and that may turn out to be what I do here, but I'm still interested in learning how to better use the Aggregation functions.
