Codeigniter - 子表使用主键的位置

Question

I have two tables blog and category, where blog.categoryid=category.id. In the below snippet I am obtaining list of active categories with atleast one blog post.

$this->db->select('c.*',FALSE);
$this->db->from('category c');
$this->db->where('c.cattype','posts');
$this->db->where('c.activefrom <=', date('Y-m-d'));
/* TODO - where category has atleast one blog post */
$this->db->limit(10,$offset);
$query = $this->db->get();
$result = $query->result_array();

The above snippet provides the active category list, but I need to achieve list of active categories with atleast one blog post

Answer 1

If you join (inner join) category table to blog table and group by category.id, then you have a list of categories with at least one blog post.

Add these lines:

$this->db->join('blog', 'blog.categoryid = category.id');
$this->db->group_by('c.id');

More information at: https://www.codeigniter.com/userguide2/database/active_record.html