Django, model filter and order by other foreignkey model

user_6426449 0
Viewed 35

model1:

class Tag(models.Model):
    text = models.CharField(max_length=255, null=True, blank=True, unique=True)

model2:

class TagConnect(models.Model):
    tag = models.ForeignKey("category.Tag", on_delete=models.CASCADE, null=True, blank=True)
    created = models.DateTimeField(auto_now_add=True)

I want to get top 5 Tag object by count of TagConnect object that has created in recent 24 hours.

The more TagConnect object exists, the tag is more likely on high priority.

If tag "rabbit" has 2 TagConnect in 24 hours and "horse" has 10 TagConnect but those are not in 24 hours, "rabbit" has more high priority.

Maybe it will be like..

Tag.objects.order_by(tag_connect_in_24hours)[:5]

How to do this?

filter django
1 Answers

I want to get top 5 Tag object by count of TagConnect object that has created in recent 24 hours.

You can first filter the related model, then perform a Count, and then order by that Count and take the first five objects, so:

from datetime import timedelta
from django.db.models import Count
from django.utils.timezone import now

Tag.objects.filter(
    tagconnect__created__gte=now()-timedelta(days=1)
).annotate(
    nconnect=Count('tagconnect')
).order__by('-nconnect')[:5]

Since django-3.2, you can use .alias(…) [Django-doc] to avoid aggregating twice: one for the extra attribute the Tags will use, and one to ORDER BY. By using alias(…) we only do this in the ORDER BY clause:

from datetime import timedelta
from django.db.models import Count
from django.utils.timezone import now

Tag.objects.filter(
    tagconnect__created__gte=now()-timedelta(days=1)
).alias(
    nconnect=Count('tagconnect')
).order__by('-nconnect')[:5]
user_675790
Related