Saravanan Saravanan - 11 months ago 58
MySQL Question

Codeigniter - where primary key is used by child table

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 Source

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