Hi folks! I have a piece of low hanging fruit I'd like to suggest and contribute: ALL subqueries.
Here's the PG docs on ALL subqueries <https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-ALL> but basically it causes the subquery expression to evaluate to true only if all rows themselves evaluate to true when compared against the supplied value & comparison operator. The syntax is: <value> <operator> ALL (<subquery>) I've played around with this idea in my "Stupid Django Tricks" repo <https://github.com/shangxiao/stupid-django-tricks/tree/master/all_subqueries> and have taken the liberty of also creating a small patch for Django <https://github.com/shangxiao/django/commit/ddfecdc0561b299ce834485b19d2fd57bca8d4b3> . There are a couple of points to note: 1. Postgres, MySQL & SQL Server all support ALL subquerying *but unfortunately SQLite does not*. A cursory search about Oracle suggests it may support it but couldn't see anything in the official docs. 2. An equivalent expression is to use NOT EXISTS and move the comparison logic into the subquery & invert it (but leaving any correlating logic uninverted). A couple of further points on the NOT EXISTS approach: 1. It uses double negatives and hinders the readability. 2. It's not always obvious what the correct way to invert the subquery is – I found I had to be careful with filters involving many relationship lookups. That being said I found that getting ALL working in Django only took a few lines. From my initial experimentation I saw 3 possible ways of doing this: 1. Lookups: A lookup could be defined for each operator supported. eg using Exact: class All(Exact): lookup_name = "all" def get_rhs_op(self, connection, rhs): return connection.operators[super().lookup_name] % f"ALL {rhs}" Parent.objects.annotate(test=Value("test")).filter(test__all=Subquery(subquery)) 2. Subclass Subquery This is my favoured approach as it resembles Python's all() builtin. It uses almost identical code to Exists except for one line that defines the exists expression. The idea is that subqueries supply a single boolean column created with annotate: class All(Subquery): template = "'t' = ALL (%(subquery)s)" output_field = fields.BooleanField() # similar methods to Exists omitted Parent.objects.filter( All( Child.objects.annotate(is_result=Q(<expression evaluating to bool>)).filter(something=OuterRef('something').values('is_result') ) ) 2. Translate the expression to NOT EXISTS. I'm not entirely sure what the best approach is for this, or even whether it's possible, but it would need to negate the subquery filtering while leaving the correlating filter (ie the OuterRef) alone. My naive attempt here works for the simplest of cases but doesn't take into account anything more complex. <https://github.com/shangxiao/stupid-django-tricks/blob/master/all_subqueries/models.py#L119> I found this work well for my codebase and I'd love to explore contributing it back to Django. I'd suggest the subquery subclass as it results in more readable code – but there is the question about lack of support for SQLite. One approach would be to note in the documentation on how to write the equivalent using NOT EXISTS. What do folks think? Cheers, David -- 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/13780399-0b14-48de-86d1-24a530199c5dn%40googlegroups.com.
