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

Question

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

Eloquent is an ORM, it will translate physical relational tables into objects. I don't think it applies to derived tables. You can however add a scope in your users table to filter down to that derived table. — apokryfos
– apokryfos, Commented Jun 13, 2017 at 14:38
@apokryfos could I just wrap this into DB::raw() in theory? — omrakhur
– omrakhur, Commented Jun 13, 2017 at 14:38
Yes you can, but that's not really using Eloquent, that's just using the query builder. — apokryfos
– apokryfos, Commented Jun 13, 2017 at 14:39
Check out query scopes in case it helps (and in case you have a users model you can scope) — apokryfos
– apokryfos, Commented Jun 13, 2017 at 14:40
You could use hydrateRaw. It is not documented though: laravel.com/api/5.3/Illuminate/Database/Eloquent/… — Tuim
– Tuim, Commented Jun 13, 2017 at 14:45

Tuim · Accepted Answer · 2017-06-13 14:50:49Z

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

answered Jun 13, 2017 at 14:50

Tuim

2,5211 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

omrakhur Over a year ago

thanks. This works well. I wish this method was documented in the official Laravel docs.

Bartłomiej Sobieszek · Accepted Answer · 2017-06-13 14:19:26Z

1

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

answered Jun 13, 2017 at 14:19

Bartłomiej Sobieszek

2,8362 gold badges28 silver badges44 bronze badges

Comments

Andrii Lutskevych · Accepted Answer · 2017-06-13 14:55:24Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related