我有评级表,其中对用户给出的每个产品进行评级,我正在检索所有评级记录,但同时我想根据每个产品获得平均评级,但我无法获得输出
Query :
$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
从表中获取数据ratings,使用左连接和 select 来求平均值。
the join() 函数 https://codeigniter.com/userguide3/database/query_builder.html?highlight=join#CI_DB_query_builder::joinCodeigniter 允许您编写选择部分而不是表名,但您需要将其放入括号中:
$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
并按照您的预期输出。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)