Consultas Personalizadas

¿Qué son las Consultas Personalizadas?

Las Consultas Personalizadas son una potente funcionalidad del backend que permite a usuarios y administradores definir, almacenar y ejecutar consultas SQL dinámicas adaptadas a necesidades empresariales específicas. A diferencia de las consultas estándar, que son fijas y limitadas a lógica predefinida, las Consultas Personalizadas permiten una recuperación de datos flexible, filtrado complejo e informes avanzados directamente desde la capa de base de datos.

Definición y Propósito

Las Consultas Personalizadas están diseñadas para abordar escenarios donde los endpoints API por defecto no son suficientes. Proporcionan:

• Composición dinámica de consultas: Se pueden especificar entidades, joins, filtros y campos de salida.
• Parametrización: Las consultas pueden incluir variables, permitiendo personalización en tiempo de ejecución (por ejemplo, filtrar por usuario, fecha o estado).
• Acceso avanzado a datos: Útil para analítica, integraciones y dashboards personalizados.

Estructura de los metadatos de una Consulta Personalizada

Los metadatos de una Consulta Personalizada solo contienen un campo de descripción, que define el propósito o contexto de la consulta.

{
  "description": "string"
}

Estructura de una entidad de Consulta Personalizada

Campo	Tipo	Descripción
id	string	Identificador único de la entidad.
name	string	Nombre de la entidad.
fields	Array<Field>	Cada campo de la entidad usado en la consulta con su tipo.

{
  "id": "00000000-0000-0000-0000-0123456789AB",
  "name": "Empleados",
  "fields": [
    {
      "name": "emp_no",
      "type": "number"
    },
    {
      "name": "first_name",
      "type": "string"
    },
    {
      "name": "last_name",
      "type": "string"
    },
    {
      "name": "dept_no",
      "type": "string"
    }
  ]
}

Estructura de los metadatos de una subconsulta personalizada

Campo	Tipo	Descripción
type	string	Tipo de visualización (por ejemplo, tabla, gráfico).
width	number	Ancho de la visualización en unidades de grid.
widget	object	Configuración específica del tipo de visualización.
widget.columns	Array<string>	Columnas a mostrar en la visualización.
widget.rows	Array<string>	Filas a mostrar en la visualización.

{
  "type": "table",
  "width": 3,
  "widget": {
    "columns": [
      "column1",
      "column2"
    ],
    "rows": [
      "267686b4-bc9c-436d-a7fa-b8fe3bf9dfa2.field1",
      "267686b4-bc9c-436d-a7fa-b8fe3bf9dfa2.field2"
    ]
  }
}

Estructura de un join en una subconsulta personalizada

Campo	Tipo	Descripción
type	string	Tipo de join (por ejemplo, INNER, LEFT, RIGHT).
entity	string	ID de la primera entidad en el join.
field	string	Campo de la primera entidad para el join.
entity2	string	ID de la segunda entidad en el join.
field2	string	Campo de la segunda entidad para el join.

{
  "type": "INNER",
  "entity": "267686b4-bc9c-436d-a7fa-b8fe3bf9dfa2",
  "field": "dept_no",
  "entity2": "1ab82e53-574c-4c5b-b24c-fc9e25809cc2",
  "field2": "dept_no"
}

Estructura de un match en una subconsulta personalizada

Campo	Tipo	Descripción
combinator	string	Operador lógico para combinar reglas (por ejemplo, “and”, “or”).
id	string	Identificador único de la condición de match.
not	boolean	Niega toda la condición de match si es true.
rules	Array<Rule>	Arrreglo de reglas individuales para filtrar.
rules[].id	string	Identificador único de la regla.
rules[].field	string	Campo sobre el que se aplica la regla.
rules[].operator	string	Operador de la regla (por ejemplo, ”=”, ”>”, ”<”, “IN”).
rules[].value	string	Valor a comparar con el campo.
rules[].valueSource	string	Fuente del valor para la regla.

