Pulling information from 2 tables

I would like some help with my nested query for my news article page - basically I want each article to have its related comments displayed underneath but at the moment it only returns one comment for each article :(

function get_records($limit, $offset, $sort) {
   $this->db->select('news.*, COUNT(comments.news_id) as comments, comments.comment as comment, news.id as id, news.created_on as created_on, CONCAT(users.firstname, " ", users.surname) as author, categories.category as category, news_types.type as news_type', FALSE);
   $this->db->from('news', 'com开发者_运维技巧ments');
   $this->db->join('users', 'users.id = news.author', 'left');
   $this->db->join('comments', 'comments.news_id = news.id', 'left');
   $this->db->join('categories', 'categories.id = news.category', 'left');
   $this->db->join('news_types', 'news_types.id = news.news_type', 'left');
   $this->db->group_by('news.id');
   $this->db->order_by('news.id', 'DESC');
   $this->db->limit($limit, $offset);
   $query = $this->db->get();
   if($query->num_rows() > 0) {
      return $query->result_array();
   }
}  

---

 $this->db->group_by('news.id');

GROUP BY will return only one record per news item to you, that is why you only get one comment. You will need to have a second query fetch all the comments OR remove the GROUP BY to get all the comments with the redundant news item information (which really isn't a good idea).

---

Here's what you want to do - in theory, not code:

You'll want to make a big array of news stories with one item in the array being another array of the matching comments.

1. Gather all your news stories in one query.
2. Loop through your news stories and while looping through, run a another query that grabs the comments that match the news story.
3. Dump the comments into an array and either attach the array to your result() item as an object property, or attach to result_array() as a new array item for each.

Then return the whole new array/object to the controller from your model.

;)