Laravel-如何将包含连接查询的派生表转换为雄辩的语法? [英] Laravel - How to convert a derived table containing concatenation queries into Eloquent syntax?

问题描述

如何将以下内容转换为口才的语法?我知道那里有一个派生表，所以不确定在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屋！