I have a table containing Posts, with a number of columns. id, title, author, tags, timestamp

I’m trying to use SQLAlchemy to sort/filter these posts by each of the columns. For example, a user can decide they want to see all posts from author, with tags a, b, c, ordered by timestamp descending.

Is there a way to dynamically build a query starting from a simple sqlalchemy.select()?

For example:

import sqlalchemy as sa

query = sa.select(models.Post)
if userProvidedAuthor:
    query.filter(models.Post.author == userProvidedAuthor)
if userProvidedOrder:
    if userProvidedDirection == 'asc':
        query.order_by(userProvidedOrder.asc())
    else:
        query.order_by(userProvidedOrder.desc())

results = session.execute(query)

Obviously this example won’t run, but I hope it illustrates what I’m trying to do.

• Create a Select
• ‘append’ some filters to it
• ‘append’ some order_bys to it
• run the query

Some things I’ve considered:

• Running raw SQL that I generate on the fly based on user input. Not ideal for obvious reasons.
• A massive, ugly chain of if statements for every possible combination of filters/order_by’s/directions
• Creating a list of separate queries based on the filter inputs, run them all, somehow combine the results and prune duplicates if that isn’t done already, then display those
• Scrapping this entire project and starting from scratch

Any ideas/help would be appreciated. For context, I’m trying to build a blog site in Flask as a learning project.