Grouping where statements in Active Record - working example - what do you think?

[eluser]Jon L[/eluser]
Keep in mind that I'm developing with a copy of the AR class that I have extracted from CI SVN, and I am not working within CI, which is why you'll see me accessing the AR class differently.

Anyways, I've seen others requesting the ability to group WHERE statements, and I've wanted a solution for this as well. After working on this headache for 2 solid days, I have a solution that works well.

* another note, the build_query method is an implementation to compile queries without executing, so I can test more easily

Example code:

Code:

<?php
$mysqli->where('test', 'value');
$mysqli->where('test2', 'value2');
$mysqli->group_where();
$mysqli->where('test4', 'value4');
$mysqli->group_where_or();
$mysqli->where('test5', 'value5');
$mysqli->where('test6', 'value6');
$mysqli->ungroup_where();
$mysqli->or_where('test7', 'value7');
$mysqli->ungroup_where();
$mysqli->where('test8', 'value8');

echo $mysqli->build_query('test');
?>

Result:

Code:

SELECT *
FROM (`test`)
WHERE `test` = 'value' AND `test2` = 'value2' AND (`test4` = 'value4' OR (`test5` = 'value5' AND `test6` = 'value6') OR `test7` = 'value7') AND `test8` = 'value8'

Thoughts anyone?
The only issue I have currently, is how to deal with the where cache, because ar_where now stores a multi-dimensional array, which could pose problems if someone is starting/stopping cache & mixing that with different calls to group_where()

The good thing about these changes, is that I should now be able to add in support for sub-selects, nested joins, etc...


