Laravel 8 select query inside select query-CodePudding

I'm making a web app in Laravel 8 (trying to keep up with the cool kids) that lets me take a photo of an area of my body (location) and mark where I injected in the area (site) and rate the pain for next time.

Back in the old days when I knew where I stood with PHP/mySql this query would get me a list of locations with the latest marker (site) added to each location. I've used a location id of 3 as an example of the variable I'd use.

SELECT (SELECT updated_at FROM sites WHERE sites.location = 3 order by updated_at DESC LIMIT 1) AS updated FROM `locations` WHERE id = 3;

How do I do this in Laravel? This is where I've got to:

$locations = DB::table('locations')
->select('locations.id', 'locations.name', 'locations.url', 'sites.updated_at')
->leftJoin('sites', 'locations.id', '=', 'sites.location')
->where('locations.user', '=', $user->id)
->groupBy('sites.location')
->orderBy('updated_at', 'asc')
->get();

This gets me the list of locations but the sites.updated_at is not right so /i think the equivalent of the SQL query should do it.

Hopefully that's enough info to make a good question.

How do I get the query into the query, I don't know the correct terms for the question..

CodePudding user response：

$locations = Location::with(['sites'=> function ($query){
                $query->select('id', 'location','updated_at');
               }])
             ->orderBy(updated_at', 'asc')
             ->groupBy('id')
             ->get();

CodePudding user response：

You can use the function selectSub as follows:

DB::table('locations')->selectSub(function($query) use ($variableOfThree) { 
    $query->table('sites')->where(...);
  }, 'updated')
  ->where(...)
  ->get()