using or_where() on same field

[eluser]WanWizard[/eluser]
[cut]

I was a bit premature with a solution. Due to the fact that where and like clauses are stored separately, you close the proper sequence if you mix and match where() and like(), causing the brackets to mismatch...

[eluser]WanWizard[/eluser]
This is a better one, to be used like this:

Code:

$this->db->where('groups_members.group_id', $this->session->userdata('group_id'));
$this->db->where('groups_members.join_date <=', $end_date);
$this->db->bracket('open');
$this->db->or_where('groups_members.resign_date', NULL);
$this->db->where('groups_members.resign_date >=', $start_date);
$this->db->bracket('close');
$this->db->join('groups_members', 'groups_members.member_id = members.user_id');
$this->db->order_by('last_name', 'asc');
$query = $this->db->get('members');

Method chaining is supported for this method as well. The code I use now in my MY_DB_active_rec.php:

Code:

/**
* variable to store the requested WHERE clause bracket state
*/
var $ar_bracket_open            = FALSE;
var $last_bracket_type        = 'where';

/**
* Allows you to insert brackets into your WHERE clause
*
* @access    public
* @param    string
* @return    object
*/
function bracket($type = NULL)
{
    if ( strtolower($type) == 'open' )
    {
        $this->ar_bracket_open = TRUE;
    }
    elseif ( strtolower($type) == 'close' )
    {
        // fetch the key of the last entry added
        end($this->ar_where);
        $key = key($this->ar_where);

        // add a bracket close
        $this->ar_where[$key] .= ')';

        // update the AR cache clauses as well
        if ($this->ar_caching === TRUE)
        {
            $this->ar_cache_where[$key] = $this->ar_where[$key];
        }
    }

    return $this;
}

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

/**
* Where
*
* Called by where() or orwhere()
*
* @access    private
* @param    mixed
* @param    mixed
* @param    string
* @return    object
*/
function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
{
    // store this as the last_bracket_type
    $this->last_bracket_type = 'where';

    // call the original method
    $result = parent::_where($key, $value, $type, $escape);

    // do we need to add a bracket open
    if ( $this->ar_bracket_open )
    {
        // fetch the key of the last entry added
        end($this->ar_where);
        $key = key($this->ar_where);

        // was this the first entry?
        if ( $key == 0 )
        {
            // first where clause, simply prefix it with a bracket open
            $this->ar_where[$key] = '(' . $this->ar_where[$key];
        }
        else
        {
            // subsequent where clause, strip the type before adding the bracket open
            $this->ar_where[$key] = $type . ' (' . substr($this->ar_where[$key], strlen($type));
        }

        // reset the bracket state
        $this->ar_bracket_open = FALSE;

        // update the AR cache clauses as well
        if ($this->ar_caching === TRUE)
        {
            $this->ar_cache_where[$key] = $this->ar_where[$key];
        }
    }

    // return the result
    return $result;
}

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

/**
* Like
*
* Called by like() or orlike()
*
* @access    private
* @param    mixed
* @param    mixed
* @param    string
* @return    object
*/
function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '')
{
    // store this as the last_bracket_type
    $this->last_bracket_type = 'like';

    // do we already have entries for the where clause?
    if ( $where_count = count($this->ar_where) > 0 )
    {
        // yes, add a dummy entry to force the $type being added
        $this->ar_like[] = '';
    }

    // call the original method
    $result = parent::_like($field, $match, $type, $side, $not);

    // fetch the key of the last entry added
    end($this->ar_like);
    $key = key($this->ar_like);

    // do we need to add an open bracket
    if ( $this->ar_bracket_open  )
    {
        // was this the first entry?
        if ( $where_count == 0 )
        {
            // first where clause, simply prefix it with a bracket open
            $this->ar_like[$key] = '(' . $this->ar_like[$key];
        }
        else
        {
            // subsequent where clause, strip the type before adding the bracket open
            $this->ar_like[$key] = $type . ' (' . substr($this->ar_like[$key], strlen($type));
        }

        // reset the bracket state
        $this->ar_bracket_open = FALSE;

        if ($this->ar_caching === TRUE)
        {
            $this->ar_cache_like[$key] = $this->ar_like[$key];
        }
    }

    // add the like to the ar_where array to maintain where clause sequence
    $this->ar_where[] = $this->ar_like[$key];
    $this->ar_like = array();

    // return the result
    return $result;
}

Have fun!

[eluser]Armchair Samurai[/eluser]
Another solution, which doesn't require hacking the CI core. Slightly messy, but gets the job done.

Code:

$start_date = $this->db->escape($start_date);
$sql = "(gm.resign_date >= $start_date OR gm.resign_date IS NULL)";

$this->db->join('groups_members gm', 'gm.member_id = m.user_id');
$this->db->where('gm.join_date <=', $end_date);
$this->db->where($sql, NULL, FALSE);
$this->db->where('gm.group_id', $this->session->userdata('group_id'));
$this->db->order_by('last_name', 'asc');

$query = $this->db->get('members m');