CODEWITHSUNDEEP
djangoannotationsdjango-queryset
Dylan
Dylan

Reputation: 2365

Django simple annotation across two models

I've got three models, User, Achievement, and UserAchievement: 

class User(AbstractUser):
    email = models.EmailField(_('email address'), unique=True)
    ...

class Achievement(models.Model):
    name = models.CharField(max_length=64)
    points = models.PositiveIntegerField(default=1)

class UserAchievement(models.Model):
    # Use a lazy reference to avoid circular import issues
    user = models.ForeignKey('users.User', on_delete=models.CASCADE)
    achievement = models.ForeignKey(Achievement, on_delete=models.CASCADE)

I'd like to annotate Users with a 'points' column that sums up the total points they've earned for all their achievements as listed in the UserAchievement table.

But clearly I'm not fully up to speed with how annotations work. When I try:

users = User.objects.annotate(points=Sum('userachievement__achievement__points'))
for u in users:
   print(u.email, u.points)

It crashes with:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/dylan/.local/share/virtualenvs/server-E23dvZwD/lib/python3.7/site-packages/django/db/models/query.py", line 274, in __iter__
self._fetch_all()
  File "/Users/dylan/.local/share/virtualenvs/server-E23dvZwD/lib/python3.7/site-packages/django/db/models/query.py", line 1242, in _fetch_all
self._result_cache = list(self._iterable_class(self))
  File "/Users/dylan/.local/share/virtualenvs/server-E23dvZwD/lib/python3.7/site-packages/django/db/models/query.py", line 78, in __iter__
setattr(obj, attr_name, row[col_pos])
AttributeError: can't set attribute

Upvotes: 1

Views: 251

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32244

You can use a ManyToManyField from User to Achievement using UserAchievement as a through table to simplify your queries

class User(AbstractUser):
    email = models.EmailField(_('email address'), unique=True)
    achievements = models.ManyToManyField('Achievement', through='UserAchievement')
    ...

This can be used like so for annotations

User.objects.annotate(points=Sum('achievements__points'))

And if you have an instance of user

user.achievements.all()

As for the error, I am convinced you have a property on your User model named points

Upvotes: 2

Related Questions