CODEWITHSUNDEEP
pythonpython-3.xflasksqlalchemy
knusperwurst
knusperwurst

Reputation: 155

SQLAlchemy search using varying keywords

Im struggling at a very specific problem here: I need to make a LIKE search in SQLAlchemy, but the amount of keywords are varying. Heres the code for one keyword:

search_query = request.form["searchinput"]
if selectet_wg and not lagernd:
    query = db_session.query(
        Artikel.Artnr,
        Artikel.Benennung,
        Artikel.Bestand,
        Artikel.Vkpreisbr1
    ).filter(
        and_(
            Artikel.Benennung.like("%"+search_query+"%"),
            Artikel.Wg == selectet_wg
        )
    ).order_by(Artikel.Vkpreisbr1.asc())

"searchinput" looks like this : "property1,property2,property3", but also can be just 1,2,5 or more propertys. I want to split the searchinput at "," (yes i know how to do that :) ) and insert another LIKE search for every property. So for the above example the search should be looking like this:

search_query = request.form["searchinput"]
if selectet_wg and not lagernd:
    query = db_session.query(
        Artikel.Artnr,
        Artikel.Benennung,
        Artikel.Bestand,
        Artikel.Vkpreisbr1
    ).filter(
        and_(
            Artikel.Benennung.like("%"+search_query+"%"), #property1
            Artikel.Benennung.like("%"+search_query+"%"), #property2
            Artikel.Benennung.like("%"+search_query+"%"), #property3
            Artikel.Wg == selectet_wg
        )
    ).order_by(Artikel.Vkpreisbr1.asc())

I dont think its a smart idea just to make an if statement for the amount of propertys and write down the query serveral times... Im using the newest version of sqlalchemy and python 3.4

Upvotes: 2

Views: 1808

Answers (2)

TobiMarg
TobiMarg

Reputation: 3797

It should be possible to create a list of you like filters and pass them all to and_.
First create a list of the like queries:

search_queries = search_query.split(',')
filter = [Artikel.Benennung.like("%"+sq"%") for sq in search_queries]

Then pass them to and_, unpacking the list:

and_(
     Artikel.Wg == selectet_wg,
     *filter
    )

*filter has to be the last argument to and_ otherwise python will give you an error.

Upvotes: 4

r-m-n
r-m-n

Reputation: 15090

You can call filter multiple times:

search_query = request.form["searchinput"]
if selectet_wg and not lagernd:
    query = db_session.query(
        Artikel.Artnr,
        Artikel.Benennung,
        Artikel.Bestand,
        Artikel.Vkpreisbr1
    ).filter(Artikel.Wg == selectet_wg)

    for prop in search_query.split(','):
        query = query.filter(Artikel.Benennung.like("%"+prop+"%"))

    query = query.order_by(Artikel.Vkpreisbr1.asc())

Upvotes: 2

Related Questions