{
  "combinator": "and",
  "id": "00000000-ABCD-0000-0000-0123456789AB",
  "not": false,
  "rules": [
    {
      "operator": "=",
      "value": "2000",
      "valueSource": "value",
      "id": "00000000-0000-CDEF-0000-0123456789AB",
      "field": "00000000-0000-0000-0000-0123456789AB.emp_no"
    }
  ]
}

Estructura de la salida de una subconsulta personalizada

Un arreglo de strings que representa los campos a incluir en la salida de la consulta.

[
  "00000000-0000-0000-0000-0123456789AB.emp_no",
  "00000000-0000-0000-0000-0123456789AA.dept_no"
]

Estructura de una variable de Consulta Personalizada

Campo	Tipo	Descripción
name	string	Nombre de la variable.
label	string	Etiqueta legible para la variable.
type	string	Tipo de dato de la variable (por ejemplo, string, number).

{
  "name": "month",
  "label": "Mes",
  "type": "string"
}

Estructura de una Consulta Personalizada

Un objeto de Consulta Personalizada completo combina todos los componentes anteriores en una sola definición.

{
  "name": "Departamentos de Empleados",
  "metadata": { <CustomQueryMetadata> },
  "entities": [ { <CustomQueryEntity> } ],
  "queries": [
    {
      "name": "Consulta de ejemplo",
      "metadata": { <CustomQuerySubQueryMetadata> },
      "jsonData": {
        "entity": "00000000-0000-0000-0000-0123456789AB",
        "joins": [ { <CustomQuerySubQueryJoin> } ],
        "match": { <CustomQuerySubqueryMatch> },
        "output": [ <CustomQuerySubqueryOutput> ]
      }
    }
  ],
  "variables": [ {<CustomQueryVariable> } ]
}

Casos de uso típicos

• Inteligencia de negocios: Generación de informes personalizados, agregaciones y métricas para la gestión.
• Integración: Suministro de datos adaptados a sistemas externos o socios.
• Exploración por el usuario: Permite a usuarios avanzados crear sus propias vistas y filtros.
• Auditoría y cumplimiento: Extracción de registros específicos para revisión o exportación.

¿Cómo se ejecutan las Consultas Personalizadas?

Almacenamiento y representación interna

• Las consultas personalizadas se almacenan en la tabla custom_queries, con campos para name, metadata, variables, json_data (definición de la consulta) y sql_data (la sentencia SQL generada).
• El campo json_data es un objeto estructurado que describe la lógica de la consulta, incluyendo:
  • entity: La tabla o vista principal a consultar.
  • match: Condiciones de filtrado, soportando sustitución de variables (por ejemplo, {{month}}).
  • joins: Arreglo de definiciones de joins, especificando relaciones entre entidades.
  • output: Lista de campos a devolver en el resultado.

Creación de consultas y generación de SQL

• Cuando se crea o actualiza una consulta, el backend analiza la definición JSON y utiliza un generador seguro de consultas (SecureQueryBuilder) para generar el SQL correspondiente.
• El SQL se almacena junto al JSON para una ejecución eficiente.
• Las variables se validan y asignan a parámetros SQL para evitar inyecciones y garantizar la corrección.

Flujo de ejecución en tiempo de ejecución

1. Gestión de la solicitud: El controlador recibe una solicitud de ejecución, analiza los parámetros (límite, página, orden, variables, índices de subconsulta).
2. Validación: Se asegura de que la consulta exista, que las variables sean soportadas y que los índices sean válidos.
3. Sustitución de variables: Sustituye los placeholders en el SQL por los valores proporcionados, incluyendo variables de sistema (por ejemplo, current_user_id).
4. Ejecución SQL: Ejecuta la consulta contra la base de datos, aplicando paginación y orden según sea necesario.
5. Formateo de la respuesta: Devuelve resultados, conteo y metadatos de paginación en un formato estandarizado.

Ejemplo: Consulta avanzada con joins y variables

