Casos de Uso: JOINs Multi-Schema (Cross-Schema Querying)

Versión: 2.0.0 Fecha: 2026-02-04 Audiencia: Backend Developers Nivel: Avanzado

Tabla de Contenidos

Introducción

Este documento presenta casos de uso prácticos del patrón unificado de JOINs con consolidación multi-schema (queries que abarcan múltiples schemas simultáneamente usando UNION ALL).

IMPORTANTE - Dos tipos de cross-schema:

Cross-level directo (SIN UNION ALL): JOIN entre niveles jerárquicos en 1 query
- Ejemplo: suc0001caja001.recibos JOIN suc0001.facturas (1 query directa)
- Ver: casos-uso-simple.md Caso 8 (si existe) o documentación adicional
Multi-schema consolidado (CON UNION ALL): Consolidar N schemas + JOIN cross-level
- Ejemplo: Todas las cajas JOIN con sucursal (UNION ALL de queries cross-level)
- Este documento cubre este caso

Contexto Arquitectónico:

Sistema Bautista usa arquitectura multi-tenant (cada request trabaja en 1 schema para aislamiento). Sin embargo, estos casos de uso requieren multi-schema querying (consolidación de N schemas) para:

Buscar registros sin saber en qué schema están
Consolidar datos de múltiples schemas (ej: todas las cajas de una sucursal)
Realizar consolidación con JOINs cross-level (todas las CAJAS → SUCURSAL)

Jerarquía de Schemas:

EMPRESA (nivel 1): Schema public - Datos compartidos
SUCURSAL (nivel 2): Schemas suc0001, suc0002 - Datos por sucursal
CAJA (nivel 3): Schemas suc0001caja001 - Datos por punto de venta

Diferencia clave:

Multi-tenant: 1 schema por request (aislamiento) → Ver casos-uso-simple.md
Multi-schema: N schemas por query (consolidación) → Este documento

Prerrequisitos:

Leer Guía Completa para entender componentes fundamentales
Leer docs/architecture/database/multi-schema.md para entender multi-schema querying

Caso 1: JOIN Cross-Level con Multi-Schema (CAJA → SUCURSAL)

Descripción del Problema

Consolidar movimientos de caja de TODAS las cajas de una sucursal, cada uno con información de movimientos bancarios asociados.

Desafío multi-schema:

Cada caja tiene sus movimientos en su propio schema (suc0001caja001, suc0001caja002, etc.)
Los movimientos bancarios están en schema de sucursal (suc0001)
Necesitamos consolidar TODO con UNION ALL

Jerarquía Cross-Level:

suc0001caja001.movimientos_caja (CAJA) ──┐
suc0001caja002.movimientos_caja (CAJA) ──┼─→ UNION ALL
suc0001caja003.movimientos_caja (CAJA) ──┘
   ↓ JOIN (cada uno)
suc0001.movimientos_bancarios (SUCURSAL)

Estructura de Tablas

sql

-- Nivel SUCURSAL: Movimientos bancarios
CREATE TABLE movimientos_bancarios (
    id SERIAL PRIMARY KEY,
    numero_cheque VARCHAR(50),
    monto DECIMAL(10,2) NOT NULL,
    fecha DATE NOT NULL,
    banco_id INTEGER,
    deleted_at TIMESTAMP NULL
);

-- Nivel CAJA: Movimientos de caja
CREATE TABLE movimientos_caja (
    id SERIAL PRIMARY KEY,
    tipo VARCHAR(20) NOT NULL, -- 'INGRESO' | 'EGRESO'
    monto DECIMAL(10,2) NOT NULL,
    concepto VARCHAR(200),
    movimiento_bancario_id INTEGER, -- FK a nivel SUCURSAL
    fecha DATE NOT NULL,
    deleted_at TIMESTAMP NULL
);

-- Índices
CREATE INDEX idx_movimientos_caja_bancario ON movimientos_caja(movimiento_bancario_id);
CREATE INDEX idx_movimientos_bancarios_fecha ON movimientos_bancarios(fecha);

Implementación

Models sin schemaLevel (Auto-Resolución)

php

<?php

namespace App\Models\Tesoreria;

use App\Models\Contracts\ModelMetadata;
use PDO;

/**
 * Movimientos Bancarios (Nivel SUCURSAL)
 *
 * Nota: El nivel de schema se auto-resuelve con MultiSchemaService.
 * NO es necesario declarar schemaLevel() manualmente.
 */
final class MovimientoBancarioModel implements ModelMetadata
{
    private PDO $conn;

    public function __construct(PDO $connection)
    {
        $this->conn = $connection;
    }

    public static function table(): string { return 'movimientos_bancarios'; }
    public static function alias(): string { return 'mb'; }
    public static function primaryKey(): string { return 'id'; }

