Error executing a query. I need to show how many alarms per operator

Question

Error executing a query. I need to show how many alarms per operator

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

This is my code:

public function alarmaAtendidaO()
 {
            $data = \DB::select('SELECT operadors.nombre AS Nombre, operadors.apellido AS Apellido,
            (SELECT COUNT(ala.id) FROM alarmas ala INNER JOIN operadors op ON op.id = ala.operador_id
              WHERE ala.operador_id=op.id) AS Cantidad de alarmas
                INNER JOIN  operadors ope ON ope.id = alarmas.operador_id
                INNER JOIN  empleados em ON em.id = ope.empleado_id
                INNER JOIN  usuarios usu ON usu.id = em.usuario_id
                INNER JOIN  personas per ON per.id = usu.id
                FROM operador opera');
            echo dd($data);
}

and this error is generated:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'de alarmas
INNER JOIN operadors ope ON ope.id = alarmas.operador_id
I' at line 3 (SQL: SELECT ope.nombre AS Nombre, ope.apellido AS Apellido,
(SELECT COUNT(ala.id) FROM alarmas ala INNER JOIN operadors op ON op.id = ala.operador_id
WHERE ala.operador_id=ope.id) AS Cantidad de alarmas
INNER JOIN operadors ope ON ope.id = alarmas.operador_id
INNER JOIN empleados em ON em.id = ope.empleado_id
INNER JOIN usuarios usu ON usu.id = em.usuario_id
INNER JOIN personas per ON per.id = usu.id
FROM operadors opera)

php mariadb laravel-5

asked by Miguel Cortes 21.06.2017 в 21:05

source

2 answers

ListView setSelection - select item but do not change the background Help with a cycle

score 0 · Answer 1

The error that shows you is because the alias is not enclosed in quotes and you have several words:

... WHERE ala.operador_id=op.id) AS "Cantidad de alarmas" ...

you could also join them in a single word with underscores or underscores :

... WHERE ala.operador_id=op.id) AS cantidad_de_alarmas ...

score 0 · Answer 2

Try the following query, if it works, comment to explain some details of how you built it, since you have some semantic errors.

SELECT 
ope.nombre AS Nombre, 
ope.apellido AS Apellido, 
(SELECT COUNT(ala.id) FROM alarmas as ala INNER JOIN operadors as op ON op.id = ala.operador_id WHERE ala.operador_id = op.id) AS cantidad_alarmas
FROM operador as ope
INNER JOIN  alarmas as ala ON ope.id = ala.operador_id
INNER JOIN  empleados as em ON em.id = ope.empleado_id
INNER JOIN  usuarios as usu ON usu.id = em.usuario_id
INNER JOIN  personas as per ON per.id = usu.id ;