Laravel含关联与子查询的查询语句始终返回相同产品如何排查

你写的查询逻辑存在分组规范错误：外层查询仅按community_id分组，但select子句包含了未参与分组、也没有被聚合函数包裹的products.name字段。在MySQL非严格SQL模式下，数据库会默认返回分组后该社区第一条命中记录的商品名称，和你计算的max(sub.sum)没有对应关系，因此无论实际销量最高的商品是哪个，返回的永远是排序第一的「产品1」。

方案1：使用窗口函数（MySQL 8.0+ 推荐） #

窗口函数是这类分组取TOP N场景的最优解，先按社区分区对商品销量倒序排名，取每个分区排名第一的记录即可：

$result = \DB::table('purchases')
    ->join('suppliers', 'purchases.supplier_id', '=', 'suppliers.id')
    ->join('purchase_details', 'purchases.id', '=', 'purchase_details.purchase_id')
    ->join('communities', 'suppliers.community_id', '=', 'communities.id')
    ->join('products', 'purchase_details.product_id', '=', 'products.id')
    ->whereNull('purchases.deleted_at')
    ->selectRaw('
        communities.name as communityName,
        products.name as productName,
        sum(purchase_details.qty) as total_ventas
    ')
    ->groupBy('suppliers.community_id', 'purchase_details.product_id')
    ->havingRaw('ROW_NUMBER() OVER (PARTITION BY suppliers.community_id ORDER BY sum(purchase_details.qty) DESC) = 1')
    ->get();

方案2：兼容MySQL 5.x 版本 #

如果使用的是不支持窗口函数的老版本MySQL，可以先将所有社区的商品按销量倒序排序，再在外层按社区分组，分组时会默认保留排序后的第一条记录：

$salesSub = \DB::table('purchases')
    ->join('suppliers', 'purchases.supplier_id', '=', 'suppliers.id')
    ->join('purchase_details', 'purchases.id', '=', 'purchase_details.purchase_id')
    ->join('communities', 'suppliers.community_id', '=', 'communities.id')
    ->join('products', 'purchase_details.product_id', '=', 'products.id')
    ->whereNull('purchases.deleted_at')
    ->selectRaw('
        communities.name as communityName,
        products.name as productName,
        sum(purchase_details.qty) as total_ventas,
        suppliers.community_id
    ')
    ->groupBy('suppliers.community_id', 'purchase_details.product_id')
    ->orderBy('suppliers.community_id')
    ->orderBy('total_ventas', 'desc');

$result = \DB::table(\DB::raw("({$salesSub->toSql()}) as sub"))
    ->mergeBindings($salesSub->getQuery())
    ->groupBy('community_id')
    ->get(['communityName', 'productName', 'total_ventas']);

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