CODEWITHSUNDEEP
pythonsqlsqlalchemyflask-sqlalchemy
Tres
Tres

Reputation: 1495

Translate SQL referencing parent clause to SQLAlchemy

I'm basically trying to get this SQL query working in 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>;

I have it all working except for the reference to p.id in the subquery:

# 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

I have a special case where I need to return the number of related objects, so I'm attempting to just add a subquery column:

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)

The problem is that Prompt.id is not properly resolved to the id for that row. How would I properly translate the above SQL to SQLAlchemy?

Upvotes: 0

Views: 230

Answers (1)

javex
javex

Reputation: 7544

If you use a subquery in a select statement, you don't do subquery but instead doas_scalar` (which tells SQLAlchemy that there will be a scalar result, one row, one column):

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

Upvotes: 1

Related Questions