CodeIgniter Forums
ActiveRecord Self Join output does not match SQL output - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: ActiveRecord Self Join output does not match SQL output (/showthread.php?tid=53947)

ActiveRecord Self Join output does not match SQL output - El Forum - 08-15-2012

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

ActiveRecord Self Join output does not match SQL output - El Forum - 08-15-2012

[eluser]Massaki[/eluser]
instead of:
Code:
return $this->db->get()->row();
Try this:
Code:
return $this->db->get()->result_array();
And execute a var_dump(). This is what I would do.

ActiveRecord Self Join output does not match SQL output - El Forum - 08-15-2012

[eluser]jelatin[/eluser]
Turns out

Code:
$this->db->select('t1.id, t1.material, t2.id, t2.material, t3.id, t3.material, t4.id, t4.material');

Needed to be

Code:
$this->db->select('t1.id AS t1id, t1.material AS t1material, t2.id AS t2id, t2.material AS t2material, t3.id AS t3id, t3.material AS t3material, t4.id AS t4id, t4.material AS t4material');