php - 如何使用 codeigniter 查询获得每个产品的平均评分？

Question

我有评级表，其中包含用户给出的每个产品的评级，我正在检索所有评级记录，但同时我想获得基于每个产品的平均评级，但我无法获得输出

询问 ：

$this->db->select('ratings.*');
$this->db->select('select AVG(ratings) as avg_rating from ratings group by product_id');
$this->db->from('ratings');
$this->db->join('users','users.id=ratings.user_id');
$this->db->get()->result();

评分表

id   user_id product_id  rating  

1      4         1         4
 
2      5         2         4

3      6         1         2


4      7         4         4

预期输出：

id   user_id product_id  rating          avg rating

1      4         1         4               3
 
2      5         2         4               4

3      6         1         2               3


4      7         4         4               4

标签： phpmysqlcodeigniteraverage

Answer 1

从表rating中获取数据，使用左连接和 select 作为平均值。

Codeigniter的join() 函数允许你写一个选择部分而不是表名，但是你需要把它放在括号中：

$this->db->select('t1.*, t2.avg_rating, t3.*');
$this->db->from('ratings t1');
$this->db->join('
                  (select product_id, avg(rating) as avg_rating 
                   from ratings 
                   group by product_id)  t2','t2.product_id=t1.product_id','left'
                );
$this->db->join('users t3','t3.id=t1.user_id','left'); 
$this->group_by('t1.userid')
$this->db->get()->result();

生成：

SELECT t1.*, t2.avg_rating, t3.*
FROM ratings t1
left join 
        (select product_id, avg(rating) as avg_rating from ratings group by product_id)  t2 
    on t2.product_id=t1.product_id
left join users t3
    on t1.user_id = t3.id
group by t1.user_id

并按预期输出。