Criteria Filters Pg

El objetivo principal de este paquete, es preparar los filtros para consultas SQL utilizadas para el motor de base de datos PostgreSQL. Las funcionalidades principales que provee esta utilidad son las siguientes:

1. Crear una búsqueda genérica por coincidencias según los campos especificados
2. Crear búsquedas específicas sobre campos particulares
3. Crear paginación de registros
4. Crear ordenamiento de registros
5. Utilización de binding de parámetros para evitar inyección SQL

Instalar dependencia

npm i criteria-filters-pg

Importar paquete en su proyecto

import { criteriaFilter } from "criteria-filters-pg/dist/criteriaPattern/criteriaFilter";

Si utilizas un gestor de alias para rutas como module-alias podrías crear una ruta de la siguiente manera:

import path from 'path'
import * as moduleAlias from 'module-alias';

moduleAlias.addAliases({
    "@criteriaPattern": path.resolve(__dirname, "../../node_modules/criteria-filters-pg/dist/criteriaPattern")
});

y luego importar el paquete tal como se muestra a continuación:

import { criteriaFilter } from '@criteriaPattern/criteriaFilter';

Creando una consulta con búsqueda general y específica

Consiste básicamente en la utilización del parámetro search y la definición de campos en donde debe buscar las coincidencias según el parámetro antes mencionado.

1. Definición de query base

let query = `
            SELECT 
                names, 
                lastnames,
                rut,
                created_at,
                updated_at
            FROM
                users`;

2. Definición de configuración inicial para query base

const configInitialQueryBase = { 
    query, 
    fieldsSearch: ["names", "lastnames", "rut", "created_at", "updated_at"] 
};

Propiedades del objeto configInitialQueryBase

Propiedad	Descripcion
query	Query base utilizada
fieldsSearch	Campos por los cuáles se pueden realizar las búsquedas, requerido para las búsquedas generales por coincidencia
params	En el caso de que la query base tenga una o más claúsulas where, esta propiedad debe definirse. Más adelante veremos un ejemplo utilizando esta propiedad

3. Definición de filtros mediante queryString

Si estás utilizando una Rest API, por lo general las queryString vienen en el objeto request y está definido como un objeto. Por ejemplo, si estas utilizando ExpressJS, puedes acceder al queryString usando req.query.

// Simulando un objeto queryString
const filtersQueryString = { 
    created_at: "2021-05-01,2021-05-31",
    page: 1,
    limit: 10,
    orderBy: "created_at",
    orderType: "DESC",
    search: "pedro"
};

Para este primer ejemplo de queryString, estan presentes los filtros típicos que son:

• page y limit para la pagincación de registros,
• orderBy y orderType para el ordenamiento de registros
• search para la búsqueda por coincidencias en los campos definidos en el punto N° 2.

Propiedades adicionales:

• created_at para aplicar un criterio de búsqueda adicional, permitiendo además filtrar por fecha de creación.

Propiedades del objeto filtersQueryString | Propiedad | Descripcion | Opcional | Por defecto | | :--- | :----: | :----: | :----: |
| page | Para paginación de registros | Sí | | limit | Límite de registros por página | Sí | 10 | search | Búsqueda general por coincidencias según campos definidos | Sí |
| orderBy | Ordenar por algún campo | Sí | | orderType | Orden ascendente o descendente | Sí | DESC | created_at | Campo especifico para buscar por fecha de creación | Sí |

4. Utilizando la función criteriaFilter

La función será la encargada de entregarnos las partes o piezas necesarias para armar la query resultante. Dicha función recibe 2 argumentos que son:

• configInitialQueryBase que corresponde al objeto definido en el punto N° 2.
• filtersQueryString que corresponde al objeto definido anteriormente en el punto N° 3.

const {
    where,
    params,
    orderByString,
    paginationString,
    page,
    limit
} = criteriaFilter(configInitialQueryBase, filtersQueryString);

Qué retorna la función criteriaFilter:question: | Propiedad | Descripcion | Tipo | | :--- | :----: | :----: | | where | La claúsula where, según los campos de busqueda general por coincidencia y busquedas especificas por campos | String | | params | Lista de parámetros creados dinámicamente según las búsquedas aplicadas, a los cuales se les ha añadido binding para evitar inyección SQL | Array | | orderByString | Instrucción de ordenamiento de registros | String | | paginationString | Instrucción de paginación de registros | String | | page | Número de página | Number | | limit | Límite de registros por página | Number |

5. Query resultante

query =
    `${query}
    ${where}
    ${orderByString}
    ${paginationString}`;

console.log(query);
/* ================= Resultado final =================
SELECT
    names,
    lastnames,
    rut,
    created_at,
    updated_at
FROM
    users
    WHERE 1=1 AND ("names"::text ILIKE '%' || $1 || '%'::text OR "lastnames"::text ILIKE '%' || $1 || '%'::text OR "rut"::text ILIKE '%' || $1 || '%'::text OR "created_at"::text ILIKE '%' || $1 || '%'::text OR "updated_at"::text ILIKE '%' || $1 || '%'::text) AND "created_at"::date BETWEEN $2 AND $3
    ORDER BY "created_at" DESC
    OFFSET 0
    LIMIT 10
*/

Otro ejemplo? :+1::+1::+1:

En esta ocasión tenemos una query base con una claúsula where predefinida, y también estaremos utilizando el filtrado de los campos según sus alias

1. Query base

let query = `
SELECT 
    *
FROM
(
    SELECT 
        id AS "userId",
        nombres AS "userNames",
        apellidos AS "userLastnames",
        email AS "userEmail",
        fono AS "userPhone",
        rut AS "userRut",
        ciudad AS "cityName"
    FROM
        users
) AS t1
WHERE 
    "cityName" = $1
`;

2. Configuración inicial query base

const configInitialQueryBase = { 
    query, 
    params: ["Concepción"],  
    fieldsSearch: ["userNames", "userEmail", "userPhone", "userRut", "cityName"] 
};

Para este caso la propiedad fieldsSearch tiene configurada las columnas de búsqueda según los alias establecidos en la query base y además se ha especificado la propiedad params, debido a que la query base tiene una condición en la claúsula where

3. Filtros queryString

// Simulando un objeto queryString
const filtersQueryString = { 
    page: 1,
    limit: 10,
    orderBy: "userNames",
    orderType: "ASC",
    search: "criterio búsqueda general"
};

4. Función criteriaFilter

Como se mencionó anteriormente, esta función recibe 2 argumentos los cuáles han sido definidos en los puntos N° 2 y N° 3 de la sección.

const {
    where,
    params,
    orderByString,
    paginationString,
    page,
    limit
} = criteriaFilter(configInitialQueryBase, filtersQueryString);

5. Query resultante

query =
    `${query}
    ${where}
    ${orderByString}
    ${paginationString}`;

console.log(query);
/* ================= Resultado final =================
SELECT
    *
FROM
(
    SELECT
        id AS "userId",
        nombres AS "userNames",
        apellidos AS "userLastnames",
        email AS "userEmail",
        fono AS "userPhone",
        rut AS "userRut",
        ciudad AS "cityName"
    FROM
        users
) AS t1
WHERE
    "cityName" = $1
    AND ( "userNames"::text ILIKE '%' || $2 || '%'::text OR "userEmail"::text ILIKE '%' || $2 || '%'::text OR "userPhone"::text ILIKE '%' || $2 || '%'::text OR "userRut"::text ILIKE '%' || $2 || '%'::text OR "cityName"::text ILIKE '%' || $2 || '%'::text)
    ORDER BY "userNames" ASC
    OFFSET 0
    LIMIT 10

*/