如何在Codeigniter中编写类别/子类别数据库查询（一级深度）？

Question

Ah, the aged old question of parent / child categories in PHP. My apologies if this question has been asked already (I know it has in many forms), but doing a search couldn't specifically answer how to do this using one query and Codeigniter.

Basically I am developing a classifieds website using Codeigniter. A listing can be assigned to a category. A category can be stand-alone (no children) or a category can have one child.

Using Codeigniter's "Active Record" functions, I have the following that almost works.

$this->db->select('l.*');
$this->db->select('c.cat_parent, c.cat_slug, c.cat_name, c.cat_description, c.cat_display');
$this->db->select('c2.cat_slug AS parent_cat_slug, c2.cat_name AS parent_cat_name, c2.cat_description AS parent_cat_description, c2.cat_display AS parent_cat_display');

$this->db->limit($limit, $offset);

$this->db->join('listing_status ls', 'ls.status_id = l.listing_status');
$this->db->join('categories c', 'c.cat_id = l.listing_category');
$this->db->join('categories c2', 'c2.cat_parent = c.cat_id');

return $this->db->get('listings l')->result();

I want to be able to pull out a listing, it's assigned category and if that category the listing belongs to has a parent, get the parent category details as well. I've added in 2 joins and it should be working, is there something I've missed?

Answer 1

After a little messing around, I realised that my second join was wrong. Here is my updated and working code in hopes it helps someone else.

$this->db->select('l.*');
$this->db->select('c.cat_parent, c.cat_slug, c.cat_name, c.cat_description, c.cat_display');
$this->db->select('pc.cat_slug AS parent_cat_slug, pc.cat_name AS parent_cat_name, pc.cat_description AS parent_cat_description, pc.cat_display AS parent_cat_display');

$this->db->limit($limit, $offset);

$this->db->join('listing_status ls', 'ls.status_id = l.listing_status');
$this->db->join('categories c', 'c.cat_id = l.listing_category');
$this->db->join('categories pc', 'pc.cat_id = c.cat_parent');

return $this->db->get('listings l')->result();