Welcome Guest, Not a member yet? Register   Sign In
using or_where() on same field
#1

[eluser]blorriman[/eluser]
I am trying to to a where(), or_where() on the same field (groups_members.resign_date). I want to find members who have either not resigned (field is NULL), or who resigned after the start_date.

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

It's not working and I can't seem to find an answer - anyone have a suggestion?
#2

[eluser]WanWizard[/eluser]
After the query, put
Code:
echo $this->db->last_query();
and check what it makes of it.

The issue is probably that you need to add brackets when mixing AND's and OR's in a WHERE clause. Which Active Record can't do.
#3

[eluser]blorriman[/eluser]
I'm not sure why, but by putting the or_where() before the where() seems to work.
Code:
$this->db->where('groups_members.group_id', $this->session->userdata('group_id'));
$this->db->where('groups_members.join_date <=', $end_date);
$this->db->or_where('groups_members.resign_date', NULL);
$this->db->where('groups_members.resign_date >=', $start_date);
$this->db->join('groups_members', 'groups_members.member_id = members.user_id');
$this->db->order_by('last_name', 'asc');
$query = $this->db->get('members');
#4

[eluser]WanWizard[/eluser]
It works, but probably not the way you intend, as the query is still assembled without brackets.

So you query becomes something like "WHERE A = 1 AND B = 2 AND C = 3 OR D = 4". Which means that as long as D = 4 the row is selected, all the other fields are ignored.
Probably not what you intended.
#5

[eluser]blorriman[/eluser]
Well, this is what I'm getting, and it seems to work for members who have not resigned (resign_date IS NULL) as well as for members who resigned after the start_date.
Code:
SELECT * FROM (`members`)
JOIN `groups_members` ON `groups_members`.`member_id` = `members`.`user_id`
WHERE `groups_members`.`group_id` = '81'
AND `groups_members`.`join_date` <= '2010-05-08'
OR `groups_members`.`resign_date` IS NULL
AND `groups_members`.`resign_date` >= '2010-04-28'
ORDER BY `last_name` asc
#6

[eluser]WanWizard[/eluser]
Exactly.

Which would return all records in which group_members.resign_date IS NULL, regardless of the other values.
#7

[eluser]blorriman[/eluser]
You're right, so are you saying this can't be done in Active Record Class?
#8

[eluser]WanWizard[/eluser]
Correct.

Depending on the type of query some tricks work, but you loose automatic escaping and table prefixing, so imho not worth the trouble.

If you dare to modify CI's DB_active_rec.php (I have it extended, but default CI doesn't support that), you can replace the where() and or_where() method by these:
Code:
// --------------------------------------------------------------------

/**
* Where
*
* Our version allows you to add open and close brackets to the where clause
*
* Generates the WHERE portion of the query. Separates
* multiple calls with AND
*
* @access    public
* @param    mixed
* @param    mixed
* @param    mixed
* @return    object
*/
function where($key, $value = NULL, $escape = TRUE, $bracket = NULL)
{
    if ( strtolower($bracket) == 'open' )
    {
        return $this->_where($key, $value, 'AND (', $escape);
    }
    elseif ( strtolower($bracket) == 'close' )
    {
        $result = $this->_where($key, $value, 'AND ', $escape);
        end($this->ar_where);
        $this->ar_where[key($this->ar_where)] .= ')';
        return $result;
    }
    else
    {
        return $this->_where($key, $value, 'AND ', $escape);
    }
}

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

/**
* OR Where
*
* Our version allows you to add open and close brackets to the where clause
*
* Generates the WHERE portion of the query. Separates
* multiple calls with OR
*
* @access    public
* @param    mixed
* @param    mixed
* @param    mixed
* @return    object
*/
function or_where($key, $value = NULL, $escape = TRUE, $bracket = NULL)
{
    if ( strtolower($bracket) == 'open' )
    {
        return $this->_where($key, $value, 'OR (', $escape);
    }
    elseif ( strtolower($bracket) == 'close' )
    {
        $result = $this->_where($key, $value, 'OR ', $escape);
        end($this->ar_where);
        $this->ar_where[key($this->ar_where)] .= ')';
        return $result;
    }
    else
    {
        return $this->_where($key, $value, 'OR ', $escape);
    }
}
These will add a fourth parameter, which can have the value 'open' or 'close'. Omitting it makes them fall back to CI's default behaviour.
#9

[eluser]blorriman[/eluser]
Thanks WanWizard,
I'll take a look at this.
#10

[eluser]WanWizard[/eluser]
Note that this doesn't work on the first ->where() of your query.
To fix that I have to find a bit more time, as that involves a lot more changes, which means a better solution than this hack must be found.




Theme © iAndrew 2016 - Forum software by © MyBB