query in eloquent model

I´m traying to do this query in eloquent.

SELECT llamada_estado.desc, count(id_estado) 
                                      FROM estadisticas INNER JOIN llamada_estado ON estadisticas.id_estado = llamada_estado.id 
                                      WHERE updated_at BETWEEN '2021-11-01' AND '2021-11-30' 
                                      GROUP BY estadisticas.id_estado

i´m traying this:

$query2=Estadisticas::query()->whereDate('updated_at', '<=', $toDate)
                                     ->whereDate('updated_at', '>=', $fromDate)
                                     ->join('llamada_estado', 'estadisticas.id_estado', '=', 'llamada_estado.id')
                                     ->groupBy('estadisticas.id_estado')
                                     ->get();

but always return that x column it´s not included in group by, but in phpMyAdmin i´m doing this query:

SELECT llamada_estado.desc, count(id_estado) 
FROM estadisticas 
INNER JOIN llamada_estado ON estadisticas.id_estado = llamada_estado.id 
WHERE updated_at BETWEEN '2021-11-01' AND '2021-11-30' 
GROUP BY estadisticas.id_estado

and this it´s my result:

desc, count(id_estado)
NUEVA 399
PENDIENTE 104
ANULADA 117
CONFIRMADA 50
PASADA A COMERCIALES 175
MAYOR 196
ERRONEO NO EXISTE 649
AUSENTE 681

and i need this result in eloquent.

my tables it´s:

my table call status

CREATE TABLE `llamada_estado` (
  `id` int(10) UNSIGNED NOT NULL,
  `nombre` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `desc` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `clase_span` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `clase` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `hex` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `llamada_estado`
--

INSERT INTO `llamada_estado` (`id`, `nombre`, `desc`, `clase_span`, `clase`, `hex`) VALUES
(1, 'nueva', 'NUEVA', 'badge bg-primary text-white', 'primary', '#5c80d1'),
(2, 'pendiente', 'PENDIENTE', 'badge bg-warning text-white', 'warning', '#f3b760'),
(3, 'anulada', 'ANULADA', 'badge bg-danger text-white', 'danger', '#d26a5c'),
(4, 'confirmada', 'CONFIRMADA', 'badge bg-success text-white', 'success', '#46c37b'),
(5, 'comercializada', 'PASADA A COMERCIALES', 'badge bg-info text-white', 'info', '#70b9eb'),
(6, 'confirmada-ausente', 'CONFIRMADA/AUSENTE', 'badge bg-secondary text-white', 'secondary', '#6c757d'),
(7, 'confirmada-anulada', 'CONFIRMADA/ANULADA', 'badge bg-dark text-white', 'dark', '#343a40'),
(8, 'MAYOR', 'MAYOR', 'badge bg-danger text-white', 'danger', '#d26a5c'),
(9, 'Erroneo-no existe', 'ERRONEO NO EXISTE', 'badge bg-danger text-white', 'danger', '#d26a5c'),
(10, 'Robinson', 'ROBINSON', 'badge bg-danger text-white', 'danger', '#d26a5c'),
(11, 'ausente', 'AUSENTE', 'badge bg-warning text-white', 'warning', '#f3b760');

my table statistics

--
-- Estructura de tabla para la tabla `estadisticas`
--

CREATE TABLE `estadisticas` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_empleado` int(10) UNSIGNED NOT NULL,
  `id_llamada` int(10) UNSIGNED NOT NULL,
  `id_estado` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `estadisticas`
--

INSERT INTO `estadisticas` (`id`, `id_empleado`, `id_llamada`, `id_estado`, `created_at`, `updated_at`) VALUES
(72, 9, 89008, 1, '2021-08-26 06:47:38', '2021-08-26 06:47:38'),
(75, 9, 56617, 1, '2021-08-26 06:47:38', '2021-08-26 06:47:38'),
(77, 9, 78359, 1, '2021-08-26 06:47:38', '2021-08-26 06:47:38'),
(80, 9, 70361, 1, '2021-08-26 06:47:39', '2021-08-26 06:47:39'),
(87, 9, 89023, 1, '2021-08-26 06:47:39', '2021-08-26 06:47:39'),
(89, 9, 84052, 1, '2021-08-26 06:47:39', '2021-08-26 06:47:39'),
(90, 9, 89026, 1, '2021-08-26 06:47:39', '2021-08-26 06:47:39'),
(92, 9, 89028, 1, '2021-08-26 06:47:39', '2021-08-26 06:47:39'),
(108, 23, 89044, 1, '2021-08-26 06:50:12', '2021-08-26 06:50:12'),
(109, 23, 89045, 1, '2021-08-26 06:50:12', '2021-08-26 06:50:12'),
(112, 23, 89048, 1, '2021-08-26 06:50:12', '2021-08-26 06:50:12'),
(124, 23, 56496, 1, '2021-08-26 06:50:12', '2021-08-26 06:50:12'),
(126, 23, 89062, 1, '2021-08-26 06:50:12', '2021-08-26 06:50:12'),
(129, 23, 89065, 1, '2021-08-26 06:50:13', '2021-08-26 06:50:13'),
(130, 23, 89066, 1, '2021-08-26 06:50:13', '2021-08-26 06:50:13'),
(142, 24, 89078, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(144, 24, 55175, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(146, 24, 58093, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(149, 24, 72171, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(152, 24, 89088, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(155, 24, 89091, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(156, 24, 89092, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(161, 24, 73468, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(164, 24, 89100, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(165, 24, 89101, 1, '2021-08-26 06:50:36', '2021-08-26 06:50:36'),
(193, 26, 56101, 1, '2021-08-26 06:51:36', '2021-08-26 06:51:36'),
(195, 26, 89131, 1, '2021-08-26 06:51:36', '2021-08-26 06:51:36'),
(196, 26, 83856, 1, '2021-08-26 06:51:36', '2021-08-26 06:51:36'),
(197, 26, 78782, 1, '2021-08-26 06:51:36', '2021-08-26 06:51:36'),
(225, 11, 89161, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(227, 11, 89163, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(229, 11, 89165, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(231, 11, 85394, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(233, 11, 89169, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(236, 11, 60645, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(242, 11, 84919, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(246, 11, 59005, 1, '2021-08-26 06:52:01', '2021-08-26 06:52:01'),
(263, 11, 68628, 1, '2021-08-26 06:52:37', '2021-08-26 06:52:37'),
(264, 11, 89200, 1, '2021-08-26 06:52:37', '2021-08-26 06:52:37'),
(265, 11, 89201, 1, '2021-08-26 06:52:37', '2021-08-26 06:52:37'),
(268, 11, 89204, 1, '2021-08-26 06:52:37', '2021-08-26 06:52:37'),
(269, 11, 89205, 1, '2021-08-26 06:52:37', '2021-08-26 06:52:37'),

My question is, how i can to do this query in eloquent?

Thanks for help and read

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3GMSke6
via IFTTT