php - Laravel leftJoin 和计数问题

Question

此代码返回“count”和“odds”值完全相同(它们不应该相同)。它实际上只计算“o.id”并为两者返回相同的值。

如何正确计算“b.id”？

    \DB::table('matches as m')
  ->selectRaw('     m.id as match_id, 
                    m.date_hour as date, 
                    m.tournament_id as tournament_id,
                    h.name as host_name, 
                    g.name as guest_name, 
                    COUNT(o.id) as odds,
                    COUNT(b.id) as count
                     ')
  ->whereRaw('DATE(m.date_hour) = DATE(NOW())') //OK 
  ->leftJoin('teams as h','h.id','=','m.host_id')
  ->leftJoin('teams as g','g.id','=','m.guest_id')
  ->leftJoin('odds as o','o.match_id','=','m.id')
  ->leftJoin('bets as b','b.match_id','=','m.id') 
  ->groupBy('m.id')
  ->having('odds','>','0')
  ->get();

Answer 1

替换:

COUNT(o.id) as odds,
COUNT(b.id) as count

与:

COUNT(DISTINCT(o.id)) as odds,
COUNT(DISTINCT(b.id)) as count

它应该可以工作。