I need a bit of help converting this SQL to CI Active Record format

[eluser]solconnection[/eluser]
Hi there, being new to CI i was hoping some kind soul could point me in the right direction in converting the below SQL query to the format CI uses.

Alternatively, is there an easy way to use pure SQL queries?, it would be nice to keep the code database agnostic, but in all likelihood it's going to be running on an SQL in most (all?) cases.

Code:

select root.name  as root_name
     , down1.name as down1_name
     , down2.name as down2_name
     , down3.name as down3_name
  from categories as root
left outer
  join categories as down1
    on down1.parentid = root.id
left outer
  join categories as down2
    on down2.parentid = down1.id
left outer
  join categories as down3
    on down3.parentid = down2.id
where root.parentid is null
order
    by root_name
     , down1_name
     , down2_name
     , down3_name

i am trying to implement a subcategories system using the theory on this page
http://sqllessons.com/categories.html

if there is a good solution existing then id be happy just to learn from that too

thanks in advance for any advice have a nice day
-D

[eluser]WanWizard[/eluser]
Something like

Code:

$this->db->select('root.name AS root_name, down1.name AS down1_name, down2.name AS down2_name, down3.name AS down3_name');
$this->from('categories root');
$this->db->join('categories down1', 'down1.parentid = root.id', 'LEFT OUTER');
$this->db->join('categories down2', 'down2.parentid = down1.id', 'LEFT OUTER');
$this->db->join('categories down3', 'down3.parentid = down2.id', 'LEFT OUTER');
$this->db->where('root.parentid', NULL);
$this->db->order_by('root_name', 'ASC');
$this->db->order_by('down1_name', 'ASC');
$this->db->order_by('down2_name', 'ASC');
$this->db->order_by('down3_name', 'ASC');
$query = $this->db->get();

[eluser]solconnection[/eluser]
[quote author="WanWizard" date="1282603974"]Something like .... [/quote]

struth, that was quick!!! thank you very much, ill see how it goes, appreciate the help!

-D

[eluser]mddd[/eluser]
You can easily just use pure sql if you want:

Code:

$query = $this->db->query('select * from my_table where id=1');
$results = $query->result();
// or:
$results = $this-.db->query('select * from my_table where id=1')->result();

[eluser]solconnection[/eluser]
[quote author="mddd" date="1282604130"]You can easily just use pure sql if you want: ...[/quote]

another really fast and helpful reply is this the best forum ever?

thanks mddd, much appreciated
-D

[eluser]solconnection[/eluser]
got it all working thanks guys!

[eluser]Jondolar[/eluser]
You may want to look at nested sets or materialized paths as an alternative approach to managing subcategories.

Good luck with your project.

[eluser]solconnection[/eluser]
Thanks Jondolar, i shall indeed check into these approaches

-D