查询父模型时可以过滤关系的内容吗? [英] Can the contents of a relationship be filtered when querying parent model?

问题描述

我有两个模型分别代表电影及其放映时间.我想查询所有电影，但它们的show_times关系应仅包含将来的放映时间.

I have two models representing movies and their show times. I would like to query for all movies, but their show_times relationship should only contain those show times that are in the future.

class PKMovie(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    url = db.Column(db.String(255))
    show_times = db.relationship('ShowTime')

class ShowTime(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.Date)
    pk_movie_id = db.Column(db.Integer, db.ForeignKey('pk_movie.id'))

查询父级时是否可能影响关系的内容?

Is it possible to affect the contents of the relationship when querying the parent?

推荐答案

默认情况下，关系是一个简单的相等表达式:Parent.id == ForeignKey.id.您不能在查询时更改关系的内容*，但是可以创建另一个仅选择所需项目的关系.

A relationship, by default, is a simple equality expression: Parent.id == ForeignKey.id. You cannot change the contents of a relationship at query time*, but you can create another relationship that selects only the items you want.

class PKMovie(db.Model):
    # ...
    future_show_times = db.relationship(
        lambda: ShowTime,
        primaryjoin=lambda: db.and_(
            PKMovie.id == ShowTime.pk_movie_id,
            ShowTime.date >= db.func.current_timestamp()
        ),
        viewonly=True
    )

访问实例的future_show_times将仅返回将来的放映时间.您可以在查询期间急于加载此关系，以免在访问时引起额外的数据库查询.

Accessing an instance's future_show_times will return only the show times that are in the future. You can eager load this relationship during the query so that it doesn't incur an extra database query when accessed.

PKMovie.query.options(db.joinedload(PKMovie.future_show_times)).all()

请参见有关关系的文档.

*从技术上讲，您可以在查询时更改关系，如此答案.但是，我认为明确定义这些其他关系要清楚得多.

* Technically, you can change the relationship at query time, as demonstrated by this answer. However, I think it's much clearer to explicitly define these other relationships.

这篇关于查询父模型时可以过滤关系的内容吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！