SQL query to laravel db query-CodePudding

How to query this on laravel db query ?

SELECT DISTINCT notifications.id,users.firstname,users.lastname,notifications.created_at, notifications.read FROM notifications 
LEFT JOIN users ON users.id = notifications.user_id
LEFT JOIN (SELECT is_accepted,friend_id FROM friends) AS fnds
ON fnds.friend_id = notifications.user_to_notify
WHERE notifications.user_to_notify = 5
GROUP BY notifications.id,users.firstname,users.lastname,notifications.created_at, notifications.read ,fnds.is_accepted
ORDER BY notifications.created_at

CodePudding user response：

You could try this

DB::table("notifications")
->leftJoin("users", function($join){
    $join->on("users.id", "=", "notifications.user_id");
})
->leftJoin("where_subquery_group_1_ as fnds", function($join){
    $join->on("fnds.friend_id", "=", "notifications.user_to_notify");
})
->select("distinct notifications.id", "users.firstname", "users.lastname", "notifications.created_at", "notifications.read")
->where("notifications.user_to_notify", "=", 5)
->groupBy("notifications")
->get();

The conversion was done with this tool

https://jjlabajo.github.io/SQLtoEloquent/

CodePudding user response：

If you have a model for all your tables:

Notifications::leftJoin("users", "users.id", "=", "notifications.user_id")
                  ->leftJoin(("SELECT is_accepted,friend_id FROM friends") as "fnds", "fnds.friend_id", "notifications.user_to_notify")
                  ->where("notifications.user_to_notify" = 5)
                  ->groupBy("notifications.id", "users.firstname", "users.lastname","notifications.created_at", "notifications.read", "fnds.is_accepted")
                  ->orderBy("notifications.created_at")
                  ->get()