在Joomla中实现MySQL两表关联并汇总字段值

问题核心 #

直接关联两张表再分组会因笛卡尔积导致金额计算错误，正确思路是先分别对两张表按owner汇总金额，再通过左连接关联结果集，确保每个owner的金额统计准确，同时覆盖仅存在于invoices表的记录（如owner166）。

原生SQL实现 #

SELECT 
    i.owner,
    COALESCE(i.total_invoices, 0.00) AS invoices,
    COALESCE(e.total_expenses, 0.00) AS expenses
FROM 
    (SELECT owner, SUM(amount) AS total_invoices FROM invoices GROUP BY owner) i
LEFT JOIN 
    (SELECT owner, SUM(amount) AS total_expenses FROM expenses GROUP BY owner) e
ON i.owner = e.owner
ORDER BY i.owner;

Joomla数据库查询构建器实现 #

$db = JFactory::getDbo();
$query = $db->getQuery(true);

// 构建invoices表汇总子查询
$subQueryInvoices = $db->getQuery(true)
    ->select([$db->quoteName('owner'), 'SUM(' . $db->quoteName('amount') . ') AS total_invoices'])
    ->from($db->quoteName('invoices'))
    ->group($db->quoteName('owner'));

// 构建expenses表汇总子查询
$subQueryExpenses = $db->getQuery(true)
    ->select([$db->quoteName('owner'), 'SUM(' . $db->quoteName('amount') . ') AS total_expenses'])
    ->from($db->quoteName('expenses'))
    ->group($db->quoteName('owner'));

// 主查询关联两个子查询
$query->select([
    $db->quoteName('i.owner'),
    'COALESCE(' . $db->quoteName('i.total_invoices') . ', 0.00) AS invoices',
    'COALESCE(' . $db->quoteName('e.total_expenses') . ', 0.00) AS expenses'
])
->from('(' . $subQueryInvoices . ') AS ' . $db->quoteName('i'))
->leftJoin('(' . $subQueryExpenses . ') AS ' . $db->quoteName('e') . ' ON ' . $db->quoteName('i.owner') . ' = ' . $db->quoteName('e.owner'))
->order($db->quoteName('i.owner'));

$db->setQuery($query);
$result = $db->loadAssocList();

// 循环输出结果验证（可选）
foreach ($result as $row) {
    echo "Owner: {$row['owner']}, 发票总额: {$row['invoices']}, 支出总额: {$row['expenses']}<br>";
}

关键说明 #

• 先分组汇总再关联：避免两张表关联产生笛卡尔积，防止金额重复计算。
• COALESCE函数：处理无对应支出记录的owner，将支出金额默认设为0.00，完全匹配期望结果。

内容的提问来源于stack exchange，提问作者user1680430