CODEWITHSUNDEEP
sqldjangodjango-modelsdjango-orm
AlexMe
AlexMe

Reputation: 96

Django ORM - LEFT JOIN Table2 ON Table2.Column = 123

What i whant to achieve:

http://sqlfiddle.com/#!5/dccc4/5

DB Scheme: 

CREATE TABLE Table1 
(
 idTable1 integer primary key
);

CREATE TABLE Table2
(
  idTable1 integer,
  user_id integer,
  num integer
);

INSERT INTO Table1 (idTable1) VALUES (1);
INSERT INTO Table1 (idTable1) VALUES (2);
INSERT INTO Table2 (idTable1, user_id, num) VALUES (1, 11, 111);
INSERT INTO Table2 (idTable1, user_id, num) VALUES (1, 22, 222);

Raw SQL query:

SELECT    T1.idTable1,
          IFNULL(T2.num, 0) num
FROM      Table1 T1
LEFT JOIN Table2 T2 
ON        T1.idTable1 = T2.idTable1 
AND       T2.user_id  = 11
WHERE     T1.idTable1 = 1

Resulting QuerySet:

| idTable1 | num |
|----------|-----|
|        1 | 111 |

Is it possible to code using Django ORM?

UPDATE

Models are: 

class Table1(models.Model):
    # ...

class Table2(models.Model):
    user = models.ForeignKey(User)
    table1 = models.ForeignKey(Table1)

    num = models.IntegerField(null=True)

Upvotes: 0

Views: 146

Answers (1)

f43d65
f43d65

Reputation: 304

Something like this:

from django.db.models import Q, Value as V
from django.db.models.functions import Coalesce

objects = Table1.objects.filter(
              Q(table2__isnull=True) | Q(table2__user=11),
              id=1
          ).annotate(
              num=Coalesce('table2__num', V(0))
          ).values('id', 'num')

It is LEFT JOIN, but I don't know if Django can construct AND part in queries like ... LEFT JOIN ... ON ... AND ... WHERE. In this case, end result is the same (I hope).

Upvotes: 1

Related Questions