Use parameter with DB::raw in Laravel 9

Question

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.

score 0 · Accepted Answer · answered Jul 22 '22 at 11:15

0

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

answered Jul 22 '22 at 11:15

AKT

56
5

does this prevent sql injections as well ? – Yu_Jain Jul 22 '22 at 11:17
1

Yes it does, But I would suggest not to use raw queries. You can use Laravel's relationships for better. – AKT Jul 22 '22 at 11:21
1

I've read that Laravel Eloquent is slower than query builder. Therefore, I am trying to use query builder as much as possible – Yu_Jain Jul 22 '22 at 13:19
1

Yeah right, but eloquent can be faster too by using in correct way. But still DB queries are much faster. Did the above solution work for you? – AKT Jul 25 '22 at 09:00

Use parameter with DB::raw in Laravel 9

1 Answers1