From: Ben T. <bt...@gm...> - 2010-12-21 02:46:23
|
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. |
From: Oleg B. <ph...@ph...> - 2010-12-21 10:13:08
|
Hi! On Mon, Dec 20, 2010 at 09:46:16PM -0500, Ben Timby wrote: > class Path(sqlobject.SQLObject): > parent = sqlobject.ForeignKey('SyncPath', cascade=True) As far as I understand from the following query, 'SyncPath' should be just 'path', right? > path = sqlobject.StringCol(alternateID=True) > > 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... Aliases are created using sqlobject.sqlbuilder.Alias: p1 = Alias(Path) This uses autogenerated numbers for alias naming. If you want to give a specific name to an alias: p1 = Alias(Path, 'p1') Now you can use the alias as a table: p1.q.parent_id == id. > sql = Path.select(sqlbuilder.LEFTJOINOn( The first parameter for .select() is a WHERE condition. JOINs are provided in the 'join' parameter. Pass a JOIN or a list of JOINs: Path.select(join=LEFTJOINOn(...)) Path.select(join=[LEFTJOINOn(...), LEFTJOINOn(...)]) Oleg. -- Oleg Broytman http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2010-12-23 22:50:59
|
On Wed, Dec 22, 2010 at 10:44:49PM -0500, Ben Timby wrote: > Oleg, I tried this out, my code is: > > -- > joins = [] > alias1 = Path > path_parts = path.split(os.sep) > for depth, part in enumerate(path_parts): > alias2 = sqlbuilder.Alias(Path) > joins.append(sqlbuilder.LEFTJOINOn(alias1, alias2, > AND(alias1.q.id==alias2.q.parent, alias2.q.path==part))) > alias1 = alias2 > print Path.select(join=joins) > -- > > Which produces the following query: > -- > SELECT path.id, path.parent_id, path.path, path.depth, path.child_name > FROM path > LEFT JOIN path path_alias1 > ON (((path.id) = (path_alias1.parent_id)) > AND ((path_alias1.path) = (''))), path path_alias1 > LEFT JOIN path path_alias2 > ON (((path_alias1.id) = (path_alias2.parent_id)) > AND ((path_alias2.path) = ('home'))), path path_alias2 > LEFT JOIN path path_alias3 > ON (((path_alias2.id) = (path_alias3.parent_id)) > AND ((path_alias3.path) = ('btimby'))) > WHERE 1 = 1 > -- > > and the following error: > -- > *** OperationalError: ambiguous column name: path_alias1.parent_id > -- > > I think this is because of the redundant alias... > -- > LEFT JOIN path path_alias1 > ON (((path.id) = (path_alias1.parent_id)) > AND ((path_alias1.path) = (''))), path path_alias1 > -- > > "path path_alias1" shows up twice in the above snippet. > > Can you spot my error? Thanks. Use LEFTJOIN(None, alias) to avoid table doubling. Something like this: joins = [] alias1 = Path path_parts = path.split(os.sep) for depth, part in enumerate(path_parts): alias2 = sqlbuilder.Alias(Path) joins.append(sqlbuilder.LEFTJOINOn(None, alias2, AND(alias1.q.id==alias2.q.parent, alias2.q.path==part))) alias1 = alias2 print Path.select(join=joins) Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |