Because an inner join in the raw query has not been replicated in the Eloquent query - whereHas is not a JOIN.
Apr 16, 2021
3
Level 1
Complexe request with Eloquent
Hi,
I've been tearing my hair out for more than a day on an SQL query that I can't formulate with Eloquent ... It's dramatic.
When I write this :
$categories = DB::select(DB::raw("
SELECT categories.*, FLOOR(count(*) * 100 / {$nbFollowers}) as nb_categories
FROM users
INNER JOIN user_following ON user_following.user_source = users.id
INNER JOIN user_category ON user_category.user_id = user_following.user_source
INNER JOIN categories ON user_category.category_id = categories.id
WHERE user_following.user_target = {$userId} GROUP BY categories.id"));
it's works ! I get all categories of all users that following me AND the number of each category.
[
{
"id": "1",
"lib": "pingpong",
"path": "/img/category/png/pingpong.png",
"nb_categories": "1"
},
{
"id": "2",
"lib": "test",
"path": "/img/category/png/test.png",
"nb_categories": "2"
},
BUT ! When I try to do the same with Eloquent ...
$categories = Category::whereHas('users.following', function ($q) use ($userId) {
return $q->where('user_target', '=', $userId);
})
->select('id', 'lib', 'path', DB::raw('count(*) as nb_categories'))
->groupBy('id')
->get();
I get all categories of all users that following me BUT the nb_categories still equals to 1 ...
[
{
"id": 1,
"lib": "pingpong",
"path": "/img/category/png/pingpong.png",
"total": "1"
},
{
"id": 2,
"lib": "test",
"path": "/img/badges/png/test.png",
"total": "1"
},
Can someone explain to me where I am wrong ?
Thank you VERY MUCH !
Level 1
I resolved the issue with use of
DB::select(DB::raw(myCustomRequest))
I use it in my API for stats so doesn't need Eloquent or Model :)
Please or to participate in this conversation.