[SQLObject] Self LEFT JOIN.

[Ben T. <bt...@gm...>]

I am using an adjacency list to store file system paths.

--
class Path(sqlobject.SQLObject):
    parent = sqlobject.ForeignKey('SyncPath', cascade=True)
    path = sqlobject.StringCol(alternateID=True)
--

I would like to retrieve an object given it's path in one fell swoop.
Below is an example...


path: /home/btimby/somefile

sql:

select p2.*
from path as p0
left join path as p1
on p1.parent_id = p0.id
and p0.path = 'home'
left join path as p2
on p2.parent_id = p1.id
and p1.path = 'btimby'
and p2.path = 'somefile';

How could I accomplish this in SQLObject? I would have code similar to
the following, but not sure how to generate the aliases and multiple
levels of joins...

--
def get_path(path):
    path_parts = path.split(os.sep)
    sql = Path
    for part in path_parts:
        sql = Path.select(sqlbuilder.LEFTJOINOn(
            sql,
            Path,
            sqlbuilder.AND(
                Path.q.id == Path.q.parent,
                Path.q.path == part
            )
        ))
    print sql
--

The above is a bit naive, but hopefully the solution is possible.

Thanks.