Apply multiple filters one at a time in query

Question

I want to apply filter in SQL query but in any order. What i mean is that if i have three parameters name para1, para2 and para3 and i want to apply only para1 and para3 parameters but do not want to apply para2 parameter in query or I want to apply only para3 parameter but don't want to apply para1 and para2 parameters in query.

In attached image if i want to apply filer on assigned_user_id and do not want to apply filer on Status or Dept column than result should show record filter on first parameter only.

Please help.

Need some sample data and expected output with your try! Check this [sample question](https://stackoverflow.com/q/53372997/7124761) — Prashant Pimpale, Sep 17 '19 at 05:41
@PrashantPimpale if any solution is already available in stack overflow please share link. — Syed Imran Shah, Sep 17 '19 at 05:44
Question is not clear! Request you to read the question again! — Prashant Pimpale, Sep 17 '19 at 05:45
How that *para1 2 3 looks* like? Where do you want to pass it? Is it in Stored Procedure? AdHoc query? What you will get by passing those parameters (in terms of results)? — Prashant Pimpale, Sep 17 '19 at 05:46
as per your question, It seems you apply parameter using dynamic query based on condition. — Bhargav J Patel, Sep 17 '19 at 05:47
@PrashantPimpale yes it is stored procedure which takes three parameters. — Syed Imran Shah, Sep 17 '19 at 05:49
@BhargavJPatel how to write dynamic query and apply filters of those parameters which do have value. If a parameter don't have value than it should not be applied. — Syed Imran Shah, Sep 17 '19 at 05:51
Check this:https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure — Prashant Pimpale, Sep 17 '19 at 05:57
[Why images are not good while posting a question](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question) — Prashant Pimpale, Sep 17 '19 at 05:58
@PrashantPimpale thank you for sharing useful link. It resolve my issue. — Syed Imran Shah, Sep 17 '19 at 06:23

score 2 · Answer 1 · answered Sep 17 '19 at 06:15

I think what you want is to have a stored proc where you pass 3 params p1,p2,p3, and one or more of them might be null. I will leave how to pass null for an unused parameter from whatever your caller application is up to you.

The way to do the sql selection, is this.

where
   (@p1 is null null or column1=@p1)
   and (@p2 is null or column2=@p2)
   and (@p3 is null or column3=@p3)

score 0 · Answer 2 · answered Sep 17 '19 at 06:13

Let ruby language for example. If you have any expressions to filter, you can manipulate with its as a strings:

conditions = {
  para2: '> 0',
  para3: '= 0',
  para4: '!= 100500',
}

so, you can collect them to where clause:

where_clause_rules = conditions.map do |key, value|
  "#{key} #{conditions[key]}"
end

where_clause = "WHERE #{where_clause_rules.join(' AND ')}"

and you have a result:

WHERE para2 > 0 AND para3 = 0 AND para4 != 100500

add this string to your sql:

SELECT * 
FROM your_table
#{where_clause}

you give result:

SELECT * 
FROM your_table
WHERE para2 > 0 AND para3 = 0 AND para4 != 100500

run sql:

Activerecord::Base.connection.execute(sql)

enjoy

This will not help! – Prashant Pimpale Sep 17 '19 at 06:24 — Prashant Pimpale, Sep 17 '19 at 06:24

Apply multiple filters one at a time in query

2 Answers2