Django queryset and GROUP BY
I am struggling with django queries and GROUP BY queries, I know there are many similar questions, but I really don't understand: /
I would like to be able to create a query similar to this (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 user ratings, showing only the best score for each player.
For clarification, here are 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)
source to share
There is no high level in the queryset group_by
. It is used for calls aggregate
and annotate
, but it is not available to you.
There is a low-level API that is not documented at all. You can get an internal description of the request:
queryset = ... #whatever query you'd want to group by
query = queryset.query
and then you can change the member group_by
that is a list by adding the field you want to group:
query.group_by.append('a_field')
But:
- you need to seriously know what you are doing.
- there is no guarantee of the stability of this API.
The current alternative for this is with raw ( django.db.connection.*
methods) SQL query.
Edit . I've seen this third party app that could help you with reporting. I don't know if you can use reports in code, or if you have to limit yourself to reports in view mode (i.e. not know if you can process reports in code or just have them as final results).
source to share