Laravel原始子查询同表

Question

I have a table Users with these columns:

     $users = User::select(['id','name','level','parent','updated_at'])
     ->where('level','>',1)
     ->get();

Output:

      id   name      level       parent   updated_at
      ----+---------+-----------+--------+-------------
      12   Jhon      1           0        2016-02-01
      99   Carl      2           12       2016-02-01

Then: I try this for show column parent how name id associated for example, in second row, show Jhon replacing 12.

I tried this code... Any help to read...

     $users = User::select(['id','name','level',
     DB::raw("SELECT * FROM users WHERE id = users.parent)
     'updated_at'])
     ->where('level','>',1)
     ->get();

Expected Output:

      id   name      level       parent   updated_at
      ----+---------+-----------+--------+-------------
      12   Jhon      1                    2016-02-01
      99   Carl      2           Jhon     2016-02-01

Answer 1

You want a leftJoin to get the parent name:

$users = User::select(['users.id','users.name','users.level', 'parent.name')
 ->leftJoin('users AS parent','users.parent','=','parent.id')
 ->where('users.level','>',1)
 ->get();