How to build complex query when param is uncertainty?

[nlimpid]

example

select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})

if 'm_score' is empty, then the sql change to

select * from tb where name={{name}}

so is there any method to take out of whole sub query when the param is null.

[caibirdme]

@nunchaju It's better to use two different sqls to meet your needs. Sql should be intuitive, if not, people may get puzzled If write one sql but another is executed. WYSIWYG (what you see is what you get) is very important

if isEmptySlice(m_score) {
   build(sql1, params...)
} else {
   build(sql2, params...)
}

[nlimpid]

@caibirdme I see, so the following is more like a question than a issue

if the params is more than two, the combination will be much.

example:

select * from tb where name={{name}} and age > {{age}} and id in (select uid from anothertable where score in {{m_score}}) and city in (select city from tb2 where tb2id = {{city}}

the judgement condition will be like this:

if name == "" && age != 0 && ! isEmptySlice(m_score) && city != "" {
    build(sql1, params...)
} else if name != "" && age != 0 && ! isEmptySlice(m_score) && city != "" {
    build(sql2, params...)
...// maybe n*n
}

[caibirdme]

Isn't it weird that you want to search the information for someone whose name={{name}} while the {{name}} is an empty string? If you ignore this comparison, it's very likely to be wrong. Queries with different conditions are absolutely different, they should be separated

[xurwxj]

@nunchaju @caibirdme i forked to add a DynQuery for dynamic sql generate.

https://github.com/xurwxj/gendry