Recursive select with web2py

In this article we will deal with a somewhat well-known but unexplained subject. This tip applies not only to web2py but to anyone who needs to organize their data into tree structures using data from the database. http://www.web2py.com/books/default/chapter/31/06/data-abstruction-layer#-Recursive-select-s.

db.define_table('thing',
                    Field('name'),
                    Field('owner_id', 'reference person'))

The web2py documentation proposes the following table structure and how to recursively select this data.

person =  db.person(id)
for thing in person.thing.select(orderby=db.thing.name):
    print person.name, 'owns', thing.name

The fact is that if we do the db (). Select () command it will create a new database connection and so as many times as needed. If we think of a system that receives several visitors per minute (or second), even a table with little data would overload our application. So which one is ideal? Ideally, we will always make a single database connection to get everything we need, so we will process this data into a tree. I will use a preorder algorithm to scan the list of Rows and organize them into a dictionary that will contain the structure below.

roots = { 1: {'id':1, 'name': 'Paul', 'childreen': {
    3: {'id': 3, 'name': 'Jessica', 'childreen': {}
   ,8: {'id': 8, 'name': 'Robert', 'childreen': {...}
}

This way we can take advantage of this in dropdown menus, tree structures and more.
Here is the algorithm:

rows = db(db.person).select().as_dict()
roots = {}# Pre order algorithm
def pre_order(node):
    for k, v in rows.items():
        if node['id'] == v['owner_id']:
            if 'childreen' not in node:
                node['childreen'] = {}
            node['childreen'][k] = v
            del rows[k]
            pre_order(v)# Filter fathers
for k, v in rows.items():
    if v['owner_id']==None:
        del rows[k]
        # Now search by son
        pre_order(v)
        roots[k] = v

The above algorithm combined with web2py’s DAL facility is a hand in hand.
I hope I helped and answered questions.

Thanks!