Notes on Software Development

Tiniest query builder

Personally, I am not a fan of Query Builders, I prefer pure SQL. However, constructing dynamic queries from search forms can be quite tedious:

$sql = 'SELECT * FROM book';
$isFirst = true;
if (isset($_GET['title'])) {
  if ($isFirst) $sql .= ' WHERE ';
  $sql .= ' title = ? ';
}
if (isset($_GET['price'])) {
  if ($isFirst) $sql .= ' WHERE ' else $sql .= ' AND ';
  $sql .= ' price < ? ';
}

There are several issues with this code, first of all it's very verbose. We need to repeat ourselves a lot, for every query parameter we have to write at least two IF statements. So, it's certainly not DRY.

Secondly, we need to make sure we add the padding spaces and we need to keep track of the glue (i.e. do we have to use a 'AND or a WHERE').

Another drawback of this approach is that our query gets chopped up into different pieces scattered throughout the conditionals. That makes it hard to read the original query.

Recently, I created a very, very small Query Builder that solves this issue without having to learn a new syntax.

The idea is to use arrays for the various query parts and pass those arrays to a build function. The book query can then be written like this:

$sql = build_query([
[               'SELECT * FROM book'],
[$title         ,'WHERE','title = ?'],
[$price         ,'AND','price < ?'],
[$order         ,'ORDER BY ? ASC'],
[$limit         ,'LIMIT ?']
]);

Now, if we have a $title and a $price the query will be:

'SELECT * FROM book WHERE title = ? AND price < ? '

If we only have a $price and a $limit:

'SELECT * FROM book WHERE price < ? LIMIT ?'

The Query Builder works very easy, it simply loops through the array, each element is another array inside this main array, let's call this inner array a 'piece'.

A piece can have one, two or three elements.
If it has one element, the element is simply concatenated to the final query.
If a piece has two elements, the second element will be concatenated only if the first evaluates to TRUE.
Finally a piece having three elements works the same as a piece with two elements, except that it will use the glue provided in the second element to concat the value of the third element. The glue acts as a little tube of glue. If there is still glue left in the tube (WHERE) it will preserve this until it can be applied (so the first AND will be ignored in case of a WHERE condition).

Because we can align the conditions to the left and the query parts to the right, we maintain a view on the 'full query' containing all possible clauses. Also we don't have to worry about padding spaces and glue because the function takes care of all that. Yet, we have pure SQL, no new syntax!

Here is the entire source of my query builder:

function build_query($pieces) {
  $sql = '';
  $glue = NULL;
  foreach( $pieces as $piece ) {
    $n = count( $piece );  
    switch( $n ) {
      case 1:
        $sql .= " {$piece[0]} ";
        break;
      case 2:
        $glue = NULL;
        if (!is_null($piece[0])) $sql .= " {$piece[1]} ";
        break;
      case 3:
        $glue = ( is_null( $glue ) ) ? $piece[1] : $glue;
	if (!is_null($piece[0])) { 
		$sql .= " {$glue} {$piece[2]} ";
		$glue = NULL;
	}
        break;
    }
  }
  return $sql;
}

That's just about 20 lines of code! I think this is the tiniest Query Builder ever :)

Edit: 14 May 2016 - Fixed glue bug in Query Builder.

Edit: 16 Jan 2016 - Thanks Francesco for adding support for 0 values in the Query! (Altough I did it a little bit different).

Return to my homepage.

Articles

A collection of articles I have written about software development.

Projects

 



a picture of meGabor de Mooij
Software Developer
Developing in PHP, Javascript and C.