    public function getAll(): array
    {
        $sql = "SELECT * FROM " . self::table() . " WHERE deleted_at IS NULL";
        return $this->conn->query($sql)->fetchAll();
    }
}

/**
 * Movimientos de Caja (Nivel CAJA)
 *
 * Nota: El nivel de schema se auto-resuelve con MultiSchemaService.
 * No es necesario declarar schemaLevel() manualmente.
 */
final class MovimientoCajaModel implements ModelMetadata
{
    private PDO $conn;

    public function __construct(PDO $connection)
    {
        $this->conn = $connection;
    }

    public static function table(): string { return 'movimientos_caja'; }
    public static function alias(): string { return 'mc'; }
    public static function primaryKey(): string { return 'id'; }

    public function getAll(): array
    {
        $sql = "SELECT * FROM " . self::table() . " WHERE deleted_at IS NULL";
        return $this->conn->query($sql)->fetchAll();
    }
}

MultiSchemaService Integration

php

<?php

namespace App\Services\Core;

use PDO;

/**
 * Servicio para resolución de schemas en queries cross-level
 */
class MultiSchemaService
{
    private PDO $conn;

    public function __construct(PDO $connection)
    {
        $this->conn = $connection;
    }

    /**
     * Obtiene mapping de schemas para tablas relacionadas
     *
     * @param string $primaryTable Tabla principal (ej: 'movimientos_caja')
     * @param string $relatedTable Tabla relacionada (ej: 'movimientos_bancarios')
     * @param array $cajaIds IDs de cajas (ej: [1, 2, 3])
     * @param string $currentSchema Schema actual (ej: 'suc0001')
     * @return array [['primary' => 'suc0001caja001', 'related' => 'suc0001'], ...]
     */
    public function getRelatedTableSchemas(
        string $primaryTable,
        string $relatedTable,
        array $cajaIds,
        string $currentSchema
    ): array {
        $mapping = [];

        // Descubrir schemas de tabla principal
        $primarySchemas = $this->discoverTableSchemas($primaryTable);

        foreach ($primarySchemas as $primarySchema) {
            // Resolver schema de tabla relacionada
            $relatedSchema = $this->resolveRelatedSchema(
                $primarySchema,
                $relatedTable
            );

            if ($relatedSchema) {
                $mapping[] = [
                    'primary' => $primarySchema,
                    'related' => $relatedSchema
                ];
            }
        }

        return $mapping;
    }

    /**
     * Descubre en qué schemas existe una tabla
     */
    private function discoverTableSchemas(string $tableName): array
    {
        $sql = "SELECT schema_name
                FROM information_schema.tables
                WHERE table_name = :table_name
                AND table_schema NOT IN ('pg_catalog', 'information_schema')
                ORDER BY schema_name";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['table_name' => $tableName]);

        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    /**
     * Resuelve schema de tabla relacionada según jerarquía
     *
     * Lógica:
     * 1. Buscar en mismo schema
     * 2. Subir un nivel (CAJA → SUCURSAL)
     * 3. Fallback a public (EMPRESA)
     */
    private function resolveRelatedSchema(string $primarySchema, string $relatedTable): ?string
    {
        // 1. Mismo schema
        if ($this->tableExistsInSchema($relatedTable, $primarySchema)) {
            return $primarySchema;
        }

        // 2. Schema padre (CAJA → SUCURSAL)
        if (preg_match('/^(suc\d+)caja\d+$/', $primarySchema, $matches)) {
            $parentSchema = $matches[1]; // ej: 'suc0001'
            if ($this->tableExistsInSchema($relatedTable, $parentSchema)) {
                return $parentSchema;
            }
        }

        // 3. Schema padre (SUCURSAL → EMPRESA)
        if (preg_match('/^suc\d+$/', $primarySchema)) {
            if ($this->tableExistsInSchema($relatedTable, 'public')) {
                return 'public';
            }
        }

        return null;
    }

    /**
     * Verifica si tabla existe en un schema
     */
    private function tableExistsInSchema(string $tableName, string $schema): bool
    {
        $sql = "SELECT EXISTS (
                    SELECT 1
                    FROM information_schema.tables
                    WHERE table_name = :table_name
                    AND table_schema = :schema
                )";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['table_name' => $tableName, 'schema' => $schema]);

        return (bool) $stmt->fetchColumn();
    }
}

Query Class con UNION ALL (JoinSpec directo)

php

<?php

namespace App\Models\Queries\Tesoreria;

use App\Models\Queries\BaseQuery;
use App\Models\Contracts\JoinSpec;
use App\Models\Tesoreria\{MovimientoCajaModel, MovimientoBancarioModel};

