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/ p...@phdru.name
Programmers don't die, they just GOSUB without RETURN.
------------------------------------------------------------------------------
Learn how Oracle Real Application Clusters (RAC) One Node allows customers
to consolidate database storage, standardize their database environment, and,
should the need arise, upgrade to a full multi-node Oracle RAC database
without downtime or disruption
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
