Suggestion/Discussion for Active Record WHERE-clause grouping feature

[eluser]OverZealous[/eluser]
@jedd

I know that the term group is really overloaded, but I wanted something that made sense. And open_parentheses takes too long to type. ;-) I did spend some time thinking it through before adding it to DMZ. At one time I thought of using where_group_start/end, but again, that is very long.

Yes, group_start implies AND, like where, like, etc.

I think sub queries are a great goal, but would be nearly impossible without a massive rewrite of ActiveRecord. Currently, the way AR works is to store each parameter on one of several internal (and shared) arrays, then join them all at once into a query. So, it would be difficult to re-use the AR methods on a subquery, because there would need to be a second copy of those arrays.

This highlights another problem, one I've run into with DMZ. With AR, you have to build and run a query all at once. If you attempt to run a different query while building the first one, you'll get errors. Of course, that's way more obvious when using AR directly, but it sneaks up on you with DMZ, because it looks like you are querying on a specific object.

Anyway, subqueries are something to try for down the road, I would think. You can still embed a subquery manually into an AR query, like so:

Code:

$this->db->where(
    '(SELECT subtable.column FROM subtable WHERE thistable.field = subtable.field) > 5)'
);

But it is, of course, ugly, and kinda negates the benefit of using AR!