/**
 * Query para movimientos de caja conciliados con movimientos bancarios
 *
 * MultiSchemaService auto-resuelve que:
 * - 'movimientos_caja' está en nivel CAJA (schemas: suc0001caja001, suc0001caja002, etc.)
 * - 'movimientos_bancarios' está en nivel SUCURSAL (schema: suc0001)
 * - Y resuelve el mejor matching para cross-level JOIN
 */
class MovimientosCajaConciliadosQuery extends BaseQuery
{
    public function execute(): array
    {
        // Base SQL con placeholder {schema}
        $baseSql = sprintf(
            "SELECT
                mc.id,
                mc.tipo,
                mc.monto,
                mc.concepto,
                mc.fecha,
                mb.id AS banco_id,
                mb.numero_cheque,
                mb.banco_id
            FROM {schema}.%s mc",
            MovimientoCajaModel::table()
        );

        // Obtener lista de schemas (desde Service)
        $schemaList = $this->getSchemaList();

        // Ejecutar con UNION ALL - JoinSpec creado directamente
        // MultiSchemaService resolverá automáticamente que mb debe buscarse en schema padre
        return $this->executeMultiSchema(
            $baseSql,
            [
                JoinSpec::autoWithSchema(
                    'mc',
                    MovimientoCajaModel::class,      // Nivel auto-detectado: CAJA
                    MovimientoBancarioModel::class,  // Nivel auto-detectado: SUCURSAL
                    'LEFT'
                )
            ],
            $schemaList,
            [],
            'mc.fecha DESC, mc.id DESC',
            20,
            0
        );
    }

    /**
     * Obtiene lista de schemas para query
     * En producción, esto vendría del Service con MultiSchemaService
     */
    private function getSchemaList(): array
    {
        // Ejemplo: Cajas de sucursal 1
        return ['suc0001caja001', 'suc0001caja002', 'suc0001caja003'];
    }
}

SQL Generado

sql

(
  SELECT 'suc0001caja001' AS _schema,
         mc.id,
         mc.tipo,
         mc.monto,
         mc.concepto,
         mc.fecha,
         mb.id AS banco_id,
         mb.numero_cheque,
         mb.banco_id
  FROM suc0001caja001.movimientos_caja mc
  LEFT JOIN suc0001.movimientos_bancarios mb ON mb.movimiento_caja_id = mc.id
  WHERE deleted_at IS NULL
)
UNION ALL
(
  SELECT 'suc0001caja002' AS _schema,
         mc.id,
         mc.tipo,
         mc.monto,
         mc.concepto,
         mc.fecha,
         mb.id AS banco_id,
         mb.numero_cheque,
         mb.banco_id
  FROM suc0001caja002.movimientos_caja mc
  LEFT JOIN suc0001.movimientos_bancarios mb ON mb.movimiento_caja_id = mc.id
  WHERE deleted_at IS NULL
)
UNION ALL
(
  SELECT 'suc0001caja003' AS _schema,
         mc.id,
         mc.tipo,
         mc.monto,
         mc.concepto,
         mc.fecha,
         mb.id AS banco_id,
         mb.numero_cheque,
         mb.banco_id
  FROM suc0001caja003.movimientos_caja mc
  LEFT JOIN suc0001.movimientos_bancarios mb ON mb.movimiento_caja_id = mc.id
  WHERE deleted_at IS NULL
)
ORDER BY fecha DESC, id DESC
LIMIT 20

Test de Integración

php

<?php

use PHPUnit\Framework\TestCase;

class MovimientosCajaConciliadosQueryTest extends TestCase
{
    private PDO $conn;
    private MultiSchemaService $multiSchemaService;

    protected function setUp(): void
    {
        $this->conn = new PDO('pgsql:host=localhost;dbname=testdb', 'user', 'pass');
        $this->multiSchemaService = new MultiSchemaService($this->conn);

        $this->setupSchemas();
        $this->seedData();
    }

    private function setupSchemas(): void
    {
        // Crear schemas de test
        $this->conn->exec("CREATE SCHEMA IF NOT EXISTS suc0001");
        $this->conn->exec("CREATE SCHEMA IF NOT EXISTS suc0001caja001");
        $this->conn->exec("CREATE SCHEMA IF NOT EXISTS suc0001caja002");

        // Crear tablas en cada schema
        $this->createTables('suc0001');
        $this->createTables('suc0001caja001');
        $this->createTables('suc0001caja002');
    }

