[Django] #32449: Issues with RawSQL annotations dependencies and count()
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: annotate, count, | Triage Stage:
RawSQL, select_related, JOIN | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* type: Uncategorized => New feature
* resolution: => wontfix
Comment:
Thanks for this ticket, however `RawSQL()` is the last resort and I don't
think we would like to add any extra API to it. You can do the same by
creating a custom function (preferable):
{{{
class CustomName(Func):
function = 'custom_name'
output_field = CharField()
Account.objects.select_related('org').annotate(org_name=CustomName('org__name')).count()
}}}
or by using `QuerySet.extra()` (which also supports `tables`):
{{{
Account.objects.select_related('org').extra(select={'org_name': 'SELECT
custom_name(accounts_org.name)'}).count()
}}}
or by specifying the full subquery in `RawSQL()`:
{{{
Account.objects.select_related("org").annotate(org_name=RawSQL(
'SELECT custom_name(accounts_org.name) '
'FROM accounts_org '
'WHERE accounts_account.org_id = ccounts_org.id
)).count()
}}}
I treated this as a request for a new API, because resolving #28477 will
strip unused annotation, so crash is a duplicate of #28477.
--
Ticket URL: <https://code.djangoproject.com/ticket/32449#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: João Carneiro Haas | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: annotate, count, | Triage Stage:
RawSQL, select_related, JOIN | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/32449#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: João Carneiro Haas | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: annotate, count, | Triage Stage:
RawSQL, select_related, JOIN | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by João Carneiro Haas):
Replying to [comment:1 Mariusz Felisiak]:
>You can do the same by creating a custom function
Ok, that actually solves the issue I presented, but I'm at fault here, the
`custom_name` example was just the easiest way I found to explain the
issue, we're actually doing a Postgres recursive query, which as far as I
know there's no way to represent in a Django structure.
Here's a more truthful example on how we're using the `RawSQL`. It's not
the exact query and models, but I believe if there's a way to solve for
this our case would also be solved:
{{{
#!python
# nodes/models.py
class Node(models.Model):
pass
class Edge(models.Model):
parent = models.ForeignKey(Node)
child = models.ForeignKey(Node)
class A(models.Model):
node = models.ForeignKey(Node)
class B(models.Model):
a = models.ForeignKey(A)
# b_qs is a really complex 'B' queryset
b_qs =
b_qs.objects.select_related('A').annotate(graph_child_count=RawSQL("""
WITH RECURSIVE graph_children(id) AS (
SELECT "e"."child_id"
FROM "nodes_edge" AS "e"
WHERE "e"."parent_id" = "nodes_a"."node_id"
UNION
SELECT "e"."child_id"
FROM "nodes_edge" AS "e", "graph_children"
WHERE "e"."parent_id" = "graph_children"."id"
) SELECT COUNT(*) FROM "graph_children"
""", [])).filter(graph_child_count__lt=10).count()
}}}
Now, regarding this:
>`RawSQL()` is the last resort
>using QuerySet.extra()
`extra` is the real last resort though. Every place in the documentation
says it should be avoided, and it will be deprecated as soon as it's not
needed anymore, and if needed, you should use a `RawSQL` with an annotate.
Yes, I believe I could use `extra` to solve my issue, but I want to use a
reccomended way to solve this issue.
So, to summarize, if there's a way I'm unaware of about encapsulating my
query in a Django structure (such as in the `Func` `custom_name` example),
yeah, I think this ticket is invalid. Otherwise, I really think there
should be a way to run more specific queries while having the guarantee
that stuff inside the final query will be there.
--
Ticket URL: <https://code.djangoproject.com/ticket/32449#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: João Carneiro Haas | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: annotate, count, | Triage Stage:
RawSQL, select_related, JOIN | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Replying to [comment:3 João Carneiro Haas]:
> Ok, that actually solves the issue I presented, but I'm at fault here,
the `custom_name` example was just the easiest way I found to explain the
issue, we're actually doing a Postgres recursive query, which as far as I
know there's no way to represent in a Django structure.
We have an accepted ticket for CTE, see #28919.
> So, to summarize, if there's a way I'm unaware of about encapsulating my
query in a Django structure (such as in the `Func` `custom_name` example),
yeah, I think this ticket is invalid. Otherwise, I really think there
should be a way to run more specific queries while having the guarantee
that stuff inside the final query will be there.
There is no need for a new API, because resolving #28477 will strip unused
annotation, so crash is a duplicate of #28477. You can also push
everything to a
[https://code.djangoproject.com/ticket/32449?replyto=3#comment:1
subquery].
--
Ticket URL: <https://code.djangoproject.com/ticket/32449#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: João Carneiro Haas | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: annotate, count, | Triage Stage:
RawSQL, select_related, JOIN | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by João Carneiro Haas):
Replying to [comment:4 Mariusz Felisiak]:
> We have an accepted ticket for CTE, see #28919.
Yeah, I believe this will end up solving my issue.
> There is no need for a new API, because resolving #28477 will strip
unused annotation, so crash is a duplicate of #28477
The crash will still occur even after #28477 is fixed, since you can (and
probably will) use the annotation on other statements which will influence
`count`:
{{{
#!python
b_qs =
b_qs.objects.annotate(graph_child_count=RawSQL(...)).filter(graph_child_count__lt=10).count()
}}}
So yeah, the CTE ticket solves our specific issue, although I still
believe this feature would be useful for other non-supported Django stuff
which require a `RawSQL`.
--
Ticket URL: <https://code.djangoproject.com/ticket/32449#comment:5>