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

IGP · Accepted Answer · 2021-05-08T21:05:47.480

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:

(probable culprit) ' INNER JOIN farm_blocks as frm_b CROSS JOIN crops c ' .
(probably less important) You're passing '6' instead of 6)
(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.

Thank you. How did you output the query from eloquent into SQL syntax? You are right I believe with your assessment at bullet number 1. How do I write that query `' INNER JOIN farm_blocks as frm_b CROSS JOIN crops c ' ` in eloquent? — Hmerman6006, May 08 '21 at 20:30
Simply change `get()` to `toSql()` at the end of your query. Instead of a collection, you'll get a string with the sql. It's the best way to make sure you're getting the query you want if you're trying to convert a query into eloquent. — IGP, May 08 '21 at 20:43
Thanks again. How do you write in eloquent this part `' INNER JOIN farm_blocks as frm_b CROSS JOIN crops c '`. I have tried this `->join('farm_blocks', function ($join) { $join->on( 'farms.id', '=', 'farm_blocks.farm_id')->crossJoin('crops', 'farm_blocks.crop_id', '=', 'crops.id') }) `, but I get an error. Can you make that part of your answer please? — Hmerman6006, May 08 '21 at 20:48
Without knowing the exact error there's little I can do to help. In any case, I've edited the answer with the query builder code needed to reproduce your query. — IGP, May 08 '21 at 21:06
Maybe your error was simply that you forgot adding `;` near the end `'crops.id'); })` — IGP, May 08 '21 at 21:16
No my eloquent version of the `crossJoin` gives a `Syntax error or access violation` error. Your query on the other hand worked perfectly thank you. Just one correction when using `from` it should be `from('fertilappuser as fau')`. Thank you again for your time. — Hmerman6006, May 08 '21 at 21:25
Did the query I posted in the answer work for you? What laravel version are you using? — IGP, May 08 '21 at 21:26
Yes it did. Thank you. You are a wizard and know what you are talking about. Laravel 5.6.39. Added version to my question also. — Hmerman6006, May 08 '21 at 21:29

Laravel eloquent giving different results from DB facade

1 Answers1