I have a built DataTables serverside
that I have noticed when it has more than one page and I make datatables.ajax.reload(null, false)
it change the order of the rows randomly within the same range of rows (within the same page of the datatables). When calling the function actualizarDataTable(_dtAPI, false)
I see that the JSON
one that returns the Repository
already comes with the records exchanged, and every 3 calls the order is re-established. I have no idea why the behavior, in the javascript configuration the columns are a orderable=false
, and in the query to the DB it is always done order by
on the same column, that is, I do not use the order coming from the client side.
Customer side:
$(document).ready(function () {
var _tablaDatosElement = $('#tablaDatos');
var _dtAPI = $(_tablaDatosElement)
.on('draw.dt', function () {
$(_tablaDatosElement).find('[data-toggle="tooltip"]').tooltip();
})
.on('preXhr.dt', function (e, settings, data) {
data.verTodos = $('#chkVerTodos').prop('checked') ? true : false;
data.columns = [];
})
.on('xhr.dt', function (e, settings, json, xhr) {
switch (xhr.status) {
case 0:
toastr.error("Conexión interrumpida!!!", '', {
timeOut: 2100,
preventDuplicates: false,
positionClass: 'toast-top-center',
progressBar: true,
showDuration: 800,
hideDuration: 400,
extendedTimeOut: 1000,
showEasing: "swing",
hideEasing: "linear",
showMethod: "fadeIn",
hideMethod: "fadeOut",
});
break;
case 401:
toastr.error("Su sesión ha expirado. No es posible realizar la operación.", '', {
timeOut: 2400,
preventDuplicates: false,
positionClass: 'toast-top-center',
onHidden: function () {
window.location = _HOMEPAGE;
}
});
break;
case 400:
case 403:
case 404:
case 406:
case 500:
toastr.error(xhr.responseText, '', {
timeOut: 2500,
preventDuplicates: false,
positionClass: 'toast-top-center',
progressBar: true,
showDuration: 800,
hideDuration: 400,
extendedTimeOut: 1000,
showEasing: "swing",
hideEasing: "linear",
showMethod: "fadeIn",
hideMethod: "fadeOut",
});
break;
default:
break;
}
})
.on('init.dt', function () {
$('#contenedorTablaDatos').removeClass('hidden');
})
.DataTable({
"columns": [
{"data": "numero", "className": "text-center", "searchable": false, "orderable": false},
{"data": "nombre", "className": "text-left", "searchable": false, "orderable": false},
{"data": "edad", "className": "text-center", "searchable": false, "orderable": false},
{"data": "fum", "className": "text-center", "searchable": false, "orderable": false},
{"data": "eg", "className": "text-center", "searchable": false, "orderable": false},
{"data": "fpp", "className": "text-center", "searchable": false, "orderable": false},
{"data": "ingresos_link", "className": "text-center", "searchable": false, "orderable": false},
{"data": "editar_link", "className": "text-center", "searchable": false, "orderable": false},
{"data": "nacimientos_link", "className": "text-center", "searchable": false, "orderable": false},
{"data": "finalizar_link", "className": "text-center", "searchable": false, "orderable": false},
{"data": "eliminar_link", "className": "text-center", "searchable": false, "orderable": false},
],
"columnDefs": [
{"name": "numero", "targets": 0, "className": "text-center", "searchable": false, "orderable": false},
{
"name": "nombre",
"targets": 1,
"orderable": false,
"searchable": false,
"render": function (data, type, full) {
if (full['emb_fallecida'] === true) {
return '<a class="lnkCargarVista" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Ver Hoja resumen">' + data.nombre + ' <b>(FALLECIDA)</b></a>';
} else {
return '<a class="lnkCargarVista" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Ver Hoja resumen">' + data.nombre + '</a>';
}
}
},
{"name": "edad", "targets": 2, "className": "text-center", "searchable": false, "orderable": false},
{"name": "fum", "targets": 3, "className": "text-center", "searchable": false, "orderable": false},
{
"name": "eg",
"targets": 4,
"searchable": false,
"orderable": false,
"render": function (data, type, full) {
if (full['emb_finalizado'] === false) {
if (data.enabled === true) {
return '<a class="dtBtnActualizarEG lnkCursorPuntero" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Actualiar E.G">' + data.eg + '</a>';
} else {
return data.eg;
}
} else {
return data.eg;
}
}
},
{"name": "fpp", "targets": 5, "searchable": false, "orderable": false},
{
"name": "ingresos_link",
"targets": 6,
"render": function (data, type, full) {
return '<a class="lnkCargarVista" data-src="' + data + '" data-toggle="tooltip" title data-original-title="Ver Historial de ingresos"><span class="fa fa-calendar"></span></a>';
}
},
{
"name": "editar_link",
"targets": 7,
"render": function (data, type, full) {
if (data.enabled === true) {
return '<a class="dtBtnEditar btn btn-xs btn-primary btn-flat" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Editar registro"><span class="fa fa-edit"></span> Editar</a>';
} else {
return '<a class="btn btn-xs btn-primary btn-flat disabled"><span class="fa fa-edit"></span> Editar</a>';
}
}
},
{
"name": "nacimientos_link",
"targets": 8,
"render": function (data, type, full) {
if (data.enabled === true) {
return '<a class="dtBtnNacimientos btn btn-xs btn-primary btn-flat" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Registrar nacimientos"><span class="fa fa-plus-sign"></span> Nacimientos</a>';
} else {
return '<a class="btn btn-xs btn-primary btn-flat disabled"><span class="fa fa-plus-sign"></span> Nacimientos</a>';
}
}
},
{
"name": "finalizar_link",
"targets": 9,
"render": function (data, type, full) {
if (data.enabled === true) {
return '<a class="dtBtnFinalizarEmbarazo btn btn-xs btn-primary btn-flat" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Finalizar embarazo"><span class="fa fa-power-off"></span> Finalizar</a>';
} else {
return '<a class="btn btn-xs btn-primary btn-flat disabled"><span class="fa fa-power-off"></span> Finalizar</a>';
}
}
},
{
"name": "eliminar_link",
"targets": 10,
"render": function (data, type, full) {
if (data.enabled === true) {
return '<a class="dtBtnEliminar btn btn btn-xs btn-danger btn-flat" data-src="' + data.src + '" data-toggle="tooltip" title data-original-title="Eliminar embarazada"><span class="fa fa-remove"></span> Eliminar</a>';
} else {
return '<a class="btn btn-xs btn-danger btn-flat disabled" data-toggle="tooltip" title data-original-title="Eliminar embarazada"><span class="fa fa-remove"></span> Eliminar</a>';
}
}
}
],
"processing": true,
"serverSide": true,
"ajax": {
"url": $(_tablaDatosElement).data('src'),
"type": "POST"
},
"searchDelay": 1000,
"paging": true,
"info": true,
"searching": true,
"lengthChange": false,
"order": false,
"autoWidth": true,
"FixedHeader": true,
"responsive": true,
"language": {
"lengthMenu": "Mostrar _MENU_ registros por página",
"zeroRecords": "No se encontraron registros",
"info": "Mostrando _START_ a _END_ de _TOTAL_ registros",
"infoEmpty": "No se encontraron registros",
"emptyTable": "No hay registros disponibles",
"infoFiltered": "(filtrados de _MAX_ registros totales)",
"loadingRecords": "Cargando...",
"processing": "Cargando...",
"search": "Buscar por Nombre o Número de Identidad:",
"paginate": {
"first": "Primera",
"last": "Última",
"next": "Siguiente",
"previous": "Anterior"
}
},
"stateSave": true,
"stateDuration": 60 * 60 * 24,
"stateSaveCallback": function (settings, data) {
try {
data.verTodos = $('#chkVerTodos').prop('checked');
localStorage.setItem('DTembcmf' + _tablaDatosElement.data('cmf'), JSON.stringify(data));
} catch (e) {
console.log('on stateSaveCallback ' + e.message);
}
},
"stateLoadCallback": function (settings) {
try {
var estado = JSON.parse(localStorage.getItem('DTembcmf' + _tablaDatosElement.data('cmf')));
if (estado !== null) {
$('#chkVerTodos').prop('checked', estado.verTodos);
}
return estado;
} catch (e) {
console.log('on stateLoadCallback ' + e.message);
}
},
"createdRow": function (row, data, dataIndex) {
if (data['emb_aro'] === true) {
$(row).toggleClass('danger');
}
}
});
$('#lnkActualizarDataTables').on('click', function (e) {
e.preventDefault();
e.stopPropagation();
actualizarDataTable(_dtAPI, false); // simplemente _dtAPI.ajax.reload(null, false);
});
$('#lnkRegistrarEmbarazada').on('click', function (e) {
e.preventDefault();
e.stopPropagation();
var elemento = $(this);
agregarRegistro(elemento, _dtAPI, true);
});
$(_tablaDatosElement).find('tbody').on('click', 'a.dtBtnActualizarEG', function (e) {
e.preventDefault();
e.stopPropagation();
var elemento = $(this);
editarRegistro(elemento, _dtAPI);
});
$(_tablaDatosElement).find('tbody').on('click', 'a.dtBtnEditar', function (e) {
e.preventDefault();
e.stopPropagation();
var elemento = $(this);
editarRegistro(elemento, _dtAPI);
});
$(_tablaDatosElement).find('tbody').on('click', 'a.dtBtnEliminar', function (e) {
e.preventDefault();
e.stopPropagation();
var elemento = $(this);
eliminarRegistro(elemento, _dtAPI);
});
$(_tablaDatosElement).find('tbody').on('click', 'a.dtBtnFinalizarEmbarazo', function (e) {
e.preventDefault();
e.stopPropagation();
var elemento = $(this);
editarRegistro(elemento, _dtAPI);
});
$(_tablaDatosElement).find('tbody').on('click', 'a.dtBtnNacimientos', function (e) {
e.preventDefault();
e.stopPropagation();
var elemento = $(this);
editarRegistro(elemento, _dtAPI);
});
$('#chkVerTodos').iCheck({
checkboxClass: 'icheckbox_minimal-blue',
}).on('ifChanged', function (e) {
actualizarDataTable(_dtAPI, false); // simplemente _dtAPI.ajax.reload(null, false);
});
});
Controller:
public function DTableEmbarazadasNodoCMFAction(Request $request, ManagerRegistry $manager, UuidEncoder $uuidEncoder, LoggerInterface $logger): Response
{
if ($request->isXmlHttpRequest()) {
try {
if (!$request->isMethod(Request::METHOD_POST)) {
throw new \Exception("Operación no soportada", 406);
}
$cmfId = $uuidEncoder->decode($request->get('estructuraId'));
$draw = $request->request->getInt('draw');
$start = $request->request->getInt('start');
$length = $request->request->getInt('length', \intval($this->getParameter('resultados_por_pagina')));
$filtro = $request->get('search');
$verTodos = $request->request->getBoolean('verTodos', false);
$em = $manager->getManager();
$cmf = $em->getRepository(EstructuraOrganizativa::class)->findOneJoinTipoEstructuraOrganizativa($cmfId);
if (\is_null($cmf) || ($cmf->getTipoEstructuraOrganizativa()->getId() !== 6)) {
throw new \Exception("No se encontró el CMF seleccionado!!!", 404);
}
$resultado = $em->getRepository(Embarazada::class)->embarazadasPorEstructuraDTable($cmf, $start, $length, $verTodos, $filtroNombre = $filtro['value']);
$data = [];
$numero = $start + 1;
foreach ($resultado['results'] as $embarazada) {
$data[] = [
'emb_aro' => $embarazada->getEsDeAltoRiesgo(),
'emb_finalizado' => $embarazada->getEmbarazoFinalizado(),
'emb_fallecida' => $embarazada->getFallecida(),
'DT_RowId' => $uuidEncoder->encode($embarazada->getIdPublico()),
'numero' => $numero,
'nombre' => [
'nombre' => $embarazada->getNombre(),
'src' => $this->generateUrl('embarazada_hoja_resumen', ['id' => $uuidEncoder->encode($embarazada->getIdPublico())])
],
'edad' => $embarazada->getEdad(),
'fum' => $embarazada->getFechaUltimaMenstruacion()->format('d/m/Y'),
'eg' => [
'eg' => $embarazada->getEmbarazoFinalizado() ? '<b>FINALIZADO</b>' : $embarazada->getEdadGestacional(),
'src' => $this->generateUrl('embarazada_actualizar_eg', ['id' => $uuidEncoder->encode($embarazada->getIdPublico())]),
'enabled' => (($this->isGranted(AppRoles::ROLE_ESP_MUN) || $this->isGranted(AppRoles::ROLE_ESP_AS) || $this->isGranted(AppRoles::ROLE_EST_MUN) || $this->isGranted(AppRoles::ROLE_EST_AS) || $this->isGranted(AppRoles::ROLE_J_GBT) || $this->isGranted(AppRoles::ROLE_EST_GBT) || $this->isGranted(AppRoles::ROLE_MEDICO)) && $embarazada->getEmbarazoFinalizado() === false) ? true : false
],
'fpp' => $embarazada->getEmbarazoFinalizado() ? $embarazada->getFechaFinalizado()->format('d/m/Y') : $embarazada->getFpp()->format('d/m/Y'),
'ingresos_link' => $this->generateUrl('ingreso_historial_embarazada', ['embarazadaId' => $uuidEncoder->encode($embarazada->getIdPublico())]),
'editar_link' => [
'src' => $this->generateUrl('embarazadas_editar', ['id' => $uuidEncoder->encode($embarazada->getIdPublico())]),
'enabled' => (($this->isGranted(AppRoles::ROLE_ESP_MUN) || $this->isGranted(AppRoles::ROLE_ESP_AS) || $this->isGranted(AppRoles::ROLE_EST_MUN) || $this->isGranted(AppRoles::ROLE_EST_AS) || $this->isGranted(AppRoles::ROLE_J_GBT) || $this->isGranted(AppRoles::ROLE_EST_GBT) || $this->isGranted(AppRoles::ROLE_MEDICO)) && $embarazada->getEmbarazoFinalizado() === false) ? true : false
],
'nacimientos_link' => [
'src' => $this->generateUrl('nacimiento_registrar', ['embarazadaId' => $uuidEncoder->encode($embarazada->getIdPublico()), 'unidadRegistraId' => $uuidEncoder->encode($embarazada->getCmf()->getIdPublico())]),
'enabled' => (($this->isGranted(AppRoles::ROLE_ESP_MUN) || $this->isGranted(AppRoles::ROLE_ESP_AS) || $this->isGranted(AppRoles::ROLE_EST_MUN) || $this->isGranted(AppRoles::ROLE_EST_AS) || $this->isGranted(AppRoles::ROLE_J_GBT) || $this->isGranted(AppRoles::ROLE_EST_GBT) || $this->isGranted(AppRoles::ROLE_MEDICO)) && $embarazada->getEmbarazoFinalizado() === false) ? true : false
],
'finalizar_link' => [
'src' => $this->generateUrl('embarazada_finalizar_embarazo', ['id' => $uuidEncoder->encode($embarazada->getIdPublico())]),
'enabled' => (($this->isGranted(AppRoles::ROLE_ESP_MUN) || $this->isGranted(AppRoles::ROLE_ESP_AS) || $this->isGranted(AppRoles::ROLE_EST_MUN) || $this->isGranted(AppRoles::ROLE_EST_AS) || $this->isGranted(AppRoles::ROLE_J_GBT) || $this->isGranted(AppRoles::ROLE_EST_GBT) || $this->isGranted(AppRoles::ROLE_MEDICO)) && $embarazada->getEmbarazoFinalizado() === false) ? true : false
],
'eliminar_link' => [
'src' => $this->generateUrl('embarazadas_eliminar', ['id' => $uuidEncoder->encode($embarazada->getIdPublico())]),
'enabled' => (($this->isGranted(AppRoles::ROLE_ESP_MUN) || $this->isGranted(AppRoles::ROLE_ESP_AS) || $this->isGranted(AppRoles::ROLE_EST_MUN) || $this->isGranted(AppRoles::ROLE_EST_AS) || $this->isGranted(AppRoles::ROLE_J_GBT) || $this->isGranted(AppRoles::ROLE_EST_GBT) || $this->isGranted(AppRoles::ROLE_MEDICO)) && $embarazada->getEmbarazoFinalizado() === false) ? true : false
],
];
$numero += 1;
}
$dataRespuesta = [
'draw' => (int) $draw,
'recordsTotal' => \intval($resultado['totalResults']),
'recordsFiltered' => \intval($resultado['countResults']),
'data' => $data
];
return new JsonResponse($dataRespuesta);
} catch (\Exception $exc) {
if (\in_array($exc->getCode(), [404, 406])) {
return new Response($exc->getMessage(), $exc->getCode());
}
$logger->error(sprintf("[%s:%s]: %s", self::class, __FUNCTION__, $exc->getMessage()));
return new Response("Ocurrió un error inesperado al ejecutar la operación", 500);
}
} else {
throw $this->createNotFoundException("Recurso no encontrado.");
}
}
repository:
public function embarazadasPorEstructuraDTable(EstructuraOrganizativa $estructura, $start = 1, $length = 10, $verTodos = false, $filtroNombre = ''): array
{
$tipoEstructuraId = $estructura->getTipoEstructuraOrganizativa()->getId();
$mainQuery = $this->getEntityManager()->createQuery();
$countQuery = $this->getEntityManager()->createQuery();
$totalResultsQuery = $this->getEntityManager()->createQuery();
$mainDql = "SELECT e, tc, ub, cmf, gbt, asalud, mcpio, prov FROM App:Embarazada e JOIN e.tipoCaptacion tc JOIN e.ubicacion ub JOIN e.estructuraOrganizativa cmf JOIN cmf.parent gbt JOIN gbt.parent asalud JOIN asalud.parent mcpio JOIN mcpio.parent prov";
$countDql = "SELECT COUNT(e.id) FROM App:Embarazada e JOIN e.estructuraOrganizativa cmf JOIN cmf.parent gbt JOIN gbt.parent asalud JOIN asalud.parent mcpio JOIN mcpio.parent prov";
$totalResultDql = "SELECT COUNT(e.id) FROM App:Embarazada e JOIN e.estructuraOrganizativa cmf JOIN cmf.parent gbt JOIN gbt.parent asalud JOIN asalud.parent mcpio JOIN mcpio.parent prov";
switch ($tipoEstructuraId) {
case 2:
$mainDql .= " WHERE (prov = :estructuraId)";
$countDql .= " WHERE (prov = :estructuraId)";
$totalResultDql .= " WHERE (prov = :estructuraId)";
break;
case 3:
$mainDql .= " WHERE (mcpio = :estructuraId)";
$countDql .= " WHERE (mcpio = :estructuraId)";
$totalResultDql .= " WHERE (mcpio = :estructuraId)";
break;
case 4:
$mainDql .= " WHERE (asalud = :estructuraId)";
$countDql .= " WHERE (asalud = :estructuraId)";
$totalResultDql .= " WHERE (asalud = :estructuraId)";
break;
case 5:
$mainDql .= " WHERE (gbt = :estructuraId)";
$countDql .= " WHERE (gbt = :estructuraId)";
$totalResultDql .= " WHERE (gbt = :estructuraId)";
break;
case 6:
$mainDql .= " WHERE (cmf = :estructuraId)";
$countDql .= " WHERE (cmf = :estructuraId)";
$totalResultDql .= " WHERE (cmf = :estructuraId)";
break;
default:
break;
}
if (!empty($filtroNombre)) {
$mainDql .= " AND ((e.nombre LIKE :filtroNombre) OR (e.numeroIdentidad LIKE :filtroNombre))";
$countDql .= " AND ((e.nombre LIKE :filtroNombre) OR (e.numeroIdentidad LIKE :filtroNombre))";
$mainQuery->setParameter('filtroNombre', $filtroNombre . '%');
$countQuery->setParameter('filtroNombre', $filtroNombre . '%');
}
if ($verTodos === false) {
$mainDql .= " AND (e.embarazoFinalizado = FALSE) AND (e.fallecida IS NULL OR e.fallecida = FALSE)";
$countDql .= " AND (e.embarazoFinalizado = FALSE) AND (e.fallecida IS NULL OR e.fallecida = FALSE)";
$totalResultDql .= " AND (e.embarazoFinalizado = FALSE) AND (e.fallecida IS NULL OR e.fallecida = FALSE)";
}
$mainDql .= " ORDER BY e.fechaUltimaMenstruacion ASC";
$mainQuery->setDQL($mainDql);
$countQuery->setDql($countDql);
$totalResultsQuery->setDql($totalResultDql);
$mainQuery->useQueryCache(true);
$countQuery->useQueryCache(true);
$totalResultsQuery->useQueryCache(true);
$mainQuery->setParameter('estructuraId', $estructura->getId());
$countQuery->setParameter('estructuraId', $estructura->getId());
$totalResultsQuery->setParameter('estructuraId', $estructura->getId());
$mainQuery->setFirstResult($start);
$mainQuery->setMaxResults($length);
$resultados = $mainQuery->getResult();
$countResults = $countQuery->getSingleScalarResult();
$totalResults = $totalResultsQuery->getSingleScalarResult();
$resultado = ["results" => $resultados, "countResults" => $countResults, "totalResults" => $totalResults];
return $resultado;
}
La inconsistencia se debe a que solo ordenas por la ultima fecha del periodo. Debes ampliar el ordenamiento a otros campos para tratar de mantener un resultado homogeneo.