How to use filter in Count Q in Django

Question

I'm trying to sort a query set by its foreign key's number of objects that has been created in the last 7 days.

As in if there are three titles like:

Title one --> total entry: 100, entries in last 7 days: 5

Title two --> total entry: 10, entries in last 7 days: 8

Title three --> total entry: 50, entries in last 7 days: 2

I want them ordered like:

Title two > Title one > Title three

This is what I'm trying to get the above generator:

all_titles = Title.objects.annotate(
    num_entries=Count(
        "entry", filter = Q(entry__entry_date2__gte=make_aware(
            datetime.datetime.today()-datetime.timedelta(days=7)
            )
        )
    )
).order_by("num_entries")

This is what my models look like:

class Title(models.Model):

    title_text = models.CharField(max_length=50, null=True)
    title_url = models.CharField(max_length=100, null=True)
    title_channels = models.ManyToManyField(TitleChannels)

    def __str__(self):
        return self.title_text


class Entry(models.Model):

    entry_title = models.ForeignKey(Title, on_delete=models.CASCADE)
    entry_text = models.CharField(max_length=2500, null=True, blank=True)
    entry_author = models.ForeignKey(User, on_delete=models.CASCADE, null=True, blank=True)
    entry_date = models.CharField(max_length=20, null=True, blank=True)
    entry_points = models.IntegerField(default=0, null=True, blank=True)
    entry_readability = models.BooleanField(default=True)
    entry_date2 = models.DateTimeField(null=True, blank=True)

    def __str__(self):
        return self.entry_text

Instead of ordering it by what I want, I'm getting a list that is sorted by Titles entry_set's length.

Answer 1

Keep in mind that order_by by default, sorts from lower to greater, so your order will be

Title three --> 2 entries in the last week
Title one   --> 5 entries in the last week
Title two   --> 8 entries in the last week

So I think you want to sort by descending order: .order_by("-num_entries")

I've created a small demo with data that should pretty much resemble yours to show the difference:

last_week = datetime.datetime.today() - datetime.timedelta(days=7)
count_last_week = Count(
    "entry",
    filter=Q(
        entry__entry_date2__gte=(
            django.utils.timezone.make_aware(last_week)
        )
    )
)
q = Title.objects.annotate(
    entries_last_week=count_last_week
).order_by(
    "entries_last_week"
)
for title_entry in q.all():
    print(
        "%s: %s entries last week (total entries: %s)" % (
            title_entry.title_text,
            title_entry.entries_last_week,
            Entry.objects.filter(entry_title=title_entry).count()
        )
    )
print('----')
q = Title.objects.annotate(
    entries_last_week=count_last_week
).order_by(
    "-entries_last_week"  # Important '-'
)
for title_entry in q.all():
    print(
        "%s: %s entries last week (total entries: %s)" % (
            title_entry.title_text,
            title_entry.entries_last_week,
            Entry.objects.filter(entry_title=title_entry).count()
        )
    )

I added the total number of entries by title in the print() to show that all the values (count annotations) are properly calculated.

Title three: 2 entries last week (total entries: 50)
Title one: 5 entries last week (total entries: 100)
Title two: 8 entries last week (total entries: 10)
----
Title two: 8 entries last week (total entries: 10)
Title one: 5 entries last week (total entries: 100)
Title three: 2 entries last week (total entries: 50)

---

EDIT as per the OP's comments/chat messages:

The possibility to pass filters to the Count() (or other annotations) was added in Django 2.0

In Django 1.11.X you should still be able to do this with Subqueries:

import datetime
import django.utils.timezone
from django.db.models import OuterRef, Subquery, Count, IntegerField

last_week = django.utils.timezone.make_aware(
    datetime.datetime.today() - datetime.timedelta(days=7)
)
entries_last_week_sub_q = Entry.objects.filter(
    entry_date2__gte=last_week,
    entry_title=OuterRef('pk')  # The primary key of the Title objects
                                # where this subquery will be "embedded"
).values(
    'entry_title'
).annotate(
    cnt=Count('*')
).values('cnt')[:1]

q = Title.objects.annotate(
    entries_last_week=Subquery(entries_last_week_sub_q, output_field=IntegerField()),
).order_by(
    '-entries_last_week'
)
print("query %s" % q.query)
print('----')
print("Using subqueries:")
for title_entry in q.all():
    print("%s: %s entries last week" % (
        title_entry.title_text, title_entry.entries_last_week)
    )

Tested in a Django 2.0, though, not in a 1.11... :-(

Inspiration came from here and of course, the Django docs.

And also, maybe it's worth taking a look to prefetch_related for Title.entry_set, but using a Prefetch() object so you can narrow down the Entry objects to be loaded by providing a queryset of Entries that have been created in the last week? Something like this:

q = Title.objects.prefetch_related(
    Prefetch('entry_set', queryset=Entry.objects.filter(entry_date2__gte=last_week))
).annotate(
    entries_last_week=Count('entry_set),
).order_by(
    '-entries_last_week'
)

This method (as opposed to the example with the Subqueries a few lines above) has not been tested at all, though.