CODEWITHSUNDEEP
pythonflasksqlalchemy
Charlie Morton
Charlie Morton

Reputation: 787

SQLAlchemy filter based on logic

I have a flask app which uses SQLAlchemy. For one of the endpoints '/items' I want to return a list of items.

So that's easy ItemModel.query.all() where ItemModel is the class representing items.

But say I want to allow url query string parameters e.g '/items?type=book'

If I hardcoded it would be simple with TopicModel.query.filter_by(type=book).all()

With multiple it could be TopicModel.query.filter_by(type=book).filter_by(colour=yellow).all()

But how could I efficiently generate that query so that it only filters by a parameter if the user gives a that parameter in the query string?

Should something like this work? If so would it perform multiple requests to the database (and therefore be slow and resource intensive)?

def get(self):
    type = request.args.get('type')
    color = request.args.get('color')
    size = request.args.get('size')

    query = ItemModel.query

    if type:
        query = query.filter_by(type=type)
    if color:
        query = query.filter_by(color=color)
    if size:
        query = query.filter_by(size=size)
    return {'items' : query.all() }

Upvotes: 0

Views: 1045

Answers (2)

krishna
krishna

Reputation: 1029

Please check out this. You should not fetch all until you apply all conditions to the query.

def get(self):
    type = request.args.get('type')
    color = request.args.get('color')
    size = request.args.get('size')

    query_obj = ItemModel.query

    if type:
        query_obj = query_obj.filter_by(type=type)
    if color:
        query_obj = query_obj.filter_by(color=color)
    if size:
        query_obj = query_obj.filter_by(size=size)
    return {'items' : query_obj.all() }

Upvotes: 1

Pandurang Choudekar
Pandurang Choudekar

Reputation: 382

Here is your repaired code. 

def get(self):
    type = request.args.get('type')
    color = request.args.get('color')
    size = request.args.get('size')

    query = ItemModel.query.all()

    if type:
        query = query.filter_by(type=type).all()
    if color:
        query = query.filter_by(color=color).all()
    if size:
        query = query.filter_by(size=size).all()
    return {'items' : query }

Upvotes: 0

Related Questions