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.

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, Apr 09 '15 at 14:43
I'm voting to close this question as off-topic because it is a code review question. — ratchet freak, Apr 09 '15 at 14:43

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

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.

answered Apr 09 '15 at 14:48

Robert Harvey

198,589
55
464
673

this is really neat! i've never used a technique like this. thanks very much – dot Apr 09 '15 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 Apr 09 '15 at 15:00
1

I sometimes do this and make an array of where predicates, then I join the array with `' AND '` – Matthew Apr 09 '15 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 Apr 09 '15 at 16:55

is there a cleaner / simpler way to code this?

1 Answers1