SQLAlchemy 如何在多对多中按子项过滤 [英] SQLAlchemy how to filter by children in many to many
问题描述
我问了一个我在 SQLAlchemy 中遇到的问题,并在写作时找到了解决方案.无论如何我都会发布它以防万一它对某人有帮助:)
I was asking for a problem I had in SQLAlchemy and found the solution while writing. I post it anyway just in case it helps somebody :)
假设我有一个似乎有效的多对多关系(至少我可以获取孩子)三个表:posts、tags 和 post_tags.
Let's say I have a many to many relationship that seems to work (at least I can fetch children) Three tables: posts, tags and post_tags.
import sqlalchemy as alc
class Tag(Base):
__tablename__ = 'tags'
id = alc.Column(alc.Integer, primary_key=True)
name = alc.Column(alc.String)
accepted = alc.Column(alc.Integer)
posts = relationship('Post', secondary=post_tags)
class Post(Base):
__tablename__ = 'posts'
id = alc.Column(alc.Integer, primary_key=True)
text = alc.Column(alc.String)
date_out = alc.Column(alc.Date)
tags = relationship('Mistake_Code', secondary=post_tags)
# relational table
post_tags = alc.Table('check_point_mistakes',
Base.metadata,
alc.Column('post_id', alc.Integer,ForeignKey('posts.id')),
alc.Column('tag_id', alc.Integer, alc.ForeignKey('tags.id')))
现在我的问题是我想在 Post 中首先按 date_out 过滤.我可以这样:
Now my problem is that I want to filter first by date_out in Post. I can get it like this:
# assume start_date and end_date
query = (
session.query(Post)
.filter(Post.date_out.between(start_date, end_date))
)
但如何同时按标签过滤?
But how to filter by tags at the same time?
推荐答案
query = (
session.query(Post)
.join(Post.tags) # It's necessary to join the "children" of Post
.filter(Post.date_out.between(start_date, end_date))
# here comes the magic:
# you can filter with Tag, even though it was not directly joined)
.filter(Tag.accepted == 1)
)
免责声明:这是我实际代码的极简示例,我可能在简化时犯了一个错误.
Disclaimer: this is a veeery reduced example of my actual code, I might have made a mistake while simplifying.
希望对大家有所帮助.
这篇关于SQLAlchemy 如何在多对多中按子项过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!