Alejandro Beltran Alejandro Beltran - 4 months ago 8
MySQL Question

Laravel raw subQuerie same table

I have a table Users with this 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

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();