#32449: Allow specifying tables in RawSQL().
-------------------------------------+-------------------------------------
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
Has patch: 0 | Needs documentation: 0
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 <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/066.eb071395b546c1b3ed59b80e2ceef8c1%40djangoproject.com.
