Laravel关联Transaction与Reviews表统计金额及平均评分问题

数据表与需求 #

• Transaction表：存储订单处理记录、金额、product id、productname
• Reviews表：存储各产品的评价信息

需求：关联两张表，筛选Transaction表中status为completed的数据，展示产品名称、该产品的总交易金额，同时结合Reviews表计算对应产品的平均评分。

遇到的问题 #

尝试的关联查询代码无法正确统计产品PR0001的总金额（应为1255），但单独统计交易数据的代码可正常运行，现寻求能实现关联并展示评分的正确代码。

尝试的关联代码（存在问题） #

$join = DB::table('transactions')->select('transactions.productid')->selectRaw('SUM(transactions.amount) as totalamount')->join('reviews','transactions.productid', '=', 'reviews.productid')->where('transactions.busid', '=', $user)->where('transactions.status', '=', 'Completed')->groupBy('transactions.productid')->get();

正常运行的无关联统计代码 #

$topProduct = Transaction::where('busid', $user)->select([DB::raw('SUM(amount) as amount'), DB::raw('productname as productname'), (DB::raw('count(productid) as prodcount'))])
            ->where('status', 'completed')->groupBy('productname')->orderBy('amount', 'desc')->get();

问题原因 #

你使用的是内连接（join），会导致两种问题：

1. 若产品有多条评价记录，交易记录会与每条评价重复关联，SUM(amount)会被重复计算，导致总金额错误；
2. 若产品无评价记录，会被直接排除在结果之外，不符合需求。

正确的关联查询代码 #

方式一：左连接+分组统计 #

$result = DB::table('transactions')
    ->select(
        'transactions.productname',
        DB::raw('SUM(transactions.amount) as total_amount'),
        DB::raw('AVG(reviews.rating) as average_rating') // 假设reviews表用rating字段存储评分
    )
    ->leftJoin('reviews', 'transactions.productid', '=', 'reviews.productid')
    ->where('transactions.busid', $user)
    ->where('transactions.status', 'Completed')
    ->groupBy('transactions.productid', 'transactions.productname')
    ->get();

方式二：子查询预计算评分（更高效） #

// 先预统计每个产品的平均评分
$reviewScores = DB::table('reviews')
    ->select(
        'productid',
        DB::raw('AVG(rating) as average_rating')
    )
    ->groupBy('productid');

// 关联交易表统计金额
$result = DB::table('transactions')
    ->select(
        'transactions.productname',
        DB::raw('SUM(transactions.amount) as total_amount'),
        DB::raw('COALESCE(review_scores.average_rating, 0) as average_rating') // 无评价时默认返回0
    )
    ->leftJoinSub($reviewScores, 'review_scores', function($join) {
        $join->on('transactions.productid', '=', 'review_scores.productid');
    })
    ->where('transactions.busid', $user)
    ->where('transactions.status', 'Completed')
    ->groupBy('transactions.productid', 'transactions.productname', 'review_scores.average_rating')
    ->get();

代码说明 #

• 使用leftJoin（左连接）保留所有符合条件的交易产品，即使产品无评价记录；
• 方式二通过子查询先计算每个产品的平均评分，再与交易表关联，避免了交易记录因多评价被重复关联导致的金额计算错误；
• COALESCE函数用于处理无评价的产品，返回默认值（如0），避免结果出现null；
• 分组时需包含productname，确保产品名称与金额、评分对应正确。

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