Convert this MySQL Query to Codeigniter Active Record

Question

I have got two tables :

Dress (dress_id, title)
Dress_comment (comment_id, dress_id, comment)

I have been trying from hours now and unable to convert this query into the corresponding Codeigniters' Active Class Record functions.

Here is the query :

SELECT d.dress_id, d.title, COALESCE(dc.count, 0) 
FROM dress d
JOIN (select dress_id, count(comment_id) as count from dress_comment group by dress_id) dc 
ON d.dress_id = dc.dress_id 
ORDER BY d.dress_id;

This is what I have tried till now :

    //Creating the select subquery for the inner join
    $this->db->select('dress_id, count(comment_id) as count')
                         ->from('Dress_comment')
                         ->group_by('dress_id')
                         ->get();
    $subQuery = $this->db->last_query();

    //Main Query 
    $this->db->select('d.dress_id, d.title, COALESCE(dc.count,0)')
             ->from('Dress')
             ->join($subQuery . ' dc','dc.dress_id = d.dress_id','left')
             ->order_by('d.dress_id');

    $result = $this->db->get();

    return $result->result_array();

But Codeigniter is not running this query and always giving mysql syntax error. The sql format that codeigniter is converting this active methods to is

SELECT `d`.`dress_id`, `d`.`title`, COALESCE(dc.count, `0)` 
FROM (`Dress`) LEFT JOIN `SELECT`     `dress_id`, count(comment_id) as count 
FROM (`Dress_comment`) 
GROUP BY `dress_id` dc 
ON `dc`.`dress_id` = `d`.`dress_id` 
ORDER BY `d`.`dress_id`

Waris Ali Waris Ali · Accepted Answer · 2014-08-16T23:41:05

I was able to solve the problem using the mysql query in the join function like this :

        $this->db->select('d.dress_id, d.title, dc.count as comments, dl.count as likes, dt.title, di.image_url')
             ->from('Dress d')
             ->join('(select dress_id, count(comment_id) as count from dress_comment group by dress_id) dc','dc.dress_id = d.dress_id','left')
             ->order_by('d.dress_id');

Convert this MySQL Query to Codeigniter Active Record

1 Answers