Hello David,
Do you know if ALL provides any performance benefits over NOT EXISTS? Given
we already support
Employee.objects.filter(
~Exists(Employee.objects.filter(
manager=OuterRef("pk"),
join_date__gt=OuterRef("join_date")
)
)
If it's not the case I'm not sure adding support for ALL to core is worth
it, particularly because we need to emulate it on SQLite.
If we were to add it I think All(lookup, subquery) would make the most
sense as it still looks a bit like how you'd do things In Python and it's
relatively easy to translate to NOT EXISTS as you basically take the lookup
and the selected value and turn it into
~Exists(subquery.exclude(Lookup(OuterRef(field, value))).
Employee.objects.filter(
All(
"join_date__lte", Employee.objects.filter(
manager=OuterRef("pk")
).values("join_date")
)
)
Generally I'm +0 on the idea though unless we can prove there are use cases
for it that Exists can't solve.
Cheers,
Simon
Le mercredi 24 août 2022 à 09:53:46 UTC-4, shang.xia...@gmail.com a écrit :
> 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/154a5e47-9700-4e6b-a63e-0df9c6d8af21n%40googlegroups.com.
