Cannot access foreign key ID in nested join

Using Django Using the ORM
1

Let’s have models like this:

class A(models.Model):
    id = models.UUIDField()

class B(models.Model):
    id = models.UUIDField()
    a = models.ForeignKey(A)

class C(models.Model):
    id = models.UUIDField()
    b = models.ForeignKey(B)

I can do this:

C.objects.filter(a__b__id=<something>)

But I cannot do this:

C.objects.filter(a__b_id=<something>)

I get an error like this:

FieldError: Unsupported lookup 'b_id' for ForeignKey or join on the field not permitted.

The reason this matters is the working version produces an extra join on A:

SELECT "a"."id" FROM "c" INNER JOIN "b" ON ("c"."b_id" = "b"."id") LEFT OUTER JOIN "a" ON ("b"."id" = "a"."b_id")

It could just as well get:

SELECT "b"."a_id" FROM "c" INNER JOIN "b" ON ("c".b_id" = "b"."id")

Is it possible to make Django behave this way? If not, why not?

2

I believe this is a scenario that you can use FilteredRelation, i don’t use it very often to give a specific advice on it, but you can try it out.

3

It appears to have the same issue.

4

Not sure if there’s a misunderstanding here as there is no way to query A directly from C; you need to hop through B.

From this model definition against the latest version of Django

from django.db import models

class A(models.Model):
    id = models.UUIDField(primary_key=True)

class B(models.Model):
    id = models.UUIDField(primary_key=True)
    a = models.ForeignKey(A, models.CASCADE)

class C(models.Model):
    id = models.UUIDField(primary_key=True)
    b = models.ForeignKey(B, models.CASCADE)

I can do

In [1]: from app.models import C

In [2]: C.objects.filter(b__a__id=1).query.__str__()
Out[2]: 'SELECT "app_c"."id", "app_c"."b_id" FROM "app_c" INNER JOIN "app_b" ON ("app_c"."b_id" = "app_b"."id") WHERE "app_b"."a_id" = 00000000000000000000000000000001'

In [3]: C.objects.filter(b__a=1).query.__str__()
Out[3]: 'SELECT "app_c"."id", "app_c"."b_id" FROM "app_c" INNER JOIN "app_b" ON ("app_c"."b_id" = "app_b"."id") WHERE "app_b"."a_id" = 00000000000000000000000000000001'

In [4]: C.objects.filter(b__a_id=1).query.__str__()
Out[4]: 'SELECT "app_c"."id", "app_c"."b_id" FROM "app_c" INNER JOIN "app_b" ON ("app_c"."b_id" = "app_b"."id") WHERE "app_b"."a_id" = 00000000000000000000000000000001

which all make use of INNER JOIN which I tested all the way from Django 3.2.

5

I constructed the example from real code that I didn’t want to post to avoid complexity. Dangerously, I did not test it out.

I missed something in my models. The issue is coming from using the opposite end of a one-to-one field, which of course shouldn’t work. Big oversight on my part in posting here. I thought I had checked all that.

1 Like