Re: [SQLObject] Self LEFT JOIN.

[Oleg B. <ph...@ph...>]

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.