sqlobject-discuss

[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.

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.

Re: [SQLObject] Self LEFT JOIN.

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

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.