Use parameter with DB::raw in Laravel 9-CodePudding

I have this SQL query which returns all employees based on selected date :

SELECT employees.id as employee_id ,employees_salaries.*,CONCAT(first_name,' ',second_name,' ',third_name) as fullname FROM employees
JOIN employees_salaries
ON employees.id = employees_salaries.employee_id
inner join (
    select employee_id, max(effective_from) as MaxDate
    from employees_salaries
    where effective_from <= '2022-06-31'
    group by employee_id
) innerTable 
on employees_salaries.employee_id = innerTable.employee_id 
and employees_salaries.effective_from = innerTable.MaxDate;

and the same query in laravel using query builder like this:

$employees = DB::table('employees')
        ->join('employees_salaries', 'employees_salaries.employee_id', 'employees.id')
        ->join(DB::raw('(select employee_id, max(effective_from) as MaxDate from employees_salaries  where effective_from <= \'2022-06-31\' group by employee_id) innerTable'), function ($join) use ($searchDate) {
            $join->on('employees_salaries.employee_id', '=', 'innerTable.employee_id')
                ->on('employees_salaries.effective_from', '=', 'innerTable.MaxDate');
        })
        ->select(DB::raw("employees.id as employee_id,employees_salaries.*,CONCAT(first_name,' ',second_name,' ',third_name) as fullname"))
        ->get();

now, the problem is the date in the where clause (where effective_from <= \'2022-06-31\'), it should accept a parameter $searchDate instead of fixed string. But DB::raw seems to not accept any parameters. I've tried with selectRaw but it's not working either.

CodePudding user response：

For accepting a parameter, You will have to use double quotes. See the below example.

$date = '2022-01-01';
$employees = DB::table('employees')
->join('employees_salaries', 'employees_salaries.employee_id', 'employees.id')
->join(DB::raw("(select employee_id, max(effective_from) as MaxDate from employees_salaries  where effective_from <= {$date} group by employee_id) innerTable"), function ($join) {
    $join->on('employees_salaries.employee_id', '=', 'innerTable.employee_id')
        ->on('employees_salaries.effective_from', '=', 'innerTable.MaxDate');
})
->select(DB::raw("employees.id as employee_id,employees_salaries.*,CONCAT(first_name,' ',second_name,' ',third_name) as fullname"))
->get();