Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Yoh's avatar
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 !

0 likes
3 replies
tykus's avatar

Because an inner join in the raw query has not been replicated in the Eloquent query - whereHas is not a JOIN.

Yoh's avatar
Level 1

Thank you ! But can you tell me how to correct, the right method ? Have I to write all the query with 'join' method or can I still use my relationship 'followers', 'following' and 'categories' method from User Model ?

Yoh's avatar
Yoh
OP
Best Answer
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.