select_related() and RawQuerySet

Hi all

I have a particular query that requires me to use a RawQuerySet - I
need to left join the table to itself in order to select the highest
'priority' row from the table for each distinct value of a foreign key
on the model, and I need to join to the related table in order to
filter the results.

Having generated the queryset, I then want to make a dictionary of {
foreign_key_object : model_object }. So I have this working, but I
cannot use select_related() with a RawQuerySet, and so this runs N+1
queries, where N is the number of distinct foreign keys.

Here is some code, which might explain it better:

connection.queries=[]
base_products_qs = Product.objects.raw(
"""
SELECT idp_product.*, idp_productclass.*
FROM idp_product
JOIN idp_productclass
ON idp_product.product_class_id = idp_productclass.id
LEFT JOIN idp_product p2
ON idp_product.product_class_id = p2.product_class_id
AND p2.class_priority < idp_product.class_priority
WHERE p2.id IS NULL and idp_productclass.product_type != 4
""")
base_products = dict([ (p.product_class, p) for p in base_products_qs ])
len(connection.queries) # 7 queries (6 product classes)

Is there any simple way around this? I can reduce it to two queries
already, but it seems wrong to select out the info I want, throw it
away, and then fetch it again.

two query version:

base_products_qs = Product.objects.raw(
"""
SELECT idp_product.*
FROM idp_product
JOIN idp_productclass
ON idp_product.product_class_id = idp_productclass.id
LEFT JOIN idp_product p2
ON idp_product.product_class_id = p2.product_class_id
AND p2.class_priority < idp_product.class_priority
WHERE p2.id IS NULL and idp_productclass.product_type != 4
""")
base_products_ = dict([ (p.product_class_id, p) for p in base_products_qs ])
prod_classes_qs = ProductClass.objects.filter(pk__in=base_products_.keys())
prod_classes = dict([ (pc.id, pc) for pc in prod_classes_qs ])
base_products = dict([ (prod_classes.get(k), v)
for k, v in base_products_.items() ])

Cheers for any pointers

Tom

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.