I recently learned a more intuitive way to query a database, even though not quite as powerful as a select. And so without further I present my implementation of a Query by example.
db:QueryByExample = function(tbl, example)
local topLevel = {}
for key, value in pairs(example) do
value = string.trim(value)
local op, exp = string.match(value, "([<>=!]*)(.+)")
if op == "<" then
table.insert(topLevel, db:lt(tbl[key], exp))
elseif op == ">" then
table.insert(topLevel, db:gt(tbl[key], exp))
elseif op == ">=" then
table.insert(topLevel, db:gte(tbl[key], exp))
elseif op == "<=" then
table.insert(topLevel, db:lte(tbl[key], exp))
elseif op == "!=" or op == "<>" then
table.insert(topLevel, db:not_eq(tbl[key], exp))
else
if string.match(exp, "%s*||%s*") then
table.insert(topLevel, db:in_(tbl[key], string.split(exp, "%s*||%s*")))
elseif string.match(exp, "_") or string.match(exp, "%%") then
table.insert(topLevel, db:like(tbl[key], exp))
else
table.insert(topLevel, db:eq(tbl[key], exp))
end
end
end
return db:fetch(tbl,db:AND(unpack(topLevel)))
end
It is used in a pretty simple way: you fill table with the keys that equal column names and only those that are the example fields. The function will create a SELECT query with the object as WHERE statement, combined by AND.
The fields can be comparison strings as well. Comparisons can be: > (greater), >= (greater or equal), < (smaller), <= (smaller or equal), != or <> (not equal).
If a field contains two pipes (||), it is interpreted as OR for these values.
if a field contains _ or %, it's interpreted as a pattern based value. This pattern is used with a LIKE statment.
Here's an example:
local data = db:create("forgestats",
{
weapon = {
id = 0,
type = "",
short_desc = "",
damage = 0,
tohit = 0,
speed = 0,
price = 0,
_index = {"type"},
_unique = {"id"},
_violations = "IGNORE"
},
})
display(db:QueryByExample(data.weapon, {id = "> 244884"}))