Laravel last news item by category id

Question

I'm trying to get an overview of the last news items grouped by category id.

Example data:

+----+--------+-------------+-------------------------+-------------------------+
| id | title  | category_id |       created_at        |       updated_at        |
+----+--------+-------------+-------------------------+-------------------------+
|  1 | Item 1 |           1 | 2020-08-01 12:00:00.000 | 2020-08-01 12:00:00.000 |
|  2 | Item 2 |           2 | 2020-08-02 12:00:00.000 | 2020-08-02 12:00:00.000 |
|  3 | Item 3 |           4 | 2020-08-02 12:00:00.000 | 2020-08-02 12:00:00.000 |
|  4 | Item 4 |           1 | 2020-08-04 12:00:00.000 | 2020-08-04 12:00:00.000 |
|  5 | Item 5 |           2 | 2020-08-11 12:00:00.000 | 2020-08-11 12:00:00.000 |
+----+--------+-------------+-------------------------+-------------------------+

And I want the output:

+----+--------+-------------+-------------------------+-------------------------+
| id | title  | category_id |       created_at        |       updated_at        |
+----+--------+-------------+-------------------------+-------------------------+
|  3 | Item 3 |           4 | 2020-08-02 12:00:00.000 | 2020-08-02 12:00:00.000 |
|  4 | Item 4 |           1 | 2020-08-04 12:00:00.000 | 2020-08-04 12:00:00.000 |
|  5 | Item 5 |           2 | 2020-08-11 12:00:00.000 | 2020-08-11 12:00:00.000 |
+----+--------+-------------+-------------------------+-------------------------+

Is there a easy way to do this with Laravel Eloquent?

Answer 1

You will need a self join to news table to pick the latest news for each category like

select n.*
from news n
left join news n1 on n.category_id = n1.category_id
and n.created_at < n1.created_at
where n1.category_id is null

DEMO

Using query builder you might rewrite it as

DB::table('news as n')
  ->select('n.*')
  ->leftJoin('news as n1', function ($join) {
        $join->on('n.category_id', '=', 'n1.category_id')
             ->whereRaw(DB::raw('n.created_at < n1.created_at'));
   })
  ->whereNull('n1.category_id')
  ->get();