Left outer join with extra conditions in django

Question

I have problems to do this SQL with django queryset+Q objects.

How can I do that without raw query?

SELECT *
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.id == T2.fk_id AND (T2.VALUE = 'val1' OR T2.VALUE IS NULL)

Answer 1

I think you might have been looking for the Prefetch object, but that likely didn't exist 5 years ago. Any way this is one way to implement it now.

You need the filter in both locations to make sure you're getting the appropriate T1 and T2 instances. Then the Prefetch will actually do an additional query to pull all the related T2 instances. The distinct is so that if there are multiple T2 instances, there aren't duplicate T1 instances returned.

from django.db.models import Prefetch
t2_filter = Q(t2_set__value__isnull=True) | Q(t2_set__value='val1')
T1.objects.filter(
    t2_filter,
    t2_set__isnull=False,
).prefetch_related(
    Prefetch(
        't2_set',
        T2.objects.filter(t2_filter),
        to_attr='filtered_t2_set',
    )
).distinct()