Laravel-如何将包含连接查询的派生表转换为雄辩的语法? [英] Laravel - How to convert a derived table containing concatenation queries into Eloquent syntax?
本文介绍了Laravel-如何将包含连接查询的派生表转换为雄辩的语法?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将以下内容转换为口才的语法?我知道那里有一个派生表,所以不确定在Laravel中什么是正确的语法?
How do I convert the following into Eloquent syntax? I know there is a derived table in there, so unsure what's the right syntax in Laravel for this?
SELECT firstname
, CONCAT_WS
( ', '
, CASE WHEN years = 0 THEN NULL ELSE CONCAT(years,' years') END
, CASE WHEN months = 0 THEN NULL ELSE CONCAT(months, ' months') END
, CASE WHEN days = 0 THEN NULL ELSE CONCAT(days, ' days') END
) lengthOfService
FROM
( SELECT firstname
, FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425) years
, FLOOR((DATEDIFF(CURDATE(),startdate)/365.2425 - FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425))* 12) months
, CEILING((((DATEDIFF(CURDATE(),startdate)/365.2425 - FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425))* 12)
- FLOOR((DATEDIFF(CURDATE(),startdate)/365.2425 - FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425))* 12))* 30) days
FROM users
) x
推荐答案
Laravel确实提供了hydrawRaw
Laravel does provide hydrateRaw
首先创建一个模型类,我将使用App \ Models \ MyHydratedUser
First make a model class, I will use App\Models\MyHydratedUser
$query = "SELECT firstname, CONCAT_WS ( ', '
, CASE WHEN years = 0 THEN NULL ELSE CONCAT(years,' years') END
, CASE WHEN months = 0 THEN NULL ELSE CONCAT(months, ' months') END
, CASE WHEN days = 0 THEN NULL ELSE CONCAT(days, ' days') END
) lengthOfService
FROM
( SELECT firstname
, FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425) years
, FLOOR((DATEDIFF(CURDATE(),startdate)/365.2425 -
FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425))* 12) months
, CEILING((((DATEDIFF(CURDATE(),startdate)/365.2425 -
FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425))* 12)
- FLOOR((DATEDIFF(CURDATE(),startdate)/365.2425 -
FLOOR(DATEDIFF(CURDATE(),startdate)/365.2425))* 12))* 30) days
FROM users
) x";
$users = App\Models\HydratedUser::hydrateRaw($query);
$ users变量将保存HydratedUser模型的集合
The $users variable will hold a Collection of HydratedUser models
这篇关于Laravel-如何将包含连接查询的派生表转换为雄辩的语法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文