{
  "id": "3",
  "name": "Ejemplo de Consultas Personalizadas",
  "metadata": {
    "description": "Por favor, no modificar"
  },
  "queries": [
    {
      "name": "Nombres e IDs",
      "metadata": {
        "type": "table",
        "width": 6,
        "widget": [
          {
            "columnLabel": "ID",
            "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.id"
          },
          {
            "columnLabel": "Nombre",
            "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.name"
          }
        ]
      },
      "jsonData": {
        "joins": [],
        "match": {
          "id": "60359d66-a5b9-48fd-ab0d-da6240cd9117",
          "rules": []
        },
        "entity": "0b049cb4-80fc-4677-85f5-574c21278503",
        "output": [
          "0b049cb4-80fc-4677-85f5-574c21278503.id",
          "0b049cb4-80fc-4677-85f5-574c21278503.name"
        ]
      }
    },
    {
      "name": "Tabla debounce",
      "metadata": {
        "type": "table",
        "width": 6,
        "widget": [
          {
            "columnLabel": "ID",
            "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.id"
          },
          {
            "columnLabel": "Nombre",
            "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.name"
          }
        ]
      },
      "jsonData": {
        "joins": [],
        "match": {
          "id": "1971d1c0-5492-4fc1-b080-219e050295eb",
          "rules": []
        },
        "entity": "0b049cb4-80fc-4677-85f5-574c21278503",
        "output": [
          "0b049cb4-80fc-4677-85f5-574c21278503.id",
          "0b049cb4-80fc-4677-85f5-574c21278503.name"
        ]
      }
    },
    {
      "name": "Pie 1.2",
      "metadata": {
        "type": "pie",
        "width": 6,
        "widget": {
          "fieldToAggregate": "5dde2695-c956-4c3a-a41a-b28520203853.user_id"
        }
      },
      "jsonData": {
        "joins": [],
        "match": {
          "id": "fe439a0c-fff7-4b6b-855f-307f782905e4",
          "rules": []
        },
        "entity": "5dde2695-c956-4c3a-a41a-b28520203853",
        "output": [
          "5dde2695-c956-4c3a-a41a-b28520203853.user_id"
        ]
      }
    },
    {
      "name": "Barra",
      "metadata": {
        "type": "bar",
        "width": 6,
        "widget": {
          "fieldToAggregate": "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.id"
        }
      },
      "jsonData": {
        "joins": [],
        "match": {
          "id": "88f1d21d-be31-4a22-9480-6aec7c2c6278",
          "rules": []
        },
        "entity": "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8",
        "output": [
          "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.id",
          "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.item",
          "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.log"
        ]
      }
    }
  ],
  "variables": [
    {
      "name": "7b268b3107d74a8e89a74ff728e1de33name",
      "type": "string",
      "label": "Nombre de dirección test"
    }
  ]
}

Limitaciones y restricciones

Rendimiento

• Consultas muy complejas o que devuelvan grandes volúmenes de datos pueden afectar el rendimiento del sistema.
• Se aplica paginación para mitigar cargas excesivas de datos.

Seguridad

• Todos los endpoints requieren autenticación y permisos adecuados (verificados por middleware).
• La sustitución de variables es estrictamente validada; solo se aceptan variables definidas.

Tipos de consulta soportados

• Solo se permiten consultas SELECT; no se permiten actualizaciones, inserciones ni eliminaciones.
• Los joins, filtros y campos de salida deben referenciar entidades y campos válidos.
• Se soportan subconsultas mediante selección de índice, pero deben estar dentro de los límites.

Manejo de errores y casos límite

• Definición de consulta inválida: JSON mal formado o campos requeridos faltantes devuelven 400 Bad Request.
• Variables no soportadas: Proveer variables no definidas en la consulta resulta en un error descriptivo.
• Consulta inexistente: Solicitudes para consultas faltantes devuelven 404 Not Found.
• Índice de subconsulta fuera de rango: Intentar ejecutar un índice de subconsulta inexistente devuelve BadRequestError.
• Errores de base de datos: Cualquier error SQL o de conexión se envuelve y retorna como BadQueryError.

Ejemplo de respuesta de error

{
  "status": 400,
  "error": "Variables no soportadas proporcionadas: [foo, bar]"
}

Consejos avanzados de uso

• Utilizar variables para hacer las consultas reutilizables y contextuales (por ejemplo, filtrar por usuario actual).
• Combinar múltiples joins para relaciones de datos complejas.
• Aprovechar la selección de campos de salida para optimizar la carga de resultados.
• Usar parámetros de orden y paginación para controlar el flujo y rendimiento de los datos.

