[Answered ]-Join request in django between three tables and display all attributes

by

1👍

✅

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?

👤Ralf

Leave a comment