How use with() when also joining a table

Question

I have the following tables (with only relevant fields):

devices

id
name
created_at
updated_at

device_reports

id
device_id
location
created_at
updated_at

I have a report with a number of filters on it that is already working, so I want to stick with the eloquent way of doing things. Here is Controller function:

public function devices(Request $request)
{
    $devicesQuery = Device::with(['latestReport']);

    if ($request->ajax())
    {

        if($request->input('start') && $request->input('start')!='')
        {
            $start_date = date('Y-m-d', strtotime($request->input('start')));
            $end_date =  date('Y-m-d', strtotime($request->input('end')));
            $devicesQuery = $devicesQuery->lastReportBetween($start_date,$end_date);
        }

        $devices = $devicesQuery->paginate(10);

        return Response::json(View::make('devices/table', array('devices' => $devices))->render());
    }
}

The model's latestReport is defined as:

public function latestReport()
{
    return $this->hasOne('App\Models\DeviceReport')->latest();
}

The model's function lastReportBetween is defined as:

public function scopeLastReportBetween($query, $start, $end)
{
    $query = $query->join('device_reports AS dr', 'dr.device_id', '=', 'devices.id');
    $query = $query->where('dr.id', '=', DB::raw('(SELECT max(dr2.id) FROM device_reports AS dr2 WHERE dr2.device_id = devices.id)'));
    $query = $query->where(DB::raw("(IFNULL(dr.gps_time, dr.created_at))"), '>=', DB::raw("STR_TO_DATE('".$start."', '%Y-%m-%d')"));
    $query = $query->where(DB::raw("(IFNULL(dr.gps_time, dr.created_at))"), '<=', DB::raw("STR_TO_DATE('".$end."', '%Y-%m-%d')"));

    return $query;
}

When running the above with a start/end date selected, I get the correct records returned, but I don't get anything returned in "latestReport", but when I run the page without the date filters in place, it correctly returns the device information and the most recent report record in the latestReport class variable.

Can anyone help me understand how to change this code such that I do get the latestReport back when I also call the lastReportBetween function?

Pretty sure you manually need to protect DB::raw() against SQL injections on the $start and $end PHP variables.. i believe DB::qoute(..) or DB::escape() works not sure annymore if those are even valid functions in your Laravel version. Otherwise you need to do DB::connection()->getPdo()->quote(..) — Raymond Nijland
– Raymond Nijland, Commented Feb 12, 2019 at 20:34

LebowskiT1000 · Accepted Answer · 2019-02-12 21:19:57Z

1

I figured out my problem. I should have been using "whereHas()" instead of manual joins and whatnot.

public function scopeLastReportBetween($query, $start, $end)
{
    return $query->whereHas('latestReport', function($reportsQuery) use ($start, $end)
            {
                $reportsQuery->whereBetween('created_at', [$start, $end])
                    ->where('device_reports.id', '=', DB::raw('(SELECT max(dr2.id) FROM device_reports AS dr2 WHERE dr2.device_id = device_reports.device_id)'));

            });
}

answered Feb 12, 2019 at 21:19

LebowskiT1000

211 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How use with() when also joining a table

devices

device_reports

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

devices

device_reports

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related