How to perform left join in django?

Question

i like to join two tables using left join operation.

this is what i like to do:

SELECT * FROM orders LEFT JOIN products ON (orders.product_id = products.id);

my model classes:

class Orders(models.Model):
    name = models.CharField(max_length=100, null=False, blank=False)
    qty = models.IntegerField(null=False, blank=False)
    product = models.ForeignKey(Products, on_delete=models.CASCADE)
    customer = models.ForeignKey(Customers, on_delete=models.CASCADE)

    class Meta:
        db_table = 'orders'

class Products(models.Model):
    name = models.CharField(max_length=100, null=False)
    qty = models.IntegerField(default=0, null=True)
    price = models.FloatField(null=True, default=0)
    description = models.CharField(max_length=500, null=True, default=None)

    class Meta:
        db_table = "products"

In Django you should *not* think in terms of JOINs. Can you explain *what* you aim to do? — Willem Van Onsem, Feb 14 '22 at 13:32

score 0 · Answer 1 · answered Feb 14 '22 at 13:52

I don't see any point of JOINs in Django. If you really want model data equivalent to Left JOIN, you can use a list for that.

def leftJoin():
    lst = []
    lst += Orders.objects.all()
    for order in Order.objects.all():
        for product in Products.objects.all():
            if order.product_id == product.id:
                lst += product
    return lst

score 0 · Answer 2 · answered Feb 14 '22 at 16:17

The beauty about an ORM is that you no longer think in "Rows and Columns" rather in "Objects and Properties"

Django will do the Joins for you if you structure your query correctly.

e.g.

If you want to retrieve rows for a related table in a single query, you can do the following:

orders = Orders.objects.all().select_related('product')

# This will do a join on the two tables and you can access the products from the orders without causing another DB request

for order in orders:
  print(order.id, order.product.id)

If you want to filter orders by a certain product you can do the below:

orders = Orders.objects.filter(product__name='Cool Product Name')

You can see this answer also: https://stackoverflow.com/a/21272153/1184181

How to perform left join in django?

2 Answers2