How to convert From Raw MYSQL Query to Laravel 10 Query Builder with use index(some_column)

Question

I wanted to convert my Cakephp to Laravel 10.
Here in 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);

then this is now the raw query.

"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);"

After converting it to Laravel 10 I got an error. Here is my code.

$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);

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

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

Please help me. Thanks in advance.

Solved... Query Referrence here

Answer 1

The main issue here is that Laravel wasn't able to understand the use index() in your code.

For Laravel 9 and above, 3 new methods useIndex, forceIndex and ignoreIndex were introduced to the Illuminate\Database\Query\Builder that allows adding index hints.

$result = DB::table('students as Student')
    ->useIndex('course')
    ->select('Student.name', 'Room.id')
    ->leftJoin(DB::raw('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)
    ->get();

You'll need to use DB::raw() for adding index hints on joins though

For Laravel < 9, use DB::raw() in the table() method

$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 ($courseJoin) {
        $courseJoin->on('Student.course', '=', 'Room.course');;
    })
    ->whereIn('Student.course', [1, 2])
    ->where('Room.weekday', 2)
    ->where('Room.semester', 3)
    ->where('Room.year', 2024)
    ->get();

Answer 2

I assume two tables mentioned in the query correctly named as students & rooms.

Try this.

$results = DB::table('students as Student')
    ->select('Student.name', 'Room.id')
    ->join('rooms as Room', 'Student.course', '=', 'Room.course')
    ->whereIn('Student.course', $courses)
    ->where('Room.weekday', $day)
    ->where('Room.semester', $semester)
    ->where('Room.year', $year)
    ->get();

In whereIn clause you can also specify data instead of $courses as an array format like this-> [1,2,3].

implement this.i think it will work.If not then let me know.

N:B :- remember to import DB facade to the top of the script by using use Illuminate\Support\Facades\DB;.

In case you need to use index(some_columns) in the query try this.

$result = DB::table(DB::raw('students as Student USE INDEX(course)'))
    ->select('Student.name', 'Room.id')
    ->join(DB::raw('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)
    ->get();

Check this solution also and let me know.