fetch all related data linked by a FK

Using Django Using the ORM
1 
class Customer(models.Model):
    cust_id = models.CharField(max_length=255)
    name = models.CharField(max_length=255, blank=True, null=True)
    reg_no = models.CharField(max_length=255, blank=True, null=True)
    address = models.CharField(max_length=1000, blank=True, null=True)

class ServiceOrder(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, blank=True, null=True)
    name = models.CharField(max_length=255)
    so_number = models.CharField(max_length=255, blank=True, null=True)

SOFs = ServiceOrder.objects.all().order_by('-id')

for sof in SOFs:
    try:
        customer = Customer.objects.get(id=sof.customer_id)
        sof.cust_id = customer.cust_id
        sof.reg_no = customer.reg_no
        sof.cust_name = customer.name
        sof.customer_url = reverse('customers_view', args=[customer.id])
    except ObjectDoesNotExist:
        sof.cust_id = "N/A"
        sof.reg_no = "N/A"
        sof.cust_name = "N/A"
        sof.customer_url = "#"

How do I get the customer details of a service order in one go and not loop through each of the 2500 SOFs ?

2

This query is unnecessary.

Inside this loop, sof is an instance of ServiceOrder. Each ServiceOrder has a ForeignKey to Customer, so sof.custom is a reference to the Customer related to that ServiceOrder. The query is redundant.

3

For some reason I was getting an error for sof.cust_id = customer.cust_id

Does this make any faster ?

SOFs = ServiceOrder.objects.all().order_by('-id').prefetch_related('customer')
4

Correct:

Wrong function. Since it’s ServiceOrder that has the ForeignKey, you want to use select_related.

Will this improve your original code? No.

Will it improve the performance using the predefined FK reference? Yes.

5

The issue here is that I have this for Customer model class

    def __str__(self):
        return self.name + " (" + self.cust_id + ")"
6

Which model is that __str__ method in?

7

Customer :

class Customer(models.Model):
    cust_id = models.CharField(max_length=255) # Alpha-Numeric Generated ID for the purpose of Sales People
    partner_id = models.CharField(max_length=255, blank=True, null=True)
    name = models.CharField(max_length=255, blank=True, null=True)
    pan = models.CharField(max_length=255, blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.name + " (" + self.cust_id + ")"
8

So what’s the issue?

9

Before I did prefetch_related('customer') it was this :

SOFs = ServiceOrder.objects.filter(sales_id=userid).order_by('-id')

And sof.customer.cust_id was not accessible.

10

You’re going to have to show the full code that was throwing an error, along with the complete traceback. We cannot diagnose general descriptions of problems.

11

I lost track of where it was happening - I since then edited the code to prefetch_related and was wondering if there’s a difference between select_related and prefetch_related - both seem to work.

12

Yes, there is a difference - see the docs for them.

However, neither of them are required. They are a purely optional performance enhancement. Their presence (or absence) does not affect the functionality of your code. Adding them does not make something “work”. Removing them doesn’t make anything “not work”.

1 Like