    private function createTables(string $schema): void
    {
        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS {$schema}.movimientos_caja (
                id SERIAL PRIMARY KEY,
                tipo VARCHAR(20) NOT NULL,
                monto DECIMAL(10,2) NOT NULL,
                concepto VARCHAR(200),
                movimiento_bancario_id INTEGER,
                fecha DATE NOT NULL,
                deleted_at TIMESTAMP NULL
            )
        ");

        // Solo en schema SUCURSAL
        if (preg_match('/^suc\d+$/', $schema)) {
            $this->conn->exec("
                CREATE TABLE IF NOT EXISTS {$schema}.movimientos_bancarios (
                    id SERIAL PRIMARY KEY,
                    numero_cheque VARCHAR(50),
                    monto DECIMAL(10,2) NOT NULL,
                    fecha DATE NOT NULL,
                    deleted_at TIMESTAMP NULL
                )
            ");
        }
    }

    private function seedData(): void
    {
        // Movimientos bancarios (nivel SUCURSAL)
        $this->conn->exec("
            INSERT INTO suc0001.movimientos_bancarios (id, numero_cheque, monto, fecha) VALUES
            (1, 'CH-001', 1000.00, '2026-01-15'),
            (2, 'CH-002', 2000.00, '2026-01-16')
        ");

        // Movimientos caja001
        $this->conn->exec("
            INSERT INTO suc0001caja001.movimientos_caja (tipo, monto, concepto, movimiento_bancario_id, fecha) VALUES
            ('INGRESO', 1000.00, 'Depósito CH-001', 1, '2026-01-15'),
            ('EGRESO', 500.00, 'Retiro', NULL, '2026-01-16')
        ");

        // Movimientos caja002
        $this->conn->exec("
            INSERT INTO suc0001caja002.movimientos_caja (tipo, monto, concepto, movimiento_bancario_id, fecha) VALUES
            ('INGRESO', 2000.00, 'Depósito CH-002', 2, '2026-01-16'),
            ('EGRESO', 300.00, 'Retiro', NULL, '2026-01-17')
        ");
    }

    public function test_execute_returns_movimientos_from_all_cajas()
    {
        $query = new MovimientosCajaConciliadosQuery($this->conn);
        $results = $query->execute();

        // Debe retornar movimientos de ambas cajas
        $this->assertCount(4, $results);

        // Verificar presencia de campo _schema
        $this->assertArrayHasKey('_schema', $results[0]);
    }

    public function test_execute_includes_banco_info_for_conciliados()
    {
        $query = new MovimientosCajaConciliadosQuery($this->conn);
        $results = $query->execute();

        // Filtrar movimientos conciliados
        $conciliados = array_filter($results, fn($r) => $r['banco_id'] !== null);

        $this->assertCount(2, $conciliados);

        // Verificar que tienen número de cheque
        foreach ($conciliados as $mov) {
            $this->assertNotNull($mov['numero_cheque']);
        }
    }

    public function test_multiSchemaService_resolves_correct_schemas()
    {
        $mapping = $this->multiSchemaService->getRelatedTableSchemas(
            'movimientos_caja',
            'movimientos_bancarios',
            [1, 2],
            'suc0001'
        );

        $this->assertCount(2, $mapping);

        // Verificar resolución CAJA → SUCURSAL
        $this->assertEquals('suc0001caja001', $mapping[0]['primary']);
        $this->assertEquals('suc0001', $mapping[0]['related']);

        $this->assertEquals('suc0001caja002', $mapping[1]['primary']);
        $this->assertEquals('suc0001', $mapping[1]['related']);
    }

    protected function tearDown(): void
    {
        $this->conn->exec("DROP SCHEMA IF EXISTS suc0001 CASCADE");
        $this->conn->exec("DROP SCHEMA IF EXISTS suc0001caja001 CASCADE");
        $this->conn->exec("DROP SCHEMA IF EXISTS suc0001caja002 CASCADE");
    }
}

Resultado Esperado

json

[
  {
    "_schema": "suc0001caja002",
    "id": 2,
    "tipo": "EGRESO",
    "monto": "300.00",
    "concepto": "Retiro",
    "fecha": "2026-01-17",
    "banco_id": null,
    "numero_cheque": null
  },
  {
    "_schema": "suc0001caja002",
    "id": 1,
    "tipo": "INGRESO",
    "monto": "2000.00",
    "concepto": "Depósito CH-002",
    "fecha": "2026-01-16",
    "banco_id": 2,
    "numero_cheque": "CH-002"
  },
  {
    "_schema": "suc0001caja001",
    "id": 2,
    "tipo": "EGRESO",
    "monto": "500.00",
    "concepto": "Retiro",
    "fecha": "2026-01-16",
    "banco_id": null,
    "numero_cheque": null
  },
  {
    "_schema": "suc0001caja001",
    "id": 1,
    "tipo": "INGRESO",
    "monto": "1000.00",
    "concepto": "Depósito CH-001",
    "fecha": "2026-01-15",
    "banco_id": 1,
    "numero_cheque": "CH-001"
  }
]

Caso 2: JOIN Cross-Level con Multi-Schema (SUCURSAL → EMPRESA)

Descripción del Problema

Consolidar facturas de TODAS las sucursales, cada una con información de productos compartidos (nivel EMPRESA).

Desafío multi-schema:

Cada sucursal tiene sus facturas en su propio schema (suc0001, suc0002, etc.)
Los productos están compartidos en public (EMPRESA)
Necesitamos consolidar TODO con UNION ALL

Jerarquía Cross-Level:

suc0001.facturas (SUCURSAL) ──┐
suc0002.facturas (SUCURSAL) ──┼─→ UNION ALL
suc0003.facturas (SUCURSAL) ──┘
   ↓ JOIN (cada uno)
public.productos (EMPRESA)

Estructura de Tablas

sql

-- Nivel EMPRESA: Productos compartidos
CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    codigo VARCHAR(50) UNIQUE NOT NULL,
    nombre VARCHAR(200) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    deleted_at TIMESTAMP NULL
);

-- Nivel SUCURSAL: Facturas
CREATE TABLE facturas (
    id SERIAL PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    cliente_id INTEGER NOT NULL,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    fecha DATE NOT NULL,
    deleted_at TIMESTAMP NULL
);

-- Nivel SUCURSAL: Items de factura
CREATE TABLE factura_items (
    id SERIAL PRIMARY KEY,
    factura_id INTEGER NOT NULL REFERENCES facturas(id),
    producto_id INTEGER NOT NULL, -- FK a nivel EMPRESA
    cantidad INTEGER NOT NULL DEFAULT 1,
    precio_unitario DECIMAL(10,2) NOT NULL,
    deleted_at TIMESTAMP NULL
);

Implementación

Models

php

/**
 * Productos compartidos - MultiSchemaService detectará nivel EMPRESA
 */
final class ProductoModel implements ModelMetadata
{
    public static function table(): string { return 'productos'; }
    public static function alias(): string { return 'p'; }
    public static function primaryKey(): string { return 'id'; }
}

/**
 * Facturas por sucursal - MultiSchemaService detectará nivel SUCURSAL
 */
final class FacturaModel implements ModelMetadata
{
    public static function table(): string { return 'facturas'; }
    public static function alias(): string { return 'f'; }
    public static function primaryKey(): string { return 'id'; }
}

/**
 * Items de factura - MultiSchemaService detectará nivel SUCURSAL
 */
final class FacturaItemModel implements ModelMetadata
{
    public static function table(): string { return 'factura_items'; }
    public static function alias(): string { return 'fi'; }
    public static function primaryKey(): string { return 'id'; }
}

Query Class (con JoinSpecs directos)

php

/**
 * Facturas con productos - Ejemplo de JOIN cross-level SUCURSAL → EMPRESA
 *
 * MultiSchemaService detecta automáticamente que 'productos' está en 'public'
 * y resuelve el JOIN correctamente desde cada schema de sucursal.
 */
class FacturasConProductosQuery extends BaseQuery
{
    public function execute(): array
    {
        $baseSql = sprintf(
            "SELECT
                f.id AS factura_id,
                f.numero,
                f.total,
                f.fecha,
                p.codigo AS producto_codigo,
                p.nombre AS producto_nombre,
                fi.cantidad,
                fi.precio_unitario,
                (fi.cantidad * fi.precio_unitario) AS subtotal
            FROM {schema}.%s f",
            FacturaModel::table()
        );

        // Lista de schemas de sucursales
        $schemaList = ['suc0001', 'suc0002', 'suc0003'];

        // JoinSpecs creados directamente
        // MultiSchemaService auto-resuelve el schema de productos (public)
        return $this->executeMultiSchema(
            $baseSql,
            [
                JoinSpec::auto('f', FacturaModel::class, FacturaItemModel::class, 'INNER'),         // factura → factura_items (mismo schema)
                JoinSpec::autoWithSchema('fi', FacturaItemModel::class, ProductoModel::class, 'INNER') // factura_items → productos (cross-level, auto-resuelto)
            ],
            $schemaList,
            [],
            'f.fecha DESC, f.id ASC',
            50,
            0
        );
    }
}

SQL Generado

sql

(
  SELECT 'suc0001' AS _schema,
         f.id AS factura_id,
         f.numero,
         f.total,
         f.fecha,
         p.codigo AS producto_codigo,
         p.nombre AS producto_nombre,
         fi.cantidad,
         fi.precio_unitario,
         (fi.cantidad * fi.precio_unitario) AS subtotal
  FROM suc0001.facturas f
  INNER JOIN suc0001.factura_items fi ON fi.factura_id = f.id
  INNER JOIN public.productos p ON p.id = fi.producto_id
  WHERE deleted_at IS NULL
)
UNION ALL
(
  SELECT 'suc0002' AS _schema, ...
  FROM suc0002.facturas f
  INNER JOIN suc0002.factura_items fi ON fi.factura_id = f.id
  INNER JOIN public.productos p ON p.id = fi.producto_id
  WHERE deleted_at IS NULL
)
UNION ALL
(
  SELECT 'suc0003' AS _schema, ...
  FROM suc0003.facturas f
  INNER JOIN suc0003.factura_items fi ON fi.factura_id = f.id
  INNER JOIN public.productos p ON p.id = fi.producto_id
  WHERE deleted_at IS NULL
)
ORDER BY fecha DESC, id ASC
LIMIT 50

Caso 3: Consolidación con Filtros

Descripción del Problema

Obtener movimientos conciliados de múltiples cajas con filtros por fecha y tipo. Requiere COUNT separado para paginación correcta.

Implementación

Query Class con COUNT

php

class MovimientosCajaConciliadosConPaginacionQuery extends BaseQuery
{
    private array $schemaList;
    private array $filters;

    public function __construct(PDO $connection, array $schemaList, array $filters = [])
    {
        parent::__construct($connection);
        $this->schemaList = $schemaList;
        $this->filters = $filters;
    }

    /**
     * Ejecuta query con datos paginados
     */
    public function execute(): array
    {
        $baseSql = sprintf(
            "SELECT
                mc.id,
                mc.tipo,
                mc.monto,
                mc.concepto,
                mc.fecha,
                mb.numero_cheque
            FROM {schema}.%s mc",
            MovimientoCajaModel::table()
        );

        return $this->executeMultiSchema(
            $baseSql,
            [
                JoinSpec::autoWithSchema(
                    'mc',
                    MovimientoCajaModel::class,
                    MovimientoBancarioModel::class,
                    'LEFT'
                )
            ],
            $this->schemaList,
            $this->filters,
            'mc.fecha DESC, mc.id DESC',
            20,
            0
        );
    }

    /**
     * Ejecuta COUNT query para meta.totalRowCount
     */
    public function executeCount(): int
    {
        $baseSql = sprintf(
            "SELECT COUNT(*) FROM {schema}.%s mc",
            MovimientoCajaModel::table()
        );

        return $this->executeCountMultiSchema(
            $baseSql,
            [
                JoinSpec::autoWithSchema(
                    'mc',
                    MovimientoCajaModel::class,
                    MovimientoBancarioModel::class,
                    'LEFT'
                )
            ],
            $this->filters,
            $this->schemaList
        );
    }
}

Service con Paginación

php

<?php

namespace App\Services\Tesoreria;

use App\Models\Queries\Tesoreria\MovimientosCajaConciliadosConPaginacionQuery;
use App\Services\Core\MultiSchemaService;

class MovimientosCajaService
{
    private PDO $conn;
    private MultiSchemaService $multiSchemaService;

    public function __construct(PDO $connection, MultiSchemaService $multiSchemaService)
    {
        $this->conn = $connection;
        $this->multiSchemaService = $multiSchemaService;
    }

    /**
     * Obtiene movimientos conciliados con paginación
     *
     * @param array $cajaIds IDs de cajas
     * @param array $filters Filtros (fecha_desde, fecha_hasta, tipo)
     * @param int $pageIndex Página (0-indexed)
     * @param int $pageSize Tamaño de página
     * @return array ['data' => [...], 'meta' => [...]]
     */
    public function getMovimientosConciliados(
        array $cajaIds,
        array $filters = [],
        int $pageIndex = 0,
        int $pageSize = 20
    ): array {
        // 1. Obtener mapping de schemas
        $schemaMapping = $this->multiSchemaService->getRelatedTableSchemas(
            'movimientos_caja',
            'movimientos_bancarios',
            $cajaIds,
            $this->getCurrentSchema()
        );

        $schemaList = array_column($schemaMapping, 'primary');

        // 2. Preparar filtros para SQL
        $sqlFilters = $this->prepareSqlFilters($filters);

        // 3. Crear query
        $query = new MovimientosCajaConciliadosConPaginacionQuery(
            $this->conn,
            $schemaList,
            $sqlFilters
        );

        // 4. Ejecutar COUNT (query separada)
        $totalCount = $query->executeCount();

        // 5. Ejecutar DATA query
        $data = $query->execute();

        // 6. Retornar con metadata
        return [
            'data' => $data,
            'meta' => [
                'totalRowCount' => $totalCount,
                'pageIndex' => $pageIndex,
                'pageSize' => $pageSize,
                'totalPages' => (int) ceil($totalCount / $pageSize)
            ]
        ];
    }

    private function getCurrentSchema(): string
    {
        // En producción, obtener desde X-Schema header o session
        return 'suc0001';
    }

    private function prepareSqlFilters(array $filters): array
    {
        $sqlFilters = [];

        if (isset($filters['fecha_desde'])) {
            $sqlFilters['mc.fecha >='] = $filters['fecha_desde'];
        }

        if (isset($filters['fecha_hasta'])) {
            $sqlFilters['mc.fecha <='] = $filters['fecha_hasta'];
        }

        if (isset($filters['tipo'])) {
            $sqlFilters['mc.tipo'] = $filters['tipo'];
        }

        return $sqlFilters;
    }
}

Caso 4: Optimización con Schema Único

Descripción del Problema

Detectar cuando solo hay un schema y evitar UNION ALL innecesario.

Implementación

BaseQuery con Detección Inteligente

php

abstract class BaseQuery
{
    /**
     * Ejecuta query con detección inteligente de single vs multi-schema
     */
    protected function executeMultiSchema(
        string $baseSql,
        array $joins,
        array $schemaList,
        array $filters = [],
        string $orderBy = '',
        ?int $limit = null,
        ?int $offset = null
    ): array {
        // Optimización: Si solo hay 1 schema, query simple
        if (count($schemaList) === 1) {
            return $this->executeSingleSchema(
                $baseSql,
                $joins,
                $schemaList[0],
                $filters,
                $orderBy,
                $limit,
                $offset
            );
        }

        // Multi-schema: UNION ALL
        return $this->executeWithUnionAll(
            $baseSql,
            $joins,
            $schemaList,
            $filters,
            $orderBy,
            $limit,
            $offset
        );
    }

    /**
     * Ejecuta query simple sin UNION (optimización)
     */
    private function executeSingleSchema(
        string $baseSql,
        array $joins,
        string $schema,
        array $filters,
        string $orderBy,
        ?int $limit,
        ?int $offset
    ): array {
        $sql = str_replace('{schema}', $schema, $baseSql);
        $sql = $this->applyJoins($sql, $joins, true);
        $sql = $this->applyFilters($sql, $filters);

        if ($orderBy) $sql .= " ORDER BY $orderBy";
        if ($limit) $sql .= " LIMIT $limit";
        if ($offset) $sql .= " OFFSET $offset";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute($filters);

        return $stmt->fetchAll();
    }

    /**
     * Ejecuta query con UNION ALL
     */
    private function executeWithUnionAll(
        string $baseSql,
        array $joins,
        array $schemaList,
        array $filters,
        string $orderBy,
        ?int $limit,
        ?int $offset
    ): array {
        $queries = [];

        foreach ($schemaList as $schema) {
            $sql = str_replace('{schema}', $schema, $baseSql);
            $sql = str_replace('SELECT', "SELECT '$schema' AS _schema,", $sql);
            $sql = $this->applyJoins($sql, $joins, true);
            $sql = $this->applyFilters($sql, $filters);

            $queries[] = "($sql)";
        }

        $unionSql = implode(' UNION ALL ', $queries);

        if ($orderBy) $unionSql = "($unionSql) ORDER BY $orderBy";
        if ($limit) $unionSql .= " LIMIT $limit";
        if ($offset) $unionSql .= " OFFSET $offset";

        $stmt = $this->conn->prepare($unionSql);
        $stmt->execute($filters);

        return $stmt->fetchAll();
    }
}

Performance: UNION ALL vs Loop de Queries

Contexto: Multi-schema querying puede implementarse con:

Loop de queries (N queries separadas) → Anti-pattern
UNION ALL (1 query consolidada) → Patrón recomendado

Benchmark Comparativo

php

class PerformanceBenchmarkTest extends TestCase
{
    /**
     * Test: Loop de queries (anti-pattern para multi-schema)
     */
    public function test_performance_loop_queries()
    {
        $schemaList = ['suc0001caja001', 'suc0001caja002', 'suc0001caja003'];

        $start = microtime(true);

        $results = [];
        foreach ($schemaList as $schema) {
            $sql = "SELECT * FROM {$schema}.movimientos_caja LIMIT 100";
            $stmt = $this->conn->query($sql);
            $results = array_merge($results, $stmt->fetchAll());
        }

        $duration = microtime(true) - $start;

        echo "\nLoop de queries: {$duration}s\n";
        echo "Resultados: " . count($results) . "\n";
    }

    /**
     * Test: UNION ALL (optimizado)
     */
    public function test_performance_union_all()
    {
        $schemaList = ['suc0001caja001', 'suc0001caja002', 'suc0001caja003'];

        $start = microtime(true);

        $queries = [];
        foreach ($schemaList as $schema) {
            $queries[] = "(SELECT * FROM {$schema}.movimientos_caja)";
        }
        $unionSql = implode(' UNION ALL ', $queries) . " LIMIT 300";

        $stmt = $this->conn->query($unionSql);
        $results = $stmt->fetchAll();

        $duration = microtime(true) - $start;

        echo "\nUNION ALL: {$duration}s\n";
        echo "Resultados: " . count($results) . "\n";
    }
}

Resultados típicos:

Loop de queries: 0.0845s
Resultados: 300

UNION ALL: 0.0312s
Resultados: 300

Mejora: 2.7x más rápido

Resumen de Casos Multi-Schema

Caso	Cross-Level	Multi-Schema	Complejidad	Performance
1. CAJA → SUCURSAL	✅	✅ (N cajas)	Alta	UNION ALL
2. SUCURSAL → EMPRESA	✅	✅ (N sucursales)	Alta	UNION ALL
3. Consolidación con Filtros	✅	✅ (N schemas)	Muy Alta	UNION ALL + COUNT separado
4. Optimización Single	-	❌ (1 schema)	Media	Query simple (sin UNION)

Nota: "Multi-Schema" indica que se consolidan N schemas simultáneamente. Cuando N=1, se optimiza a query simple.

Recursos Relacionados

Documentación de JOINs

Guía Completa: Componentes fundamentales
Casos Simple: JOINs en mismo schema (multi-tenant normal)
Index: Resumen ejecutivo

Documentación de Arquitectura de Base de Datos

docs/architecture/database/index.md: Comparación de 3 conceptos arquitectónicos
docs/architecture/database/multi-tenant.md: Schema-based tenancy (1 schema por request)
docs/architecture/database/multi-schema.md: Cross-schema querying (N schemas por query)
docs/architecture/database/multi-modo.md: Dual database pattern (oficial/prueba)

Patrones Relacionados

docs/backend/cross-schema-queryable-pattern.md: Patrón CrossSchemaQueryable

Última actualización: 2026-02-04 Versión: 2.0.0 Autor: Sistema Bautista - Arquitectura Backend Nota: Corregida terminología multi-tenant vs multi-schema (2026-02-04)

Fase 1 Mvp

Fase 2 Sse

Fase 3 Production

Joins

Retenciones

Ganancias

Catalogos

Extensiones

Presupuestos

Recursos Principales

Relaciones

Catalogos Base

Cupon Pago

Facturacion

Gestion Miembros

Grupos Familiares

Soporte Tecnico

Architecture

Backend

Api

Database

Migrations

Middleware

Security

Services

Deployment

Development

Testing

Frontend

Integrations

Payment Gateways

Retenciones

Casos de Uso: JOINs Multi-Schema (Cross-Schema Querying) ​

Tabla de Contenidos ​

Introducción ​

Caso 1: JOIN Cross-Level con Multi-Schema (CAJA → SUCURSAL) ​

Descripción del Problema ​

Estructura de Tablas ​

Implementación ​

Models sin schemaLevel (Auto-Resolución) ​

MultiSchemaService Integration ​

Query Class con UNION ALL (JoinSpec directo) ​

SQL Generado ​

Test de Integración ​

Resultado Esperado ​

Caso 2: JOIN Cross-Level con Multi-Schema (SUCURSAL → EMPRESA) ​

Descripción del Problema ​

Estructura de Tablas ​

Implementación ​

Models ​

Query Class (con JoinSpecs directos) ​

SQL Generado ​

Caso 3: Consolidación con Filtros ​

Descripción del Problema ​

Implementación ​

Query Class con COUNT ​

Service con Paginación ​

Caso 4: Optimización con Schema Único ​

Descripción del Problema ​

Implementación ​

BaseQuery con Detección Inteligente ​

Performance: UNION ALL vs Loop de Queries ​

Benchmark Comparativo ​

Resumen de Casos Multi-Schema ​

Recursos Relacionados ​

Documentación de JOINs ​

Documentación de Arquitectura de Base de Datos ​

Patrones Relacionados ​

Casos de Uso: JOINs Multi-Schema (Cross-Schema Querying)

Tabla de Contenidos

Introducción

Caso 1: JOIN Cross-Level con Multi-Schema (CAJA → SUCURSAL)

Descripción del Problema

Estructura de Tablas

Implementación

Models sin schemaLevel (Auto-Resolución)

MultiSchemaService Integration

Query Class con UNION ALL (JoinSpec directo)

SQL Generado

Test de Integración

Resultado Esperado

Caso 2: JOIN Cross-Level con Multi-Schema (SUCURSAL → EMPRESA)

Descripción del Problema

Estructura de Tablas

Implementación

Models

Query Class (con JoinSpecs directos)

SQL Generado

Caso 3: Consolidación con Filtros

Descripción del Problema

Implementación

Query Class con COUNT

Service con Paginación

Caso 4: Optimización con Schema Único

Descripción del Problema

Implementación

BaseQuery con Detección Inteligente

Performance: UNION ALL vs Loop de Queries

Benchmark Comparativo

Resumen de Casos Multi-Schema

Recursos Relacionados

Documentación de JOINs

Documentación de Arquitectura de Base de Datos

Patrones Relacionados