Building a dynamic no-fail where clause
I have built an interface, where the user fetches records from the database. User has the option of specifying 0 or more of the filters. There are four filters A, B, C, D (let's say these are the fields in a certain table)
Here's what my query should look like:
select * from table where (A = v1 or B = v2 or C = v3) and D = v4
I am trying to come with a way to formulate the query, whereas when a specific filter is specified, it is applied, if it isn't, it is ignored. But this should hold for all the sixteen cases.
What I have been able to come up with so far are these methods:
select * from table where ( (A = v1 and 1) or (B = v2 and 1) or (C = v3 and 1) ) and D = v4
v1 or other values are set to -1 when they are not specified. So in case they are not specified, they're simply ignored, because then the other filter (from among A, B, C) is used. But this fails in the case when none of A, B, and C are specified. In that case, false is Anded with D, and D is not applied.
Is there a way to come with a where clause for this case? I am open to programmatic solutions to this one as well, where I add or not add clauses through code, but I would prefer it this way. And I would really not want to have a lot of if-else statements.
What about using case construct
select * from table where (A = CASE WHEN v1 IS NOT NULL THEN v1 else '' END) OR (B = CASE WHEN v2 IS NOT NULL THEN v2 else '' END) OR (C = CASE WHEN v3 IS NOT NULL THEN v3 else '' END) OR (CASE WHEN v1 is null and v2 is null and v3 is null then 1 else 0 end) and D = v4
if v1-v4 are the values you're searching for and all of them are -1 if not specified, you can do this:
SELECT * FROM table WHERE ( (A = v1 OR -1 = v1) or (B = v2 OR -1 = v2) or (C = v3 OR -1 = v3) ) AND (D = v4 OR -1 = v4)
Databases have a hard time optimizing dynamic where clauses. Typically, they'll produce a plan that's optimal for the first invocation. So if your first search is for filter A and B, the query plan will be optimized for that. The next query will also use that plan, even if it's using filters C and D. Adding where clauses in code tends to perform much better.
But it is possible, for example:
where ( A = @FilterAValue or B = @FilterBValue or C = @FilterCValue ) and D = coalesce(@FilterDValue, D)
And then you can toggle the filters with the FilterXValue parameters. If the filter for A, B or C is null, the other parts of the or will still be evaluated. A = null or B = 1 is the same as unknown or B = 1 which is only true when B = 1.