SQLAlchemy - How to filter_by multiple dynamic OR values?

Question

I have a pretty reasonable use case: Multiple possible filter_by matches for a single column. Basically, a multiselect JS dropdown on front end posts multiple company industries to the backend. I need to know how to write the SQLAlchemy query and am surprised at how I couldn't find it.

{ filters: { type: "Industry", minmax: false, value: ["Financial Services", "Biotechnology"] } }

@app.route("/dev/api/saved/symbols", methods=["POST"])
@cross_origin(origin="*")
def get_saved_symbols():
    req = request.get_json()
    # res = None
    # if "minmax" in req["filters"]:
    #     idx = req["filters"].index("minmax")
    #     if req["filters"][idx] == "min":
    #         res = db.session.query.filter(Company[req["filter"]["type"]] >= req["filters"]["value"])
    #     else:
    #         res = db.session.query.filter(Company[req["filter"]["type"]] <= req["filters"]["value"])

    # else:
    res = db.session.query.filter_by(Company[req["filters"]["type"]] == req["filters"]["value"])

    return jsonify(res)

As you can see I am also working on a minmax which is like an above or below filter for other columns like price or market cap. However, the multiselect OR dynamic statement is really what I am stuck on...

`filter_by()` is the wrong tool for this job. Use `filter()` instead. See https://stackoverflow.com/questions/7604967/sqlalchemy-build-query-filter-dynamically-from-dict, and https://stackoverflow.com/questions/8603088/sqlalchemy-in-clause. — Ilja Everilä, Feb 13 '22 at 07:56

score 2 · Answer 1 · answered Jul 13 '22 at 20:21

I ended up creating a separate filter function for this that I can than loop over results with.

I will just show the first case for brevity. I am sending a list of strings in which I create a list of filters and then use the or_ operator imported from sqlalchemy package.

def company_filter(db, filter_type, filter_value, minmax):
    match filter_type:
        case "industry":
            filter_list = []

            for filter in filter_value:
                filter_list.append(Company.industry == filter)

            return db.query(Company).with_entities(Company.id, Company.symbol, Company.name, Company.monthly_exp).filter(or_(*filter_list))
        ...

SQLAlchemy - How to filter_by multiple dynamic OR values?

1 Answers1