This is essentially ticket #1401 and I've attached this there as well as moved up the priority, however this issue is extremely complicated and would require some serious rethinking of the relationship()'s inner workings. It would take several days to come up with a general solution so I can't give you a fix for this right now.
http://www.sqlalchemy.org/trac/ticket/1401 On Feb 1, 2012, at 5:40 AM, Pau Tallada wrote: > Hi! > > I have a table with a self-reference of two columns that represents a tree > structure. > I was trying to build an outerjoin to select all the nodes have children but > NO grandchildren, but the SQL constructed was incorrect, as it was not > aliasing properly one of the columns. > > note_t Table('node_t', metadata, > Column('id', Integer, primary_key=True), > Column('project_id', Integer), > Column('parent_id', Integer), > sa.ForeignKeyConstraint( > ['project_id', 'parent_id'], > ['node_t.project_id', 'node_t.id])) > > mapper(Node, node_t, properties= { > 'children' : relationship(Node, > remote_side=[note_t.c.id, node_t.c.project_id] > ) > }) > > print str(session.query(Node).outerjoin(Node, Node.children, aliased=True))) > > Generated (simplified): > SELECT node.id, node.project_id, node.parent_id > FROM node > LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id > AND node.project_id = node.project_id > > Expected: > SELECT node.id, node.project_id, node.parent_id > FROM node > LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id > AND node.project_id = parent.project_id > > Making the join condition explicit generates the correct SQL > Parent = aliased(Node) > print str(session.query(Node).outerjoin(Parent, (Node.parent_id == Parent.id) > & (Node.project_id == Parent.project_id))) > > I have attached a small test file (test.py) that shows this behaviour and is > based on one of your tests suites (test_relationships). > > Thanks in advance! > > Pau. > -- > ---------------------------------- > Pau Tallada Crespí > Dep. d'Astrofísica i Cosmologia > Port d'Informació Científica (PIC) > Tel: +34 93 586 8233 > ---------------------------------- > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > <test.py> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
