How to perform Outer Joins using Django ORM?

Question

I have the following models:

class Partner(models.Model):
    partner_name = models.CharField(max_length=50)

class Allocation(models.Model):
    partner = models.ForeignKey(
        Partner,
        related_name='partner_allocations',
        on_delete=models.CASCADE
    )
    is_allocated = models.BooleanField(default=False)

By using Django ORM, I need the joined tables with columns as ['partner_name', 'partner_id', 'is_allocated'].

All partner names(unrelated too), and related data from Allocation model.

SQL = SELECT partner.id, allocation.partner_id, allocation.is_allocated 
      FROM Partner LEFT OUTER JOIN Allocation ON Partner.id=Allocation.partner_id

or

SQL = SELECT partner.id, allocation.partner_id, allocation.is_allocated 
      FROM Allocation RIGHT OUTER JOIN Partner ON Allocation.partner_id=Partner.id

The output for the above SQL using Django ORM.

The syntax for SQL queries might be wrong here, but they give the overall idea.

I want to use raw SQL only as the last option.

Please, can someone help.

Unfortunately you cannot perform this join (easily) by using the ORM (unless you go deep enough). You should look into using [`prefetch_related` - Django docs](https://docs.djangoproject.com/en/3.1/ref/models/querysets/#prefetch-related). Although this would perform some extra queries. The main reason (I believe) the ORM doesn't support such joins directly is to prevent excessive usage of memory by storing the same object multiple times. — Abdul Aziz Barkat, Mar 26 '21 at 17:58

score 0 · Answer 1 · answered Mar 26 '21 at 18:04

In django it is very easy to access the data through the ORM, for example:

allocation = Allocation.objects.get(id=1)
# access to partner object
print(allocation.partner)
print(allocation.partner.partner_name)

allocations = Allocation.objects.all()
for allocation in allocations:
    print(allocation.partner.partner_name)

# or
allocations = Allocation.objects.all().values('partner__id', 'partner__name', 'is_allocated')
for allocation in allocations:
    print(allocation['partner__name'])

How to perform Outer Joins using Django ORM?

1 Answers1