I have a table pages like

 ---- ----------- ---------- 
| id | slug      | revision |
 ---- ----------- ---------- 
|  1 | foo       |        0 |
|  2 | bar       |        0 |
|  3 | bar       |        1 |
 ---- ----------- ---------- 

The slug is unique (not as a key) but among all revisions. Thus, it could be used as a group by statement, if this is helpful.

Technically I have a solution for getting the latest revision only:

SELECT a.*
FROM `pages` a
  LEFT JOIN `pages` b
      ON a.slug = b.slug AND a.revision < b.revision
WHERE b.revision is NULL

In my approach I can use a select * to not take care about all the upcoming table cells.

How can I do that more elegant using Laravel Eloquent? Ideally I implement the logic directly in the Page model so that a simple Page::all() will give me the highest revisions only.

This is my model:

class Page extends Model
{
    use HasFactory, SoftDeletes, Sluggable;

    public function getRouteKeyName()
    {
        return 'slug';
    }

    protected $fillable = [
        'title',
        'slug',
        'revision',
    ];

    public function sluggable(): array
    {
        return [
            'slug' => [
                'source' => 'title',
            ],
        ];
    }

}
`` 

CodePudding user response：

This should work:

Page::select('slug', DB::raw('MAX(revision) as revision'))
    ->groupBy('slug')
    ->get();

CodePudding user response：

Using select * in this query can be archived by setting strict to false in config/database.php.

Then I needed to add a global scope:

class LatestScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        return $builder->select('*', DB::raw('MAX(revision) as revision'))
            ->groupBy('slug');
    }
}

and finally add the global scope to the Model:

class Page extends Model
{
    ...

    /**
     * The "booted" method of the model.
     *
     * @return void
     */
    protected static function booted()
    {
        static::addGlobalScope(new LatestScope);
    }

}