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
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:
' INNER JOIN farm_blocks as frm_b CROSS JOIN crops c ' .'6' instead of 6)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:
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.
inner join without onSince 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.