Laravel查询返回空集合但同逻辑phpMyAdmin查询正常，求原因

问题描述 #

以下Laravel代码执行后返回空集合，但逻辑一致的原生SQL在phpMyAdmin中能得到正确结果：

Laravel代码 #

public function sendNotifications()
{
    $matchingSubscriptions = DB::table('tournament_match_plan')
        ->join('push_subscriptions', 'push_subscriptions.age_group', '=', 'tournament_match_plan.league')
        ->where('tournament_match_plan.start', '=', '11:20:00')
        ->where('tournament_match_plan.team_1', '=', 'push_subscriptions.club')
        ->orwhere('tournament_match_plan.team_2', '=', 'push_subscriptions.club')
        ->get();

    dd($matchingSubscriptions);
}

调试结果 #

Illuminate\Support\Collection {#751 ▼ // app\Http\Controllers\Guests\GuestsPushController.php:97
  #items: []
  #escapeWhenCastingToString: false
}

可正常执行的原生SQL #

SELECT *
FROM tournament_match_plan
JOIN push_subscriptions ON push_subscriptions.age_group = tournament_match_plan.league
WHERE tournament_match_plan.start = '11:20:00'
AND (tournament_match_plan.team_1 = push_subscriptions.club OR tournament_match_plan.team_2 = push_subscriptions.club);

问题原因及解决方法 #

1. 逻辑条件分组错误 #

原生SQL里，team_1和team_2的OR条件被括号包裹，属于start = '11:20:00'的AND子条件；但你的Laravel代码直接用where(...)->orWhere(...)，生成的SQL逻辑会变成：

WHERE tournament_match_plan.start = '11:20:00'
AND tournament_match_plan.team_1 = 'push_subscriptions.club'
OR tournament_match_plan.team_2 = 'push_subscriptions.club'

这等价于「(start符合且team1符合) 或者 team2符合」，和原生SQL的逻辑完全不符。必须用闭包将OR条件包裹，实现分组效果。

2. 字段引用错误 #

代码里的'push_subscriptions.club'被当作字符串常量处理，而非数据库字段引用。需要用DB::raw()告知Laravel这是对应表的字段。

修正后的完整代码 #

public function sendNotifications()
{
    $matchingSubscriptions = DB::table('tournament_match_plan')
        ->join('push_subscriptions', 'push_subscriptions.age_group', '=', 'tournament_match_plan.league')
        ->where('tournament_match_plan.start', '=', '11:20:00')
        ->where(function($query) {
            $query->where('tournament_match_plan.team_1', '=', DB::raw('push_subscriptions.club'))
                  ->orWhere('tournament_match_plan.team_2', '=', DB::raw('push_subscriptions.club'));
        })
        ->get();

    dd($matchingSubscriptions);
}

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