php - 在 EE 中使用 CodeIgniter 对两个 SQL 行求和

Question

我正在使用提供的 Active Record 类对 ExpressionEngine 中 MySQL 数据库中的两个不同行及其字段值(forum_total_topics 和 forum_total_posts)求和。我尝试了多个版本的代码来求和这些数字。我曾尝试通过 saying 将 MySQL 查询传递给请求，但失败了。

$SQL = "SELECT forum_id, sum(forum_total_topics) + sum(forum_total_posts)
FROM exp_forums"

ee()->db->select($SQL); 
$query = ee->db->get('exp_forums');
echo $query;

回显总和，得到如下错误:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

SELECT (SELECT SUM(forum_total_topics) + SUM(forum_total_posts) FROM (exp_forums)

一个困惑的解决方案

所以我尝试将整个请求拆分来寻找解决方案。通过返回多个数组并对它们求和，我终于得到了下面的代码，但它看起来很乱。如何在一行左右返回表中的两行并对它们求和？

$topics = ee()->db->select_sum('forum_total_topics')->get('exp_forums');
foreach($topics->result_array() as $topicrow) {
    $totalTopics = $topicrow['forum_total_topics'];
}

$posts = ee()->db->select_sum('forum_total_posts')->get('exp_forums');
foreach($posts->result_array() as $postrow) {
    $totalPosts = $postrow['forum_total_posts'];
}

$total = $totalTopics + $totalPosts;

如有任何建议，我们将不胜感激!

尝试过的建议

我尝试过这样的建议，

$SQL = "SELECT forum_id, SUM(forum_total_topics + forum_total_posts) AS total
FROM exp_forums
GROUP BY forum_id";

$query = ee()->db->select($SQL); 
echo $query;

取而代之的是这个错误。

A PHP Error was encounteredSeverity: 4096Message: Object of class CI_DB_mysql_driver could not be converted to stringFilename: libraries/Functions.php(679) : eval()'d codeLine Number: 98

Answer 1

试试这样吧

SELECT forum_id, sum(forum_total_topics+forum_total_posts)
FROM exp_forums
GROUP BY forum_id

sqlfiddle demo