Feature Request - Support Dynamic Where Clauses for Query

[vitulrana]

Hi Team,

We need a Feature in NORM where we don't have to write multiple queries for multiple cases/filters which we want rather NORM should be able to take decision on its own that if Given Param is NULL/Empty is some case it should not add its Column in WHERE clause.

For Example -

We want to have a Users API where we want to support filtering by name, address_line, city, state and zip_code.

Current Solution -

We have to write multiple queries for each case like

SELECT * FROM users WHERE name = 'XXXX';
SELECT * FROM users WHERE address_line = 'XXXX';
....
....
SELECT * FROM users WHERE zip_code = 'XXXX';

And then if we need combination we will need combinations also, then we would few more queries like

SELECT * FROM users WHERE zip_code = 'XXXX'; AND city = 'XXXX'; and so on.......

Expected Solution -

NORM should generate query on the fly according to Parameters given to it like below code somewhat -

if (!addressLine.isNullOrEmpty()) then
query = query + WHERE a.address_line = addressLine

if (!city.isNullOrEmpty()) then
query = query + WHERE a.city = city

if (!state.isNullOrEmpty()) then
query = query + WHERE a.state = state

if (!zip_code.isNullOrEmpty()) then
query = query + WHERE a.zip_code = zip_code

[kdabir]

This is one of the very useful feature, however we need to carefully design it since we need to ideally parse the query to be able to produce syntactically correct dynamic query. Just imagine cases like Group By,Order By present at the end of static query written in the file. We will probably create a simple DSL for dynamic queries. Stay tuned.