Nested select_related to get data from various models django ORM

Question

i have 5 models below:

class Products:
    product_name=models.CharField(max_length=300,blank=True)
    product_price=models.IntegerField(blank=True)

class ProductImages(models.Model):
    image=models.ImageField(upload_to='product_images',null=True,blank=True)
    image_product=models.ForeignKey(Product,on_delete=models.CASCADE)

class ProductModel(models.Model):
    model_product=models.ForeignKey(Product,null=True,on_delete=models.CASCADE)
    model_name=models.CharField(max_length=130,blank=True)

class Colour(models.Model):
    colour_name=models.CharField(max_length=20,blank=True)
    colour_product=models.ForeignKey(Product,on_delete=models.CASCADE)

class Sale:
     name=models.CharField(max_length=30,null=True,blank=True)

class ProductSale:
    saleid=models.ForeignKey(Sale,on_delete=models.CASCADE)
    product=models.ForeignKey(Product,on_delete=models.CASCADE)

I am filtering and getting products present in ProductSale on the basis of sale id

prod=ProductSale.objects.filter(sale=sale.id).prefetch_related('product')

Now i have product ids, but i want additional details also that is product images,colour,Model which is present in ProductImages model,Colour model,ProductModel model.How can i achieve this in most optimize way below is my try but its taking time and not optimized.

prod=ProductSale.objects.filter(sale=sale.id).prefetch_related('product')

for prod in prod:
    pm=ProductModel.objects.filter(model_product=prod.product_id).values()
    pc=Colour.objects.filter(colour_product=prod.product_id).values()
    images=prod.product.productimages_set.all().order_by('id')[:2].values()

    sale_product.append({
        "product_id": prod.product.id,
        "product_name": prod.product.product_name,
        "stock": prod.product.product_quantity,
        "product_price": prod.product.product_price,
        "sale_price": prod.product.sale_price,
        "saleprice_startdate": prod.product.saleprice_startdate,
        "saleprice_enddate": prod.product.saleprice_enddate,
        "product_category": prod.product.category_name,
        "product_reviews": review_product,
        "review_count": len(pr),
        "product_images": images,
        "product_colour": pc,
        "product_model": pm,
    })

How can i optimize the query get the data in a format which i am using in a dictionary, Kindly help badly stuck in this.

Why are you using `prod in prod`? What is `pr`? What is `review_product`? — Brian Destura, Jul 12 '21 at 07:35
Also `pc` and `pm` will have lists as results. Is that fine what you expect? — Brian Destura, Jul 12 '21 at 07:36
I am using prod in prod, to loop through product and find the colour (pc), product model(pm) against each id of product, Pr is product review which is also i am getting which i didn't mentioned here to avoid to much code, so people can understand easily — Muhammad Nabeel, Jul 12 '21 at 08:00

score 0 · Answer 1 · answered Jul 12 '21 at 08:07

Have a try with:

prod = ProductSale.objects.select_related(
    'product',
).prefetch_related(
    'product__colour',
    'product__productreview',
    Prefetch('product__productimages', queryset=ProductImages.objects.order_by('id'))),
).filter(sale=sale.id)

for p in prod:
    pm = p.product
    pc = p.colour.all()
    images = p.productimages.all()[:2]
    review_product = p.productreview.all()

    sale_product.append({
        "product_id": p.product.id,
        "product_name": p.product.product_name,
        "stock": p.product.product_quantity,
        "product_price": p.product.product_price,
        "sale_price": p.product.sale_price,
        "saleprice_startdate": p.product.saleprice_startdate,
        "saleprice_enddate": p.product.saleprice_enddate,
        "product_category": p.product.category_name,
        "product_reviews": review_product,
        "review_count": len(review_product),
        "product_images": images,
        "product_colour": pc,
        "product_model": pm,
    })

ok thanks let me try this, i will get back to you – Muhammad Nabeel Jul 12 '21 at 08:12 — Muhammad Nabeel, Jul 12 '21 at 08:12

Nested select_related to get data from various models django ORM

1 Answers1