Django models: how to get a two tables left join including nulls on left

Question

First post here and Django newbie, so please, be indulgent.

I've the base model User and another model with a User foreignkey:

class User(AbstractUser):
    ...

class UserDailyActivity(models.Model)
    user = models.ForeignKey("User")
    day = models.DateField()
    last_operation_time = models.TimeField()
    ...

I'm trying to reproduce in Django a simple sql query that list all User model records and some of the related UserDailyActivity columns, that may exist or not. In plain SQL:

SELECT 
    User.id,
    User.username,
    Daily.id as daily_id
FROM 
    User
LEFT JOIN 
    UserDailyActivity as Daily on (user.id = Daily.user_id)
WHERE 
    Daily.day = TODAY()

As expected, this is the query result, that includes the joined column:

+----+------------+----------+
| id | user_name  | daily_id |
+----+------------+----------+
|  1 | One        | NULL     |
|  4 | Two        | 10       |
|  5 | Three      | NULL     |
+----+------------+----------+

I've read different post here about use annotation, prefetch, filtered relation etc, but I've to admit that I failed miserably.

Can someone point me on the right way to get this working ?

Answer 1

You should not think in terms of queries, but in terms of models and data, so instead of designing a query and then trying to mimic this in the ORM, you should think what you need, and how you do this with the Object Relational Mapper (ORM). This makes querying easier, but often the ORM will also optimize queries if specified effectively.

You can make a combination of filtering and annotating with:

from datetime import date

from django.db.models import F, Q

User.objects.filter(
    Q(userdailyactivity=None) | Q(userdailyactivity__day=date.today())
).annotate(daily_id=F('userdailyactivity'))

---

Note: It is normally better to make use of the settings.AUTH_USER_MODEL ^[Django-doc] to refer to the user model, than to use the User model ^[Django-doc] directly. For more information you can see the referencing the User model section of the documentation.