0

I am using https://github.com/owen-it/laravel-auditing which logs every change to every model (which uses a special trait) to a table called audits:

  • id
  • auditable_type = name of the model class which was changed
  • auditable_id = id of the model which was changed
  • created_at = date of change
  • ...

With the following example data:

| id | auditable_type | auditable_id | created_at |
|----|----------------|--------------|------------|
| 1  | App\City       | 1            | 2019-01-01 |
| 2  | App\City       | 2            | 2019-01-02 |
| 3  | App\City       | 2            | 2019-01-02 |

Now I wanna query the two latest changes (order by created_at DESC) of all models, which is pretty easy:

Audit::query()
    ->limit(2)
    ->orderBy("created_at", "DESC")
    ->get()

Which will result to this:

| id | auditable_type | auditable_id | created_at |
|----|----------------|--------------|------------|
| 2  | App\City       | 2            | 2019-01-02 |
| 3  | App\City       | 2            | 2019-01-02 |

But how to get the latest changed model rows, not the latest changes:

| id | auditable_type | auditable_id | created_at |
|----|----------------|--------------|------------|
| 1  | App\City       | 1            | 2019-01-01 |
| 3  | App\City       | 2            | 2019-01-02 |

Normally, I would use GroupBy, but thats not possible (directly) on eloquent models, right?

bernhardh
  • 3,137
  • 10
  • 42
  • 77
  • You can use groupBy with the query builder... `groupBy('auditable_type', 'auditable_id')` – Devon Bessemer Apr 01 '19 at 12:59
  • Yes, you can use `groupBy` on a collection. as shown in the [Laravel documentation](https://laravel.com/docs/5.8/collections#method-groupby). You can also `groupBy` on a query builder as shown [here](https://laravel.com/docs/5.8/queries#ordering-grouping-limit-and-offset). –  Apr 01 '19 at 13:01
  • did your db server supports window functions? https://en.wikipedia.org/wiki/SQL_window_function – michail1982 Apr 01 '19 at 14:17
  • @devon and @swonder: That was my first guess, but then I get the `this is incompatible with sql_mode=only_full_group_by` error. – bernhardh Apr 03 '19 at 15:14
  • @michail1982: nope. MySQL 5.7 – bernhardh Apr 03 '19 at 15:15
  • Ah.. I now found the solution. Just use group by and disable strict mode: https://stackoverflow.com/a/50393074/1006818 – bernhardh Apr 03 '19 at 15:41

1 Answers1

0

You can try this :

public function latest($column = 'created_at')
{
    return $this->orderBy($column, 'desc');
} 
Audit::->latest()->take(2)->get();
Manisha
  • 70
  • 8