Laravel eloquent giving different results from DB facade

Question

I have a laravel eloquent query that is giving me a different result from the same query using the DB facade. I cannot understand why the result would be different. The result set is the same length (6), but the one data object key ha stays the same for the eloquent query (which is not correct), while the DB facade returns the ha key values correctly.
Eloquent:

$add = FertilAppUser::join('farms', 'fertilappuser.farm_id', '=', 'farms.id')
            ->join('farm_blocks', 'farms.id', '=', 'farm_blocks.farm_id') 
            ->crossJoin('crops', 'farm_blocks.crop_id', '=', 'crops.id')
            ->select('fertilappuser.block_id', 'fertilappuser.id', 'farm_blocks.hectares as ha')
            ->where('fertilappuser.farm_id', '=',  16)
            ->whereNotIn('fertilappuser.block_id', [6])
            ->groupBy('fertilappuser.id')
            ->get();

The eloquent query returns a Collection of 6 object items, but the ha key stays the same:

                    Collection {
                        #items: array:6 [
                          0 =>  {
                            #original: array:3 [
                              "block_id" => 140
                              "id" => 7
                              "ha" => 2.5 // does not vary
                            ]
                          }
                          1 =>  {
                            #original: array:3 [
                              "block_id" => 809
                              "id" => 66
                              "ha" => 2.5 // does not vary
                            ]
                          }
                          2 =>  {
                            #original: array:3 [
                              "block_id" => 152
                              "id" => 67
                              "ha" => 2.5 // does not vary
                            ]
                          }
                          3 =>  {
                            #original: array:3 [
                              "block_id" => 143
                              "id" => 68
                              "ha" => 2.5 // does not vary
                            ]
                          }
                          4 =>  {
                            #original: array:3 [
                              "block_id" => 149
                              "id" => 69
                              "ha" => 2.5 // does not vary
                            ]
                          }
                          5 =>  {
                            #original: array:3 [
                              "block_id" => 673
                              "id" => 70
                              "ha" => 2.5 // does not vary
                            ]
                          }
                        ]
                      }

DB facade:

$add = DB::select('SELECT fau.id id, fau.block_id, frm_b.hectares ha ' .
        ' FROM fertilappuser as fau ' .
        ' INNER JOIN farms AS f ' .
            ' ON (fau.farm_id = f.id) ' .
        ' INNER JOIN farm_blocks as frm_b CROSS JOIN crops c ' .
            ' ON (fau.block_id = frm_b.id AND frm_b.crop_id = c.id) ' .
        ' WHERE fau.farm_id = ? AND fau.block_id NOT IN (?) ' .
        ' GROUP BY fau.id ', [16, '6']);

The DB facade returns an array of length 6 and the associated object key ha values vary and are correct.

array:6 [
  0 => {#985
    +"id": 7
    +"block_id": 140
    +"ha": 2.5 // correct
  }
  1 => {#983
    +"id": 66
    +"block_id": 809
    +"ha": null // correct
  }
  2 => {#988
    +"id": 67
    +"block_id": 152
    +"ha": null // correct
  }
  3 => {#1021
    +"id": 68
    +"block_id": 143
    +"ha": 4.3 // correct
  }
  4 => {#1022
    +"id": 69
    +"block_id": 149
    +"ha": 3.5
  }
  5 => {#1023
    +"id": 70
    +"block_id": 673
    +"ha": 2.53 // correct
  }
]

Does anyone know why there are differing results? Is my joins on the eloquent query maybe incorrect?

Laravel 5.6.39

Answer 1

Your query with eloquent is converted to the following SQL code:

select
    `fertilappuser`.`block_id`,
    `fertilappuser`.`id`,
    `farm_blocks`.`hectares` as `ha`
from
    `fertilappuser`
inner join
    `farms` on `fertilappuser`.`farm_id` = `farms`.`id`
inner join
    `farm_blocks` on `farms`.`id` = `farm_blocks`.`farm_id`
cross join
    `crops` on `farm_blocks`.`crop_id` = `crops`.`id`
where
    `fertilappuser`.`farm_id` = ?
and
    `fertilappuser`.`block_id` not in (?)
group by
    `fertilappuser`.`id`

Your SQL differs in some parts:

1. (probable culprit) ' INNER JOIN farm_blocks as frm_b CROSS JOIN crops c ' .
2. (probably less important) You're passing '6' instead of 6)
3. (not important at all) Aliases

Now, I'm not sure which query is correct, but basically different queries implies different results.

---

Just using the console (php artisan tinker) I was able to produce the following query (no setup needed since I'm not actually hitting any database)

select
    `fau`.`id` as `id`,
    `fau`.`block_id`,
    `frm_b`.`hectares` as `ha`
from
    `fertilappuser` as `fau`
inner join
    `farms` as `f` on (
        `fau`.`farm_id` = `f`.`id`
    )
inner join
    `farm_blocks` as `frm_b`
cross join
    `crops` as `c` on (
        `fau`.`block_id` = `frm_b`.`id` and `frm_b`.`crop_id` = `c`.`id`
    )
where `fau`.`farm_id` = ?
and `fau`.`block_id` not in (?)
group by `fau`.`id`

By running this code:

// You should be able to replace the first line by either
// DB::table('fertilappuser', 'fau')
// or FertilAppUser::from('fertilappuser', 'fau')
// to keep the alias
DB::connection('mysql')->table('fertilappuser', 'fau')
    ->select('fau.id as id', 'fau.block_id', 'frm_b.hectares as ha')
    ->join('farms as f', function ($join) {
        return $join->on(['fau.farm_id' => 'f.id']);
    })
    ->join('farm_blocks as frm_b', function ($join) {
        return $join;
    })
    ->crossJoin('crops as c', function ($join) {
        return $join->on(['fau.block_id' => 'frm_b.id', 'frm_b.crop_id' => 'c.id']);
    })
    ->where('fau.farm_id', 16)
    ->whereNotIn('fau.block_id', ['6']) // I still think this should be just 6
    ->groupBy('fau.id')
    ->toSql(); // replace with ->get(); to get the results

Some weird things here:

• Using array notations in the $joins.

Usually you'd write

$join->on(['fau.farm_id' => 'f.id'])

$join->on(['fau.block_id' => 'frm_b.id', 'frm_b.crop_id' => 'c.id'])

as

$join->on('fau.farm_id', 'f.id')

$join->on('fau.block_id', 'frm_b.id')->on('frm_b.crop_id', 'c.id')

but using the array notation tells eloquent to put a pair of parenthesis around it. I'm not sure if it actually helps your query or not but I wanted it to be exactly the same.

• The inner join without on

Since Eloquent forces you to add a condition when using the join() method, I just passed a closure and made it return the $join itself without adding anything.