MySQL - Paginar datos

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.

1. Obtener el numero de registros y calcular el rango a seleccionar acorde a la pagina solicitad
2. 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. (El offset de LIMIT)

• @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 😁