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

Related Posts

Codeigniter : Parse error: syntax error, unexpected ‘const’ (T_CONST), expecting variable (T_VARIABLE) in Laravel project

I’m getting following error: **Parse error: syntax error, unexpected ‘const’ (T_CONST), expecting variable (T_VARIABLE)** Note : It’s working in local but facing issue in production server. private…

Firebase receive notification while tab is active or on focus

What i want is to be able to perform an action when a user receives a notification while the browser is open and tab is active or…

Laravel’s alias loader does not find class

We have a legacy project that we cannot update and we need to make some changes in symfony’s Response.php in vendor. We have solved this by copying…

Laravel 5 – generic document management

I have a system where you can create different types of unique documents. For instance, one document is called Project Identified and this expects certain inputs. Originally,…

Laravel Nova limit the results in indexQuery

I ran intro a situation where I need to limit the results of a resource to only 3 results. To be more specific, based on the logged…

Auditoria en laravel 5.8 [closed]

Cómo puedo automatizar el registro de actividades de un usuario en laravel? Si un usuario ingresa a un app de laravel, debo guardar toda su actividas, a…

Leave a Reply

Your email address will not be published.