Better Fly Better Fly - 1 month ago 7
MySQL Question

Make multiple queries in codeigniter and the results based in the first one

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

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