Convert SQL containing DISTINCT and a subquery in the SELECT clause to CodeIgniter active record

I have this query

SELECT
    distinct blog.*,
    (
        select
            count(blog_id)
        from
            blog_comment
        where
            blog_comment.blog_id = blog.id
    ) as comment
FROM
    blog
Left JOIN
    blog_comment
        ON blog.id = blog_comment.blog_id

I want this query, including the subquery, in CI's active record format.

You can do so

$this->db->select('distinct blog.*,
(select count(blog_id) from blog_comment where blog_comment.blog_id=blog.id) as comment ',FALSE);
$this->db->from('blog')
$this->db->join('blog_comment','blog.id = blog_comment.blog_id','LEFT');
$this->db->get();

You original query can be rewritten without using subquery

SELECT DISTINCT 
  b.*,
  COUNT(bc.blog_id) `comment`
FROM
  blog b
  LEFT JOIN blog_comment  bc
  ON b.id = bc.blog_id 
  GROUP BY b.id

Active Record

$this->db->select('b.*, COUNT(bc.blog_id) `comment`',FALSE);
$this->db->from('blog b')
$this->db->join('blog_comment bc','b.id = bc.blog_id ','LEFT');
$this->db->group_by('b.id'); 
$this->db->get();