is there a cleaner / simpler way to code this?

Question

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.

score 3 · Accepted Answer · answered Apr 09 '15 at 14:48

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.

is there a cleaner / simpler way to code this?

1 Answers1