Join request in django between three tables and display all attributes-CodePudding

I have three models

class A(models.Model):
    field1 = models.IntegerField()

class B(models.Model):
   id_a = models.ForeignKey(A,on_delete=models.CASCADE)
   field1 = models.IntegerField()
   field2 = models.IntegerField()

class C(models.Model):
   id_a = models.ForeignKey(A,on_delete=models.CASCADE)
   field1 = models.IntegerField()
   field2 = models.IntegerField()

I want to write a request that looks like this: SELECT * FROM B,C,A WHERE B.id_a=C.id_a WHERE A.id_a=2 and display all the attributes of the two tables
Here is what I tried to do:

a_id_att = 1
data = B.objects.filter(id_a=C.objects.filter(id_a=a_id_att)[0])

It does not work. How to write the join and make to display all the attributes of the tables?

CodePudding user response：

The SQL statement that you wrote seems strange.

SELECT * FROM B, C, A
    WHERE B.id_a = C.id_a
        AND A.id_a = 2

It seems that you want a single row from A and then all related rows from B and C, which your SQL query does NOT achieve. Did you mean something like this:

SELECT * FROM B, C, A
    WHERE A.id = 2
        AND B.id_a = A.id
        AND C.id_a = A.id

You can achieve something like that in Django using prefetch_related(), which builds a query so that the related rows are also loaded into memory in the first query and not in subsequent queries.

# this will return a queryset with a single element, or empty
qs = A.objects.prefetch_related('b_set', 'c_set').filter(id=2)

for elem in qs:               # here the single DB query is made
    print(elem.field1)        # A.field1
    for det in elem.b_set.all():
        print(det.field1)     # B.field1, does NOT make another DB query
        print(det.field2)     # B.field2, does NOT make another DB query
    for det in elem.c_set.all():
        print(det.field1)     # C.field1, does NOT make another DB query
        print(det.field2)     # C.field2, does NOT make another DB query

Note: I use b_set here because that is the default for the ForeignKey field; this changes if the field would specify a different related_name.

Does this address and solve your issue?