Question 20083 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I optimize this Django query to get faster result? – Mysql

items = Items.objects.filter(active=True)
price_list = []
for item in items:
   price = Price.objects.filter(item_id = item.id).last()
   price_list.append(price)

Price model can have multiple entry for single item, I have to pick last element. How can we optimize above query to avoid use of query in loop.

Tags:

2

Answers


  1. 0

    Try this:

    item_ids = list(Items.objects.filter(active=True).values_list('id',flat=True))

price_list=Price.objects.filter(item_id__in=item_ids).values("item_id").order_by("-item_id").distinct("item_id")
    Login or Signup to reply.
  2. 0

    If you’re using a Foreign Key to connect the Price and Item models, you can probably use this:

    prices = Price.objects.filter(item__active=True)

    Another approach would be to get the item ids first and then filter based on those (as Hemal has mentioned previously, however I believe that the query in Hemal’s answer has a problem as it only provides the item ids whether the question needs the whole Price objects):

    item_ids = Items.objects.filter(active=True).values_list('id', flat=True)
prices = Prices.objects.filter(item_id__in=item_ids)

    Also, in the question, you are picking the last price, without ordering it on anything. I believe that you should order it on the specific field you want to ignore any non-determinism.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search