Active Record Join with Table Alias and Table Prefix

[eluser]Firestorm ZERO[/eluser]
I'm using CI v1.6.1.

Doing this...

Code:

$this->db->select('a.name, b.name');
$this->db->from('dept');
$this->db->join('people AS a', 'dept.supervisor = a.people_id', 'left');
$this->db->join('people AS b', 'dept.head = b.people_id', 'left');

will produce the query...

Code:

SELECT pre_a.name, pre_b.name
FROM (`pre_dept`)
LEFT JOIN `pre_people` AS a ON pre_dept.supervisor = pre_a.people_id
LEFT JOIN `pre_people` AS b ON pre_dept.head = b.people_id

Expected result is...

Code:

SELECT a.name, b.name
FROM (`pre_dept`)
LEFT JOIN `pre_people` AS a ON pre_dept.supervisor = a.people_id
LEFT JOIN `pre_people` AS b ON pre_dept.head = b.people_id

The problem only occurs if you have a table prefix (in the example above it is "pre_").

I just did a search and apparently this problem happened before.
http://codeigniter.com/bug_tracker/bug/1825/
But it says it is resolved. Seems it popped back.

EDIT: Apparently, the 1st join will also have the prefix in the ON statement. But the 2nd join doesn't. I think because if I'm using the same table.

[eluser]Firestorm ZERO[/eluser]
Ok. Found a temp solution. I can only use the alias in the 2nd join.

Code:

$this->db->select('people.name');
$this->db->select('b.name AS head', FALSE);
$this->db->from('dept');
$this->db->join('people', 'dept.supervisor = people.people_id', 'left');
$this->db->join('people AS b', 'dept.head = b.people_id', 'left');

[eluser]barbazul[/eluser]
Hi

The SVN version has de db prefix removed on the ON clause but stilll has the first problem.
the thing is: select() method always appends the db prefix when you pass something that looks like aaaa.bbbbb because it assumes 'aaaa' is a table name.
at this point you don't have the aliases set so there is no way to distinguish between an alias or a table name.

I'll look further into this and see if I can come up with a solution

[eluser]barbazul[/eluser]
I think I got a fix for this... check the bug tracker

[eluser]Derek Allard[/eluser]
Oh Barbazul, I owe you an apology. You PMed me today about this and I said I'd try to get to it. You did all the work, and you got it. Thanks. Committed.

[eluser]barbazul[/eluser]
You don't owe any apologies!!! What you owe me are 150 bucks for my programming services!!!

But I might accept a CI t-shirt as bribe, though

Note: I think there is another bug related to this one, I'll work on it tomorrow. I'm way too sleepy right now