Ad

How To Do Self Join In Query Builder No Eloquent

- 1 answer

Having hard time to do self join with query builder but getting unexpected results or query while using toSql() or get(), please don't suggest eloquent, just query builder with raw query.

Trying to get all child menus followed by their parent. Id column also required

$users = DB::table('all_menus as A all_menus as B')
->select(DB::raw('A.menu_name__v1 As menu_name_parent, B.menu_name__v1 As menu_name_child'))
->where('A.id', '=' 'B.parent_menu_id__v1')
->toSql();
Ad

Answer

join() method supports to define an alias name, so you can self join with alias name like this query:

$users = DB::table('all_menus as A')
           ->join('all_menus as B', 'A.id', '=', 'B.parent_menu_id__v1')
           ->select('A.menu_name__v1 AS menu_name_parent', 'B.menu_name__v1 AS menu_name_child')
           ->toSql();
Ad
source: stackoverflow.com
Ad