Castwide Developer's Resource

Dynamic SQL Queries

In most of my web development, I use parameterized queries instead of a full-blown ORM for databases. Even though I'm a big proponent of OOP, most ORMs require too much boilerplate for my taste, and I like to have easy access to manipulate SQL queries directly (granted, of course, the benefit of parameterization to secure queries against SQL injection). A recent project, however, has proven that writing query strings by hand can become more of a pain than it's worth.

One of the pages in the application requires a base SQL query with four joins and a grouping. If the user is not an administrator, it requires an additional join and a where clause to limit access only to data that the administrator permits. The logic to generate the query string quickly turned into spaghetti code. I eventually broke it into two separate queries, one for administrators and one for everyone else. This implementation requires me to maintain query changes in two different places, even though the only difference between them is one more join clause and one more where clause for non-administrative users.

My new solution is a helper class that generates dynamic SQL SELECT queries. I've seen similar code in other applications and libraries, but couldn't find one that suited all of my needs, so I wrote a quick one of my own. The entire library is about 200 lines of code, including a base class with some common functionality that would be useful for other query types (INSERT, UPDATE, etc.).

The BuildSqlSelect class works like this:

$select = new BuildSqlSelect();
$select->table('user AS u');
$select->field('u.userid', 'u.username', 'u.email');
$select->where('u.online = ?', 1);
$select->order('u.username');
$select->innerJoin('usergroup AS ug', 'u.usergroupid = ug.usergroupid');
$select->where('ug.usergroupname != ?', 'admin');
echo $select->output();
/* SQL output:
	SELECT u.userid, u.username, u.email
	FROM user AS u
	INNER JOIN usergroup AS ug ON u.usergroupid = ug.usergroupid
	WHERE u.online = 1 AND ug.usergroupname != 'admin'
	ORDER BY u.username
*/

The biggest advantage to BuildSqlSelect is the fact that it automatically puts clauses in the correct order regardless of the order in which they were generated. This makes it much easier to modify queries based on conditions compared to building it as a simple string.

I'll continue looking for libraries that might be more robust than this one, but I'd prefer something with similar functionality, syntax notwithstanding.

More News

Comments