Laravel get latest record for each group

Question

I am trying to migrate some Raw SQL to an Eloquent (or Query Builder) scope on my model. My Parts history table looks like this:

+----+---------+--------+------------+
| id | part_id | status | created_at |
+----+---------+--------+------------+
|  1 |       1 |      1 | ...        |
|  2 |       1 |      2 | ...        |
|  3 |       2 |      1 | ...        |
|  4 |       1 |      2 | ...        |
|  5 |       2 |      2 | ...        |
|  6 |       1 |      3 | ...        |

Notice the same part_id can have multiple entries where the status is the same.

At the moment I use the following to select the latest status:

$part = Part::leftjoin( DB::raw("
 (SELECT t1.part_id, ph.status, t1.part_status_at 
  FROM (
    SELECT part_id, max(created_at) part_status_at
    FROM part_histories
    GROUP BY part_id) t1 
  JOIN part_histories ph ON ph.part_id = t1.part_id AND t1.part_status_at = ph.created_at) as t2
  )", 't2.part_id', '=', 'parts.id')->where( ... )

I am trying to make a scope on the parts model out of this, so far I have this:

public function scopeWithLatestStatus($query)
{
    return $query->join(DB::raw('part_histories ph'), function ($join) {
         $join->on('ph.part_id', '=', 't1.id')->on('t1.part_status_at', '=', 'ph.created_at');
      })
      ->from(DB::raw('(select part_id as id, max(created_at) part_status_at from part_histories GROUP BY part_id) t1'))
      ->select('t1.id', 'ph.part_status', 't1.part_status_at');
}

which is part way there (but still using some raw SQL), I just can't figure out the rest

Answer 1

You could rewrite your query as left join to get the same results

select a.* 
from part_histories a
left join part_histories b on a.part_id = b.part_id 
                            and a.created_at < b.created_at
where b.part_id is null

and I guess you can transform easily above query in your scope something like

public function scopeWithLatestStatus($query)
{
    return $query->leftJoin('part_histories as b', function ($join) {
                $join->on('a.part_id', '=', 'b.part_id')
                     ->where('a.created_at', '<', 'b.created_at');
            })
        ->whereNull('b.part_id')
        ->from('part_histories as a')
        ->select('a.*');
}

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

Edit using above query as has relation,To get the latest history for each part you can define a hasOne relation like

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class Part extends Model
{
    public function latest_history()
    {
        return $this->hasOne(\App\Models\PartHistory::class, 'part_id')
            ->leftJoin('part_histories as p1', function ($join) {
                $join->on('part_histories.part_id', '=', 'p1.part_id')
                    ->whereRaw(DB::raw('part_histories.created_at < p1.created_at'));
            })->whereNull('p1.part_id')
            ->select('part_histories.*');
    }
}

And then to load parts with their latest history you could eager load above defined mapping as

$parts = Part::with('latest_history')->get();

You will have a list of parts along with latest history as

Array
(
    [0] => Array
        (
            [id] => 1
            [title] => P1
            [latest_history] => Array
                (
                    [id] => 6
                    [created_at] => 2018-06-16 08:25:10
                    [status] =>  1
                    [part_id] => 1
                )

        )
....
)