Problem with where and or_where

[eluser]mvdg27[/eluser]
Hi guys,

I'm running into a problem with a query, that has both where statements and or_where statements. Not sure if it's an issue with CI, or that I'm doing something wrong in my query. The case is that I'm selecting menu-items. Now for my 'protected website'-module, I want to select only the items that are linked to the groups the logged-in user belongs to.

This query works fine for the normal menu:

Code:

$this->CI->db->where('active', 1);
$this->CI->db->where('parent', 0);
$this->CI->db->get('table');

For the protected menu, I've added a Join statement and an or_where statement:

Code:

$this->CI->db->where('active', 1);
$this->CI->db->where('parent', 0);

$this->CI->db->join('Defs', 'Defs.item_id = table.id');
$this->CI->db->group_by('id');

// loop through groups this user belongs to
foreach($this->CI->session->userdata('groups') as $group) {
  $this->CI->db->or_where('group_id', $group);
}

$this->CI->db->get('table');

In this code the where statements for 'active' and 'parent' are ignored.

Any idea as to why this happens? And is there a way to fix my query?

Thanks in advance for the input.

Michiel

[eluser]Armchair Samurai[/eluser]
I'm guessing that AR is outputting something like this:

Code:

SELECT *
FROM table
JOIN Defs d ON Defs.item_id = table.id
WHERE active = 1
AND parent = 0
OR group_id = ?
OR group_id = ?
OR group_id = ?
OR group_id = ?
GROUP BY id

but what you probably want is

Code:

SELECT *
FROM table
JOIN Defs ON Defs.item_id = table.id
WHERE active = 1
AND parent = 0
AND
(
    group_id = ?
    OR group_id = ?
    OR group_id = ?
    OR group_id = ?
)
GROUP BY id

The current version of CI doesn't support brackets, so you'll have to write that portion of the query out manually.

Alternately, you might want to look into WHERE... IN, which in this case should give you the same results and a lot cleaner code than a bunch of OR statements

[eluser]mvdg27[/eluser]
You are absolutely right .. wasn't aware that the AR didn't add brackets automatically. Your WHERE .. IN solution actually does it more elegantly anyway!

Thanks