thanks for the great test case, makes this easy.
The with_polymorphic() function is used for this kind of situation, more
common in joined inheritance but since you want to alias here, works for
single also:
Piece = with_polymorphic(Item, '*', aliased=True)
s.query(Item) \
.outerjoin(BasketPiece, BasketPiece.basket_id == Item.id) \
.outerjoin(Piece, Piece.id == BasketPiece.piece_id) \
.filter(or_(Food.calories > 100, Book.pages < 500,
Piece.Food.calories > 100, Piece.Book.pages < 500)) \
.all()
the docs are kind of disorganized on with_polymorphic() (should have
it's own top-level section, single-inh should also refer to it).
On 04/25/2017 05:57 PM, Shane Carey wrote:
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_(Food.calories > 100, Book.pages < 500, Piece.calories > 100,
Piece.pages < 500)) \
.all()
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
