Django Left join how

Question

I fairly new to Django and stuck with creating a left join in Django. I tried so many, but none of them seems to be working:

The query I want to translate to Django is:

select ssc.id
      ,mgz.Title
      ,tli.id
      ,tli.Time
  
from Subscription ssc

join Person prs
  on ssc.PersonID = prs.id
 and prs.id = 3

join Magazine mgz
  on mgz.id = ssc.MagazineID
 and mgz.from <= date.today()
 and mgz.until > date.today()

left join TimeLogedIn tli
  on tli.SubscriptionID = ssc.id
 and tli.DateOnline = date.today()

The model I'm using looks like this:

class Magazine(models.Model):
      Title = models.CharField(max_length=100L)    
      from = models.Datefield()
      until = models.Datefield()
      Persons = models.ManyToManyField(Person, through='Subscription')

class Person(models.Model): 
      user = models.OneToOneField(User, on_delete=models.CASCADE)
      Magazines = models.ManyToManyField(Magazine, through='Subscription')

class Subscription(models.Model):
      MagazineID = models.ForeignKey(Magazine,on_delete=models.CASCADE)
      PersonID = models.ForeignKey(Person,on_delete=models.CASCADE)

class TimeLogedIn(models.Model):
      SubscriptionID = models.ForeignKey('Subscription', on_delete=models.CASCADE)
      DateOnline = models.DateField()
      Time = models.DecimalField(max_digits=5, decimal_places=2)

Like I said, tried so many but no succes and now I don't know how to do this in Django ORM , is it even possible? I created already a raw-query and this is working ok, but how to create this in Django ORM?

I think you may want a Q object with `DateOnLine=date.today() | DateOnline__isnull=True` https://docs.djangoproject.com/en/3.1/topics/db/queries/#complex-lookups-with-q-objects and previous similar question https://stackoverflow.com/a/51210121/4872140 — AMG, Mar 02 '21 at 02:52
Thanks for the answer, I will dive into it and let it know if I succeeded — MacMax, Mar 02 '21 at 20:17

NKSM · Accepted Answer · 2021-03-02T21:46:27.660

You can use field lookups lte and gt to filter your objects and then values() method.

You can also querying in the opposite direction and use Q objects for null values:

from django.db.models import Q

Subscription.objects.filter(
    PersonID_id=3,
    MagazineID__from__lte=date.today(), 
    MagazineID__until__gt=date.today()
 ).filter(
    Q(TimeLogedIn__DateOnline=date.today()) | Q(TimeLogedIn__DateOnline__isnull=True)
 ).values("id", "MagazineID__Title", "TimeLogedIn__id", "TimeLogedIn__Time")

OR from TimeLogedIn:

TimeLogedIn.objects.filter(DateOnline=date.today()).filter(
    SubscriptionID__MagazineID__from__lte=date.today(), 
    SubscriptionID__MagazineID__util__gt=date.today()
).values(
    "SubscriptionID_id", "SubscriptionID__MagazineID__Title", "id", "Time"
)

Querysets also have the query attribute that contains the sql query to be executed, you can see it like following:

print(TimeLogedIn.objects.filter(...).values(...).query)

Note: Behind the scenes, Django appends "_id" to the field name to create its database column name. Therefore it should be subscription, instead of SubscriptionID.

You can also use prefetch_related() and select_related() to prevent multiple database hits:

SubscriptionID.objects.filter(...).prefetch_related("TimeLogedIn_set")
SubscriptionID.objects.filter(...).select_related("PersonID")

Thanks for the response!! I was wondering, is this creating the left join? I want all the subscriptions for Magazines active for today and if there is a TimeLogedIn object for today, the TimeLogedIn.Time, otherwise I want a null value — MacMax, Mar 02 '21 at 21:01
@MacMax, I have updated my answer. I added another approach. — NKSM, Mar 02 '21 at 21:30

Django Left join how

1 Answers1