CODEWITHSUNDEEP
phpmysqllaravel
cookie
cookie

Reputation: 43

Laravel Query builder returns empty array despite it being not null

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

select * from 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 and with a raw query like thus:

$leaf_nodes = DB::select( DB::raw("SELECT name FROM nested_category WHERE rgt = lft + 1") );
print_r(DB::getQueryLog());
var_dump($leaf_nodes);

In my browser I get the expected results i.e.

Array ( [0] => Array ( [query] => Illuminate\Database\Query\Expression Object ( [value:protected] => SELECT name FROM nested_category WHERE 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 then does it fail to work using the 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 the browser:

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

With the $leaf_nodes array now empty?

Upvotes: 1

Views: 1864

Answers (1)

Damien Pirsy
Damien Pirsy

Reputation: 25445

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

If you want to use an expression, wrap it with raw() :

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

or use the whereRaw() method directly:

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

Upvotes: 4

Related Questions