A more complete example
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *
Base = declarative_base()
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
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 Basket(Item):
pieces = relationship('BasketPiece', primaryjoin='Basket.id ==
BasketPiece.basket_id',)
__mapper_args__ = {
'polymorphic_identity': 'basket'
}
class BasketPiece(Base):
__tablename__ = 'basket_piece'
basket_id = Column(None, ForeignKey(Basket.id), primary_key=True)
piece_id = Column(None, ForeignKey(Item.id), primary_key=True)
quantity = Column(Integer)
piece = relationship(Item, foreign_keys=piece_id)
if __name__ == '__main__':
e = create_engine('sqlite:///poly_self_ref.db', echo=True)
Base.metadata.create_all(e)
s = Session(sessionmaker(e))
Piece = aliased(Item)
s.query(Item) \
.outerjoin(BasketPiece, BasketPiece.basket_id == Item.id) \
.outerjoin(Piece, Piece.id == BasketPiece.piece_id) \
.filter(or_(Item.calories > 100, Item.pages < 500, Piece.calories > 100,
Piece.pages < 500)) \
.all()
On Tuesday, April 25, 2017 at 4:36:18 PM UTC-5, Shane Carey wrote:
>
> 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.
