未传递某些参数时，如何执行SQL查询过滤？

Question

I've built a REST endpoint for listing items, /api/items, and it can be passed a few options via query params for filtering the result.

For example, GET /api/items?minPrice=30&maxPrice=100&minRating=4&onSale=true

How can I translate this into my SQL queries?

If I get a request with only maxPrice=50, then what about the other parameters? Let's just assume the following is my query -

select * from items 
where minPrice=(???) and maxPrice=50 and minRating=(???) and onSale=(???)

How do I "ignore" a filter if nothing was requested for it, and only filter on maxPrice=50? Is there a programmatic way to do this?

In my case, I am using postgres, so I have paramterized query with &1, &2, etc. with the values for each one.

Answer 1

You should provide meaningful default values, if the values are not provided in the REST call. e.g.

minValue = 0 
maxValue = <veryHighValue>
minRating = 0
onSale in (true, false)

Or you can start with these default values, and override then with the ones provided from the REST call.