I have good news on this front, in that I've nailed down how this will work, including a patch that gets this basic thing working as a proof of concept. However, the issue of being able to distinguish "remote" and "foreign" in a binary expression where a column points to itself can benefit from moving completely to the newer concept I'm working on, which is different enough that I think it should be for the next major SQLAlchemy release (currently it's called 0.8). I'll keep the ticket updated with progress reports.
On Feb 1, 2012, at 10:49 AM, Michael Bayer wrote: > 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 [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
