How to convert mysql query to laravel 5?

Question

Below i have pasted mysql query. Please help me to convert this query to laravel 5.

select
t.username,
sum(case when t.status_id = 1 then t.count else 0 end) as status_1,
sum(case when t.status_id = 0 then t.count else 0 end) as status_0,
sum(case when t.status_id = 0 and t.status_desc = 2 then t.count else 0 end)
         as status_0_2,
sum(case when t.status_id = 0 and t.status_desc = 3 then t.count else 0 end)
as status_0_3
from (
select username, status_id, status_desc, count(status_desc) as count
from log
group by username, status_id, status_desc
) as t
group by t.username;

Why do you want to convert it to Laravel's scheme? Just create a view and query it with Eloquent... — N.B.
– N.B., Commented Jan 20, 2016 at 8:12
For my project am working in laravel, and i have the solution in mysql but i didn't have any idea about using case in laravel. that's why.\ — Rachana Tajane
– Rachana Tajane, Commented Jan 21, 2016 at 11:15
Alright, makes sense then. Good luck with converting it! I believe the given answer does what you need, have you tried it? — N.B.
– N.B., Commented Jan 21, 2016 at 16:46

jardis · Accepted Answer · 2016-01-20 07:38:29Z

Here is how you could write it (you may want to test this code, however).

Something to note about using DB::raw:

DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder. They therefore can create a vector for attack via SQL injection.

With that in mind, I am using them here assuming that you aren't passing any user input into them in order to do the counting and conditional query parameters.

Please, take a look at the Laravel Documentation for more info on how the query builder works. Most people won't always be kind enough to write your queries for you.

// compile the sql for the select query
$selectRaw = \DB::table('log')->select([
    'username', 
    'status_id', 
    'status_desc', 
    \DB::raw('count(status_desc) as count')
])->groupBy('username', 'status_id', 'status_desc')->toSql();

// create and execute the full query
$result = \DB::table(\DB::raw("({$selectRaw}) as t"))->select([
    't.username', 
    \DB::raw('sum(case when t.status_id = 1 then t.count else 0 end) as status_1'),
    \DB::raw('sum(case when t.status_id = 0 then t.count else 0 end) as status_0'),
    \DB::raw('sum(case when t.status_id = 0 and t.status_desc = 2 then t.count else 0 end) as status_0_2'),
    \DB::raw('sum(case when t.status_id = 0 and t.status_desc = 3 then t.count else 0 end) as status_0_3'),
])->groupBy('t.username')->get();

Collectives™ on Stack Overflow

How to convert mysql query to laravel 5?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related