python - 我如何在 sqlalchemy ORM 中表达这个查询？

Question

这是我的 SQL 查询:

select survey_spec.survey_spec_id, cr.login as created_by, installed_at, req.login as requested_by, role
from survey_spec
        join (select survey_spec_id, role, max(installed_at) as installed_at from survey_installation_history group by 1, 2) latest using (survey_spec_id)
        left join survey_installation_history using (survey_spec_id, role, installed_at)
        left join users cr on created_by = cr.user_id
        left join users req on requested_by = req.user_id
where survey_id = :survey_id
order by created_at desc, installed_at desc

我有 survey_spec、survey_installation_history 和 users 的 ORM 实体，并且 survey_spec.installations 是一种关系使用 survey_spec_id 作为键来 survey_installation_history。

Answer 1

您有到目前为止所得到的示例输出吗？即输出:

print survey_spec.query.filter(survey_spec.survey_id==survey_id).options(
         eagerload(...))

如果您只想加载实体，您可以绕过 SQL 生成并从给定的 literal SQL 加载。 ，大致如下:

session.query(survey_spec).from_statement("""select survey_spec.survey_spec_id, cr.login as created_by, installed_at, req.login as requested_by, role
from survey_spec
 join (select survey_spec_id, role, max(installed_at) as installed_at from survey_installation_history group by 1, 2) latest using (survey_spec_id)
 left join survey_installation_history using (survey_spec_id, role, installed_at)
 left join users cr on created_by = cr.user_id
 left join users req on requested_by = req.user_id
where survey_id = :survey_id
order by created_at desc, installed_at desc""").params(survey_id=survey_id).all()