Question:

Address the N+1 problem in Django with prefetch

Problem:

With the following code I am getting N numbers of queries based on the loop. How to avoid that ?

I tried using prefetch_related but that didn't worked or am i doing the wrong way ?

Models

class Product(models.Model):

name = models.CharField()

....

class ProdcutOffer(models.Model):

product = models.ForeignKey(Product, related_name="offers")

....

def my_view(request):

qs = Product.objects.filter(is_active=True).prefetch_related("offers")

data = []

for product in qs:

data.append(

{

....,

"offer":product.offers.filter(is_active=True, is_archived=False).last()

}

)

paginator = Paginator(data, 10)

try:

page_obj = paginator.page(page_num)

except PageNotAnInteger:

page_obj = paginator.page(1)

except EmptyPage:

page_obj = paginator.page(paginator.num_pages)

return page_obj

Solution 1:

We can first introduce a >FilteredRelation [Django-doc] that contains the primary key of the latest ProductOffer, and then select related these:

from django.db.models import FilteredRelation, OuterRef, Q, Subquery

qs = Product.objects.annotate(

latest_offer=FilteredRelation(

'offer',

condition=Q(

post=Subquery(

ProductOffer.objects.filter(

is_active=True, is_archived=False, product_id=OuterRef('pk')

)

.order_by('-pk')

.values('pk')[:1]

)

).select_related('latest_offer')

the Products that arise from this will have an extra attribute .latest_offer that contains the latest offer. You thus can use this to render it for example as:

{% for product in page_obj %}

{{ product }}: {{ product.latest_offer }}

{% endfor %}

Answered by: >Willem Van Onsem

Solution 2:

How about using django's Prefetch method inside the prefetch_rleated. You can pass a custom queryset inside the Prefetch method.

You could do something like this:

qs = Product.objects.filter(is_active=True).prefetch_related(

models.Prefetch(

"offers",

queryset=ProductOffer.objects.filter(is_active=True, is_archived=False),

to_attr="product_offers"

)

data = []

for product in qs:

try:

data.append({"offers": product.product_offers[0]})

except IndexError:

pass

the to_attr attribute will simply create a new attribute for the queryset which is a list.

Answered by: >Abdulla Dlshad

Credit: >Stackoverflow

Ritu Singh

Your Answer

Submit

Ritu Singh

0 Answers

Top Questions

Ritu Singh

0 Answers

Top Questions

Related Questions