CODEWITHSUNDEEP
pythonsqlsqlalchemyflask-sqlalchemy
nos
nos

Reputation: 20872

better way to write queries with 2 selection criteria

I am writing a web app where database query comes from two selection boxes, one is about sex and the other is about class_type. In my code, I have a base query which has the following form

q = User.query.with_entities(*(getattr(User, col) for col in cols))

And depending on whether sex and class_type are chosen to be all, I break the cases as follows

if sex=='all' and class_type=='all':
    rows = q.all()
elif sex=='all' and class_type!='all':
    rows = q.filter_by(class_type=class_type)
elif sex!='all' and class_type=='all':
    rows = q.filter_by(sex=sex)
else:
    rows = q.filter_by(sex=sex, class_type=class_type)

Is there a better way to write this logic?

Upvotes: 1

Views: 59

Answers (1)

xli
xli

Reputation: 1308

You could do this:

filters = {}
if sex != 'all':
    filters['sex'] = sex
if class_type != 'all':
    filters['class_type'] = class_type
rows = q.filter_by(**filters)

You would only need one condition per attribute, and an empty dictionary for filters would result in a query with no WHERE clause.

Upvotes: 1

Related Questions