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

Answer 1

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)