How to get AVERAGE from two tables using JOIN in Codeigniter

Question

I have a table structure as given below, i want to get the avg rating of each post, for that i am writing following query to join and get records but it gives syntax error :

$this->db->select($query = $this->db->query('SELECT * FROM post,AVG(`rating`) AS `avg_rating` JOIN review ON `post`.`id` = `review`.`post_id`');

Answer 1

you dint use group by and to execute use like this $this->db->query(" your sql");

$this->db->query('select rating.post_id,avg(rating.rating) as `avg_rating` from rating,post where rating.post_id=post.id group by rating.post_id');