A new way to query the database

Post Reply
User avatar
keneanung
Site Admin
Posts: 94
Joined: Mon Mar 21, 2011 9:36 am
Discord: keneanung#2803

A new way to query the database

Post by keneanung »

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.
Code: [show] | [select all] lua
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:
Code: [show] | [select all] lua
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"}))

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: A new way to query the database

Post by Vadi »

That's an interesting way to do it. The syntax is more intuitive that way for what it supports.

User avatar
keneanung
Site Admin
Posts: 94
Joined: Mon Mar 21, 2011 9:36 am
Discord: keneanung#2803

Re: A new way to query the database

Post by keneanung »

I'm about to add this to the DB.lua file. While writing the Unittests, how deeply should I cover them? Each case once? Combinations?

nawaz
Posts: 1
Joined: Tue Jan 06, 2015 9:41 am

Re: A new way to query the database

Post by nawaz »

this is very nice post
Nawaz

Post Reply