CODEWITHSUNDEEP
phpmysqllaravel-4
awdesarrollos
awdesarrollos

Reputation: 90

Laravel query with if and sum

I have two tables called trabajos and tecnicos.

Trabajos Table: id | tecnico_id | fecha_ingreso | hora_ingreso | fecha_egreso | hora_egreso

Tecnicos Table id | nombre

Note: fecha_ingreso is date that the work is received. hora_ingreso is time that the work is received. fecha_egreso is date the work is completed. hora_egreso is time the work is completed. These details are string.

I need to create an array with the following format:

Array
(
[0] => Array
    (
        [tecnico] => Pedro
        [good] => 15
        [bad] => 2
    )

[1] => Array
    (
        [tecnico] => Juan
        [good] => 19
        [bad] => 3
    )

[2] => Array
    (
        [tecnico] => Martin
        [good] => 18
        [bad] => 5
    )

)

A technician makes a job, this job has a date/time of entry and date/time of completion. If the date/time of completion is 30 minutes more than the date/time of entry should add 1 to bad but was summarily 1 good. The array should group by technical name.

This is my query:

$trabajos = DB::table('trabajos')
    ->join('tecnicos', 'trabajos.responsable_diagnostico', '=', 'tecnicos.id')
    ->where('trabajos.created_at', '>=', $desde4, 'and', 'trabajos.created_at', '<=', $hasta4)
    ->select(DB::raw('tecnicos.nombre', 
        SUM(if(TIMEDIFF(STR_TO_DATE(CONCAT('trabajos.fecha_egreso', ' ', 'trabajos.hora_egreso'), '%d/%m/%Y %H:%i'),
        STR_TO_DATE(CONCAT('trabajos.fecha_ingreso', ' ', 'trabajos.hora_ingreso'), '%d/%m/%Y %H:%i'))<'00:30:00',1,0) as good,
        SUM(if(TIMEDIFF(STR_TO_DATE(CONCAT('trabajos.fecha_egreso', ' ', 'trabajos.hora_egreso'), '%d/%m/%Y %H:%i'),
        STR_TO_DATE(CONCAT('trabajos.fecha_ingreso', ' ', 'trabajos.hora_ingreso'), '%d/%m/%Y %H:%i'))<'00:30:00',0,1) as bad))
    ->groupBy('tecnicos.nombre')
    ->get();

This query shows me this error in the first IF:

syntax error, unexpected 'if' (T_IF), expecting ')'

Can you help me find the error? Thank you very much for your help.

Upvotes: 1

Views: 2340

Answers (1)

Bogdan
Bogdan

Reputation: 44526

You have to enclose the entire string of DB::raw in quotes:

$trabajos = DB::table('trabajos')
    ->join('tecnicos', 'trabajos.responsable_diagnostico', '=', 'tecnicos.id')
    ->where('trabajos.created_at', '>=', 'created')
    ->where('trabajos.created_at', '<=', 'created')
    ->select(DB::raw("tecnicos.nombre,
        SUM(if(TIMEDIFF(STR_TO_DATE(CONCAT('trabajos.fecha_egreso', ' ', 'trabajos.hora_egreso'), '%d/%m/%Y %H:%i'),
        STR_TO_DATE(CONCAT('trabajos.fecha_ingreso', ' ', 'trabajos.hora_ingreso'), '%d/%m/%Y %H:%i'))<'00:30:00',1,0)) as good,
        SUM(if(TIMEDIFF(STR_TO_DATE(CONCAT('trabajos.fecha_egreso', ' ', 'trabajos.hora_egreso'), '%d/%m/%Y %H:%i'),
        STR_TO_DATE(CONCAT('trabajos.fecha_ingreso', ' ', 'trabajos.hora_ingreso'), '%d/%m/%Y %H:%i'))<'00:30:00',0,1)) as bad"))
    ->groupBy('tecnicos.nombre')
    ->toSql();

You closed by mistake the quotes after 'tecnicos.nombre', and PHP is throwing a syntax error because it's trying to evaluate the if statement that should be inside the SQL query.

Upvotes: 1

Related Questions