在codeigniter中进行多个查询，结果基于第一个查询

Question

I want to display all rows of table article, and for each of article row i want get the SUM of votes from another table (likes), and this with one query.

Here what i have:

$query = "SELECT article.title,article.tags,article.description,article.slug,users.username,article.photo,article.id,article.date,SUM(likes.votes) as upvotes
          FROM article";
$query .= " LEFT JOIN users ON article.user_id = users.user_id ";
$query .= " LEFT JOIN likes ON likes.article_id = article.id ";

But my problem with this query, i get only one row ! because in table likes there only one row ...

I want to display results based on article table (i have about 50 rows in it)... and if there nothing related to votes for a specific article, we show (0 votes).

Thank you.

Answer 1

Add group by to the code :

$query = "SELECT article.title, article.tags, article.description, article.slug, users.username, article.photo, article.id, article.date, SUM(likes.votes) as upvotes FROM article";
$query .= " LEFT JOIN users ON article.user_id = users.user_id ";
$query .= " LEFT JOIN likes ON likes.article_id = article.id ";
$query .= " GROUP BY article.id";

Or use codeigniter method:

$this->db->select("article.title, article.tags, article.description, article.slug, users.username, article.photo, article.id, article.date");
$this->db->select("SUM(likes.votes) as upvotes", false);
$this->db->from("article");
$this->db->join("users","article.user_id = users.user_id");
$this->db->join("likes","likes.article_id = article.id");
$this->db->group_by("article.id");
$query = $this->db->get();
return $query->result_array();