#32449: Issues with RawSQL annotations dependencies and count()
-------------------------------------+-------------------------------------
Reporter: João | Owner: nobody
Carneiro Haas |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: annotate, count,
Severity: Normal | RawSQL, select_related, JOIN
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
We have in our application a RawSQL annotation which depends on a internal
join to work:
{{{
#!python
# accounts/models.py
class Org(models.Model):
name = models.TextField()
class Account(models.Model):
org = models.ForeignKey(Org)
query =
Account.objects.select_related("org").annotate(org_name=RawSQL("SELECT
custom_name(accounts_org.name)"))
}}}
Well, sometimes Django tries to optimize the queries and removes unneded
JOINs, such as when calling count:
{{{
#!python
query.count()
# SELECT COUNT(*) FROM (
# SELECT "accounts_account"."id" AS Col1, (SELECT
"accounts_org"."name") AS "org_name"
# FROM "accounts_org"
# GROUP BY "devices_device"."id", (SELECT "accounts_org"."name")
# )
}}}
Which results in an 'missing FROM-clause' error.
Since (I believe) it's impossible for Django to analyze the passed RawSQL
query, I would like to request some way to specify which
joins/select_related the annotated RawSQL depends on.
My suggestion would be to pass a list of table dependencies to the RawSQL:
{{{
#!python
raw = RawSQL("SELECT custom_name(accounts_org.name)", tables=["org"])
}}}
If the ticket gets 'confirmed' and an API gets specified I can try
tackling this issue.
For now I'm using the following workaround, which forces a join:
{{{
#!python
query = Accounts.objects.annotate(org_id=F('org__id'), custom_name=...)
}}}
Obs.: Fixing this before https://code.djangoproject.com/ticket/28477 would
result in queries with unused annotations (such as the ones above) to
always perform the join. I believe it won't impact the development of this
ticket, but who knows ¯\_(ツ)_/¯
--
Ticket URL: <https://code.djangoproject.com/ticket/32449>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.a70f7243e9b01d8746fde6a3c5feb071%40djangoproject.com.
