flask - sqlalchemy - 自引用查询
问题描述:
我尝试在sqlalchemy中进行查询以获取在父级和子级上过滤的自引用关系。flask - sqlalchemy - 自引用查询
category_country = Table('category_country', Base.metadata,
Column('category_id', Integer, ForeignKey('category.id'), primary_key=True),
Column('country_id', Integer, ForeignKey('country.id'), primary_key=True)
)
class Category(Base):
__tablename__ = "category"
id = Column(Integer, primary_key=True, autoincrement=True)
parent_id = Column(Integer, ForeignKey('category.id'))
subcategories = relationship("Category", backref=backref('parent', remote_side=id))
countries = relationship(Country, secondary = category_country, backref='categories')
class Country(Base):
__tablename__ = "country"
id = Column(Integer, primary_key=True)
查询
category = s.query(Category).join(Category.countries).options(contains_eager(Category.countries)).filter(Country.id == 1).filter(Category.id == category_id).join(Category.countries, aliased=True).join(Category.subcategories, aliased=True).options(contains_eager(Category.countries)).filter(Country.id == 1).first()
,但它不工作。我需要找到孩子这是从国家1和其父是CATEGORY_ID和国家也是1
答
我没有完全得到我的第一次读你的模型/代码,但我会解决这个的办法是通过拆分自我指涉加入到子查询()语句这样的:
filter_by_country = (db.session.query(...)
.filter(...)
.subquery())
final_results = (db.session.query(...)
.join(filter_by_country,
db.and_(Category.id == filter_by_country.c.id, ..., ...))
.options(...)
.filter(...)
.etc(...).first())
我发现这种模式可以帮助简化这些类型的查询。希望这可以帮助。