Self join possible using Active Record?

Self join possible using Active Record? - 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: Self join possible using Active Record? (/showthread.php?tid=24596)

Self join possible using Active Record? - El Forum - 11-13-2009

[eluser]CARP[/eluser]
Hi...
I'm building some sort of catalog.
I have a category table, which has

id - autoinc
name - varchar(100)
desc - tinytext
fk_cat - int(10) foreign key that points to category.id (self reference foreign key)

Of course, I can't execute the following code..

Code:
$this->db->select('*');

$this->db->from('category');

$this->db->join('category', 'category.id = category.fk_id', 'left');

$this->db->order_by('category.name', 'asc');

$query = $this->db->get();

becase I'm getting the following error

Error Number: 1066
Not unique table/alias: 'category'

It seems there's no way to give alias to each table in Active Record calls, right? Any workaround?
Thanks a lot in advance,

Self join possible using Active Record? - El Forum - 11-13-2009

[eluser]Armchair Samurai[/eluser]
Definitely possible - just alias the tables

Code:
$this->db->join('category c2', 'c.id = c2.fk_id', 'left');

$this->db->order_by('c.name', 'asc');

$query = $this->db->get('category c');

Self join possible using Active Record? - El Forum - 11-13-2009

[eluser]CARP[/eluser]
Very quick reply... working fine, but which kind of join will ignore the rows which have fk_category == null ?

Self join possible using Active Record? - El Forum - 11-13-2009

[eluser]Chad Fulton[/eluser]
I think that you might in fact want:

Code:
$this->db->join('category c2', 'c2.fk_id = c.id', 'left');

$this->db->order_by('c.name', 'asc');

$query = $this->db->get('category c');

You want to join where the c2.fk_id = c.id, not where c.id = c2.fk_id

Self join possible using Active Record? - El Forum - 11-13-2009

[eluser]CARP[/eluser]
Yes! you're right... it worked perfect!

PS: Another question (if possible)... how do you select (using Active Record) records which its fk_category are = NULL (I want to select main categories, that doesn't have child categories)

Thanks again

Edit: found it...
$this->db->where("fk_category", NULL);