Get the top n rows of each day from same table in Django

Question

I am sure this is not a novel/new problem. I really tried to look into other solutions. However, I could not find how to solve this.

I have a model like

class Deal(models.Model):
    title = models.CharField(max_length=1500)
    viewCounter = models.SmallIntegerField(default=0)
    thumbsUpCounter = models.SmallIntegerField(default=0)
    createDateTime = models.DateTimeField(auto_now_add=True)
    owner = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, null=False, related_name='deal_owner',
                          editable=False)

Now I want to get top 10 (or less) deals ordering by thumbsUpCounter and viewCounter of every day. I tried to look into the Subquery and Outerref. However, I could not figure out how can I get the right results.

** I am using MySQL.

Thanks in advance.

are you trying to group by date, and find top 10 hits? – ha-neul Dec 16 '20 at 04:53 — ha-neul, Dec 16 '20 at 04:53
@ha-neul yes. that is what I was looking for. Thanks – sadat Dec 16 '20 at 05:00 — sadat, Dec 16 '20 at 05:00

ha-neul · Answer 1 · 2020-12-16T05:22:19.047

1

try

from django.db.models.functions import TruncDate

query = Deal.objects.annotate(date=TruncDate('createDateTime'))\ # extract date 
        .values('date')\                                        # group by date
        .order_by('-thumbsUpCounter')\                          # order by
         [:10]                                                  # slice first 10

edited Dec 16 '20 at 05:22

answered Dec 16 '20 at 05:09

ha-neul

3,058
9
24

could you please check the syntax? there are some syntax issues for braces. – sadat Dec 16 '20 at 05:21
yes, I missed one ```)``` in the first line. updated – ha-neul Dec 16 '20 at 05:22
really thanks for your answer. But I am getting an error 'dict' object has no attribute 'owner', owner is a another model of Deal model. Do you have any idea of this. – sadat Dec 16 '20 at 06:21
? I don’t see owner anywhere in your q? – ha-neul Dec 16 '20 at 06:25
I did not realize that the foreign key would give any error. I added the owner. – sadat Dec 16 '20 at 06:29
First of all, if you print out the query, does it show top 10 that you want? Second, you have something like q.owner in your template? – ha-neul Dec 16 '20 at 06:33
It does not show the rows, it gives an error. I am using django rest, there is no template. – sadat Dec 16 '20 at 06:39
just to let you know.. your solution actually doesn't work. – sadat Dec 16 '20 at 11:24
thanks for letting me know. were you able to print out ```print(query)``` in the terminal? – ha-neul Dec 16 '20 at 13:27
There is no grouping , how would that work anyway? – sadat Dec 16 '20 at 13:59
in django ORM, values is for grouping. we grouped by the date. when you say no grouping, could you be more specific? – ha-neul Dec 16 '20 at 14:02
I reposted the question https://stackoverflow.com/questions/65324003/get-top-n-records-for-each-group-with-django-queryset – sadat Dec 16 '20 at 14:02

Get the top n rows of each day from same table in Django

1 Answers1