I have a Puzzle model and a Session model
During a session, user will play puzzles and rate them. result is recorded through the following model:
class Play(models.Model):
puzzle = models.ForeignKey(
'puzzles.Puzzle', on_delete=models.CASCADE, related_name='plays')
rating = models.IntegerField(default=-1)
session = models.ForeignKey(Session, blank=True,
null=True, on_delete=models.SET_NULL, related_name='plays')
a same puzzle can be played more than once during the same session.
I'm trying to annotate the Session.objects with the rating_avg_from_session_plays which is averaging the rating_average_by_puzzle.
in other terms:
- for each puzzle played during the session, I want to obtain the average
rating(avg_pzl_rating) - and I want afterwards, for each session, I want to average these
avg_pzl_ratingto obtain the average session rating (avg_session_pzl_rating)
Using Subquery and group_by syntaxes, I've not been able so far to achieve the expected result.
Here is the closest I've reached:
rating_average_by_puzzle_from_session_plays = Subquery(Play.objects.filter(session_id=OuterRef(
'id')).values('puzzle_id').order_by('puzzle_id').annotate(
puzzle_rating_average=Avg('rating')).values('puzzle_rating_average'))
Session.objects.annotate(rating_avg_from_session_plays=Avg(rating_average_by_puzzle_from_session_plays))
What Am I doing wrong ?
from the result it appears like only the first rating is taken into account, while the following on a Session object is working very well:
session_object.plays.values('puzzle_id').order_by('puzzle_id').annotate(
puzzle_rating_average=Avg('rating')).aggregate(
rating_average=Avg('puzzle_rating_average'))['rating_average'] or 0
here is the corresponding sql request sent to the database when I'm trying to annotate the Session queryset:
SELECT "games_session"."id", "games_session"."datetime", "games_session"."rating_average",
"games_session"."user_id", AVG((SELECT AVG(U0."rating") AS "puzzle_rating_average"
FROM "games_play" U0 WHERE U0."session_id" = "games_session"."id"
GROUP BY U0."puzzle_id")) AS "rating_avg_from_session_plays" FROM "games_session"
GROUP BY "games_session"."id", "games_session"."datetime", "games_session"."rating_average",
"games_session"."user_id"
As requested by willeM_ Van Onsem, here is a fixture (json) to illustrate an example of data to be averaged:
[
{
"model": "games.play",
"pk": 207,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 208,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 209,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 210,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 211,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 212,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 213,
"fields": {
"puzzle": 105,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 214,
"fields": {
"puzzle": 105,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 215,
"fields": {
"puzzle": 106,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 218,
"fields": {
"puzzle": 107,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 220,
"fields": {
"puzzle": 109,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 224,
"fields": {
"puzzle": 113,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 225,
"fields": {
"puzzle": 114,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 232,
"fields": {
"puzzle": 108,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 233,
"fields": {
"puzzle": 110,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 246,
"fields": {
"puzzle": 111,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 247,
"fields": {
"puzzle": 112,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 248,
"fields": {
"puzzle": 115,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 510,
"fields": {
"puzzle": 104,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 511,
"fields": {
"puzzle": 105,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 512,
"fields": {
"puzzle": 106,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 513,
"fields": {
"puzzle": 107,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 514,
"fields": {
"puzzle": 108,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 515,
"fields": {
"puzzle": 109,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 516,
"fields": {
"puzzle": 110,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 517,
"fields": {
"puzzle": 111,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 518,
"fields": {
"puzzle": 112,
"rating": 3,
"session": 2
}
},
{
"model": "games.play",
"pk": 519,
"fields": {
"puzzle": 113,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 520,
"fields": {
"puzzle": 114,
"rating": 1,
"session": 2
}
},
{
"model": "games.play",
"pk": 521,
"fields": {
"puzzle": 115,
"rating": 2,
"session": 2
}
},
{
"model": "games.play",
"pk": 522,
"fields": {
"puzzle": 115,
"rating": 2,
"session": 2
}
},
{
"model": "games.session",
"pk": 2,
"fields": {
"datetime": "2020-10-28T20:26:52.023Z",
"rating_average": "2.2"
}
}
]
For this specific session (31 plays over 12 puzzles), running the queryset annotation gives an average of 1.0 (same result with limitating the avg_pzl_rating to be averaged with [:1], [:3], [:8]), while the direct aggregation on the idividual sessio object gives 2.1527777777777777
