Flask应用中SQLAlchemy一对多关联获取最大日期

Question

我的数据
有四列：rowid，工作（不是唯一的），状态，日期。

查询需求
需要返回每个工作的唯一结果
状态为1的最大日期和
状态为0的最大日期
都在同一行中返回。

为什么这个方法不行（这真的是最好的方法吗？）:

from sqlalchemy import func
from sqlalchemy.orm import aliased

j=aliased(models.Jobs)

y=db.session.query(j.job,j.status,\
  func.max(j.start).\
  label("start")).\
  filter(j.status ==0).\
  group_by(j.job)   

x=db.session.query(models.Jobs.job,\
  models.Jobs.status,\
  func.max(models.Jobs.start).\
  label("start")).\
  filter(models.Jobs.status ==1).\
  group_by(models.Jobs.job)

  nj=x.join(y,x[0]==y[0])#i tried x.job, but it claimed it didn't have a job

我的追踪结果是

>>> nj=x.join(y,x[0]==y[0])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "K:\Meinzer\Production\ProjectManagement\flask\lib\site-packages\sqlalche
my\orm\query.py", line 1638, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 1, in <lambda>
  File "K:\Meinzer\Production\ProjectManagement\flask\lib\site-packages\sqlalche
my\orm\query.py", line 50, in generate
    fn(self, *args[1:], **kw)
  File "K:\Meinzer\Production\ProjectManagement\flask\lib\site-packages\sqlalche
my\orm\query.py", line 1767, in _join
    outerjoin, create_aliases, prop)
  File "K:\Meinzer\Production\ProjectManagement\flask\lib\site-packages\sqlalche
my\orm\query.py", line 1807, in _join_left_to_right
    onclause, outerjoin)
  File "K:\Meinzer\Production\ProjectManagement\flask\lib\site-packages\sqlalche
my\orm\query.py", line 1952, in _join_to_left
    "Tried joining to %s, but got: %s" % (right, ae))
sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from.
Tried joining to SELECT jobs_1.job AS job, jobs_1.status AS jobs_1_status, max(j
obs_1.start) AS start
FROM jobs AS jobs_1
WHERE jobs_1.status = :status_1 GROUP BY jobs_1.job, but got: Can't find any for
eign key relationships between 'jobs' and '_FromGrouping object'. Perhaps you me
ant to convert the right side to a subquery using alias()?
>>>

这是我的模型（我应该从语法开始吗？）

class Syntax(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(150), unique=True,)
    jobs = db.relationship('Jobs',lazy='dynamic', backref='jobhistory')
    position = db.Column(db.Integer)
    def __repr__(self):
        return '<filename %r>' % (self.filename)

class Jobs(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    job = db.Column(db.String(150),db.ForeignKey('syntax.filename'))
    start = db.Column(db.DateTime)
    stop = db.Column(db.DateTime)
    status = db.Column(db.Integer())  
    when = db.Column(db.String(150))  
    cat= db.Column(db.String(150))  
    message  = db.Column(db.String(150))  
    errorvar = db.Column(db.String(150))   
    errormsgvar = db.Column(db.String(150))  
    # syntaxhistory_id = db.Column(db.String, db.ForeignKey('syntax.filename')) 
    def __repr__(self):
        return '<Job %r>' % (self.job)

Answer 1

下面的查询会返回你想要的数据，结果是一个包含元组的 列表，每个元组的格式是 (Syntax, max_start0, max_start1)：

q = (db.session.query(Syntax,
        func.max(case([(Jobs.status == 0, Jobs.start)])).label("max_start0"),
        func.max(case([(Jobs.status == 1, Jobs.start)])).label("max_start1"),
        )
    .outerjoin(Jobs)
    .group_by(Syntax)
    )

如果你只想要 filename（而不是整个 Syntax 实例），只需在 query(...) 和 group_by(...) 中将 Syntax 替换为 Syntax.filename。