Codeigniter 2.1 - join two tables and count in the same query

Question

I have two tables:

news ->     
  id_news    
  title    
  body    
  date_created    
  image    
  category_id    

comments ->     
  id_comments    
  news_id    
  body    
  date_created

How can I write query to get all news, count all the comments for every news and present that query in the view part?

Answer 1

select
      N.ID_News,
      N.Title,
      N.Body,
      N.Date_Created,
      N.Image,
      N.Category_ID,
      count(C.ID_Comments) CommentCount
   from
      News N
         LEFT JOIN Comments C
            on N.ID_News = C.News_ID
   group by 
      N.ID_News
   order by
      whatever column(s) are important to you