不执行表达式的SQLAlchemy first（）

Question

我无法想象，如何将此SQL代码转换为SQLAlchemy语句：

SELECT TaskID
FROM TaskPushes p
WHERE PushToEmployeeID = 5
AND PushDate = (
   SELECT TOP 1 PushDate
   FROM TaskPushes
   WHERE TaskID = p.TaskID
   ORDER BY PushDate desc
)

不知道如何在这里使用alias并在不执行嵌套查询的情况下调用first()。 以下是我的简化模型：

^{pr2}$

多亏了iLoop，我现在有了以下代码：

pushes_alias = aliased(TaskPush)
date_query = Query(TaskPush.date, session=self.session).filter(TaskPush.task_id == pushes_alias.task_id).\
                    order_by(TaskPush.date.desc()).limit(1)

employee_tasks_query = Query(pushes_alias.task_id, session=self.session).\
   filter(TaskPush.push_by_employee_id == 5).\
   filter(pushes_alias.date == date_query.subquery()).\
   all()

哪个执行以错误结束：returned no FROM clauses due to auto-correlation

Answer 1

这个怎么样？在

>>> p = aliased(TaskPush)
>>> q_inner = (Query(TaskPush.date)
...            .filter(TaskPush.task_id == p.task_id)
...            .order_by(TaskPush.date.desc())
...            .limit(1))
... 
>>> print q_inner
SELECT "TaskPushes"."PushDate" AS "TaskPushes_PushDate" 
FROM "TaskPushes", "TaskPushes" AS "TaskPushes_1" 
WHERE "TaskPushes"."TaskID" = "TaskPushes_1"."TaskID" ORDER BY "TaskPushes"."PushDate" DESC
 LIMIT :param_1

>>> q_outer = (Query(p.task_id)
...            .filter(p.push_to_employee_id == 5)
...            .filter(p.date == q_inner.subquery()))
>>> print q_outer
SELECT "TaskPushes_1"."TaskID" AS "TaskPushes_1_TaskID" 
FROM "TaskPushes" AS "TaskPushes_1" 
WHERE "TaskPushes_1"."PushToEmployeeID" = :PushToEmployeeID_1 AND "TaskPushes_1"."PushDate" = (SELECT "TaskPushes"."PushDate" 
FROM "TaskPushes" 
WHERE "TaskPushes"."TaskID" = "TaskPushes_1"."TaskID" ORDER BY "TaskPushes"."PushDate" DESC
 LIMIT :param_1)

博士，你想要^{}

另一种表述方式是使用SELECT MIN(PushDate) ...，而不是{}。在