Dynamic query

[ODtian]

Drilldowntree return a list but not a query, returning a query can make the query easier

[uralbash]

How should it look?
I have a tree below the selected node (for example node 7). Result looks like a python dictionary because the dictionary also has a tree structure. As a request, I can only display a flat structure and writing such a request is very simple dbsession.query(Tree).filter(Tree.left > node7.left).filter(Tree.right < node7.right)

           level           Nested sets example
           1                    1(1)22       ---------------------
                   _______________|_________|_________            |
                  |               |         |         |           |
           2    2(2)5           6(4)11      |      12(7)21        |
                  |               ^         |         ^           |
           3    3(3)4       7(5)8   9(6)10  | 13(8)16   17(10)20  |
                                            |    |          |     |
           4                                | 14(9)15   18(11)19  |
                                            |                     |
                                             ---------------------

[ODtian]

My idea looks like this. The current drill down tree returns a python dictionary, but if it can return a query set like how it list ancestor nodes, it will only read all data from the database when the data is finally needed, which not only makes The api looks more user-friendly and avoids unnecessary read operations. You can also use the api of the query set in sqlalchemy instead of writing a python dictionary-based query

[ODtian]

This may lead to multiple queries in some scenarios, but the performance is much better than reading all grandchildren nodes. Of course, it is possible to query directly using the left and right fields, but I will be happy if it is in node's methods.

[uralbash]

Could you give an example of how this should look? What should the function return?

[ODtian]

Allright, in current version, we use Drilldown like this:

Drilldown

Drilldown tree for a given node.

A drilldown tree consists of a node’s ancestors, itself and its immediate children. For example, a drilldown tree for a foo1 category might look something like:

Drilldown for foo1 node

level
  1                  1(root)14
                         |
                ---------------------
                |         ----------|---------------
  2          2(foo)7      |      8(foo1)13         |
                |         |     /         \        |
  3          3(bar)6      | 9(bar1)10   11(baz1)12 |
                |         --------------------------
  4          4(baz)5

categories = Category.query.all()

for item in categories:
    print(item)
    pprint(item.drilldown_tree())
    print()

<Category root>
[{'children': [{'children': [{'children': [{'node': <Category baz>}],
                              'node': <Category bar>}],
                'node': <Category foo>},
               {'children': [{'node': <Category bar1>},
                             {'node': <Category baz1>}],
                'node': <Category foo1>}],
  'node': <Category root>}]

<Category foo>
[{'children': [{'children': [{'node': <Category baz>}],
                'node': <Category bar>}],
  'node': <Category foo>}]

<Category bar>
[{'children': [{'node': <Category baz>}], 'node': <Category bar>}]

<Category baz>
[{'node': <Category baz>}]

<Category foo1>
[{'children': [{'node': <Category bar1>}, {'node': <Category baz1>}],
  'node': <Category foo1>}]

<Category bar1>
[{'node': <Category bar1>}]

<Category baz1>
[{'node': <Category baz1>}]

So what it return is a python dict and the nodes are already listed.

How path to root work:

Path to root
Returns a list containing the ancestors and the node itself in tree order.

Path to root of bar node

level      ---------------------
  1        |         1(root)14 |
           |             |     |
           |    ---------------|-----
           |    |    -----------    |
  2        | 2(foo)7 |           8(foo1)13
           |    |    |          /         \
  3        | 3(bar)6 |      9(bar1)10   11(baz1)12
           -----|-----
  4          4(baz)5

for item in categories:
    print(item)
    print(item.path_to_root()[-1])  # get root
                                    # last element in list
    pprint(item.path_to_root().all())
    print()

<Category root>
<Category root>
[<Category root>]

<Category foo>
<Category root>
[<Category foo>, <Category root>]

<Category bar>
<Category root>
[<Category bar>, <Category foo>, <Category root>]

<Category baz>
<Category root>
[<Category baz>, <Category bar>, <Category foo>, <Category root>]

<Category foo1>
<Category root>
[<Category foo1>, <Category root>]

<Category bar1>
<Category root>
[<Category bar1>, <Category foo1>, <Category root>]

<Category baz1>
<Category root>
[<Category baz1>, <Category foo1>, <Category root>]

Path to root return a sqlalchemy query set, so if you finally need the nodes, you should first call .all() method, another usage is to call .fliter() for further query. For drilldown, it's common to make some further query. Otherwise, we need to write filter in python not queries in database.

[ODtian]

I mean Query object in sqlalchemy, query set is the orm query object in Django

[ODtian]

So maybe there should be a new method for mptt node to get a query object which includes all children nodes, and drilldown should be kept to get the tree structure.

[uralbash]

So maybe there should be a new method for mptt node to get a query object which includes all children nodes, and drilldown should be kept to get the tree structure.

I will think about how to do it