[FIXED] Convert mysql query to working with laravel

Issue

I have this MySQL query which is working fine for me. Now I decided to convert my project to Laravel project so I want to convert the MySQL query to a Laravel query.

This is my query:

select c.username,
    max(case when c.attribute = 'Cleartext-Password' then c.value end) as password,
    max(case when c.attribute = 'Expiration' then c.value end) as expiration,
    max(case when c.attribute = 'ChilliSpot-Max-Total-Octets' then c.value end) as quta,
    max(case when c.attribute = 'Simultaneous-Use' then c.value end) as simul,
    max(case when c.attribute = 'Max-All-Session' then c.value end) as session,
    max(c.adsoyad) as realname, min(c.dtarih) as birthdate, min(c.telefon) as phone,min(c.tcno) as tc,max(c.email) as email,min(c.id) as id
from radcheck c 
group by c.username

How can I do it?

Solution

To convert above using laravel query builder you need to go for raw expressions

$result = DB::table('radcheck as c')
    ->select('c.username', 
    DB::raw("max(case when c.attribute = 'Cleartext-Password' then c.value end) as password"),
    DB::raw("max(case when c.attribute = 'Expiration' then c.value end) as expiration"),
    DB::raw("max(case when c.attribute = 'ChilliSpot-Max-Total-Octets' then c.value end) as quta"),
    DB::raw("max(case when c.attribute = 'Simultaneous-Use' then c.value end) as simul"),
    DB::raw("max(case when c.attribute = 'Max-All-Session' then c.value end) as session"),
    DB::raw("max(c.adsoyad) as realname"),
    DB::raw("min(c.dtarih) as birthdate"),
    DB::raw("min(c.telefon) as phone"),
    DB::raw("min(c.tcno) as tc"),
    DB::raw("max(c.email) as email"),
    DB::raw("min(c.id) as id")
    )
    ->groupBy('c.username')
    ->get();

Answered By - M Khalid Junaid