With a "partially unknown query", I mean a query which is composed of sub-queries where the original SqlAlchemy sub-query objects are not available / not known to the entity that is working with the query.
Consider the following example: I have a function that produces some query that contains sub-queries, which serves as a basis for specialized queries. An example of such a function could look like this:
def get_base_query(user_id: int) -> Query:
max_reps = get_max_reps_query().subquery()
user_reps = get_user_reps_query(user_id).subquery()
return (
session
.query(
max_reps.c.topic.label('topic'),
(max_reps.c.reps - user_reps.c.reps).label('reps'),
)
.select_from(
max_reps.join(user_reps, max_reps.c.topic == user_reps.c.topic)
)
)
Some other function is going to receive that Query object and wants to extend it. This function only knows the the structure of the input query result (i.e. two columns topic and reps). Say this function needs to extend the query in a way to limit the rows to match a certain topic.
I've tried the following ways to achieve this, but the behavior is as outlined in the comments below:
from sqlalchemy import func as F
def query_filter_topic(query: Query, topic: str) -> Query:
# Doesn't actually filter the rows.
query = query.filter(Exercise.topic == topic)
# Doesn't actually filter the rows.
query = query.filter(query.subquery().c.topic == topic)
# Results in 0 rows.
query = query.filter(F.topic == topic)
...
My hypothesis is that none of these column references match any of the columns of the outer-most SELECT of the query (because they are dynamically created columns). I was really expecting the subquery().c.exercise_name bit to work.
Is there a "correct" way to extend the query? Given that I explicitly labeled the columns in get_base_query(), I feel like there should be a way to reference those columns.
On a side note, I would expect SqlAlchemy to throw an error rather than silently accepting these columns that it apparently can't process. The
filter()calls that doesn't filter rows doesn't seem to change theQueryobject at all (judging by thestr()representation).
The only way I could figure out to get it working is the below, but it doesn't feel like the right way to do it.
def query_filter_topic(query: Query, topic: str) -> Query:
query = query.subquery()
query = session.query(*query.c).filter(query.c.topic == topic)