Hi Anssi, Thanks for your feedback! Responses inline.
1. It should not be assumed that the primary key is the field needed > in the inner query. We do have foreign keys to other fields, and one > can use this approach without foreign keys at all. Hmm, yes. Thinking about it, this seems like the biggest obstacle to implementing the query-wrapping approach. The reason I proposed just selecting the PK was to mirror the behaviour of QuerySet when passed to __in, as happens here: https://github.com/django/django/blob/master/django/db/models/query.py#L947 But since we're talking about raw SQL, we'd want maximum flexibility - so would we need to introduce a ValuesRawQuerySet in order to select arbitrary columns? It seems that would be the approach that most closely mirrors QuerySet. 2. If the query is of the form NOT IN, then we need to also filter > out null values, otherwise no results are returned if the raw query > contains a single null (NOT IN is weird...) > Yep. > 3. Does the wrapping of the subquery into (select field from > (raw_query) where field is not null) cause performance problems? It > could be possible that this prevents the DB's optimizer from working > correctly. > This would need more testing, but Postgres at least optimises the inner query away nicely. These two queries have the same execution plan: SELECT ... WHERE foo NOT IN (SELECT foo FROM (SELECT * FROM table WHERE ...) WHERE foo IS NOT NULL) SELECT ... WHERE foo NOT IN (SELECT foo FROM table WHERE ... AND foo IS NOT NULL) Same goes for the equivalent IN queries. 4. If the query doesn't select the required column (that is, it is > deferred), we don't have any way to know that. This seems like a > documentation issue, but worth mentioning. > Yes, definitely. The docs page where .raw() is introduced will have to explain this feature with a big warning. > It would be useful to have this feature, so a +1 to this idea. The > above issues do not seem too severe, so I believe it is possible to > make this work. > Me too! Thanks, Alex -- You received this message because you are subscribed to the Google Groups "Django developers" group. To view this discussion on the web visit https://groups.google.com/d/msg/django-developers/-/S11TF_KH5XkJ. To post to this group, send email to django-developers@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
