Laravel Query builder returns empty array despite it being not null

Question

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?

Maybe the "lft + 1" is interpreted as a string instead of the operation it's supposed to be. Have you tried to put the name of the column between accents (like the rgt column)? — Osuwariboy
– Osuwariboy, Commented Jul 23, 2015 at 12:56

Damien Pirsy · Accepted Answer · 2015-07-23 12:56:47Z

4

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')

answered Jul 23, 2015 at 12:56

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel Query builder returns empty array despite it being not null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related