Get the latest record on the group in the most Eloquent way 😎

[akaamitgupta]

https://medium.com/@akaamitgupta/get-the-latest-record-on-the-group-in-the-most-eloquent-way-50597a588361

[sebastiaanluca]

Good tip!

Remembered I had to solve this in the past too. For those interested:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;

trait GroupedLastScope
{
    /**
     * Get the latest entry for each group.
     *
     * Each group is composed of one or more columns that make a unique combination to return the
     * last entry for.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param array|null $fields A list of fields that's considered as a unique entry by the query.
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeLastPerGroup(Builder $query, ?array $fields = null) : Builder
    {
        return $query->whereIn('id', function (QueryBuilder $query) use ($fields) {
            return $query->from(static::getTable())
                ->selectRaw('max(`id`)')
                ->groupBy($fields ?? static::$groupedLastScopeFields);
        });
    }
}

[exSnake]

Good tip!

Remembered I had to solve this in the past too. For those interested:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;

trait GroupedLastScope
{
    /**
     * Get the latest entry for each group.
     *
     * Each group is composed of one or more columns that make a unique combination to return the
     * last entry for.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param array|null $fields A list of fields that's considered as a unique entry by the query.
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeLastPerGroup(Builder $query, ?array $fields = null) : Builder
    {
        return $query->whereIn('id', function (QueryBuilder $query) use ($fields) {
            return $query->from(static::getTable())
                ->selectRaw('max(`id`)')
                ->groupBy($fields ?? static::$groupedLastScopeFields);
        });
    }
}

Usage example?

[joefreire]

@sebastiaanluca has some exemple?

[sebastiaanluca]

My memory's a little foggy trying to remember this use case 😅 Given the example in the opening post where a users has a lot of messages and we want the last message per user:

$latest = Message::query()
    ->lastPerGroup(['user_id'])
    ->get()

Which will (should) return a list of recent messages, one per user.

If you want to be sure you're getting the latest by date instead of by auto-incremented ID, I suppose you can change max(id) to max(created_at) in the scope.

[eduardr10]

Good tip!

Remembered I had to solve this in the past too. For those interested:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;

trait GroupedLastScope
{
    /**
     * Get the latest entry for each group.
     *
     * Each group is composed of one or more columns that make a unique combination to return the
     * last entry for.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param array|null $fields A list of fields that's considered as a unique entry by the query.
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeLastPerGroup(Builder $query, ?array $fields = null) : Builder
    {
        return $query->whereIn('id', function (QueryBuilder $query) use ($fields) {
            return $query->from(static::getTable())
                ->selectRaw('max(`id`)')
                ->groupBy($fields ?? static::$groupedLastScopeFields);
        });
    }
}

Where should I put this code?

I have tried to make this work and I have not been able to achieve it.

[exSnake]

Good tip! Remembered I had to solve this in the past too. For those interested:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;

trait GroupedLastScope
{
    /**
     * Get the latest entry for each group.
     *
     * Each group is composed of one or more columns that make a unique combination to return the
     * last entry for.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param array|null $fields A list of fields that's considered as a unique entry by the query.
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeLastPerGroup(Builder $query, ?array $fields = null) : Builder
    {
        return $query->whereIn('id', function (QueryBuilder $query) use ($fields) {
            return $query->from(static::getTable())
                ->selectRaw('max(`id`)')
                ->groupBy($fields ?? static::$groupedLastScopeFields);
        });
    }
}

Where should I put this code?

I have tried to make this work and I have not been able to achieve it.

It's a trait, in a different php file then "Use GroupedLastScope" on the top of your model

[sinall]

What if the table doesn't have a id column?