CODEWITHSUNDEEP
phpmysqllaravel
user2228716
user2228716

Reputation: 115

Laravel left join only the most recent row

I'm looking for a way (preferably using Laravel's query builder) to only join the "most recent row" on two tables that have a one-to-many relation.

Here's a simplified example of what I mean:

Table 1 - robots
robotID
name

Table 2 - locations
locationID
robotID
latitude
longitude
date (Y-m-d)

I'd like to have a list of robots with only their most recent row from locations (ordered by locations.date). I've found that it sounds very simple (so the answer could be obvious!) but I can't get the expected outcome very easily without using a for-loop, which is very slow.

The expected outcome is preferably each robot with their most recent location. I've tried the following, but it's not as expected. I could just be having a slow day.

Robot::whereIn('robots.robotID', [1,2,3,4])
->leftJoin('locations', function ($join) {
    $join->on('locations.robotID', '=', 'robots.robotID');
})
->orderBy('locations.date', 'desc')
->groupBy('robots.robotID')
->get();

Thanks in advance!

Upvotes: 0

Views: 1925

Answers (2)

apokryfos
apokryfos

Reputation: 40673

groupBy isn't meant to be used that way. Group by is meant to create groups of data for the purposes of aggregation. If you group by a column then the column values of all other non-aggregated columns is arbitrary and does not necessarily match the others, which is bad. You need to heavily revise your logic before you can achieve what you need, or you can use Eloquent relationships to get robots with the most recent related location as a relationship entry.

I will be suggesting the later because it looks a lot better in OOP code (not necessarily because it's better or faster).

Admittedly this solution is a bit hacky and has one major drawback. The query still gets all models in the background but will only match one with each robot.

Define your relationship in the model:

class Robot extends Model {
  ...
  public function latestLocation() { 
       $this->hasOne(Location::class, 'robotID', 'locationID')->latest();
  }
}


$robots = Robot::whereIn('robots.robotID', [1,2,3,4])
->with('latestLocation')
->get();

Then you can access the location via e.g.

 $robots->first()->latestLocation;

Upvotes: 1

Jigius
Jigius

Reputation: 315

I think you have to using something like this query for your target:

SELECT l.*, r.name AS robot_name
FROM location l
JOIN (
  SELECT robotID, MAX(date) AS most_recent
  FROM location
  GROUP BY robotID) AS max
ON l.robotID=max.robotID AND l.date=max.most_recent
JOIN robots r ON r.robotID=l.robotID
WHERE r.robotID IN (1, 2, 3, 4)
ORDER BY l.date DESC

Upvotes: 2

Related Questions