php - 将此 MySQL 查询转换为 Codeigniter Active Record

Question

我有两张 table :

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

我已经尝试了几个小时，但无法将此查询转换为相应的 Codeigniters 的 Active Class Record 函数。

这是查询:

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;

这是我到目前为止所尝试过的:

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

但是Codeigniter没有运行这个查询并且总是给出mysql语法错误。 codeigniter 将此事件方法转换为的 sql 格式是

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`

Answer 1

我能够在 join 函数中使用 mysql 查询解决问题，如下所示:

        $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');