[eluser]tokyotech[/eluser]
I'm trying to select the first 10 free groups. In these 10 resulting rows, I want to a boolean telling me if user #14 is currently a member or not.
Code:
SELECT `group`.*, membership.userId = 14 AS isMember
FROM `group`
LEFT JOIN `membership`
ON membership.groupId = group.groupId
AND membership.userId = 14
WHERE `group`.minReputation = 0
LIMIT 0, 10
This works fine in my PHPMyAdmin. So I try to reproduce this using the active record pattern:
Code:
$this->db->select('group.*, membership.userId = ' . $userId . ' AS isMember');
$this->db->from('group');
$this->db->join('membership', 'group.groupId = membership.groupId', 'left');
$this->db->where('membership.userId', $userId);
$this->db->where('group.minReputation', 0);
$this->db->limit(GROUPS_PER_PAGE, ($page - 1) * GROUPS_PER_PAGE);
$query = $this->db->get();
The problem is, I need the first
where() to be an
AND and the second
where() to actually be a
WHERE. I think CodeIgniter cannot differentiate between the two
where()s, so I'm getting an empty result.