Ryan Exlay Ryan Exlay - 7 months ago 177
PHP Question

Laravel eloquent select first row of each group by

I have subjects table and tutorials table in SQLite database. I am trying to select current tutorial. There are my tables


  • Subjects table:

    -------------------
    | id | name |
    -------------------
    | 1 | Chemistry |
    | 2 | Physic |
    -------------------

  • Tutorials table:

    -----------------------------------------
    | id | name | subj_id | completed |
    -----------------------------------------
    | 1 | chapter 1 | 1 | 1 |
    | 2 | chapter 2 | 1 | 0 |
    | 3 | chapter 3 | 1 | 0 |
    | 4 | chapter 1 | 2 | 1 |
    | 5 | chapter 2 | 2 | 1 |
    | 6 | chapter 3 | 2 | 0 |
    | 7 | chapter 4 | 2 | 0 |
    -----------------------------------------



My current eloquent is:

$query->where('completed', false)->groupBy('subj_id')->get();


And it returns the following:

-----------------------------------------
| id | name | subj_id | completed |
-----------------------------------------
| 3 | chapter 3 | 1 | 0 |
| 7 | chapter 4 | 2 | 0 |
-----------------------------------------


Now I wish to select id 2 and 6.

-----------------------------------------
| id | name | subj_id | completed |
-----------------------------------------
| 2 | chapter 2 | 1 | 0 |
| 6 | chapter 3 | 2 | 0 |
-----------------------------------------


How could I get id 2 and 6 using sqlite?

Answer

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.

Comments