php - Laravel Query builder returns empty array despite it being not null -

based on http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ article

select * nested_category +-------------+----------------------+-----+-----+ | category_id | name                 | lft | rgt | +-------------+----------------------+-----+-----+ |           1 | electronics          | 1   | 20  | |           2 | televisions          | 2   | 9   | |           3 | tube                 | 3   | 4   | |           4 | lcd                  | 5   | 6   | |           5 | plasma               | 7   | 8   | |           6 | portable electronics | 10  | 19  | |           7 | mp3 players          | 11  | 14  | |           8 | flash                | 12  | 13  | |           9 | cd players           | 15  | 16  | |          10 | 2 way radios         | 17  | 18  | +-------------+----------------------+-----+-----+ 10 rows in set (0.00 sec) 

using laravel , raw query thus:

$leaf_nodes = db::select( db::raw("select name nested_category rgt = lft + 1") ); print_r(db::getquerylog()); var_dump($leaf_nodes); 

in browser expected results i.e.

array ( [0] => array ( [query] => illuminate\database\query\expression object ( [value:protected] => select name nested_category rgt = lft + 1 ) [bindings] => array ( ) [time] => 1 ) ) array (size=6)  0 =>    object(stdclass)[177]   public 'name' => string 'tube' (length=4)  1 =>    object(stdclass)[178]   public 'name' => string 'lcd' (length=3)  2 =>    object(stdclass)[179]   public 'name' => string 'plasma' (length=6)  3 =>    object(stdclass)[180]   public 'name' => string 'flash' (length=5)  4 =>    object(stdclass)[181]   public 'name' => string 'cd players' (length=10)  5 =>    object(stdclass)[182]    public 'name' => string '2 way radios' (length=12) 

why fail work using query builder?

$leaf_nodes = db::table('nested_category')                 ->select('name')                 ->where('rgt', '=', 'lft + 1')                 ->get();     print_r(db::getquerylog()); var_dump($leaf_nodes); 

back in browser:

array ( [0] => array ( [query] => select `name` `nested_category` `rgt` = ? [bindings] => array ( [0] => lft + 1 ) [time] => 1 ) ) array (size=0)   empty 

with $leaf_nodes array empty?

because where() method thinks you're passing down string compare column value (and fails because it's comparting rgt column value string "lft +1", literal).

if want use expression, wrap raw() :

->where('rgt', '=', \db::raw('lft + 1')) 

or use whereraw() method directly:

->whereraw('rgt = lft + 1')