SELECT（MAX）SQL到Eloquent没有原始

Question

I'm migrating my scripts with Eloquent instead of SQL in my company.

I want to do this simple query with Eloquent, but I don't know how to do that.

SQL QUERY :

SELECT MAX(date), id
FROM myTable
WHERE people > 0
GROUP BY id

+--------------------------+
|    date    |     ID      |
+--------------------------+
| 2012-08-04 |     79      |
| 2013-04-13 |     56      |
| 2013-04-13 |     55      |
+--------------------------+

After several searches, the method with Eloquent that advised is:

    MyModel::orderBy('date', 'desc')->groupBy('id')->where('people', '>', 0);

But the result is not (and it's normal...) the same :

+--------------------------+
|    date    |     ID      |
+--------------------------+
| 2012-06-25 |     79      |
| 2012-06-25 |     56      |
| 2012-06-25 |     55      |
+--------------------------+

I would simply like to know if it's possible to do a simple SELECT(MAX) with Eloquent, without using selectRaw()

Answer 1

With illuminate/database v5.4 it's not possible without using raw() methods like :

• selectRaw(),
• whereRaw(),
• ....
• or with facade DB::raw()

With illuminate/database v5.7 you can do sub-queries in a leftJoin() for example, like :

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

See: https://laravel.com/docs/5.7/queries

In my case I can not use this version of illuminate, because from version 5.5, you have to use php 7.