In my custom copy of AR, I've also addressed the following issues:
- Incorrect processing of strings passed to SELECT (old behavior just explodes on commas without checking for parenthesized statements)
- The ` mark is not added to tables/fields that are within parentheses
- update/delete didn't properly build WHERE statements (ignored LIKE)
- Added new methods between(), not_between()
- Added new method where_not()


I've also corrected some duplicate logic issues regarding the separation of WHERE and LIKE, when really LIKE should fall under WHERE and should not be stored separately (makes building of WHERE statements much easier).

Any feedback/suggestions is appreciated. I intend to contribute this code back to the community once I'm sure there are no issues that my changes introduce.

[eluser]Jon L[/eluser]
btw, it seems to me that calling this class Active Record is misleading, as it doesn't fit the AR pattern... This class is just a query builder

Hopefully EllisLab corrects that logic in a future release.

[eluser]xwero[/eluser]
Edit : warning, this is a too much ideas post. Try to filter out what you can use


You can replace the default database specific driver with your own by renaming the driver directory and files. Maybe instead of a boolean to bind the AR/query builder class use a string where an empty string is no AR, the string ci is the AR class and another string is a third party class.

There are some third classes that have the possibility to add parenthesis to the where part but most of them are part of a replacement database class.

About your snippet. I like the visual implementation of the parenthesis but ungroup_where seems inconsistent with group_where_or if you have the pair group_where-ungroup_where.

But i wonder if named groups could be even more recognizable

Code:

$this->db->where_group(array('test5'=>'value5','test6'=>'value6'),'or_sub');
$this->db->where_group(array('test4'=>'value4','test7'=>'value7','or_sub'));
echo $mysqli->build_query('test');

The idea is to form the sub groups first, if there are any, catch them in an array and inject them in their parent group. Adding several wheres in the groups is a bit ugly because there is no separation between a normal where and a grouped where.

[eluser]Jon L[/eluser]
Thanks for feedback, and just a warning, I'm writing this while working so I may not have a fully coherent post ;-)

Re: named groups - I thought about using named groups, but I'm not sure now I want to redo the logic :-)

Re: _or consistency - maybe instead allow where_group('OR')? But I'm not a fan of allowing strings unless absolutely necessary.

Really, while I like the result, and the behind-the-scenes "magic" that I conjured up, I don't like the visual implementation either, as mixing the where_group() calls in and out, looks ugly and throws off the visual code flow.

To give you an idea of how this method is implemented, there is a multi-dimensional array that is built on-the-fly as group_where() gets called. The "level" (read: depth) is sync'd with the group/ungroup calls.
When adding a new where() statement, the statement gets inserted into the last occurrence of the array (while considering depth).

Obtuse eh? :-D

Here's the behind-the-scenes snippet, input is appreciated.
Notes: _add_where() is now used by _where(), _like(), _between(), etc.
_where_level is established as having a value of 0 to begin with.
cache for the where array is currently not implemented.

When a group_where() is called, it inserts an empty array(), which holds the place for subsequent where string injections.

And just a note, the key for each array occurrence, is either AND, or OR, which is used to determine what value should precede a generated () section.

Code:

/**
     * Group Where
     *
     * Alias of group_where_and(), increments the depth
     *
     * @access    public
     * @return    none
     */
    public function group_where()
    {
        $this->group_where_and();
    }

    // --------------------------------------------------------------------

    /**
     * Group Where (and)
     *
     * Increments the depth
     *
     * @access    public
     * @return    none
     */
    public function group_where_and()
    {
        $this->_group_where('AND');
    }

    // --------------------------------------------------------------------

    /**
     * Group Where (or)
     *
     * Increments the depth
     *
     * @access    public
     * @return    none
     */
    public function group_where_or()
    {
        $this->_group_where('OR');
    }

    // --------------------------------------------------------------------

    /**
     * Group Where
     *
     * Increments the depth
     *
     * @access    protected
     * @param string $type
     * @return    none
     */
    protected function _group_where($type = 'AND')
    {
        $this->_add_where($type);
        $this->_where_level+=2;
    }

    // --------------------------------------------------------------------

    /**
     * UnGroup Where
     *
     * Decrements the depth
     *
     * @access    public
     * @return    none
     */
    public function ungroup_where()
    {
        if($this->_where_level>=2) $this->_where_level-=2;
        else $this->_where_level = 0;
    }

    // --------------------------------------------------------------------

    /**
     * Adds an entry to the WHERE array
     *
     * @todo Need to solve cached WHERE dilemma
     *
     * @access protected
     * @param string $type AND,OR
     * @param mixed $condition String or Array to be used for nesting
     * @return none
     */
    protected function _add_where($type, $condition = array())
    {
        $type = trim($type);

        $arr =& $this->_get_last($this->ar_where, $this->_where_level);
        array_push($arr, array($type, $condition));

        /*if ($this->ar_caching === TRUE)
        {
            $arr =& $this->_get_last($this->ar_cache_where, $this->_where_level);
            array_push($arr, array($type, $condition));

            $this->ar_cache_exists[] = 'where';
        }*/
    }

    // --------------------------------------------------------------------

    /**
     * Gets the last entry in an array, recursion determined by level
     *
     * @access protected
     * @param array $arr Referenced array to search
     * @param integer $level How many levels to recurse
     * @return mixed Returns a reference to the entry that is found
     */
    protected function &_get_last(&$arr, $level=0)
    {
        if($level==0 || (is_array($arr) && empty($arr)) || !is_array($arr)) {
            return $arr;
        }

        $pos = count($arr);
        if($pos>0) $pos--;

        $level--;
        return $this->_get_last($arr[$pos], $level);
    }

    // --------------------------------------------------------------------

    /**
     * Flattens a multi-dimensional array down into a string
     * Currently built specifically for use on building WHERE string
     *
     * @todo Need to make this method generic enough that it can be used for JOINs, etc.
     *
     * @access protected
     * @param array $arr
     * @return string
     */
    protected function _flatten($arr)
    {
        $return = '';
        foreach($arr AS $entry)
        {
            list($k, $v) = $entry;

            if(!empty($v))
            {
                if(!empty($return) && is_string($k))
                {
                    $return .= ' ' . $k . ' ';
                }

                if(is_array($v))
                {
                    $return .= '(';
                    $return .= $this->_flatten($v);
                    $return .= ')';
                }
                else
                {
                    $return .= $v;
                }
            }
        }
        return $return;
    }