How can I have statistics in django admin panel on User with date filter on related field?

Question

Related model

class AbstractTask(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    issued_at = models.DateTimeField(auto_now_add=True)

Problem

I need to show some User statistics per days in the admin panel. Lets say I just need the number of issued tasks. And I need to be able to filter it by issue date (how many were issued yesterday, the day before yesterday, etc).

How I am trying to do it

I use User proxy models to register ModelAdmin for different statistics pages.

I use slightly modified (changed date ranges) DateFieldListFilter on task__issued_at field:

list_filter = [
    ('task__issued_at', DateFieldListFilter),
    'username',
]

Filters on date field don't work

Filters don't work because they end up generating query similar to this:

queryset = (User.objects
    .annotate(
        # Different statistics.
        num_tasks=Count('task'),
    )
    .filter(
        # DateFieldListFilter.
        task__issued_at__gte='2020-01-01',
        task__issued_at__lt='2020-01-02',
    )
    .values('id', 'num_tasks')
)

SQL:

SELECT "auth_user"."id",
       COUNT("task"."id") AS "num_tasks"
FROM "auth_user"
LEFT OUTER JOIN "task" ON ("auth_user"."id" = "task"."user_id")
INNER JOIN "task" T3 ON ("auth_user"."id" = T3."user_id")
WHERE (T3."issued_at" >= 2020-01-01 00:00:00+03:00
       AND T3."issued_at" < 2020-01-02 00:00:00+03:00)
GROUP BY "auth_user"."id"

The problem is that filter adds second join on table "task" when I need just one.

Forcing first inner join by adding .filter(task__isnull=False) doesn't help. It just keeps performing two identical inner joins.

It is the same behavior in django 2 and 3.

Can It be done in Django? Preferably as simple as possible: without raw sql, without much magic and with continuing using DateFieldListFilter. But any solution would help.

Answer 1

The alternative QuerySet below gives the same result without any additional joins:

(queryset = User.objects
    .annotate(
        # Different statistics.
        num_tasks=Count(
            'task', 
            filter=models.Q(
                Q(task__issued_at__gte='2020-01-01') & 
                Q(task__issued_at__lt='2020-01-02')
            )
        ),
    )
    .values('id', 'num_tasks')
)

SQL:

SELECT "auth_user"."id", COUNT("task"."id") 
FILTER (WHERE ("task"."issued_at" >= 2020-01-01 00:00:00+03:00 AND "task"."issed_at" < 2020-01-02 00:00:00+03:00)) AS "num_tasks" 
FROM "auth_user"
LEFT OUTER JOIN "task" ON ("auth_user"."id" = "task"."user_id")
GROUP BY "auth_user"."id"

but not sure about the performance compared with yours.

Anyway, to make it work with the DateFieldListFilter you just need to override the queryset method:

class CustomDateFieldListFilter(DateFieldListFilter):
    def queryset(self, request, queryset):
        # Compare the requested value to decide how to filter the queryset.
        q_objects = models.Q()
        for key, value in self.used_parameters.items():
            q_objects &= models.Q(**{key: value})
        return queryset.annotate(num_tasks=Count('task', filter=models.Q(q_objects))).values('id', 'num_tasks')

and specify the new class:

list_filter = [
    ('task__issued_at', CustomDateFieldListFilter),
    ...
]

That's it.