Hallo Mike, first of all, thank you for your great great sqlalchemy!
On Aug 22, 6:49 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > im not sure about how elixir does bi-directional relationships but it > seems strange that you have set up the "children" and "parents" > relationships twice in both directions. but this might be elixir's > "strangeness". Yes, i must declare the relation twice in order to get a bi-dir one. > While the results you're getting below arent right, I would also > point out that the above query doesnt actually gain anything by > having the join to "children" since those rows arent being filtered > or fetched...unless you're just illustrating for the sake of example. My bad! What I actually want is a eager load on the self-referential m2m relation. To make things clearer, I rewrite the demo in pure SA this time: from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData(create_engine('postgres://localhost/test')) widget = Table('widget', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(40), nullable=False, unique=True), ) widget_rel = Table('widget_parents__widget_children', metadata, Column('parent_id', Integer, ForeignKey('widget.id')), Column('child_id', Integer, ForeignKey('widget.id')), UniqueConstraint('parent_id', 'child_id'), ) class Widget(object): pass mapper(Widget, widget, properties={ 'children': relation(Widget, secondary=widget_rel, primaryjoin=widget_rel.c.parent_id==widget.c.id, secondaryjoin=widget_rel.c.child_id==widget.c.id, lazy=False, join_depth=1, ) }) sess = scoped_session(sessionmaker())() and I want eager load the widget whose id=1 and all it's children if any: sess.query(Widget).filter(Widget.id==1).all() returns: DBAPIError: (ProgrammingError) missing FROM-clause entry for table "widget_1" LINE 2: ... ON widget_parents__widget_children_2.parent_id = widget_1.i... ^ 'SELECT widget_1.id AS widget_1_id, widget_1.name AS widget_1_name, widget.id AS widget_id, widget.name AS widget_name \nFROM widget LEFT OUTER JOIN widget_parents__widget_children AS widget_parents__widget_children_2 ON widget_parents__widget_children_2.parent_id = widget_1.id LEFT OUTER JOIN widget AS widget_1 ON widget_parents__widget_children_2.child_id = widget_1.id \nWHERE widget.id = %(widget_id)s ORDER BY widget.id' {'widget_id': 1} The problem is PropertyAliasedClauses returns the same alias name for both primary and secondary join, which is in this case wrong. I find no where in the docs says about eager load on self-referential m2m. Is that possible? Best! Jian --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---