Re: Making queries with custom SQL

[Javier Guerra Giraldez]

On 16 June 2016 at 16:21, TheBeardedTemplar  wrote:
> I'm using postgreSQL and have found that recursive queries are exactly what
> I need


In most cases it's much better to use a more efficient form of tree.
A common one is called MPTT, for which there are Django packages.

-- 
Javier

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAFkDaoS6Lsqe5173-1GZ%3D3c9hOqStice3DTqqEEEP84ua2QSLw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Making queries with custom SQL

[TheBeardedTemplar]

Hey everyone,

The bulk of my database consists of a tree of models (called Node), more 
than 200,000 of them, and I very regularly need to make large queries to 
get subtrees which I'm finding is causing a significant slowdown. I'm using 
postgreSQL and have found that recursive queries 
 are exactly 
what I need, but I'd like to use them nicely from within Django. Is there 
an accepted way to do this, or something I could override? Basically I'd 
like to have something similar to `Node.filter()` like 
`Node.getSubtree(root=)` which executes the following:

WITH RECURSIVE nodes(id, label, parent_id) AS (
 SELECT tn.id, tn.label, tn.parent_id FROM assets_asset AS tn WHERE tn.id = 

  UNION ALL
SELECT p.id, p.label, p.parent_id 
 FROM nodes as c, assets_asset AS p 
 WHERE c.parent_id = p.id
)
SELECT * FROM nodes AS n ORDER BY n.id ASC;


I'd appreciate any advice on how to go about this. I'm not afraid to dive 
into the source, I'm just not sure where to look.

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/aee479c2-c052-48b7-96b3-71d1c7029312%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.