CODEWITHSUNDEEP
pythondjangodjango-queryset
vitaminwater
vitaminwater

Reputation: 6944

Django queryset and GROUP BY

I'm struggling with django querysets and GROUP BY queries, I know there are plenty of similar questions, but I really don't get it:/

I would like to be able to create a request similar to this one (SQLite):

SELECT MAX(fb_game_score.value), fb_game_fbuser.first_name, fb_game_fbuser.last_name
FROM fb_game_score
JOIN fb_game_game ON (fb_game_score.game_id = fb_game_game.id)
JOIN fb_game_fbuser ON (fb_game_game.user_id = fb_game_fbuser.id)
GROUP BY fb_game_fbuser.fb_user_id;

The query is quite simple, it lists the users scores by showing only the best score for each players.

For clarification here's the model classes:

class FBUser(AbstractUser):

    fb_user_id = models.CharField(max_length=100, null=True)
    oauth_token = models.CharField(max_length=1024, null=True)
    expires = models.IntegerField(null=True)
    highest_score = models.IntegerField(null=True)


class Game(models.Model):

    identifier = models.CharField(max_length=100, db_index=True)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='games')


class Score(models.Model):

    game = models.ForeignKey(Game, related_name='scores')
    value = models.IntegerField()
    date = models.DateTimeField(auto_now=True)
    timestamp = models.FloatField(default=0)
    inter = models.BooleanField(default=False)

Upvotes: 3

Views: 2372

Answers (1)

Luis Masuelli
Luis Masuelli

Reputation: 12323

There's no high-level group_by in the queryset. It's used in calls to aggregate and annotate but it is not available to you.

There's a low-level API which is not documented at all. You can get an internal query description:

queryset = ... #whatever query you'd want to group by
query = queryset.query

and then you can alter the group_by member -which is a list- by adding a field which you'd want to group by:

query.group_by.append('a_field')

But:

  • you have to seriously know what you're doing.
  • there's no guarantee of stability of this API.

The current alternative for this is falling back to a raw (django.db.connection.* methods) SQL query.

Edit: I just saw this 3rd-party application which could help you with reports. I don't know if you can use in-code reports, or you have to limit yourself to in-view reports (i.e.: don't know if you can process reports in code or just have them as final results).

Upvotes: 4

Related Questions