CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
XSFg
XSFg

Reputation: 37

Base query to work even if parameters not supplied

I have a search with multiples parameters. Some of them optional. In this example i am only using two, for effect of simplicity.

If the industry_id is None i will have this error:

invalid input syntax for integer: ""
LINE 3: WHERE companies_company.industry_id = ''

pagination = Company.query.filter((Company.industry_id == industry_id) & (Company.size == size)).paginate(
                page, per_page=current_app.config['POSTS_PER_PAGE'],
                error_out=False)

My question is, i need to have multiples if else with all the possible combinations? In this case, a filter with:

if not size:
   Company.query.filter((Company.industry_id == industry_id
elif not industry_id:
   Company.query.filter((Company.size == size
else:
   Company.query.filter((Company.industry_id == industry_id & Company.size == size
    ...

This is horrible to maintain because i have more than 2 options and this implies many combinations of if and elses. Any alternative to keep a base query to work even if a element of filter is none?

Upvotes: 1

Views: 68

Answers (1)

dirn
dirn

Reputation: 20739

You don't have to built your entire query in one line. You can build it as you go.

query = Company.query
if size:
    query = query.filter(Company.size == size)
if industry_id:
    query = query.filter(Company.industry_id == industry_id)
result = query.all()

If you feel like this is too verbose, you could build a list of filters and then pass them all to filter().

filters = []
if size:
    filters.append(Company.size == size)
if industry_id:
    filters.append(Company.industry_id == industry_id)
result = Company.query.filter(*filters).all()

To reduce your typing even further, depending on how you obtain values size and industry_id, you could use a list comprehension.

fields = ('size', 'industry_id')
filters = [getattr(Company, field) == request.form.get(field) for field in fields]
result = Company.query.filter(*filters).all()

Upvotes: 1

Related Questions