sqlalchemy hybrid_attribute表达式 [英] sqlalchemy hybrid_attribute expression

问题描述

假设以下模型:

class Worker(Model):
    __tablename__ = 'workers'
    ...
    jobs = relationship('Job',
                        back_populates='worker',
                        order_by='desc(Job.started)',
                        lazy='dynamic')

    @hybrid_property
    def latest_job(self):
        return self.jobs.first()  # jobs already ordered descending

    @latest_job.expression
    def latest_job(cls):
        Job = db.Model._decl_class_registry.get('Job')
        return select([func.max(Job.started)]).where(cls.id == Job.worker_id).as_scalar()

class Job(Model):
    ...
    started = db.Column(db.DateTime, default=datetime.utcnow)
    worker_id = db.Column(db.Integer, db.ForeignKey('workers.id'))
    worker = db.relationship('Worker', back_populates='jobs')

虽然此查询提供了正确的结果:

While this query provides correct results:

db.session.query(Worker).join(Job.started).filter(Job.started >= datetime.datetime(2017, 5, 10, 0, 2, 45, 932983)).distinct().count()

我假设我可以直接查询该字段，但是此查询失败:

I was under the assumption I could query that field directly, but this query fails:

db.session.query(Worker).join(Job).filter(Worker.latest_job.started >= datetime.datetime(2017, 5, 10, 0, 2, 45, 932983)).count()

出现此错误:

AttributeError: Neither 'hybrid_property' object nor 'ExprComparator' object associated with Worker.latest_job has an attribute 'started'

如何直接查询此属性?我在这里想念什么?

How can I query this property directly? What am I missing here?

遵循@Ilja的建议，我尝试过:

EDIT 1: Following @Ilja advice from his answer, I have attempted:

db.session.query(Worker).\
    join(Job).\
    filter(Worker.latest_job >= datetime.datetime(2017, 5, 10, 0, 2, 45, 932983)).\
    count()

但出现此错误:

TypeError: '>=' not supported between instances of 'Select' and 'datetime.datetime'

推荐答案

在SQL(类)上下文中使用时，您将从混合属性返回标量子查询，因此只需使用它就可以使用值表达式:

You're returning a scalar subquery from your hybrid property when used in SQL (class) context, so just use it as you'd use a value expression:

db.session.query(Worker).\
    filter(Worker.latest_job >= datetime.datetime(2017, 5, 10, 0, 2, 45, 932983)).\
    count()

在这种情况下，hybrid属性本身需要显式处理相关性:

The hybrid property itself needs to explicitly handle correlation in this case:

@latest_job.expression
def latest_job(cls):
    Job = db.Model._decl_class_registry.get('Job')
    return select([func.max(Job.started)]).\
        where(cls.id == Job.worker_id).\
        correlate(cls).\
        as_scalar()

请注意，混合属性的Python端和SQL端之间存在一些不对称性.与在SQL中生成 max(started)的相关标量子查询相比，在实例上访问时，它会生成最新的 Job 对象.如果您还希望它在SQL中返回 Job 行，则可以执行类似的操作

Note that there's some asymmetry between your hybrid property's Python side and SQL side. It produces the latest Job object when accessed on an instance, compared to producing a correlated scalar subquery of max(started) in SQL. If you'd like it to return a Job row in SQL as well, you'd do something like

@latest_job.expression
def latest_job(cls):
    Job = db.Model._decl_class_registry.get('Job')
    return Job.query.\
        filter(cls.id == Job.worker_id).\
        order_by(Job.started.desc()).\
        limit(1).\
        correlate(cls).\
        subquery()

但是实际上实际上用处不大，因为通常(但不总是)这种相关子查询比结合子查询慢.例如，为了获取具有原始条件的最新职位的工人:

but that's actually less useful mostly, because usually – but not always – this kind of correlated subquery will be slower than joining against a subquery. For example in order to fetch workers with latest jobs that meet the original criteria:

job_alias = db.aliased(Job)
# This reads as: find worker_id and started of jobs that have no matching
# jobs with the same worker_id and greater started, or in other words the
# worker_id, started of the latest jobs.
latest_jobs = db.session.query(Job.worker_id, Job.started).\
    outerjoin(job_alias, and_(Job.worker_id == job_alias.worker_id,
                              Job.started < job_alias.started)).\
    filter(job_alias.id == None).\
    subquery()

db.session.query(Worker).\
    join(latest_jobs, Worker.id == latest_jobs.c.worker_id).\
    filter(latest_jobs.c.started >= datetime.datetime(2017, 5, 10, 0, 2, 45, 932983)).\
    count()

当然，如果您只想计数，则根本不需要联接:

and of course if you just want the count, then you don't need the join at all:

job_alias = db.aliased(Job)
db.session.query(func.count()).\
    outerjoin(job_alias, and_(Job.worker_id == job_alias.worker_id,
                              Job.started < job_alias.started)).\
    filter(job_alias.id == None,
           Job.started >= datetime.datetime(2017, 5, 10, 0, 2, 45, 932983)).\
    scalar()

请注意，调用 Query.scalar() 与

Please note that the call to Query.scalar() is not the same as Query.as_scalar(), but just returns the first value of the first row.

这篇关于sqlalchemy hybrid_attribute表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！