+- 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: I need a bit of help converting this SQL to CI Active Record format (/showthread.php?tid=33343)
I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[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_namei 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
I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[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();I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[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
I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[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();I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[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
I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[eluser]solconnection[/eluser]
got it all working
thanks guys!
I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[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.
I need a bit of help converting this SQL to CI Active Record format - El Forum - 08-23-2010
[eluser]solconnection[/eluser]
Thanks Jondolar, i shall indeed check into these approaches
-D