Laravel Guru-如何在Laravel中执行复杂的SQL用例语句

Question

我得到了以下SQL查询。

SELECT 
CASE
WHEN start_time > end_time AND (CURTIME() > start_time OR CURTIME() < end_time) THEN 'open'
WHEN start_time < end_time AND (CURTIME() > start_time AND CURTIME() < end_time) THEN 'open'
ELSE 'closed'
END AS now
FROM user_time;

我必须在Laravel控制器上执行它。这是我试过的。

$sqlquery = "SELECT CASE
WHEN start_time > end_time AND (CURTIME() > start_time OR CURTIME() < end_time) THEN 'open'
WHEN start_time < end_time AND (CURTIME() > start_time AND CURTIME() < end_time) THEN 'open'
ELSE 'closed'
END AS NOW_open/closed
FROM users JOIN user_time;";
$getRestaurant = User::select('users.*','user_time.*', DB::raw($sqlquery));

它不起作用。首先，"<"one_answers">"全部变成&gt和&lt。我该如何解决这个问题？

Answer 1

嗨，朋友，您可以在select内部使用DB:：raw，如下所示，假设'start_time'和'end_time'来自user_time表

User::join('user_time', 'user_time.user_id', '=', users.id)
->select(DB::raw('(CASE
WHEN user_time.start_time > user_time.end_time AND (CURTIME() > user_time.start_time AND CURTIME() < user_time.end_time+24) THEN 'open'
WHEN user_time.start_time < user_time.end_time AND (CURTIME() < user_time.start_time AND CURTIME() > user_time.end_time) THEN 'open'
ELSE 'closed'
END) AS now_open'))->get();