#186 Multiple join of table with itself fails with Postgres

closed-wont-fix
Oleg Broytman
Postgres (36)
5
2006-08-22
2006-07-04
Henrik Weber
No

With SQLobject 0.7.0 using Postgres I have tried to
join a table with itself more than once. The code
looks something like this:

alias1 = Alias(Tbl, "T1")
alias2 = Alias(Tbl, "T2")

joins = [INNERJOINOn(Tbl, alias1, Tbl.q.tblid ==
alias1.q.tblid), INNERJOINOn(Tbl, alias2, Tbl.q.tblid
== alias2.q.tblid)]

result = Tbl.select(somewherecrit, join=joins)

The resulting SQL statement looks like this:

SELECT Tbl.id, Tbl.tblid FROM Tbl INNER JOIN Tbl AS T1
ON (Tbl.tblid = T2.tblid), Tbl INNER JOIN Tbl AS T2 ON
(Tbl.tblid = T2.tblid)
WHERE ...

The resulting error message is:
psycopg2.ProgrammingError: table name "Tbl" specified
more than once

The correct statement would actually have been:

SELECT Tbl.id, Tbl.tblid FROM Tbl INNER JOIN Tbl AS T1
ON (Tbl.tblid = T2.tblid) INNER JOIN Tbl AS T2 ON
(Tbl.tblid = T2.tblid)
WHERE ...

I tried defining the join recursively, but you can't
use a Join object as parameter for a new join.

Discussion

  • Oleg Broytman
    Oleg Broytman
    2006-08-22

    • assigned_to: nobody --> phd
    • status: open --> closed-wont-fix
     
  • Oleg Broytman
    Oleg Broytman
    2006-08-22

    Logged In: YES
    user_id=4799

    If you don't want the second Tbl, do not name it. Use None
    instead:

    INNERJOINOn(None, alias2...