Laravel eloquent response differs from SQL return

Gregory Farias 0
Viewed 32

I'm trying to retrieve some data using eloquent with custom groups

my builder:

return Model::whereNotNull('t.date')
            ->selectRaw("case when datediff(date(now()), t.date) between '0' and '30' then 'up_to_30'
            when datediff (date(now()), t.date) between '31' and '60' then '31_to_60'
            when datediff (date(now()), t.date) between '61' and '90' then '61_to_90'
            else 'more_than_90'
            end as report_days_info, count(t.id)")
            ->groupBy('report_days_info')
            ->get();

my SQL query returns the data:

report_days_info COUNT(t.id)
31_to_60 1
61_to_90 3
more_than_90 18
up_to_30 3

but my laravel response is:

"data": [
    {
        "report_days_info": "more_than_90",
        "count(t.id)": 1
    },
    {
        "report_days_info": "up_to_30",
        "count(t.id)": 1
    },
    {
        "report_days_info": "31_to_60",
        "count(t.id)": 1
    }
]

I expected the Laravel response to be the same as the SQL query, am'I doing something wrong or this is how the response should be?

Here's a fiddle so you can test it

eloquent mysql php laravel
1 Answers

Try this code maybe?

return DB::table('t')->selectRaw("
   CASE
      WHEN DATEDIFF(DATE(NOW()), t.date) BETWEEN '0' AND '30' THEN 'up_to_30'  
      WHEN DATEDIFF(DATE(NOW()), t.date) BETWEEN '31' AND '60' THEN '31_to_60' 
      WHEN DATEDIFF(DATE(NOW()), t.date) BETWEEN '61' AND '90' THEN '61_to_90' 
      ELSE 'more_than_90'
   END AS report_days_info, COUNT(t.id)
")
->groupBy('report_days_info')
->get();
Rindi B0
Related