Django order by count by date-CodePudding

Good morning!

I have tried many things but can't get to order my post by likes and by date. For example, I wish to have a "popular post" page, which contains only posts from today, but order by most liked.

Here are my models:

Class Post(models.Model):
    name = models.CharField(max_length=40, default=None, null=False)
    cover = models.CharField(max_length=100, default=None, null=True, blank=True)
    content = models.TextField(max_length=2000, default=None, null=False)

class VoteDate(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE)
    date = models.DateTimeField(default=timezone.now)

The closest I came but did not work is this line:

hot_today = Post.objects.annotate(count=Count('votedate', filter=Q(votedate__date=datetime.today()))).order_by('count')[:30]

Thank you for your help!

CodePudding user response：

There are two problems here: (1) you filter by the date field of the VoteDate objects, which is a DateTime, hence the filtering will not work properly since you are comparing a datetime object with a datetime with hours, minutes, and seconds all set to zero; and (2) you should order in descending order, so -count, not count:

from django.db.models import Q
from django.utils.timezone import now

hot_today = Post.objects.annotate(
    count=Count('votedate', filter=Q(votedate__date__date=now().date()))
).order_by('-count')[:30]

You might want to add a database index on the date field to boost efficiency, and filter in the QuerySet:

class VoteDate(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE)
    date = models.DateTimeField(auto_now_add=True, db_index=True)

we can filter with:

from django.db.models import Q
from django.utils.timezone import now

hot_today = Post.objects.filter(
    votedate__date__date=now().date()
).annotate(
    count=Count('votedate')
).order_by('-count')[:30]

This will not show Posts with no votes that day, but that might not be a problem. By filtering, it will reduce the query time effectively to only VoteDates set to this day.