将SQL引用父子句转换为SQLAlchemy

Question

我基本上是在尝试让这个SQL查询在SQLAlchemy中工作：

SELECT p.id, (SELECT COUNT(*) FROM response r WHERE <conditions on r> AND r.prompt_id = p.id)
  FROM prompt p WHERE p.<conditions>;

除了子查询中对p.id的引用之外，我已经完成了所有的工作：

# used all over application
def filter_prompt(search_string, customer_id, state):
  prompts = Prompt.query
  if search_string:
    prompts = prompts.filter(Prompt.id.ilike('%' + search_string + '%'))
  elif customer_id, state, etc
    ... more filtering ...
  return prompts

我有一个特殊的情况，需要返回相关对象的数量，所以我尝试只添加一个子查询列：

prompts = filter_prompt(...)
count_subquery = session.query(func.count('response')).filter(Response.validated_at>=date, Response.prompt_id == Prompt.id).subquery()
prompts_with_response_count = prompts.add_column(count_subquery)

问题是Prompt.id没有正确地解析为该行的id。如何正确地将上述SQL转换为SQLAlchemy？

Answer 1

如果在select语句中使用子查询，则不会执行subquery but instead doas_scalar` (这会告诉SQLAlchemy将有一个标量结果，一行，一列)：

count_subquery = session.query(func.count('response')).filter(Response.validated_at>=date, Response.prompt_id == Prompt.id).as_scalar()