Codeigniter Subquery

Question

I have this query can someone help me transfer it to codeigniter style in model.

SELECT
    a.*, 
    (
        SELECT COUNT(*) FROM `comment` c 
        WHERE c.comment_article_id = a.`News_News_ID`
    ) AS counta
FROM
    `news_news` a

Update: I try this and it work nicely but im not sure it is good practice

     $sql="SELECT a.*,
     ( SELECT COUNT(*) FROM comment c WHERE c.comment_article_id = a.`News_News_ID` ) as counta
     FROM `news_news` a";
 $query = $this->db->query($sql, array('News_Cate_ID' => $cate), $start, $display);

`CI` allow you to write your custom `sql` but it always a good practice to use standard model way like http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record — urfusion, Apr 13 '16 at 11:55

urfusion · Answer 1 · 2016-04-13T11:22:49.230

0

Use join instead of sub query and according to your query you didn't need where condition while using joins

$this->db->select('a.*,COUNT(c.*) AS counta');
$this->db->from('news_news AS a');
$this->db->join('comment AS c', 'c.comment_article_id = a.News_News_ID');
$query = $this->db->get();

For more information

edited Apr 13 '16 at 11:22

answered Apr 13 '16 at 11:16

urfusion

5,528
5
50
87

And how to put my variable there. I have category, start, display – Nam Hao Apr 13 '16 at 15:13

score 0 · Answer 2 · answered Apr 13 '16 at 13:16

Try below code. This query will return each article with comment count

$this->db->select('a.*,COUNT(c.*) AS count');
$this->db->from('news_news a');
$this->db->join('comment c', 'a.News_News_ID = c.comment_article_id ','LEFT');
$this->db->group_by('a.News_News_ID')
$query = $this->db->get();

Codeigniter Subquery

2 Answers2