4 ms·
I would write a SQL function that takes all of those as optional parameters and includes a lot of these: WHERE (_title IS NULL OR title LIKE _title)
by joshuahutt 12y ago
I would write a SQL function that takes all of those as optional parameters and includes a lot of these:
WHERE (_title IS NULL OR title LIKE _title)
AND (_minPrice IS NULL OR price > _minPrice)
AND (_maxPrice IS NULL OR price < _maxPrice)
- ad_hominem 12y agoThis actually answers the question, although I would imagine the ORDER BY handling will look pretty messy (CASE statement perhaps?). And I don't know what it would look like if the requirements changed to allow ordering by multiple columns with different possible sort directions... that might get back into dynamic SQL using a RETURN QUERY EXECUTE type of thing, which is basically using a query builder in your query language.
- babuskov 12y agoMost SQL engines support ordering by column number. select c1, c2, c3, ... order by 2; would order by "c2"
- schrodinger 12y agoThat ends up being very inefficient as the optimizer will come up with one mediocre plan that works for all those criteria.