Changing the order of SQL generation

[Matthew Schinckel]

Greetings,

For a couple of things I’ve been working on, I wound up hitting exactly the same problem with respect to SQL generation.

It seems that the `FROM` clauses are generated after the SELECT, but before WHERE.

This means that any .as_sql() code that is run only on things that appear in a WHERE clause but not in a SELECT clause can reference any of the aliases that it needs to generate it’s SQL, but is not able to “ref” the alias (ie, indicate that it has actually used it).

This means that there are certain situations where a table is referenced but the SQL compiler does not know about that reference, and the table is stripped out.

I have a fairly simple PR, that is passing tests, to move the FROM generation until after any WHERE clauses have been executed.

https://github.com/django/django/pull/14683

Does anyone have any suggestions as to why the FROM needs to be generated before the WHERE?

For background the things I’ve hit this same issue in are:

Django Implied Relationship - creating implicit relationships between objects that don’t otherwise have a direct relationship

https://github.com/schinckel/django-shared-property - which is woefully under-documented, but basically allows defining an expression as something that should (a) always be annotated onto a query, but (b) also be “live”, and changes to the dependencies affect the value the property returns. https://github.com/schinckel/django-shared-property/blob/main/tests/models.py has some nice examples.

Cheers,

Matt.

[Luke Plant]

I haven't had chance to dig into this, but django-shared-property looks very interesting. IMO it would be great to be able to support it well, or even include that kind of functionality in Django itself.

The closest SQLAlchemy equivalent to this seems to be hybrid attributes - a feature I'm very jealous of. In almost every Django project I've come across the need for computed properties that should be available both at DB level (filtering/searching) and at instance level, and it is a constant source of duplication of logic.

Relating to your actual post, and without thinking it through, I'm guessing the conceptual order of SQL evaluation is relevant here - e.g.

https://stackoverflow.com/questions/21693208/sql-conceptual-order-evaluation

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#logical-processing-order-of-the-select-statement

Luke

--
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-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/9F101363-110C-498A-8C6C-554138F8D673%40schinckel.net.

[schinckel]

Hey Luke,

Yeah, I had seen hybrid attributes. I think I came across them after starting this, but I think they are pretty much the same idea. Not sure if their hybrid methods are new, but that looks fun too ;)

I'll be more than happy to look at a PR to include this in Django, once I've got a completely solid proof of concept.

With respect to the logical ordering: Django already does things in a different order to that, as it requires the SELECT to be prepared first, and has a comment as to why. Thinking a little further about that, you might be able to make an argument that the preparation of the SQL statement should be taking those steps in the reverse order, as things from each phase there are available to the following phases.

Cheers,

Matt.