laravel eloquent relation with 3 table

Question

Hi I want query from 3 table

user:

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Denny     |
|  2 | Agus      |
|  3 | Dini      |
|  4 | Angel     |
+----+-----------+

History_Education

+----+-----------+-------------+
| id |   userId  | educationId |
+----+-----------+-------------+
|  1 |  1        | 1           |
|  2 |  1        | 2           |
|  3 |  2        | 1           |
|  4 |  2        | 2           |
+----+-----------+-------------+

Education

+----+-----------+----------+
| id |   level   |   Name   |
+----+-----------+----------+
|  1 | 1         |   SD     |
|  2 | 2         |   SMP    |
|  3 | 3         |   SMA    |
|  4 | 4         |   S1     |
+----+-----------+----------+

How to query with laravel Eloquent to get the latest user education order by Level DESC expected:

+----+-----------+----------------------+
| id |   Name    |   Latest_Education   |
+----+-----------+----------------------+
|  1 | Denny     |   SMP                |
|  2 | Agus      |   SMP                |
|  3 | Dini      |    -                 |
|  4 | Angel     |    -                 |
+----+-----------+----------------------+

In normal query: select id,name ,(select E.name from education E inner join History_eductaion HE on E.id = HE.education_id where HE.userId =U.id limit 1 order by E.level DESC )latest_education from USER U

How to translate to laravel eloquent?

Mark Menzies Mark Menzies · Accepted Answer · 2017-11-05T14:40:09

The quick answer is use the query builder - your query needed to be altered slightly to match the table names and columns as listed in your question:

$result = DB::table('user')
    ->select([
        'id',
        'name',
        DB::raw("(select E.name from Education E inner join History_Education HE on E.id = HE.educationId where HE.userId = user.id order by E.level DESC limit 1) as latest_education")
    ])
    ->get();

laravel eloquent relation with 3 table

1 Answers