From mysql to eloquent query

Good morning, I also wrote stackoverflow to get help regarding the conversion of a query that I'm trying to execute, if I run it in phpmyadmin it works perfectly but when I convert it for Laravel I get errors, I tried to use two conversion tools also online but I get the same result can anyone help me resolve this error?


SELECT ticket, 
SUM(MINUTE(time_stamp)) 
AS minutes, 
SUM(MINUTE(time_stamp))/60 
AS hours, 
SUM(MINUTE(time_stamp))/(60*24) 
AS days, 
SUM(MINUTE(time_stamp))/(60*24*7) 
AS week
FROM `tickets
GROUP BY ticket

$tickets = DB::table('tickets')
        ->select('ticket', "sum(minute(time_stamp)) as minutes", "sum(minute(time_stamp))/60 as hours", "sum(minute(time_stamp))/ (60*24) as days", "sum(minute(time_stamp))/ (60*24*7) as week")
        ->groupBy("ticket")
        ->get();

The error generated is this: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sum(minute(time_stamp))' in 'field list'

$tickets = DB::table('tickets') ->select('ticket', DB::raw("SUM(MINUTE(time_stamp)) as minute"), ("SUM(MINUTE(time_stamp)) as hours"), ("SUM(MINUTE(time_stamp)) as days"), ("SUM(MINUTE(time_stamp)) as week")) ->groupBy('ticket') ->get();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'SUM(MINUTE(time_stamp))' in 'field list'

tisuchi

2 years ago

Best Answer

Level 70

@marcoplus This should work.

Ticket::select('ticket')
    ->selectRaw('SUM(MINUTE(time_stamp)) AS minutes')
    ->selectRaw('(SUM(MINUTE(time_stamp))/60) AS hours')
    ->selectRaw('(SUM(MINUTE(time_stamp))/(60*24)) AS days')
    ->selectRaw('(SUM(MINUTE(time_stamp))/(60*24*7)) AS weeks')
    ->groupBy('ticket')
    ->get();

Make sure that you provide the right column's name.

2 likes

marcoplus

2 years ago

Level 6

@tisuchi thanks, it works now

2 likes

marcoplus

2 years ago

Level 6

Something doesn't add up, I'm trying to generate a report of total hours spent on tickets and then I wanted to do it by operator, but the results I get are not like those I get in the queries I run in the database directly from phpmyadmin. Why do the results change if I run the query from Laravel? I have a time_stamp column, a ticket and an operators that I would like to count but the values change and it's very tired

1 like

Please or to participate in this conversation.