Objetivo: Obtener los resultados de forma "paginada" en MySQL, de tal forma que se pueda implementar en una interfaz a la siguiente al entregar los datos mediante una petición GET al cliente
El caso de uso se puede encontrar en el demo de una aplicacion que desarrolle, To Do 51 (Aguarda un momento mientras carga la BD 😅)
Base de datos
¿Cómo obtener los datos paginados?
Obteniendo el rango de inicio
& fin
para después ejecutar la consulta con ese rango.
Lo ideal, seria que el lenguaje de programación backend ejecute un stored procedure
donde solo deba llamar el método junto con 2 argumentos. Algo como lo siguiente:
CALL v1_GetTasksPaginated(1,2)
Donde:
-
apiPage: Es la pagina que se desea obtener de todo el conjunto de datos. Algunas REST API piden como parametro este argumento. Por ejemplo el siguiente enlace:
https://reqres.in/api/users?page=2
-
apiLimitRows: Este parametro es para indicar cuantos registros se desean obtener por cada paginado. Lo normal seria que que el lenguaje backend tenga este valor en una
variable de entorno
y siempre usase este parametro para llamar el stored procedure. Algunas REST API permiten que el cliente decida hasta cuantos registros obtener, pero por seguridad el backend deberia limitarlo a uno fijo (o el propio stored procedure).
Para lograr el cometido, se utilizan 3 stored procedures
v1_GetTasksPaginated(?,?)
Query:
BEGIN
CALL v1_GetTasksPagination(apiPage,apiLimitRows);
CALL v1_GetTasks(@startRow,@rowsPerPage);
END
El stored procedure consta de dos pasos para obtener la información paginada.
- Obtener el numero de registros y calcular el rango a seleccionar acorde a la pagina solicitad
- Obtener los registros con el rango calculado
Pero este en particular servirá indicar la pagina y cuantos registros obtener a partir de ella. Es la que se usara por el programador backend para evitar que el se encargue de esta funcionalidad y delegarlo a implementar el JWT, conexión con BD, azure de microsoft, validación del DTO, etc.
Tan sencillo como ejecutarlo de esta manera
CALL v1_GetTasksPaginated(1,2)
Dando como resultado lo siguiente:
Listo para manipularlo en el backend y enviar la respuesta al cliente 😁
v1_GetTasksPagination(?,?)
Este es el encargado de calcular
- OFFSET
- ROW_COUNT
- TOTAL_PAGES (opcional pero lo recomiendo para implementar el 1-2 paginas, 1-5 paginas, etc)
Los cuales serán utilizados en el query que obtiene los datos.
@rows: Aquí se debe obtener cuantos registros existen en dicha tabla aplicando los filtros necesarios si es requerido. Por ejemplo, en mi caso, solo cuento los registros donde su borrado logico es 0 (falso).
@rowsPerPage: Con este nos guiamos en cuantos registros deben existir por pagina. Servirá para un calculo posterior. (Es un input del stored procedure)
@startRow: Esta operación matemática permitirá saber desde que registro
n
empezar a obtener los resultados. (Eloffset
deLIMIT
)@totalPages: Para obtener el numero de paginas total de los registros, se hace un división de
@rows/@rowsPerPage
y se redondea su resultado para evitar tener paginas con decimales. No es necesario, pero puede servir para enterarnos cuantas paginas existen.
BEGIN
-- Recuerda este filtro para mas al rato
SET
@rows =(
SELECT
COUNT(*) AS noTasks
FROM
tasks
WHERE
userId = 1 AND logicalErase = 0
);
SET
@rowsPerPage =(
SELECT
apiNoRows
);
SET
@startRow =(
SELECT
((apiPage - 1) * @rowsPerPage)
);
SET
@totalPages =(
SELECT
CEIL(@rows / @rowsPerPage)
);
END
v1_GetTasks(?,?)
Este es el query encargado de obtener los datos de la tabla deseada. No se puso en un mismo stored procedure debido a que la clausula LIMIT
no permite utilizar variables. Así que se brindan como argumentos del stored procedure para lograr la tarea.
BEGIN
SELECT
Tasks.id AS id,
Tasks.description AS description,
Tasks.createdDate AS createdDate,
Tasks.completed AS completed,
Tasks.userId AS userId
FROM Tasks
-- El "filtro" del WHERE debe ser el mismo que el query
-- que cuenta los registros de la tabla para coincidir resultados
WHERE Tasks.userId = 1
AND Tasks.logicalErase = 0
ORDER BY Tasks.createdDate DESC
LIMIT apiBegin , apiNextOnes;
SELECT @totalPages AS totalPages;
END
Adjunto el .sql para quien desee importar la base de datos e intentarlo en carne propia el funcionamiento.
Descarga aqui
En cualquier caso, se puede devolver al cliente un JSON con una estructura de este tipo para que sea capaz de pintar la interfaz sin tanto problema.
{
"status": 200,
"data": {
"tasks": [
{
"id": 81,
"description": "Seleccione una fila ;)",
"createdDate": "2021-10-23T05:20:57.000Z",
"completed": false,
"userId": 1
},
{
"id": 80,
"description": "Aprobar la materia :(",
"createdDate": "2021-10-23T05:20:22.000Z",
"completed": false,
"userId": 1
}
],
"pages": 3,
"actualPage": 1
},
"errors": [
null
]
}
¿Quieres saber mas de mi? Te invito a revisar mi portafolio 😁
Top comments (0)