将查询转换为codeigniter格式

Question

Hello guy's 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 in CI format.this query having subquery in his column so I m facing problem to convert this query in to CI format i don't to how to do this please help

Answer 1

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();