Django query : Call values() on user__userprofile

I have the following models:

class UserProfile(models.Model):
    user = models.OneToOneField(User) 
    score = models.PositiveIntegerField()

class Game(models.Model):
    name = CharField(max_length=100)

class Achievement(models.Model):
    user = models.ForeignKey('User')
    game = models.ForeignKey(Game)

In settings.py I have set AUTH_PROFILE_MODULE to my UserProfile class.

For a given game, I want to get a list of the users who have, say, more than five achievements for that game.

So what I did was the following:

candidates = Achievement.objects.filter(game=game).values('user').annotate(nba=Count('id')).filter(nba__gte=5).order_by('-user__userprofile__score')

Now it works but the problem is that I get a list of values with the user id and his nba (number of achievements). But I need to print the score in template and also access other attributes of UserProfile...

So I tried changing ".values('user')" to:

.values('user','user__userprofile__score')

But it doesn't work! I get an error:

invalid field user__userprofile__score

Note that it works if I do:

.values('user','user__username')

Which seems to indicate that values can be called on attributes but not on foreignkey ?

I have also tried another way using "django-batch-select" application:

batch = Batch('achievement_set').filter(game=game)
candidates = User.objects.batch_select(achieved=batch).exclude(achieved__is_null=True)

But I get an error:

Cannot resolve keyword 'achieved' into field.

It works if I remove the "exclude" statement, but then I get a list of ALL users including those who don't have any achievement for this game (they get: achieved == [])

I've been searching everywhere but can't find a solution to my problem... some help would be highly appreciated!

回答1:

You can get user profiles by one extra query:

UserProfile.objects.filter(user_id__in=[x['user'] for x in candidates])

回答2:

I thinks you have to remove values('user') and add to the end of query_set only('user', 'user__profile') and select_related('user', 'user__profile')

candidates = [i.user for i in Achievement.objects.\
    select_related('user', 'user__userprofile').\
    annotate(nba=Count('id')).\
    filter(game=game, nba__gte=5).\
    only('user', 'user__userprofile').\
    order_by('-user__userprofile__score')]

I test on my project this select_related and onlyand it works

>>> db.connection.queries = []
>>> p = [(i.object, i.object.owner) for i in models.ImageOrVideo.objects.\
      select_related('object', 'object__owner').\
      only('object').all().\
      order_by('-object__owner__id')]
...
>>> len(db.connection.queries)
1