我想显示表article的所有行，并且对于每个文章行，我想从另一个表(likes)获得votes的SUM，并且这与一个查询。
我有:
$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 ";
但是我的问题与这个查询，我得到只有一行!因为在表likes中只有一行…
我想显示基于article表的结果(我有大约50行在里面)…如果没有与特定文章相关的投票，我们显示(0票)。
谢谢。
将group by添加到代码中:
$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";
$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();
存储函数是一种返回单个值的特殊类型的存储程序。您可以使用存储函数封装在SQL语句或存储程序之间可重用的公共公式或业务规则。
此处查询应该
$query = "SELECT article.title,article.tags,article.description,article.slug,users.username,article.photo,article.id,article.date,custom_SUM(article.id) as upvotes
FROM article";
$query .= " LEFT JOIN users ON article.user_id = users.user_id ";
和自定义mysql函数
DELIMITER $$
CREATE FUNCTION custom_SUM(p_article_id int(11)) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE likes varchar(10);
SET likes =(select sum(likes.votes) from likes where likes.article_id=p_article_id);
RETURN (likes);
END
参考链接http://www.mysqltutorial.org/mysql-stored-function/