Trying to convert Left Join of SQL into Django query-set?

Question

here is my models.py file

class Customer(models.Model):
    """All Customers details goes here"""

    name = models.CharField(max_length=255, null=False)
    firm_name = models.CharField(max_length=255, null=False)
    email = models.EmailField(null=False)
    phone_number = models.CharField(max_length=255, null=False)
    location = models.CharField(max_length=255,null=True)
    date_created = models.DateTimeField(auto_now_add=True)

    class Meta:
        """Meta definition for Customer."""

        verbose_name = 'Customer'
        verbose_name_plural = 'Customers'

    def __str__(self):
        """Unicode representation of Customer."""
        return self.name


class Order(models.Model):
    """All order details goes here.It has OneToMany relationship with  Customer"""
    STATUS = (
        ('CR', 'CR'),
        ('DR', 'DR'),
    )

    customer = models.ForeignKey(Customer, null=True, on_delete=models.SET_NULL)
    bill_name = models.CharField(max_length=255, null=False)
    payment_date=models.DateField(auto_now=False)
    status = models.CharField(max_length=255, choices=STATUS, null=False)
    amount = models.FloatField(max_length=255, null=False)
    date_created = models.DateTimeField(auto_now_add=True)
    description = models.TextField(null=True)

    class Meta:
        """Meta definition for Order."""

        verbose_name = 'Order'
        verbose_name_plural = 'Orders'

    def __str__(self):
        """Unicode representation of Order."""
        return self.bill_name

i want to access only Customer's name and all fields of Order,in short i want to convert the following SQL in Django Query-set

select name ,bill_name ,status from accounts_customer left join accounts_order on accounts_customer.id = accounts_order.customer_id where accounts_order.status="DR";

score 1 · Answer 1 · answered Jul 26 '20 at 18:56

1

You can perform join operation using two ways: 1st: via using select_related I.e. Order.objects.select_related('customer') And 2nd: via using filter: I.e. Order.objects.filter(status__iexact="DR")

answered Jul 26 '20 at 18:56

Justin Patel

58
6

Thanks buddy,you've been a great help!! – Deep Patel Jul 26 '20 at 19:22

score 1 · Accepted Answer · answered Jul 26 '20 at 19:07

To attach the customer's name on the order object, you can use annotate with an F expression. https://docs.djangoproject.com/en/3.0/ref/models/expressions/#using-f-with-annotations

orders = Order.objects.annotate(
    customer_name=F('customer__name')
).filter(status='DR')

for order in orders:
    print(order.customer_name)

If you suspect you will want to access more customer attributes, you may want to select_related (slightly more memory, larger query). What's the difference between select_related and prefetch_related in Django ORM?

orders = Order.objects.select_related('customer').filter(status='DR')

for order in orders:
    print(order.customer.name)

Thank you buddy,It was a great help with an awesome explanation — Deep Patel, Jul 26 '20 at 19:21

Trying to convert Left Join of SQL into Django query-set?

2 Answers2