Hi all,
In https://code.djangoproject.com/ticket/30653 a feature request was made to support annotating instances with an entire queryset. This was closed wontfix because you can do this with the prefetch_related mechanism. However, prefetch_related is very limited due to the design intention to support prefetching relationships defined in the ORM. My use case is to annotate each instance fetched in a queryset with a related queryset, where which objects are annotated depends on two fields of the original instance. Consider models A, B, C and D, where both A and D have foreign keys to both B and C, and you want to annotate a queryset of As with a collection of Ds which share the same pair of related B and C. (This is exactly the use case I have in mind, but of course it could be quite general.) Prefetch objects cannot be used because, firstly, a Prefetch on b__d_set (for example) will annotate the B models: there is no way to collect the D instances onto the A models; and secondly because there is no way to filter or join on the second field. prefetch_related is making some assumptions about the correct way to perform queries because of what it's there to do; a complete method for annotating querysets onto things would perhaps not want to query the related table via a "WHERE pk IN (...)" clause but instead query the root table's pk and JOIN on the subsequent tables - in the above scenario this would be very simple (... JOIN d ON (a.b_id = d.b_id AND a.c_id = d.c_id)). This avoids having to faff around getting ids for all the intermediate models in python and pass them back to the database (though presumably there's a reason for doing that in prefetch_related; maybe it would make sense here too.) The big issue for me is that there is not a good workaround for this at the moment. Suppose you frequently need access to things of this type (and need the performance gained by querying everything in one go) then you are forever querying tables directly and assembling dictionaries - instead of using the ORM to do this for you. If frequently need to do this for *the same* query, you can't really do it in a custom queryset without overriding _fetch_all, assembling the dictionary there and gluing collections onto instances there, which doesn't feel like a good thing to do. So I would suggest reopening the linked issue! Chris -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/2353e3b5-f4cf-48f2-b647-9f400c05c571n%40googlegroups.com.
