I have a self referential polymorphic relationship using single table
inheritance
Base = declarative_base()
class Item(Base):
__tablename__ = 'item'
id = Column(Integer)
type = Column(String)
__mapper_args__ = { 'polymorphic_on': type, 'with_polymorphic': '*'}
class Food(Item):
calories = Column(Integer)
__mapper_args__ = {'polymorphic_identity': 'food'}
class Book(Item):
pages = Column(Integer)
__mapper_args__ = {'polymorphic_identity': 'book'}
class Toy(Item):
rating = Column(Integer)
__mapper_args__ = {'polymorphic_identity': 'toy'}
class BasetPiece(Base):
__tablename__ = 'basket_piece'
basket_id = Column(Integer, ...)
piece_id = Column(Integer, ...)
quantity = Column(Integer)
class Basket(Item):
items = relationship()
__mapper_args__ = {'polymorphic_identity': 'food'}
So I need to essentially select all food with > 100 calories, books with <
500 pages, and baskets which contain either, in one query.
SELECT * FROM item i
LEFT OUTER JOIN basket_piece bp ON bp.basket_id = i.id
LEFT OUTER JOIN item p ON p.id = bp.piece_id
WHERE i.calories > 100 OR i.pages < 500 OR p.calories > 100 OR p.pages <
500;
But I can't figure out how to do this in sqlalchemy because the columns of
the subclasses are not attributes of the Item class.
Piece = aliased(Item)
session.query(Item).outerjoin(BasketPiece).outerjoin(Piece).filter(sa.or_(Item.calories
> 100, Item.pages < 500, Piece.calories > 100, Piece.pages < 500)).all()
How do I acheieve this?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
