3 ms·
Depending on what database you're using, you might be able to restructure your dynamic queries to fit a static form... WHERE ... AND CASE WHEN ? IS NULL TH
by k972 2y ago
Depending on what database you're using, you might be able to restructure your dynamic queries to fit a static form...
WHERE
...
AND CASE WHEN ? IS NULL THEN TRUE ELSE col ILIKE ? || '%' END
AND CASE WHEN ? IS NULL THEN TRUE ELSE col2 IN (?) END
Each "dynamic" parameter is then bound twice. (My personal preference is to use a CTE to bind and name my parameter values in the query instead of binding multiple times whenever possible).
- kelnos 2y agoThat only works when the dynamic portion is in the WHERE clause, though. I often need to dynamically generate UPDATEs and even sometimes INSERTs.
- weiznich 2y agoIt doesn't even work for all dynamic where clauses, but only for such that can lead to a fixed number of combinations. This approach doesn't work for dynamic API's that let users combination column + filters as required, which is rather common in complex web applications. (Think of the GitHub issue search box for example)