Saravanan Saravanan - 1 month ago 12
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

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

Comments