如何将带USE INDEX的MySQL原生查询转为Laravel 10 Query Builder

问题背景 #

将CakePHP 2.x项目迁移至Laravel 10时，需把带USE INDEX的MySQL查询转换为Laravel Query Builder写法，转换后触发Illuminate\Database\QueryException错误，提示SQLSTATE[HY000]: General error: 1 no such column: Student.name。

原代码与错误详情 #

CakePHP 2.x 原查询代码 #

$sql = [
    'fields' => [
        'Student.name',
        'Room.id'
    ],
    'joins' => [
        'use index(course)',
        [
            'table' => 'rooms',
            'alias' => 'Room use index(weekday, semester, year)',
            'type' => 'left',
            'conditions' => 'Student.course = Room.course'
        ]
    ],
    'conditions' => [
        'Student.course IN' => $courses,
        'Room.weekday' => $weekday,
        'Room.semester' => $semester,
        'Room.year' => $year,
    ]
];
$result = $this->Student->find('all', $sql);

对应原生SQL #

SELECT 
    Student.name, 
    Room.id 
FROM 
    students AS Student USE INDEX (course)
LEFT JOIN 
    rooms AS Room USE INDEX (weekday, semester, year)
ON 
    Student.course = Room.course 
WHERE 
    Room.weekday = '$day' 
    AND Room.semester = '$semester' 
    AND Room.year = '$year' 
    AND Student.course IN ($implodedCourse);

错误的Laravel 10代码 #

$result = DB::table('students as Student use index(course)')
    ->select('Student.name', 'Room.id')
    ->join('rooms as Room use index(weekday, semester, year)', function($courseJoin){
        $courseJoin->on('Student.course', '=', 'Room.course'); 
    })
    ->whereIn('Student.course', $courses)
    ->where('Room.weekday', $day)
    ->where('Room.semester', $semester)
    ->where('Room.year', $year);

错误信息 #

Illuminate\Database\QueryException
SQLSTATE[HY000]: General error: 1 no such column: Student.name

生成的错误SQL #

select "Student"."name", "Room"."id" from "students" as "Student use index(course)" inner join "rooms" as "Room use index(weekday, semester, year)" on "Student"."course" = "Room"."course" where "Student"."course" in (1, 3) and "Room"."weekday" = 3 and "Room"."semester" = 1 and "Room"."year" = 2024

错误原因 #

1. Laravel Query Builder会将你传入的'students as Student use index(course)'中的Student use index(course)当作完整的表别名，自动添加引号包裹，导致数据库识别的表别名是Student use index(course)而非Student，后续查询Student.name时数据库找不到对应列。
2. 原CakePHP查询使用LEFT JOIN，但你误用了Laravel默认的join()方法（对应INNER JOIN），与原查询逻辑不一致。

修正后的Laravel 10代码 #

使用DB::raw()避免Laravel对表别名和USE INDEX语句进行错误转义，同时改用leftJoin()匹配原查询的连接类型：

$result = DB::table(DB::raw('students AS Student USE INDEX (course)'))
    ->select('Student.name', 'Room.id')
    ->leftJoin(DB::raw('rooms AS Room USE INDEX (weekday, semester, year)'), function($join) {
        $join->on('Student.course', '=', 'Room.course');
    })
    ->whereIn('Student.course', $courses)
    ->where('Room.weekday', $day)
    ->where('Room.semester', $semester)
    ->where('Room.year', $year)
    ->get(); // 需调用get()/first()执行查询

验证生成的SQL #

修正后生成的SQL与原CakePHP对应的原生SQL逻辑一致：

SELECT 
    `Student`.`name`, 
    `Room`.`id` 
FROM 
    students AS Student USE INDEX (course)
LEFT JOIN 
    rooms AS Room USE INDEX (weekday, semester, year)
ON 
    `Student`.`course` = `Room`.`course` 
WHERE 
    `Student`.`course` in (1, 3) 
    AND `Room`.`weekday` = 3 
    AND `Room`.`semester` = 1 
    AND `Room`.`year` = 2024

内容的提问来源于stack exchange，提问作者トロイ