How do you dynamically add multiple options to a sqlalchemy 2.0 select query

Question

I'm trying to build a dynamic query in Async Sqlalchemy 2.0. The options come in as a list like:

select_options=[selectinload(Table.entitity1).selectinload(Table2.entity2)]

And then I do this:

query = select(model).filter(model.id == item_id).options(select_options[0])
result = await async_session.execute(query)

That works great and I get the correct response. But I want to be able to keep appending options to that statement. I tried this as an example and figured if this worked I could then just loop through select_options and add each one:

query = select(model)
query.filter(model.id == item_id)
query.options(select_options[0])

I didn't get any errors, but the response ignored the options. How can I keep adding on .options().options(), etc.?

Thanks

Answer 1

This should work, filter and options return a new select.

query = select(model)
query = query.filter(model.id == item_id)
query = query.options(select_options[0])

When you get a lot of things though it can be easier to build them up separately and build the select at the end.

and_args = []
if check_flags:
    and_args.append(or_(model.flag1 == True, model.flag2 == False))
if only_roots:
    and_args.append(model.parent_id == None)

query = select(model).filter(*and_args)