Support the ongoing development of Laravel.io →
Database Eloquent

Hi,

I can't find a way to do a left join to count elements from pivot table using Eloquent and Fluent. My SQL query is this :

SELECT settings.*
FROM settings
LEFT JOIN (
	SELECT COUNT(*) AS counter
	FROM favorites
	GROUP BY setting_id
) favorites ON (favorites.setting_id = settings.id)
WHERE settings.state = 1
ORDER BY favorites.counter DESC

So I want to use Eloquent to get my main part, like so :

$settings = Setting::where('state', '=', 1)->get();

And left join to that my count. In this example I have one count but in real I have 3 counts, so if I don't do subselect I can have Cartesian product issue.

Thanks for your help !

Last updated 3 years ago.
0

I have tried similar things but I never seem to be able do it without using DB:raw, if you find a way I would love to know.

Setting::select("settings.*")
    ->leftJoin(DB::raw("(SELECT COUNT(*) AS counter FROM favorites GROUP BY setting_id) favorites", "favorites.setting_id", "=", "settings.id")
    ->where("settings.state", "=", 1)
    ->orderBy("favorites.counter", "DESC");
Last updated 3 years ago.
0

Hi ! Are you sure your solution works because you don't return 'favorites.setting_id' from your leftJoin raw select so Eloquent can't perform the ON join clause.

Last updated 3 years ago.
0

Sign in to participate in this thread!

PHPverse

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.