I'm getting this error in Laravel 8
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 '*)
This is how I'm trying to do the query
$totalCitasGenero = DB::table('citas')->selectRaw('idEspecialidad, genero, count (*) as totalCitas')->join('personas', 'personas.id', '=', 'citas.idPersonaP')->groupBy('idEspecialidad','genero')->get();
This is how my tables looks like
CREATE TABLE `personas` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(50) NOT NULL,
`apellido` varchar(50) NOT NULL,
`cedula` varchar(10) NOT NULL,
`email` varchar(40) DEFAULT NULL,
`telefono` varchar(13) DEFAULT NULL,
`direccion` varchar(100) NOT NULL,
`ciudadResi` varchar(50) NOT NULL,
`fechaNacimiento` date NOT NULL,
`genero` varchar(100) NOT NULL,
`estado` binary(1) NOT NULL,
`idTipoPersona` int(11) NOT NULL,
`idPersona` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `personas_FK` (`idTipoPersona`),
KEY `personas_FK_1` (`idPersona`),
CONSTRAINT `personas_FK` FOREIGN KEY (`idTipoPersona`) REFERENCES `tipo_personas` (`id`),
CONSTRAINT `personas_FK_1` FOREIGN KEY (`idPersona`) REFERENCES `personas` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4
CREATE TABLE `citas` (
`idPersonaD` int(11) NOT NULL,
`idPersonaP` int(11) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`fecha` date NOT NULL,
`hora` time NOT NULL,
`idEspecialidad` int(11) NOT NULL,
`estado` int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
KEY `citas_FK` (`idPersonaD`),
KEY `citas_FK_1` (`idPersonaP`),
KEY `citas_FK_2` (`idEspecialidad`),
CONSTRAINT `citas_FK` FOREIGN KEY (`idPersonaD`) REFERENCES `personas` (`id`),
CONSTRAINT `citas_FK_1` FOREIGN KEY (`idPersonaP`) REFERENCES `personas` (`id`),
CONSTRAINT `citas_FK_2` FOREIGN KEY (`idEspecialidad`) REFERENCES `especialidades` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8mb4
on DBeaver the query works fine and this is how it looks like
select idEspecialidad, genero, count (*) as totalCitas
from citas as c join personas
where c.idPersonaP = personas.id
group by genero, idEspecialidad ;
The results I'm expecting are the ones in the picture below.
What is wrong translating the query into Laravel?
if I do a dd of the variable after I switch the ->get() as toSql() ->as suggested I get this result
"select idEspecialidad, genero, count (*) as totalCitas from `citas` inner join `personas` on `personas`.`id` = `citas`.`idPersonaP` group by `idEspecialidad`, `genero`
I have even tried coppying the query into DBeaver again and it works fine, its just in Laravel that isnt working not sure why.
The query looks fine so I have no idea why it doesn't work in this case.
CodePudding user response:
Try removing the white space that you have in count (*)
$totalCitasGenero = DB::table('citas')->selectRaw('idEspecialidad, genero, count(*) as totalCitas')->join('personas', 'personas.id', '=', 'citas.idPersonaP')->groupBy('idEspecialidad','genero')->get();