CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-2
user3653474
user3653474

Reputation: 3854

How to get AVERAGE from two tables using JOIN in Codeigniter

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

enter image description here

Upvotes: 1

Views: 762

Answers (2)

Vigneswaran S
Vigneswaran S

Reputation: 2094

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

Upvotes: 1

CodeGodie
CodeGodie

Reputation: 12132

Two problems found:

  • your code is incorrect. Codeigniter expects a certain format when it comes to creating queries, follow the manual here to understand in more depth.

  • you are getting the average rating but you are not grouping thus ending with one result, rather than one result per each post.

This is how I would rewrite your model method:

function getRatingInfo(){
    $this->db->select("*, AVG(rating) AS avg_rating");
    $this->db->from("post p");
    $this->db->join("review r", "p.id=r.post_id");
    $this->db->group_by("post_id");
    $query = $this->db->get();
    return $query->result();
}

Upvotes: 2

Related Questions