CODEWITHSUNDEEP
phpmysqlsqlcodeigniteractiverecord
Geril
Geril

Reputation: 105

Codeigniter count results from joined table

I am working on online discussion in codeigniter, and I need to get result of all topics and all their data, like post post id, post author, text etc. but also count of responses to that specific topic. 

$this->db->where('language', $lang)
         ->order_by('forum_id', 'desc')->limit($limit, $offset)
         ->get('forum');

Here is my current "db selector". My other table is called responses with cols: response_id, forum_id, author etc. How to join it there? But just number of results? Like: for this topic there is 0 answers, for this one there are 3 answers, no data from answers are needed, just data about topic and number of answers. But I need to keep there:

->where('language', $lang)
->order_by('forum_id', 'desc')->limit($limit, $offset)

Due to pagination and multi language support. Thanks.

Upvotes: 1

Views: 1155

Answers (1)

Geril
Geril

Reputation: 105

Thanks guys, here is working code:

$this->db->where('forum.language', $lang)
         ->order_by('forum.forum_id', 'desc')
         ->limit($limit, $offset)
         ->select('forum.*, COUNT(responses.response_id) as num_answers')
         ->from('forum')
         ->join('responses', 'responses.forum_id = forum.forum_id','left')
         ->group_by('forum_id')
         ->get();

Upvotes: 1

Related Questions