On Feb 10, 2010, at 10:34 AM, Michael Bayer wrote:
> Maybe, I haven't tried this, you could make an alternate (non primary)
> mapping to Item that was a join of Item and Detail, i.e. like:
>
> itemdetail = mapper(Item.__table__.join(Detail.__table__), non_primary=True)
>
> Order.items = relation(itemdetail, order_by=itemdetail.c.detail_id)
>
> I'm not 100% sure the non-primary mapper allowing additional attributes, and
> it might need to be a mapping of a select().select_from(join) and not the
> join directly, but if you have some time to experiment you might get
> something out of that.
Here, I think it works:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class Detail(Base):
__tablename__ = 'detail'
id = Column(Integer, primary_key=True)
item_id = Column(Integer, ForeignKey('item.id'))
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('order.id'))
detail = relation(Detail, uselist=False, lazy=False)
order = relation(Order, uselist=False)
j = Item.__table__.join(Detail.__table__)
itemdetail = mapper(Item, j, non_primary=True)
Order.items = relation(itemdetail, order_by=j.c.detail_id, viewonly=True)
metadata = Base.metadata
metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine))
Session.query(Order).options(eagerload(Order.items)).all()
the query is:
SELECT "order".id AS order_id, anon_1.item_id AS anon_1_item_id,
anon_1.detail_id AS anon_1_detail_id, anon_1.item_order_id AS
anon_1_item_order_id, anon_1.detail_item_id AS anon_1_detail_item_id
FROM "order" LEFT OUTER JOIN (SELECT item.id AS item_id, item.order_id AS
item_order_id, detail.id AS detail_id, detail.item_id AS detail_item_id
FROM item JOIN detail ON item.id = detail.item_id) AS anon_1 ON "order".id =
anon_1.item_order_id ORDER BY anon_1.detail_id
However I can't currently get "back_populates" or "backref" to relate the two
sides together. which isn't necessary if you dont need it.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
