omrakhur omrakhur -3 years ago 64
MySQL Question

Laravel - How to convert a derived table containing concatenation queries into Eloquent syntax?

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

Answer Source

Laravel does provide hydrateRaw

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

The $users variable will hold a Collection of HydratedUser models

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download