Left join database problem

[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.

[eluser]Armchair Samurai[/eluser]
You'll need to include the complete JOIN ON statement in the db->join() call. Try something like this:

Code:

$userId = $this->db->escape($userId);

$this->db->select('g.*');
$this->db->select("m.userId = $userId AS isMember", FALSE);
$this->db->join('membership m', 'm.groupId = g.groupId AND m.userId = '.$userId, 'left');
$this->db->where('g.minReputation', 0);
$this->db->limit(GROUPS_PER_PAGE, ($page - 1) * GROUPS_PER_PAGE);

$query = $this->db->get('group g');

[eluser]tokyotech[/eluser]
Your code gave me this error:

Code:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group g) LEFT JOIN membership m ON m.groupId = g.groupId AND m.userId = 0 WHERE ' at line 2

SELECT g.*, m.userId = 0 AS isMember FROM (group g) LEFT JOIN membership m ON m.groupId = g.groupId AND m.userId = 0 WHERE `g`.`minReputation` = 0 LIMIT 10

So I changed it to this to get rid of the ( ) around "group g":

Code:

$userId = $this->db->escape($userId);

$this->db->select('g.*');
$this->db->from('group g');
$this->db->select("m.userId = $userId AS isMember", FALSE);
$this->db->join('membership m', "m.groupId = g.groupId AND m.userId = $userId", 'left');
$this->db->where('g.minReputation', 0);
$this->db->limit(GROUPS_PER_PAGE, ($page - 1) * GROUPS_PER_PAGE);

$query = $this->db->get();

return $query->result();

Thank you. It works now!

But why did you escape the $userId? I thought I read in the manual that everything gets escaped when it's done through the active record thingy.

[eluser]Armchair Samurai[/eluser]
AFAIK the parenthesis is shouldn't make a bit of difference as CI automatically generates them and I've never had an issue before.

I think the syntax error might be caused by the use of aliases: the interpreter is probably confusing your table `group` for the SQL command GROUP - try removing the aliases and reverting everything back to the full table names.

My bad there - I'm lazy and will type the minimal amount possible.

[edit]

Ah, looks like you figured it out - nevermind.

If you'll notice in the second select(), the second parameter is set to FALSE, so nothing will be escaped. Also, in my experience CI does not escape the second parameter when using join(), hence the need to escape the data with escape().