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.

Thank you!

Answers


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.


Need Your Help

ExtJS 3.4.0 GridView body and header have different width

gridview extjs extjs3

How can I fit GridView body width to header with? Screencast with bad bahavior is here

is it possible to clear struts2 file input tag?

struts2 tags file-io

I am using Struts2 for a web application development. i have this particular problem for which i couldnt find a solution even after i googled.