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

Solution 1:

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

Solution 2:

Eloquent does not provide such specific methods. You will need to use DB::raw("...") (but that's not a solution I guess), or DB::select("SELECT ...");

Solution 3:

Without Eloquent with Query Builder

Create sub query and using DB::table with DB::raw and selectRaw() method.

$subQuery = DB::table('users')
    ->selectRaw('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');

DB::table(DB::raw('('.$subQuery->toSql().') as tableName')
    ->selectRaw("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")