sqlalchemy 过滤查询中的子项，而不是父项 [英] sqlalchemy filter children in query, but not parent

问题描述

我有一个返回 User 对象的查询.Users 有许多他们制作的 Posts.当我执行查询时，我想根据特定时间过滤在 User.post 中找到的帖子.例如，仅返回给定时间戳的 1 天内的帖子.

I have a query which returns a User object. Users have a number of Posts which they have made. When I perform a query, I want to filter the posts found in User.post based on a specific time. For example, to return only the posts within 1 day of a given timestamp.

class User(base):
    __tablename__ = 'User'

class Post(base):
    __tablename__ = 'Post'

    user_id = Column(Integer, ForeignKey('User.uid'))
    user = relationship(User, backref=backref('post')
    time = Column(DateTime, default=func.current_timestamp())

有没有办法动态改变 orm 加载孩子的方式，并在查询时指定?类似(伪代码):

Is there a way to dynamically change the way the children are loaded by the orm, and to specify that at query time? Something like (pseudocode):

User.query.filter_child(Post.time in timespan)

重要的是，如果没有符合条件的孩子，我不想过滤掉父母.我只想过滤 orm 为给定用户加载的孩子.

Importantly, I don't want to filter out parents if there are no children which match the criteria. I only want to filter the children loaded by the orm for the given Users.

推荐答案

加载自定义过滤集合 使用 contains_eager().由于目标是加载所有 User 对象，即使它们没有符合过滤条件的 Post 对象，应该使用 outerjoin()以及放在连接的 ON 子句中的帖子谓词:

Loading custom filtered collections is done using contains_eager(). Since the goal is to load all User objects even if they have no Post objects that match the filtering criteria, an outerjoin() should be used and the predicates for posts put in the ON clause of the join:

end = datetime(2019, 4, 10, 12, 0)
start = end - timedelta(days=1)

# If you want to use half closed intervals, replace `between` with
# suitable relational comparisons.
User.query.\
    outerjoin(Post, and_(Post.user_id == User.uid,
                         Post.time.between(start, end))).\
    options(contains_eager(User.post))

这篇关于sqlalchemy 过滤查询中的子项，而不是父项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！