fetch all related data linked by a FK

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:
        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 ?

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.

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')


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.

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

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

Which model is that __str__ method in?

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 + ")"

So what’s the issue?

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.

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.

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.

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