Endpoints para gestionar Consultas Personalizadas

Las Consultas Personalizadas se gestionan mediante un conjunto de endpoints RESTful, cada uno con un propósito específico en el ciclo de vida de la consulta:

Ruta	Método	Descripción
/api/v1/queries	GET	Lista todas las consultas personalizadas
/api/v1/queries	POST	Crea una nueva consulta personalizada
/api/v1/queries/{id}	GET	Recupera una consulta personalizada por ID
/api/v1/queries/{id}	PUT	Actualiza una consulta personalizada
/api/v1/queries/{id}	DELETE	Elimina una consulta personalizada
/api/v1/queries/{id}/execute	GET	Ejecuta una consulta personalizada almacenada

Crear una Consulta Personalizada

POST /api/v1/queries

<CustomQuery>

Ejemplo de respuesta:

{
  "status": 201,
  "error": false,
  "entityName": "string",
  "message": "Creado correctamente",
  "data": {
    "id": "1",
    "queries": [...],
    "variables": [...],
    "name": "Departamentos de Empleados",
    "metadata": { "description": "Consulta de empleados y departamentos" }
  }
}

Actualizar una Consulta Personalizada

Parámetro	Tipo	Descripción
id	string	El ID de la consulta personalizada a eliminar.

Solicitud:

PUT /api/v1/queries/:id

<CustomQuery>

Ejemplo de respuesta:

{
  "status": 200,
  "error": false,
  "message": "string",
  "entityName": "string",
  "data": { <CustomQuery> }
}

Listar todas las Consultas Personalizadas

Parámetro	Tipo	Descripción
limit	number	(Opcional) Número de registros por página. Por defecto es 20.
page	number	(Opcional) Número de página a recuperar. Por defecto es 1.
filter	Object	(Opcional) Objeto JSON para filtrar consultas.
ids	Array<string>	(Opcional) Arreglo de IDs de consultas a recuperar.

GET /api/v1/queries?limit=:limit&page=:page&filter=:filter[Object]&ids[]=:id[]

GET /api/v1/queries?limit=20&page=1&filter={"name":"Departamentos%20de%20Empleados"}&ids[]=1&ids[]=2&ids[]=3

Ejemplo de respuesta:

{
  "status": 200,
  "error": false,
  "message": "string",
  "entityName": "string",
  "data": [
    {
      "id": "1",
      "queries": [...],
      "variables": [...],
      "name": "Departamentos de Empleados",
      "metadata": { "description": "Consulta de empleados y departamentos" }
    }
  ]
}

Eliminar una Consulta Personalizada

Parámetro	Tipo	Descripción
id	string	El ID de la consulta personalizada a eliminar.

DELETE /api/v1/queries/:id

Respuesta:

{
  "status": 200,
  "error": false,
  "message": "string",
  "data": [ { <CustomQuery> } ]
}

Ejecutar una Consulta Personalizada

Parámetro	Tipo	Descripción
id	string	El ID de la consulta personalizada a ejecutar.
limit	number	(Opcional) Número de registros por página. Por defecto es 10.
page	number	(Opcional) Número de página a recuperar. Por defecto es 1.
sorting	string	(Opcional) Campo por el que ordenar (por ejemplo, fieldName)
subQuery	number	(Opcional) Índice de la subconsulta a ejecutar. Por defecto es 0 (primera subconsulta).
variables	Object	(Opcional) Objeto JSON que mapea nombres de variables a sus valores, por ejemplo, {"month":"2025-09","region":"West"}.

GET /api/v1/queries/:id/execute?limit=:limit&page=:page&sorting=:sorting&subQuery=:subQuery&variables=:variables[Object]

GET /api/v1/queries/42/execute?limit=10&page=1&variables={"month":"2025-09","region":"West"}

Respuesta:

{
  "status": 200,
  "error": false,
  "errors": [ { } ],
  "data": [ [...] ],
  "message": "string",
  "pagination": { <Pagination> }
}