Welcome Guest, Not a member yet? Register   Sign In
Suggestion/Discussion for Active Record WHERE-clause grouping feature
#1

[eluser]OverZealous[/eluser]
I develop the DataMapper OverZealous Edition library, which is an ORM based on Active Record.

One of the features I was always needing was the ability to group query elements in parentheses to get accurate results with complex queries. For example, this query:
Code:
SELECT * FROM table
WHERE
  table.a = 'value1' OR
  table.b = 'value2' AND
  table.c = 'value3'
Will return a completely different result set than this query:
Code:
SELECT * FROM table
WHERE
  (table.a = 'value1' OR table.b = 'value2') AND
  table.c = 'value3'

I ended up solving this within DMZ successfully, fairly easily, by:
1) Adding parentheses onto the ar_where array directly.
2) Adding a boolean flag to keep track of whether or not an open parenthesis was just added. ($_where_group_started in DMZ.)
3) Using that flag to prevent adding an AND or an OR to the query. Alternatively, just checking the last added item to the ar_where array would also work. (See _get_prepend_type($type) in DMZ.)
4) Eliminating the ar_like array, because for some reason it prevents adding LIKE clauses inline with non-LIKE clauses.

It didn't take very long, and it the concept works perfectly without relying on DMZ (except, my code is embedded in DMZ, of course).

The added public methods are: group_start, or_group_start, not_group_start, or_not_group_start, group_end.

The code to generate the grouped query using the AR-like methods in DMZ just has two extra methods:
Code:
$this->db
    ->group_start()
        ->where('table.a', 'value1')
        ->or_where('table.b', 'value2')
    ->group_end()
    ->where('table.c', 'value3');

All of the new code can be seen by downloading DMZ, and looking under src/25_activerecord.php. (There's a little too much to manually post it all.)

My questions are: Is this something that anyone else would like? Should this kind of code be added to the core AR library?

I am aware that not everyone wants to use an ORM, so hopefully this will be a useful suggestion.

---------------------------------------------

The most surprising element to me was that LIKE clauses are added out-of-sync with the normal WHERE clauses, so that even with the current code unexpected queries might be generated. For example:
Code:
$this->db
    ->where('a', 'value1')
    ->or_like('b', 'value2')
    ->where('c', 'value3');
This should generate code like:
Code:
SELECT * FROM table
WHERE
    a = 'value1' OR
    b LIKE '%value2%' AND
    c = 'value3'

But instead generates this, with the LIKE clause tacked on at the end:
Code:
SELECT * FROM table
WHERE
    a = 'value1' AND
    c = 'value3' OR
    b LIKE '%value2%'

This generates a completely different result set than what might have been intended.
#2

[eluser]jedd[/eluser]
Hi Phil,

This looks like a fantastic idea.

ObDisclaimer - I don't use an ORM, and rarely use the CI AR methods. The latter is partly due to the difficulty of encoding complex queries, such as you're describing here. I keep wanting to migrate back to the fold, so to speak, so anything that helps with that would be very well received.

I have a couple of thoughts:

- 'group' is a pretty overloaded term already (but I can't think of a better one right now!) especially as it has both verb and noun forms
- normal group_start's imply an AND (as per normal CI AR qualifiers) ... is that right?
- I'm wondering if it's too big a goal to try to combine sub-grouping with sub-queries using a similar approach to what you've done here?
#3

[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!
#4

[eluser]Keeshond Loekie[/eluser]
I like it and already have a need for this. My current option would be to create the SQL statement manually, but I rather keep everything AR and this way we have more control over the or and etc order. How do you include your AR functions into a program?
#5

[eluser]OverZealous[/eluser]
@Keeshond Loekie

That's kind of the purpose of this discussion. Because CI's built-in AR doesn't already contain support for query grouping, I am proposing that it gets added in.

The code I use for query grouping in DMZ is not something that can simply be added to CI's AR. It requires a small modification of all of the AR methods to not add binary operators right after a group has been started, or you end up with:
Code:
SELECT * FROM table
WHERE `a` = 1 AND ( AND `b` = 2 OR `c` = 3)

It also requires eliminating the dedicated $ar_like array, which isn't necessary.

DMZ can get away with this because it overrides every AR methods internally (to allow for automatic relationship and table name additions).
#6

[eluser]BrianDHall[/eluser]
I think it would be great to have sepparate from DMZ, Overzealous, because if any of us have existing projects before starting with DMZ (I have one) or if for some reason you start a new project without it this is functionality I think would be universally useful.

<useless-comment>
$this->db->left_bendy()->where()....->right_bendy();

Wink
</useless-comment>
#7

[eluser]broofa[/eluser]
I know this thread is a bit aged, but would like to chime in with a couple comments. First, for the record, here's the specific use case I have where I need the proposed feature, and that led me to find this thread:

I'm writing a business directory app (essentially a community-specific yellow pages app) that allows users to search for businesses in the database and view a Google map of the results. When generating the map data, I want to only display the intersection of businesses that match the search criteria and that also have the necessary geocode information used to map their location. Thus, my WHERE clause has to look something like this:

Quote:"WHERE geocode != '' AND (name LIKE '%search_term%' OR description LIKE '%search_term%)'

Obviously w/out the parens I get a different (and incorrect!) set of results than with them. What's important about this is that the where() call is in a different method than the like() and or_like() calls. So juggling the ordering of the these phrases is problematic... and not really the best solution anyway. OverZealous' proposed API is definitely the preferred approach.

Nor do I see this as a particularly unusual case - I think this is a core feature that really needs to be added to the CI ActiveRecord api.

Regarding the naming, I agree that 'group' is a bit overused, especially with the already existant (and unrelated) group_by() method. But the only names I can think of are open_paren()/close_paren(), or paren()/nerap(. And I doubt those are any better.
#8

[eluser]mikedfunk[/eluser]
I was just searching the forums for this capability today and came across this thread. Another vote for condition grouping in Active Record. I installed DataMapper in the mean time but I can't seem to figure out how to change a query to the datamapper version.
#9

[eluser]danmontgomery[/eluser]
I think active record is the area of CI that needs the most work (and am definitely pro-this), but this is relatively easily worked around:

Code:
$this->db->where( 'field_a', 'value' )->where( '(field_b LIKE "%value%" OR field_c = "value")', NULL, FALSE )->get( 'table' );




Theme © iAndrew 2016 - Forum software by © MyBB