对laravel db查询的SQL查询

Question

如何在laravel db查询中查询这个?

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

Answer 1

你可以试试这个

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();

转换是用这个工具完成的

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

Answer 2

如果您有一个所有表的模型:

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()