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