1

I have a web app that uses ajax to filter db results. So if you have a list of 1000 widgets, that have IDs ranging from 1-1000, by typing numbers into the ID field, you can narrow your search. Typing the number 9 will automatically query the db to return only records that have an id that start with 9 etc... The same type of a feature exists for widget names.

Here's what the code looks like in part, as far as querying is concerned: 

get_widgets = function(id, name)
        local sql 
        if name==nil and id==nil then
                sql = "SELECT * FROM widgets"
        elseif addr == nil then
                sql = "SELECT * FROM widgets WHERE id LIKE '"..id.."%'"
        elseif tn==nil then
                sql = "SELECT * FROM widgets WHERE name LIKE '"..name.."%'"
        else
                sql = "SELECT * FROM widgets WHERE id LIKE '"..id.."%' AND name LIKE '"..name.."%'"
        end 

      ... logic to connect to db and execute query.
end

Just wondering if there's a more elegant way to do this. or if what I have is ok.

Improve this question
2
  • If it works as intended then ask on codeReview.SE (it has to work they are very strict about that), if it doesn't then go to SO.
    – ratchet freak
    Commented Apr 9, 2015 at 14:43
  • 3
    I'm voting to close this question as off-topic because it is a code review question.
    – ratchet freak
    Commented Apr 9, 2015 at 14:43

1 Answer 1

Reset to default
3

SQL concatenation applications like this benefit from a technique which I will call "1=1". I don't know Lua, so I'm going to use "pseudo-Lua."

sql = "SELECT * FROM widgets WHERE 1=1 "

if id != nil then
    sql += "AND id LIKE '"..id.."%'"

if name != nil then
    sql += "AND name LIKE '"..name.."%'"

If you still need the elseif exclusivity, the only thing you can really do is start off with SELECT * FROM widgets, and tack on the WHERE clause in the elseif.

Improve this answer
4
  • this is really neat! i've never used a technique like this. thanks very much
    – dot
    Commented Apr 9, 2015 at 14:59
  • +1 Great! I always had to write logic to either concatenate the WHERE keyword or not, depending on the values but it's easier and cleaner this way.
    – Tulains Córdova
    Commented Apr 9, 2015 at 15:00
  • 1
    I sometimes do this and make an array of where predicates, then I join the array with ' AND '
    – Matthew
    Commented Apr 9, 2015 at 15:55
  • @Matthew: That's how I usually do it as well. A quick google says that in LUA 5+ you would use table.concat for this purpose. I usually only use WHERE 1=1 when editing via SMSS or when using dynamic SQL.
    – Brian
    Commented Apr 9, 2015 at 16:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.