John Main

I’ve been writing a system recently which does a lot of classic CMS functions, including creating and maintaining assets (these could be new items, marketing information or whatever) which need to be version controlled.
Each asset will have a unique reference, so to pull out the latest versions of each asset I need to do a subquery join, grouping on the reference, the SQL being along the lines of:

select assets.* from assets
inner join (select assets.reference, max(assets.created_at) as created_at from assets group by reference) as most_recent
where assets.reference = most_recent.reference and assets.created_at = most_recent.created_at

Now that’s not the simplest of SQL but it’s reasonably understandable to most of us. However try and translate that into Laravel’s query builder and you’ll have some troubles. It doesn’t like subqueries at the best of times, but when you try to make it join on a subquery then it has a complete meltdown! However after a lot of trial and error I’ve found a way:

$sub_query = \DB::table('assets')
    ->selectRaw('assets.reference, max(assets.created_at) as created_at')
    ->where('assets.active', true)
    ->groupBy('reference');

return Asset::select('assets.*')
    ->join(\DB::raw('(' . $sub_query->toSql() . ') as most_recent'), function ($join) {
        $join->on('assets.reference', '=', 'most_recent.reference')
            ->on('assets.created_at', '=', 'most_recent.created_at');
    })
    ->mergeBindings($sub_query)
    ->get();

Good grief that took some figuring out!