1

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'.

Alexandru Tugui
  • 23
  • 3
  • This can be achieved with [Query Builder](https://laravel.com/docs/8.x/queries) if that's an option for you. Generally, you would have to run your stored procedure in a subquery and provide an alias to the subquery. The alias would then act as the arbitrary table name. Could you [edit your question](https://stackoverflow.com/posts/70709742/edit) and provide your related database schema and the implementation of the `model(...)` relationship method in your `Car.php` model file? – steven7mwesigwa Jan 14 '22 at 11:37

1 Answers1

1

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.

IGP
  • 14,160
  • 4
  • 26
  • 43