在第二个参数中使用DB :: raw（）时的不同值where（）

Question

I have next query in Laravel Eloquent:

$buildings = Building::select('buildings.*')->join(
    DB::raw('('.
        (
            IngameBuilding::select('buildings.building_id', 'buildings.level')
                          ->join('buildings', 'buildings.id', '=', 'ingame_buildings.building_id')
                          ->toSql()
        ).
    ') as `added_buildings`'), 'added_buildings.building_id', '=', 'buildings.building_id')
    ->where('buildings.level', '>', 'added_buildings.level')
    ->get();

This query returns all allowed rows from base, but one row more. When I added DB::raw() in where() return values is valid.

Good-working code:

$buildings = Building::select('buildings.*')->join(
    DB::raw('('.
        (
          IngameBuilding::select('buildings.building_id', 'buildings.level')
                        ->join('buildings', 'buildings.id', '=', 'ingame_buildings.building_id')
                        ->toSql()
        ).
    ') as `added_buildings`'), 'added_buildings.building_id', '=', 'buildings.building_id')
    ->where('buildings.level', '>', DB::raw('`added_buildings`.`level`'))
    ->get();

Why first code workig, hmm.. Wrong?

Answer 1

I'm not a big fan of Laravel at all. I've got only small experience with this framework but i'm almost sure that where function accepts only a 'constant' values to be checked against.

If you'll get an output of this query using toSQL method on the query object you will see that eloquent will convert it as something like:

(...) where buildings.level > 'added_buildings.level'

so the condition checks if the buildings.level (whatever the type is) is greater than the given string and not the column value.

Using the DB::raw you're getting the proper sql as the eloquent won't parse/convert it.

You would need to use whereRaw method I suppose. http://laravel.com/docs/4.2/queries#introduction