Queryset equivalent of SQL

Question

I'm trying to improve the performance of this code:

orderitems = OrderItem.objects.filter(order__contact=contact)
for orderitem in orderitems:

    try:
        pv = ProductVariation.objects.get(product=orderitem.product)

        if pv.parent_id == parent_product.id:
            return True
    except:
        pass

Essentially I want to get rid of the 'for' loop because its slow. I would like to do it using a single queryset if possible, but I just can't get my head around the syntax. Here is the SQL that I effectively want to reproduce. It creates a list which is fairly short so I can iterate through that looking for a match:

SELECT parent_id
FROM configurable_productvariation
WHERE product_id IN (
    SELECT product_id
        FROM shop_orderitem
        WHERE order_id
        IN (
            SELECT id
            FROM shop_order
            WHERE contact_id = 4));

The '4' is the 'contact' referred to in the first line of python.

Many thanks, Thomas

BTW, I don't think your python example corresponds with sql query. Are you sure about `return True`? — San4ez, Apr 29 '12 at 18:38

score 0 · Accepted Answer · answered Apr 29 '12 at 18:17

0

This should generate sql like yours

product_ids = OrderItem.objects.filter(order__contact=contact).values_list('product_id', flat=True)
ProductVariation.objects.filter(product_id__in=product_ids)

answered Apr 29 '12 at 18:17

San4ez

8,091
4
41
62

Thanks San4ez! That solved my problem :) I just had to make a couple of small changes. It became this: **product_ids = OrderItem.objects.filter(order__contact=contact).values_list('product', flat=True)** and **ProductVariation.objects.filter(product__in=product_ids)** I don't know why product_id has to become product, but it works. – handros Apr 29 '12 at 19:40

Queryset equivalent of SQL

1 Answers1