[eluser]jelatin[/eluser]
I have a piece of SQL, it's returning the exact data I want.
Code:
SELECT t1.id, t1.material, t2.id, t2.material, t3.id, t3.material, t4.id, t4.material
FROM ml_levels t1
LEFT JOIN ml_levels t2
ON t1.parentID = t2.id
LEFT JOIN ml_levels t3
ON t2.parentID = t3.id
LEFT JOIN ml_levels t4
ON t3.parentID = t4.id
WHERE t1.id = 286
It returns:
Code:
id | material | id | material | id | material | id | material
-------------------------------------------------------------------------------------------
286 | 4Layer | 209 | Protective Film | 60 | Specialty Products | 1 | Protect
Perfect, okay, now to put it in ActiveRecord:
Code:
$this->db->select('t1.id, t1.material, t2.id, t2.material, t3.id, t3.material, t4.id, t4.material');
$this->db->from('ml_levels AS t1')->where('t1.id',286);
$this->db->join('ml_levels AS t2','t1.parentID = t2.id','left');
$this->db->join('ml_levels AS t3','t2.parentID = t3.id','left');
$this->db->join('ml_levels AS t4','t3.parentID = t4.id','left');
return $this->db->get()->row();
Looks good, but it outputs:
Code:
(
[id] => 1
[material] => Protect
)
Hmm, where are the first 6 things from my select() statement? $this->db->last_query() returns:
Code:
SELECT `t1`.`id`, `t1`.`material`, `t2`.`id`, `t2`.`material`, `t3`.`id`, `t3`.`material`, `t4`.`id`, `t4`.`material`
FROM (`ml_levels` AS t1)
LEFT JOIN `ml_levels` AS t2 ON `t1`.`parentID` = `t2`.`id`
LEFT JOIN `ml_levels` AS t3 ON `t2`.`parentID` = `t3`.`id`
LEFT JOIN `ml_levels` AS t4 ON `t3`.`parentID` = `t4`.`id`
WHERE `t1`.`id` = 286
Which I paste into SQL and it gives me the exact results I want.