SQL query to avoid fetching the entire nested set when parts of it are collapsed by the user

Question

I'm trying to tie django-mptt and contrib.admin together by providing something friendlier than a flat list in the admin. Because the trees are supposed to be large (otherwise i wouldn't be using nested sets), users should be able to expand and collapse parts of it.

When a user expands or collapses or expands a branch (ajax is used for that), a cookie is also set containing a comma separated list of collapsed branches. This way, next time this user visits the admin for my django-mptt powered model, i can show him the tree in the exact state he left it. Now i would like to use this list of collapsed branches to ease the burden on my database by fetching only needed parts of the tree.

Is there a way to do this effectively? The solutions i googled were making a query for each branch so they could avoid querying when a branch was collapsed, but that doesn't look very effective to me. Maybe it is possible with a fixed number of queries?

Answer 1

You're not really explaining what you're doing, so it's a bit hard to help. (What are you doing with the tree? How are you displaying it? What do you want the users to be able to do?)

Each element in a Django-MPTT tree has a get_children() method - and using the optional include_self=True parameter you can get a list of the element and all its children. You can use this to pre-filter subtrees so that you only display parts of it, if that's what you want.

If you want users to be able to dynamically expand and collapse parts of the tree without reloading the page, you will need to use AJAX. There are various AJAX-enabled treeview controls around - I've written one myself using jQuery - and no doubt one of them will do something along the lines of what you want.