Laravel查询构造器与原生MySQL查询返回结果不一致排查

需求背景 #

筛选尚未产生结果的exercise，且每个exercise仅保留最新的评分记录（取ratings表中ID最大的条目）。原生SQL执行后得到预期的55条结果，但转换为Laravel Query Builder写法后返回384条（包含所有旧评分记录）。已确认数据库环境一致，且通过$query->toSql()输出的SQL在MySQL控制台执行结果正确。

原生SQL实现 #

$rawquery = <<<SQL
    SELECT exercises.id AS id, r1.rating, ABS(CAST(r1.rating AS SIGNED) - :rating) AS diff
    FROM exercises
    INNER JOIN exercise_theme ON exercises.id = exercise_theme.exercise_id AND theme_id = 12
    INNER JOIN ratings r1 ON exercises.id = r1.exercise_id
    LEFT JOIN ratings r2 ON exercises.id = r2.exercise_id AND r1.id < r2.id
    LEFT JOIN results ON exercises.id = results.exercise_id AND results.user_id = :uid
    WHERE exercises.status = 'enabled' AND r2.id IS NULL AND results.id IS NULL
    ORDER BY diff;
SQL;

$results = DB::select($rawquery, ['rating' => 1500, 'uid' => Auth::user()->id]);
dd(count($results));

Laravel Query Builder初始实现 #

$query = DB::table('exercises')
    ->selectRaw('exercises.id as id, r1.rating, ABS(CAST(r1.rating as SIGNED) - ?) as diff', [$rating])
    ->join('exercise_theme', function ($join) {
        $join->on('exercises.id', '=', 'exercise_theme.exercise_id')
            ->where('theme_id', '=', 12);
    })
    ->join('ratings as r1', 'exercises.id', '=', 'r1.exercise_id')
    ->leftJoin('ratings as r2', function ($join) {
        $join->on('exercises.id', '=', 'r2.exercise_id')
            ->where('r1.id', '<', 'r2.id');
    })
    ->leftJoin('results', function ($join) {
        $join->on('exercises.id', '=', 'results.exercise_id')
            ->where('results.user_id', Auth::user()->id);
    })
    ->where('exercises.status', 'enabled')
    ->whereNull('r2.id')
    ->whereNull('results.id')
    ->orderBy('diff');

问题原因与修复方案 #

核心问题 #

在leftJoin('ratings as r2')的闭包中，使用where('r1.id', '<', 'r2.id')会被Laravel解析为将字符串'r2.id'作为绑定参数，而非比较两个字段的值。这导致实际执行的SQL条件变为r1.id < 'r2.id'，MySQL会将字符串转为数字0，使得r2.id IS NULL的筛选条件失效，最终返回所有评分记录。

修复代码 #

将比较字段的where方法替换为whereColumn方法，明确指定比较的是数据库列：

$query = DB::table('exercises')
    ->selectRaw('exercises.id as id, r1.rating, ABS(CAST(r1.rating as SIGNED) - ?) as diff', [$rating])
    ->join('exercise_theme', function ($join) {
        $join->on('exercises.id', '=', 'exercise_theme.exercise_id')
            ->where('theme_id', '=', 12);
    })
    ->join('ratings as r1', 'exercises.id', '=', 'r1.exercise_id')
    ->leftJoin('ratings as r2', function ($join) {
        $join->on('exercises.id', '=', 'r2.exercise_id')
            // 替换为whereColumn比较列值
            ->whereColumn('r1.id', '<', 'r2.id');
    })
    ->leftJoin('results', function ($join) {
        $join->on('exercises.id', '=', 'results.exercise_id')
            ->where('results.user_id', Auth::user()->id);
    })
    ->where('exercises.status', 'enabled')
    ->whereNull('r2.id')
    ->whereNull('results.id')
    ->orderBy('diff');

额外验证 #

修复后执行$query->get()，即可得到与原生SQL一致的55条结果。若仍有疑问，可通过$query->getBindings()查看实际绑定的参数，确认字段比较逻辑是否正确。

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