Join eloquent collection with result of procedure

user_14048515 0
Viewed 58

I have the following eloquent collection:

$cars = Car::with(['model'])->get();

I also have a mysql stored procedure that calculates some measure based on a date range:

$getMeasure = DB::select(
            'CALL measure("'.$start_date.'", "'.$end_date.'")'
         );

The two columns resulted from the procedure are car_id and measure.

Is it possible when selecting the cars to also get the measure column by joining it with the result of the procedure?

I have tried to do this with:

$cars = Car::with(['model'])
        ->join('call measure("'.$start_date.'", "'.$end_date.'")', 'car.id', '=', 'measure.car_id')
        ->get();

but this doesn't work as it's looking for a table called 'call measure'.

eloquent laravel mysql
1 Answers

According to most of the answers here: How can I join on a stored procedure? Using a Stored Procedure (SP) for joins is not really a viable strategy.

You're better off inlining the SP as a subquery, or use a function.

$measures = DB::query()
    ->select('.... as car_id', '.... as measure') // use DB::raw() or selectRaw() if the expressions contain functions, aggregate values or computed values
    ->from(....)
    .... // no ->get();

$cars = Car::with(['model'])
    ->joinSub($measures, 'measures', function ($join) {
        $join->on('cars.id', '=', 'measures.car_id');
    })
    ->get();

Last time I checked, Laravel had a bit of an issue with SP, where you needed to pass some specific options to the underlying PDO instance to make it work.

If you use this a lot throughout your code and want to avoid repeating the code over and over, I think you can make a query builder macro for the subquery.

user_43394020
Related