Welcome Guest, Not a member yet? Register   Sign In
I need a bit of help converting this SQL to CI Active Record format

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.

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
    by root_name
     , down1_name
     , down2_name
     , down3_name

i am trying to implement a subcategories system using the theory on this page

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

thanks in advance for any advice Smile have a nice day

Something like
$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();

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

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


You can easily just use pure sql if you want:
$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();

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

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

thanks mddd, much appreciated

got it all working Smile thanks guys!

You may want to look at nested sets or materialized paths as an alternative approach to managing subcategories.

Good luck with your project.

Thanks Jondolar, i shall indeed check into these approaches Smile


Theme © iAndrew 2016 - Forum software by © MyBB