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()
With illuminate/database v5.4 it's not possible without using raw() methods like :
selectRaw(),whereRaw(),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.