CODEWITHSUNDEEP
djangofilter
David Foie Gras
David Foie Gras

Reputation: 2080

Django, model filter and order by other foreignkey model

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?

Upvotes: 1

Views: 35

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476719

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 , 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]

Upvotes: 1

Related Questions