Laravel eloquent select first row of each group by
My problem is solved by @zerofl4g's help.
This eloquent helped by @zerofl4g.
$query->select(DB::raw("SELECT MIN(id), name, subj_id FROM tutorials WHERE completed = 0 GROUP BY subj_id"));
It doesn't work for me but help me a lot. I don't know the precise reason why not worked form me but I think I am using it as subquery. So I got duplicate select and from errors. It must surely work for someone who want to use as single query.
My solution is just select columns with DB::raw and ->from('tutorials') is also optional, as it is subquery of a long query
$query->select(DB::raw('MIN(id) as id, name, subj_id'))->from('tutorials')->where('completed', false)->groupBy('subj_id');
Final eloquent I am using is
$query->select(DB::raw('MIN(id) as id, name, subj_id'))->where('completed', false)->groupBy('subj_id');`
Hope to be helpful.
If you want to return first row in group by, you can try the code below. I think what you want to get is the latest chapter in the subject that not finish.
$data = Tutorial::where('completed', 0)->get();$groups = $data->groupBy('subj_id');$results = [];foreach($groups as $group) { $results[] = head($group);}return $results;
Get all tutorials row
Then, group by
subj_id
. The data you get is like below
{ 1: [{ id: "2", name: "chapter 2", subj_id: "1", completed: "0" }, { id: "3", name: "chapter 3", subj_id: "1", completed: "0" }], 2: [{ id: "6", name: "chapter 3", subj_id: "2", completed: "0" }, { id: "7", name: "chapter 4", subj_id: "2", completed: "0" }]}
- Foreach each group and get the minimum id by using head() helper function in laravel.
Correct query will be like
$query->where('completed', false)->get()->groupBy('subj_id');
get()
will be call first before groupBy
. I mean once get()
make collection you can use all collection method. for more details and know all collection method https://laravel.com/docs/5.8/collections#method-groupby