pyDAL recursive select

recursive_select.py

# fake table in which result of recursive select will be temporary stored
# id-values will be inherited from parent_child table
db.define_table('entry_collector',
        Field('child', 'integer'),
        Field('xpath', 'json'), # array of ids,  xpath[0] == root, xpath[-1] == child
        Field('root', 'integer'),
        Field('xdepth', 'integer'),
        migrate_enabled = False,
        fake_migrate = True
)


def with_recursive(parent, child, roots_select, q, *fields, **select_kwargs):
    """
    parent, child  - fields obj ( like  db.parent_child.parent, db.parent_child.child )
    roots_select - sql string (like 'select 123 as id' or db(db.person.id.belongs([11,22,33])._select(db.person.id))
    q, fields, select_kwargs  - args that will pass to dal: db(q).select(*fields, **select_kwargs)
    select_kwargs may include 'entry_collector' - name of fake table for recursive (default is 'entry_collector')
    returns a regular rows dal object (nothing new)
    """

    entry_collector = select_kwargs.pop('entry_collector', 'entry_collector')
    args = Storage(
        entry = parent.table._tablename,
        parent = parent.name,
        child  = child.name,
        entry_collector = entry_collector,
        roots = roots_select
    )

    rec_sql_s = \
    """
        WITH RECURSIVE
        %(entry_collector)s(id, child, xpath, root, xdepth) AS
            (SELECT NULL, id, "[" || id || " ]", id, 0 FROM (%(roots)s)
             UNION
             SELECT  %(entry)s.id,
                     %(entry)s.%(child)s,
                     rtrim(xpath," ]") || " , " || %(entry)s.%(child)s || " ]",
                     %(entry_collector)s.root,
                     %(entry_collector)s.xdepth + 1
                 FROM %(entry_collector)s
                 JOIN %(entry)s ON
                     NOT instr(%(entry_collector)s.xpath, " " ||  %(entry)s.%(parent)s || " " )
                     AND %(entry)s.%(parent)s = %(entry_collector)s.child
                 ORDER BY 5 DESC /* means BY xdepth  */

            )
    """ % args
    db = parent.table._db
    q = db(q)
    dal_select = q._db._adapter._select_aux
    def patch_select(*args, **kwargs):
        if args:
            is_recursive = False
            for fld in args[1]:
                if fld.table._tablename == entry_collector:
                    is_recursive = True
                    break
            if is_recursive:
                args = list(args)
                args[0] = rec_sql_s + args[0]
                #print 'with rec: ', args[0]
        return dal_select(*args, **kwargs)

    q._db._adapter._select_aux = patch_select
    try:
        ret = q.select(*(fields + (db[entry_collector].id,) ), **select_kwargs)
    finally:
        q._db._adapter._select_aux = dal_select
    return ret