0

I need to fetch the top performer for each month, here is the below MySql query which gives me the correct output.

select id,Name,totalPoints, createdDateTime 
from userdetail
where app=4 and totalPoints in ( select 
max(totalPoints) 
FROM userdetail
where app=4 
group by  month(createdDateTime), year(createdDateTime))
order by totalPoints desc

I am new to Django ORM. I am not able to write an equivalent Django query which does the task. I have been struggling with this logic for 2 days. Any help would be highly appreciated.

Brian Destura
  • 11,487
  • 3
  • 18
  • 34
namitha h
  • 1
  • Can you include your model? – Jerven Clark Jul 20 '21 at 07:47
  • Hi, please find the model. : class Userdetail(models.Model): appId = models.ForeignKey(AdmApplicationdata, models.PROTECT, db_column='appId', blank=True, null=True) Name = models.CharField(max_length=100, blank=True, null=True) totalPointsEarnedTillToday = models.IntegerField() createdDateTime = models.DateTimeField(blank=True, null=True) – namitha h Jul 20 '21 at 08:48
  • this is the query i have written : tp=models.UsrUserdetail.objects.filter(appId=4).values(TotalPoints=F("totalPoints"),name=F("firstName")).annotate(year1=ExtractYear("createdDateTime"),month1=ExtractMonth("createdDateTime"),mp=Max("totalPoints")) this is giving year1 and month1 as None, not sure why, i have included ExtractYear and ExtractMonth library – namitha h Jul 20 '21 at 08:58
  • Thank alot for the response. This is returning one row giving me the details of user who has scored highest points. but my requirement is to get details of users with highest points for every month. – namitha h Jul 20 '21 at 17:37

1 Answers1

0

While the GROUP BY clause in a subquery is slightly difficult to express with the ORM because aggregate() operations don't emit querysets, a similar effect can be achieved with a Window function:

UserDetail.objects.filter(total_points__in=UserDetail.objects.annotate(max_points=Window(
        expression=Max('total_points'),
        partition_by=[Trunc('created_datetime', 'month')]
    )).values('max_points')
)

In general, this sort of pattern is implemented with Subquery expressions. In this case, I've implicitly used a subquery by passing a queryset to an __in predicate.

The Django documentation's notes on using aggregates within subqueries is are also relevant to this sort of query, since you want to use the results of an aggregate in a subquery (which I've avoided by using a window function).

However, I believe your query may not correctly capture what you want to do: as written it could return rows for users who weren't the best in a given month but did have the same score as another user who was the best in any month.

tari
  • 658
  • 4
  • 14