There are several ActiveRecord styled query builder libraries out there. Some are stand alone and some come built into frameworks. However, they really have trouble with WHERE and HAVING clauses when it comes to complex SQL. Setting other databases aside - I am trying to come up with a MySQL and PostgreSQL compatible WHERE() method that could fix these current method downfalls.
What follows is a long list of ideas and examples showing the best I could come up with so far. However, I can't seem to solve all of the use cases and I feel my partial solution is sloppy. Anyone that can answer with something that solves all of these problems will not only answer this question - but a will be responsible for fixing a problem that has hunted PHP implementations for several years now.
Common Operators
= Equal
<> Not Equal
> Greater Than
< Less Than
>= Greater Than Or Equal
<= Less Than Or Equal
BETWEEN between values on right
NOT logical NOT
AND logical AND
OR logical OR
Example Where clauses
SELECT ... FROM table...
WHERE column = 5
WHERE column > 5
WHERE column IS NULL
WHERE column IN (1, 2, 3)
WHERE column NOT IN (1, 2, 3)
WHERE column IN (SELECT column FROM t2)
WHERE column IN (SELECT c3 FROM t2 WHERE c2 = table.column + 10)
WHERE column BETWEEN 32 AND 34
WHERE column BETWEEN (SELECT c3 FROM t2 WHERE c2 = table.column + 10) AND 100
WHERE EXISTS (SELECT column FROM t2 WHERE c2 > table.column)
There are many common ActiveRecord formats that the where() clause uses in the different current libraries.
$this->db->where(array('session_id' => '?', 'username' => '?'));
$this->db->fetch(array($id, $username));
// vs with is_int($key)
$this->db->where(array('session_id', 'username'));
$this->db->fetch(array($id, $username));
// vs with is_string($where)
$this->db->where('session_id', '?');
$this->db->where('username');
$this->db->fetch(array($id, $username));
// vs with is_array($value)
$this->db->where('session_id', '?');
$this->db->where('username', array('Sam', 'Bob'));
$this->db->fetch(array($id));
Here is the final format that I have so far. It should handle grouping (...) AND (...) as well as prepared statement bound params ("?" & ":name").
function where($column, $op = '=', $value = '?', $group = FALSE){}
// Single line
$this->db->where('column > 5');
$this->db->where('column IS NULL');
// Column + condition
$this->db->where('column', '=');
// WHERE column = ? (prepared statement)
$this->db->where('column', '<>');
// WHERE column <> ? (prepared statement)
// Column + condition + values
$this->db->where('column', '=', 5);
// // WHERE column = 5
$this->db->where('column', 'IN', '(SELECT column FROM t2)');
// WHERE column IN (SELECT column FROM t2)
$this->db->where('column', 'IN', array(1,2,3));
// WHERE column IN (1, 2, 3)
$this->db->where('column', 'NOT IN', array(1,2,3));
// WHERE column NOT IN (1, 2, 3)
// column + condition + values + group
$this->db->where(
array(
array('column', '<', 20),
array('column', '>', 10)
),
NULL,
NULL,
$group = TRUE
);
// WHERE (column < 20 AND column > 10)
:UPDATE:
Over the course of my question I came to realize that WHERE and HAVING conditions only get more complex the deeper you go. Trying to abstract even 80% of the features would result in a massive library just for WHERE and HAVING. As Bill points out, that just isn't reasonable for a scripting language like PHP.
The solution is just to hand craft the WHERE portion of your query. As long as you use " around your columns you can use the same WHERE query in Postgre, SQLite, and MySQL since they use almost the same SQL syntax. (For MySQL you must str_replace() them with a tick`).
There comes a point where abstraction hurts more than it helps, WHERE conditions are one such place.