在LEFT JOIN PHP MySQL CodeIgniter中选择最新一行

Question

I am trying to achieve the following: I have two tables. One of the tables is called characters and the other one is called experience. Right now I want to print a list of all characters and linking the latest row in experience to it. Added to that rows in characters without a row in experience should still be shown.

Here an example of the tables and desired output.

characters
id   |   name   |
----------------|
1    | TestChar |
2    | NewChar  |
3    | OldChar  |

experience
id |  char_id  |  experience  |
------------------------------|
1  |  1        | 683185858    |
2  |  2        | 85712849     |
3  |  1        | 687293919    |
4  |  1        | 794812393    |

output
name      |   experience   |
---------------------------|
TestChar  | 794812393      |
NewChar   | 85712849       |
OldChar   | NULL           |

So far, I made this query and it seems to work in MySQL

SELECT c.name, e1.experience
FROM characters c
LEFT JOIN experience e1 ON e1.char_id = c.id 
LEFT JOIN experience e2 ON e1.char_id = e2.char_id AND e2.id > e1.id
WHERE e2.id IS NULL;

Then, I want to implement this in CodeIgniter but that's where it goes wrong. The following is what I have right now, it fills in the c.name but the e1.exp remains empty.

$this->db->select('c.name, e1.exp');
$this->db->from('characters as c');
$this->db->join('experience as e1', 'e1.char_id = c.id', 'left');
$this->db->join('experience as e2', 'e1.char_id = e2.char_id AND e2.id > e1.id', 'left');
$this->db->where('e2.id', NULL);

Is this related to my MySQL query being wrong? Is my implementation in CodeIgniter incorrect? Both? I appreciate every bit of advice!

Answer 1

You can use a join condition that only selects the row the maximum id per char_id.

$this->db->select('c.name, e1.exp');
$this->db->from('characters as c');
$this->db->join('experience as e1', 'e1.id = (select max(id) from experience as e2 where e2.char_id = e1.char_id)', 'left');

or similarly using a derived table

$this->db->select('c.name, e1.exp');
$this->db->from('characters as c');
$this->db->join('(select max(id) max_id, char_id 
    from experience group by char_id) as t1', 't1.char_id = c.id', 'left')
$this->db->join('experience as e1', 'e1.id = t1.max_id', 'left')