Back

Eloquent / Fluent SubQuery select


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 !

jhdeveng replied 3 years ago

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");
Mushr0000m replied 3 years ago

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.

savanihd replied 10 months ago

Maybe bellow link example can help you : http://itsolutionstuff.com/post/laravel-5-join-with-subquery-in-query-builder-exampleexample.html


Sign in to participate in this thread!



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