Django How can I pass a subquery in LEFT JOIN

user_7253993 0
Viewed 264

I have 3 models (A,B,C):

class A(models.Model):

url = models.URLField()
uuid = models.UUIDField()
name = models.CharField(max_length=400)
id = models.IntegerField()

class B(models.Model):

user = models.ForeignKey(C, to_field='user_id',
                         on_delete=models.PROTECT,)
uuid = models.ForeignKey(A, to_field='uuid',
                         on_delete=models.PROTECT,)

and I want to perform the following SQL query using the Django ORM:

SELECT A.id, COUNT(A.id), COUNT(foo.user)

FROM A

LEFT JOIN (SELECT uuid, user FROM B where user = '<a_specific_user_id>') as foo
  ON A.uuid = foo.uuid_id 
  
WHERE name = '{}'

GROUP by 1

HAVING COUNT(A.id)> 1 AND COUNT(A.id)>COUNT(foo.user)

My problem is mainly with LEFT JOIN. I know I can form a LEFT JOIN by checking for the existence of null fields on table B:

A.objects.filter(name='{}', b__isnull=True).values('id', 'name')

but how can I LEFT JOIN on the specific sub-query I want?

I tried using Subquery() but it seems to populate the final WHERE statement and not pass my custom sub-query in the LEFT JOIN.

django-models django
1 Answers

For anyone stumbling upon this in the future. I directly contacted the Django irc channel and it's confirmed that, as of now, it's not possible to include a custom subquery in a LEFT JOIN clause, using the Django ORM.

user_72539930
Related