Guía Completa: Patrón Unificado de JOINs Declarativos

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

Tabla de Contenidos

Introducción

Esta guía presenta una arquitectura unificada para manejar relaciones entre tablas (JOINs) en PHP, combinando tres conceptos clave:

JOINs Declarativos: Relaciones como especificaciones reutilizables
Auto-Detection: ON clause automático basado en convenciones
Multi-Schema Support: Consolidación cross-schema (queries que abarcan N schemas simultáneamente)

Objetivo: Eliminar JOINs hardcodeados en Models manteniendo bajo acoplamiento, SQL explícito y soporte para consolidación multi-schema en arquitectura con jerarquía de schemas.

Conceptos Fundamentales

Principio de Diseño

"Un Model = Una Tabla | JOINs = Especificaciones Directas"

Separación de responsabilidades:

Models: Representan UNA tabla, métodos CRUD simples
JoinSpec: Especificación de relaciones (creada directamente)
Query Classes: Construyen queries complejas creando JoinSpecs directos
BaseQuery: Lógica compartida para aplicar JOINs

Problema que Resuelve

Anti-Pattern: JOINs Hardcodeados

php

// ❌ Código actual (problemático)
class ClienteModel {
    public function getAllWithOrdenes(): array {
        // JOIN hardcodeado
        $sql = "SELECT c.*, o.id as orden_id, o.total
                FROM clientes c
                LEFT JOIN ordenes o ON o.cliente_id = c.id";
        return $this->conn->query($sql)->fetchAll();
    }

    public function getClientesConVentas(): array {
        // Otro JOIN hardcodeado (lógica duplicada)
        $sql = "SELECT c.*, SUM(v.total) as total_ventas
                FROM clientes c
                LEFT JOIN ventas v ON v.cliente_id = c.id
                GROUP BY c.id";
        return $this->conn->query($sql)->fetchAll();
    }
}

Problemas:

Acoplamiento: ClienteModel conoce la estructura de ordenes y ventas
Duplicación: Cada query repite la lógica del JOIN
No reutilizable: El JOIN no se puede usar en otras queries
Testing complejo: Necesitas mockear múltiples tablas
Multi-schema frágil: No escala a arquitecturas multi-tenant

Solución: Patrón Unificado

php

// ✅ Solución unificada (3 componentes)

// 1. Model solo representa SU tabla (3 métodos metadata)
class ClienteModel implements ModelMetadata {
    public static function table(): string { return 'clientes'; }
    public static function alias(): string { return 'c'; }
    public static function primaryKey(): string { return 'id'; }

    public function getAll(): array {
        return $this->conn->query("SELECT * FROM clientes")->fetchAll();
    }
}

// 2. Query Class crea JoinSpec directamente
class ClienteOrdenesQuery extends BaseQuery {
    public function execute(): array {
        $sql = "SELECT c.*, o.id as orden_id, o.total
                FROM " . ClienteModel::table() . " " . ClienteModel::alias();

        // JoinSpec creado directamente (sin catálogo)
        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT')
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

Componentes Principales

1. ModelMetadata Interface

Ubicación: models/Contracts/ModelMetadata.php

Responsabilidad: Exponer metadata mínima para construcción automática de JOINs.

php

<?php

namespace App\Models\Contracts;

/**
 * Interface para Models que exponen metadata para JOINs declarativos
 *
 * Esta interface permite:
 * - Auto-generar ON clauses basadas en convenciones
 * - Mantener bajo acoplamiento entre Models
 * - Construir queries complejas sin hardcodear JOINs
 *
 * Nota: El nivel de schema (EMPRESA/SUCURSAL/CAJA) NO se define aquí.
 * MultiSchemaService lo descubre automáticamente consultando information_schema.
 */
interface ModelMetadata
{
    /**
     * Nombre de la tabla (sin prefijos de schema)
     *
     * @return string Ejemplo: 'clientes', 'ordenes', 'productos'
     */
    public static function table(): string;

    /**
     * Alias de la tabla para uso en JOINs
     *
     * Convención: Primera letra del nombre de tabla
     *
     * @return string Ejemplo: 'c', 'o', 'p'
     */
    public static function alias(): string;

    /**
     * Nombre de la columna de clave primaria
     *
     * Usado para auto-generación de ON clauses cuando el FK
     * sigue la convención {tabla}_id
     *
     * @return string Default: 'id'
     */
    public static function primaryKey(): string;
}

Auto-Resolución de Schema Level

El nivel de schema (EMPRESA, SUCURSAL, CAJA) NO se define manualmente en los Models.

MultiSchemaService lo descubre automáticamente:

Consulta qué schemas contienen la tabla
Determina el nivel según la jerarquía
Resuelve el mejor schema para JOINs cross-level

Ventajas:

Sin configuración manual redundante
Descubrimiento dinámico de estructura
Adaptación automática a cambios de schema
Menos código de mantenimiento

Ejemplo de auto-resolución:

php

// MultiSchemaService descubre automáticamente:
// - 'clientes' está en nivel EMPRESA (schemas: public)
// - 'movimientos_caja' está en nivel CAJA (schemas: suc0001caja001, suc0002caja001)
// - 'productos' está en nivel SUCURSAL (schemas: suc0001, suc0002)

$service = new MultiSchemaService($conn);
$nivel = $service->getTableLevel('clientes');        // Retorna 1 (EMPRESA)
$nivel = $service->getTableLevel('movimientos_caja'); // Retorna 3 (CAJA)
$nivel = $service->getTableLevel('productos');       // Retorna 2 (SUCURSAL)

Implementación en Model

php

<?php

namespace App\Models\Ventas;

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

/**
 * Model para tabla clientes
 *
 * Principio: Un Model = Una Tabla
 * - Solo métodos que interactúan con SU tabla
 * - Sin JOINs hardcodeados
 * - Sin dependencias de otros Models
 */
final class ClienteModel implements ModelMetadata
{
    private PDO $conn;

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

    // ============================================
    // ModelMetadata Interface
    // ============================================

    public static function table(): string
    {
        return 'clientes';
    }

    public static function alias(): string
    {
        return 'c';
    }

    public static function primaryKey(): string
    {
        return 'id'; // Default, puede omitirse si es 'id'
    }

    // Nota: schemaLevel() NO se define aquí.
    // MultiSchemaService lo descubre automáticamente.

    // ============================================
    // Data Access Methods (SOLO su tabla)
    // ============================================

    /**
     * Obtener todos los clientes activos
     *
     * NO incluir JOINs aquí. Si necesitas datos relacionados,
     * crear una Query Class que cree JoinSpecs directamente
     */
    public function getAll(): array
    {
        $sql = "SELECT * FROM " . self::table() . " WHERE deleted_at IS NULL";
        return $this->conn->query($sql)->fetchAll();
    }

    /**
     * Obtener cliente por ID
     */
    public function getById(int $id): ?array
    {
        $sql = "SELECT * FROM " . self::table() . " WHERE id = :id AND deleted_at IS NULL";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    /**
     * Insertar nuevo cliente
     */
    public function insert(array $data): int
    {
        $sql = "INSERT INTO " . self::table() . " (nombre, email, telefono, created_at)
                VALUES (:nombre, :email, :telefono, NOW())
                RETURNING id";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($data);
        return $stmt->fetchColumn();
    }

    /**
     * Actualizar cliente existente
     */
    public function update(int $id, array $data): bool
    {
        $sql = "UPDATE " . self::table() . "
                SET nombre = :nombre,
                    email = :email,
                    telefono = :telefono,
                    updated_at = NOW()
                WHERE id = :id AND deleted_at IS NULL";
        $data['id'] = $id;
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute($data);
    }

    /**
     * Soft delete de cliente
     */
    public function delete(int $id): bool
    {
        $sql = "UPDATE " . self::table() . "
                SET deleted_at = NOW()
                WHERE id = :id AND deleted_at IS NULL";
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute(['id' => $id]);
    }

    // ❌ NO incluir métodos como:
    // - getAllWithOrdenes()
    // - getClientesConVentas()
    // - getClienteConProductos()
    //
    // ✅ Esos pertenecen a Query Classes que crean JoinSpecs directos
}

Ejemplo adicional:

php

<?php

namespace App\Models\Ventas;

use App\Models\Contracts\ModelMetadata;

final class OrdenModel implements ModelMetadata
{
    public static function table(): string { return 'ordenes'; }
    public static function alias(): string { return 'o'; }
    public static function primaryKey(): string { return 'id'; }
}

Otro ejemplo:

php

<?php

namespace App\Models\Tesoreria;

use App\Models\Contracts\ModelMetadata;

final class CajaMovimientoModel implements ModelMetadata
{
    public static function table(): string { return 'movimientos_caja'; }
    public static function alias(): string { return 'cm'; }
    public static function primaryKey(): string { return 'id'; }
}

2. JoinSpec Class

Ubicación: models/Contracts/JoinSpec.php

Responsabilidad: Especificar UNA relación entre dos tablas de forma declarativa.

php

<?php

namespace App\Models\Contracts;

/**
 * Especificación declarativa de un JOIN
 *
 * Representa la relación entre dos tablas sin ejecutar SQL.
 * Soporta tres modos de construcción:
 * 1. Manual: ON clause explícito
 * 2. Auto: ON clause automático basado en convenciones
 * 3. AutoWithSchema: Auto + soporte multi-schema
 */
final class JoinSpec
{
    /**
     * Constructor manual (Modo 1)
     *
     * Uso: Cuando necesitas control total del ON clause
     *
     * @param string $leftAlias Alias de la tabla izquierda (ej: 'c')
     * @param string $rightTable Nombre de la tabla derecha (ej: 'ordenes')
     * @param string $rightAlias Alias de la tabla derecha (ej: 'o')
     * @param string $on Condición ON del JOIN (ej: 'o.cliente_id = c.id')
     * @param string $type Tipo de JOIN (INNER, LEFT, RIGHT, FULL)
     * @param bool $multiSchema Si requiere resolución multi-schema
     */
    public function __construct(
        public readonly string $leftAlias,
        public readonly string $rightTable,
        public readonly string $rightAlias,
        public readonly string $on,
        public readonly string $type = 'INNER',
        public readonly bool $multiSchema = false
    ) {
        $this->validateType();
    }

    /**
     * Constructor automático (Modo 2)
     *
     * Auto-genera ON clause basado en:
     * - FK convencional: {rightTable}_{rightPrimaryKey}
     * - Ejemplo: ordenes.cliente_id = clientes.id
     *
     * Uso: Relaciones simples en mismo schema
     *
     * @param string $leftAlias Alias de tabla izquierda
     * @param string $leftModelClass Clase del Model izquierdo
     * @param string $rightModelClass Clase del Model derecho
     * @param string $type Tipo de JOIN
     */
    public static function auto(
        string $leftAlias,
        string $leftModelClass,
        string $rightModelClass,
        string $type = 'INNER'
    ): self {
        // Obtener metadata de ambos Models
        $leftTable = $leftModelClass::table();
        $leftPK = $leftModelClass::primaryKey();
        $rightTable = $rightModelClass::table();
        $rightAlias = $rightModelClass::alias();
        $rightPK = $rightModelClass::primaryKey();

        // Generar ON automático: {rightTable}.{leftTable}_{leftPK} = {leftAlias}.{leftPK}
        $fkColumn = $leftTable . '_' . $leftPK;
        $on = "{$rightAlias}.{$fkColumn} = {$leftAlias}.{$leftPK}";

        return new self(
            $leftAlias,
            $rightTable,
            $rightAlias,
            $on,
            $type,
            false // No multi-schema
        );
    }

    /**
     * Constructor automático con multi-schema (Modo 3)
     *
     * Auto-genera ON clause + habilita resolución de schemas
     *
     * Uso: Relaciones cross-level (CAJA → SUCURSAL, SUCURSAL → EMPRESA)
     *
     * @param string $leftAlias Alias de tabla izquierda
     * @param string $leftModelClass Clase del Model izquierdo
     * @param string $rightModelClass Clase del Model derecho
     * @param string $type Tipo de JOIN
     */
    public static function autoWithSchema(
        string $leftAlias,
        string $leftModelClass,
        string $rightModelClass,
        string $type = 'INNER'
    ): self {
        $spec = self::auto($leftAlias, $leftModelClass, $rightModelClass, $type);

        // Marcar como multi-schema
        return new self(
            $spec->leftAlias,
            $spec->rightTable,
            $spec->rightAlias,
            $spec->on,
            $spec->type,
            true // Multi-schema habilitado
        );
    }

    /**
     * Valida que el tipo de JOIN sea válido
     */
    private function validateType(): void
    {
        $validTypes = ['INNER', 'LEFT', 'RIGHT', 'FULL'];
        if (!in_array(strtoupper($this->type), $validTypes)) {
            throw new \InvalidArgumentException(
                "Invalid JOIN type: {$this->type}. Must be one of: " . implode(', ', $validTypes)
            );
        }
    }

    /**
     * Retorna el JOIN como fragmento SQL (single-schema)
     *
     * @return string Ejemplo: "LEFT JOIN ordenes o ON o.cliente_id = c.id"
     */
    public function toSQL(): string
    {
        return sprintf(
            '%s JOIN %s %s ON %s',
            strtoupper($this->type),
            $this->rightTable,
            $this->rightAlias,
            $this->on
        );
    }

    /**
     * Retorna el JOIN con prefijo de schema (multi-tenant)
     *
     * @param string $schema Nombre del schema (ej: 'suc0001')
     * @return string Ejemplo: "LEFT JOIN suc0001.ordenes o ON o.cliente_id = c.id"
     */
    public function toSQLWithSchema(string $schema): string
    {
        return sprintf(
            '%s JOIN %s.%s %s ON %s',
            strtoupper($this->type),
            $schema,
            $this->rightTable,
            $this->rightAlias,
            $this->on
        );
    }

    /**
     * Verifica si esta especificación requiere multi-schema
     */
    public function requiresMultiSchema(): bool
    {
        return $this->multiSchema;
    }
}

Ejemplos de los 3 Modos

Modo 1: Manual ON

php

// Uso: JOINs complejos, self-joins, composite keys
$joinSpec = new JoinSpec(
    leftAlias: 'c',
    rightTable: 'ordenes',
    rightAlias: 'o',
    on: "o.cliente_id = c.id AND o.estado = 'activo'", // ← Condición compleja
    type: 'LEFT'
);

echo $joinSpec->toSQL();
// Output: LEFT JOIN ordenes o ON o.cliente_id = c.id AND o.estado = 'activo'

Modo 2: Auto ON

php

// Uso: FK convencional, mismo schema
$joinSpec = JoinSpec::auto(
    'c',                    // leftAlias
    ClienteModel::class,    // leftModel
    OrdenModel::class,      // rightModel
    'LEFT'                  // type
);

echo $joinSpec->toSQL();
// Output: LEFT JOIN ordenes o ON o.cliente_id = c.id
//         (auto-generado basado en convención: ordenes.cliente_id)

Modo 3: Auto con Multi-Schema

php

// Uso: Cross-level JOINs (CAJA → SUCURSAL)
$joinSpec = JoinSpec::autoWithSchema(
    'cm',                        // leftAlias
    CajaMovimientoModel::class,  // leftModel (nivel CAJA)
    MovimientoBancarioModel::class, // rightModel (nivel SUCURSAL)
    'LEFT'
);

echo $joinSpec->toSQL();
// Output: LEFT JOIN movimientos_bancarios mb ON mb.movimiento_caja_id = cm.id

echo $joinSpec->toSQLWithSchema('suc0001');
// Output: LEFT JOIN suc0001.movimientos_bancarios mb ON mb.movimiento_caja_id = cm.id

3. BaseQuery Builder

Ubicación: models/Queries/BaseQuery.php

Responsabilidad: Clase abstracta base para construir queries complejas aplicando JOINs declarativos.

php

<?php

namespace App\Models\Queries;

use App\Models\Contracts\JoinSpec;
use PDO;

/**
 * Clase base para queries con JOINs declarativos
 *
 * Provee:
 * - Aplicación de JOINs desde JoinSpecs
 * - Soporte single-schema y multi-schema (UNION ALL)
 * - Filtros comunes (soft deletes, WHERE)
 * - Paginación
 */
abstract class BaseQuery
{
    protected PDO $conn;
    protected string $schema;

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

    /**
     * Aplica una lista de JoinSpecs a un SQL base (single-schema)
     *
     * @param string $sql SQL base (ej: "SELECT * FROM clientes c")
     * @param JoinSpec[] $joins Array de especificaciones de JOIN
     * @param bool $useSchema Si true, aplica prefijo de schema a las tablas
     * @return string SQL completo con JOINs aplicados
     */
    protected function applyJoins(string $sql, array $joins, bool $useSchema = false): string
    {
        foreach ($joins as $join) {
            if (!$join instanceof JoinSpec) {
                throw new \InvalidArgumentException('Todos los elementos deben ser instancias de JoinSpec');
            }

            $sql .= ' ' . ($useSchema
                ? $join->toSQLWithSchema($this->schema)
                : $join->toSQL()
            );
        }

        return $sql;
    }

    /**
     * Ejecuta query con UNION ALL para múltiples schemas (multi-tenant)
     *
     * Optimización: Si solo hay 1 schema, usa query simple sin UNION
     *
     * @param string $baseSql SQL base sin JOINs
     * @param JoinSpec[] $joins Array de JoinSpecs
     * @param array $schemaList Lista de schemas ['suc0001', 'suc0002']
     * @param array $filters Filtros WHERE
     * @param string $orderBy Ordenamiento
     * @param int|null $limit Límite de resultados
     * @param int|null $offset Offset para paginación
     * @return array Resultados consolidados
     */
    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) {
            $schema = $schemaList[0];
            $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();
        }

        // Multi-schema: Construir UNION ALL
        $queries = [];
        foreach ($schemaList as $schema) {
            $sql = str_replace('{schema}', $schema, $baseSql);
            $sql = $this->applyJoins($sql, $joins, true);
            $sql = $this->applyFilters($sql, $filters);
            // Añadir campo _schema para tracking
            $sql = str_replace('SELECT', "SELECT '$schema' AS _schema,", $sql);
            $queries[] = "($sql)";
        }

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

        // Aplicar ordenamiento y paginación DESPUÉS del UNION
        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();
    }

    /**
     * Ejecuta COUNT query para paginación
     *
     * Optimización: Solo cuenta, no trae datos
     *
     * @param string $baseSql SQL base del data query
     * @param JoinSpec[] $joins Array de JoinSpecs
     * @param array $filters Filtros WHERE
     * @param array|null $schemaList Si multi-schema, lista de schemas
     * @return int Total de registros
     */
    protected function executeCount(
        string $baseSql,
        array $joins,
        array $filters = [],
        ?array $schemaList = null
    ): int {
        if ($schemaList && count($schemaList) > 1) {
            // Multi-schema: UNION ALL de counts
            $queries = [];
            foreach ($schemaList as $schema) {
                $sql = str_replace('{schema}', $schema, $baseSql);
                $sql = str_replace('SELECT *', 'SELECT COUNT(*)', $sql);
                $sql = $this->applyJoins($sql, $joins, true);
                $sql = $this->applyFilters($sql, $filters);
                $queries[] = "($sql)";
            }
            $unionSql = "SELECT SUM(cnt) FROM (" . implode(' UNION ALL ', $queries) . ") AS counts";
            $stmt = $this->conn->prepare($unionSql);
        } else {
            // Single-schema
            $schema = $schemaList ? $schemaList[0] : 'public';
            $sql = str_replace('{schema}', $schema, $baseSql);
            $sql = str_replace('SELECT *', 'SELECT COUNT(*)', $sql);
            $sql = $this->applyJoins($sql, $joins, true);
            $sql = $this->applyFilters($sql, $filters);
            $stmt = $this->conn->prepare($sql);
        }

        $stmt->execute($filters);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Aplica WHERE clauses comunes (soft deletes, filters)
     *
     * @param string $sql SQL base
     * @param array $filters Filtros clave-valor
     * @return string SQL con WHERE aplicado
     */
    protected function applyFilters(string $sql, array $filters = []): string
    {
        $conditions = [];

        // Soft delete por defecto
        $conditions[] = "deleted_at IS NULL";

        // Filtros adicionales
        foreach ($filters as $key => $value) {
            // Detectar operador en key (ej: 'edad >=' → 'edad >= :edad')
            if (preg_match('/^(\w+)\s*([><=!]+)$/', $key, $matches)) {
                $column = $matches[1];
                $operator = $matches[2];
                $conditions[] = "{$column} {$operator} :{$column}";
            } else {
                $conditions[] = "{$key} = :{$key}";
            }
        }

        if (!empty($conditions)) {
            $sql .= ' WHERE ' . implode(' AND ', $conditions);
        }

        return $sql;
    }

    /**
     * Método abstracto a implementar en queries concretas
     *
     * @return array Resultados de la query
     */
    abstract public function execute(): array;
}

Modos de Construcción Detallados

Modo 1: Manual ON

Cuándo usar:

ON clause complejo (múltiples condiciones)
Filtros en el JOIN (ej: AND estado = 'activo')
Self-joins
Composite keys
FK no convencional

Ejemplo 1: JOIN con Filtro

php

// Uso directo en Query
class ClienteOrdenesActivasQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT c.*, o.id as orden_id FROM clientes c";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                leftAlias: 'c',
                rightTable: 'ordenes',
                rightAlias: 'o',
                on: "o.cliente_id = c.id AND o.estado = 'activo' AND o.deleted_at IS NULL",
                type: 'LEFT'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

Ejemplo 2: Self-Join

php

// Uso directo en Query
class EmpleadoJerarquiaQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT e.nombre AS empleado, jefe.nombre AS jefe_nombre
                FROM empleados e";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                leftAlias: 'e',
                rightTable: 'empleados',
                rightAlias: 'jefe',
                on: 'jefe.id = e.jefe_id',
                type: 'LEFT'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

Ejemplo 3: Composite Key

php

// Uso directo en Query
class OrdenItemsInventarioQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT oi.*, inv.stock FROM orden_items oi";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                leftAlias: 'oi',
                rightTable: 'inventario',
                rightAlias: 'inv',
                on: 'inv.producto_id = oi.producto_id AND inv.variante_id = oi.variante_id',
                type: 'INNER'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

Modo 2: Auto ON

Cuándo usar:

FK sigue convención: {tabla}_{pk} (ej: cliente_id)
Mismo schema (no cross-level)
Relación 1:1 o 1:N simple

Convención de FK:

Tabla izquierda: clientes (PK: id)
Tabla derecha:   ordenes (FK: cliente_id)

ON generado: o.cliente_id = c.id

Ejemplo 1: Cliente → Órdenes

php

// Uso directo en Query
class ClienteOrdenesQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT c.*, o.id as orden_id FROM clientes c";

        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT')
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

// Genera automáticamente:
// LEFT JOIN ordenes o ON o.cliente_id = c.id

Ejemplo 2: Producto → Categoría

php

// Uso directo en Query
class ProductoCategoriasQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT p.*, cat.nombre as categoria FROM productos p";

        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('p', ProductoModel::class, CategoriaModel::class, 'INNER')
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

// Genera:
// INNER JOIN categorias cat ON cat.producto_id = p.id
// (asume: CategoriaModel::alias() = 'cat')

Ventajas:

✅ Menos código
✅ Consistencia automática
✅ Refactoring-friendly (cambiar PK afecta automáticamente)

Limitaciones:

❌ Solo funciona con FKs convencionales
❌ No soporta condiciones adicionales en ON

Modo 3: Auto con Cross-Schema Resolution

Cuándo usar:

Cross-level directo: JOIN entre niveles jerárquicos (CAJA → SUCURSAL, SUCURSAL → EMPRESA) en 1 query
Multi-schema consolidado: Consolidar N schemas con UNION ALL, cada uno con JOIN cross-level
FK convencional
Jerarquía de schemas

Dos escenarios de uso:

Sin executeMultiSchema(): JOIN cross-level directo (1 query)
Con executeMultiSchema(): Consolidación con UNION ALL (N queries unificadas)

Resolución de schemas:

Nivel CAJA:     suc0001caja001.movimientos_caja
Nivel SUCURSAL: suc0001.movimientos_bancarios

ON generado: mb.movimiento_caja_id = cm.id
Schema resolved: suc0001.movimientos_bancarios

Ejemplo 1: Movimiento Caja → Movimiento Bancario

php

// Models sin schemaLevel - MultiSchemaService lo auto-detecta
class CajaMovimientoModel implements ModelMetadata
{
    public static function table(): string { return 'movimientos_caja'; }
    public static function alias(): string { return 'cm'; }
    public static function primaryKey(): string { return 'id'; }
    // Schema level auto-detectado: CAJA (nivel 3)
}

class MovimientoBancarioModel implements ModelMetadata
{
    public static function table(): string { return 'movimientos_bancarios'; }
    public static function alias(): string { return 'mb'; }
    public static function primaryKey(): string { return 'id'; }
    // Schema level auto-detectado: SUCURSAL (nivel 2)
}

// Query con JoinSpec directo
class CajaMovimientosConciliadosQuery extends BaseQuery
{
    public function execute(): array
    {
        $schemaList = ['suc0001caja001', 'suc0001caja002']; // Obtenido de MultiSchemaService

        $baseSql = "SELECT cm.*, mb.numero_cheque
                    FROM {schema}.movimientos_caja cm";

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

SQL Generado:

sql

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

Integración con MultiSchemaService:

php

// En el Service
$schemas = $this->multiSchemaService->getRelatedTableSchemas(
    'movimientos_caja',
    'movimientos_bancarios',
    [1, 2], // IDs de cajas
    'suc0001'
);
// Retorna: [
//   ['primary' => 'suc0001caja001', 'related' => 'suc0001'],
//   ['primary' => 'suc0001caja002', 'related' => 'suc0001']
// ]

// Extraer schemas para query
$schemaList = array_column($schemas, 'primary');

// Ejecutar query
$query = new CajaMovimientosConciliadosQuery($this->conn);
$results = $query->executeMultiSchema($baseSql, $joins, $schemaList);

Edge Cases y Soluciones

1. FK No Convencional

Problema: FK no sigue patrón {tabla}_id.

Ejemplo: Tabla ordenes tiene FK id_cliente (no cliente_id).

Solución: Usar Modo 1 (Manual ON) directo en Query.

php

class OrdenConClienteQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT o.*, c.nombre FROM ordenes o";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                'o',
                'clientes',
                'c',
                'c.id = o.id_cliente', // ← FK no convencional
                'INNER'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

2. Composite Primary Key

Problema: Tabla con PK compuesta.

Ejemplo: Tabla orden_items con PK (orden_id, producto_id).

Solución: Modo 1 con ON completo directo en Query.

php

class OrdenItemsInventarioQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT oi.*, inv.stock FROM orden_items oi";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                'oi',
                'inventario',
                'inv',
                'inv.producto_id = oi.producto_id AND inv.variante_id = oi.variante_id',
                'LEFT'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

3. Múltiples JOINs a Misma Tabla

Problema: JOIN a la misma tabla con diferentes roles.

Ejemplo: Orden con dirección de envío y facturación.

Solución: Alias diferentes, JoinSpecs directos en Query.

php

class OrdenDetalladaQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT o.*, dir_envio.calle AS envio_calle, dir_fact.calle AS fact_calle
                FROM ordenes o";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                'o',
                'direcciones',
                'dir_envio', // ← Alias único
                'dir_envio.id = o.direccion_envio_id',
                'LEFT'
            ),
            new JoinSpec(
                'o',
                'direcciones',
                'dir_fact', // ← Alias diferente
                'dir_fact.id = o.direccion_facturacion_id',
                'LEFT'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

4. JOIN con Condiciones Adicionales

Problema: JOIN necesita filtro adicional en ON.

Ejemplo: Solo órdenes del último mes.

Solución: Modo 1 con condición adicional directo en Query.

php

class ClienteOrdenesRecientesQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT c.*, o.id as orden_id FROM clientes c";

        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                'c',
                'ordenes',
                'o',
                "o.cliente_id = c.id AND o.fecha >= CURRENT_DATE - INTERVAL '30 days'",
                'LEFT'
            )
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

5. Tres o Más Tablas en Secuencia

Problema: JOIN de múltiples tablas en cadena.

Ejemplo: Orden → Orden Items → Productos.

Solución: Múltiples JoinSpecs directos en Query.

php

class OrdenConProductosQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT o.*, p.nombre as producto_nombre, oi.cantidad
                FROM ordenes o";

        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('o', OrdenModel::class, OrdenItemModel::class, 'INNER'),      // orden → orden_items
            JoinSpec::auto('oi', OrdenItemModel::class, ProductoModel::class, 'INNER')   // orden_items → productos
        ]);

        return $this->conn->query($sql)->fetchAll();
    }
}

Guía de Adopción

Checklist de Implementación (3 Fases)

[ ] Fase 1: Foundation
- [ ] Crear ModelMetadata interface
- [ ] Crear JoinSpec class
- [ ] Crear BaseQuery class
[ ] Fase 2: Model Migration
- [ ] Implementar ModelMetadata en Models existentes
- [ ] Añadir table(), alias(), primaryKey()
- [ ] Eliminar métodos con JOINs hardcodeados
[ ] Fase 3: Query Migration
- [ ] Crear Query Classes para queries complejas
- [ ] Crear JoinSpecs directamente en cada Query
- [ ] Testing de queries migradas
[ ] Fase 4: Multi-Schema (Opcional)
- [ ] Integrar MultiSchemaService (descubre niveles automáticamente)
- [ ] Usar executeMultiSchema() para cross-level JOINs
- [ ] Verificar resolución automática de schemas con tests

Migración Paso a Paso

Paso 1: Implementar ModelMetadata

Antes:

php

class ClienteModel
{
    private PDO $conn;

    public function getAll(): array { ... }

    // ❌ JOIN hardcodeado
    public function getAllWithOrdenes(): array
    {
        $sql = "SELECT c.*, o.id as orden_id
                FROM clientes c
                LEFT JOIN ordenes o ON o.cliente_id = c.id";
        return $this->conn->query($sql)->fetchAll();
    }
}

Después:

php

class ClienteModel implements ModelMetadata
{
    private PDO $conn;

    // ✅ Metadata para JOINs (3 métodos)
    public static function table(): string { return 'clientes'; }
    public static function alias(): string { return 'c'; }
    public static function primaryKey(): string { return 'id'; }

    // ✅ Solo métodos de SU tabla
    public function getAll(): array { ... }

    // ❌ Eliminar métodos con JOINs
}

Paso 2: Crear Query Class con JoinSpec Directo

php

class ClienteOrdenesQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = "SELECT c.*, o.id as orden_id, o.total
                FROM " . ClienteModel::table() . " " . ClienteModel::alias();

        // JoinSpec creado directamente en la query
        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT')
        ]);
        $sql = $this->applyFilters($sql);

        return $this->conn->query($sql)->fetchAll();
    }
}

Paso 3: Actualizar Service

Antes:

php

class ClienteService
{
    public function getClientesConOrdenes(): array
    {
        return $this->model->getAllWithOrdenes(); // ❌ Método eliminado
    }
}

Después:

php

class ClienteService
{
    public function getClientesConOrdenes(): array
    {
        $query = new ClienteOrdenesQuery($this->conn);
        return $query->execute();
    }
}

Estrategia de Testing

Testing de Models (Sin JOINs)

php

<?php

use PHPUnit\Framework\TestCase;

class ClienteModelTest extends TestCase
{
    private PDO $conn;
    private ClienteModel $model;

    protected function setUp(): void
    {
        $this->conn = $this->createMock(PDO::class);
        $this->model = new ClienteModel($this->conn);
    }

    public function test_table_returns_correct_name()
    {
        $this->assertEquals('clientes', ClienteModel::table());
    }

    public function test_alias_returns_correct_alias()
    {
        $this->assertEquals('c', ClienteModel::alias());
    }

    public function test_primaryKey_returns_id()
    {
        $this->assertEquals('id', ClienteModel::primaryKey());
    }


    public function test_getAll_returns_all_clientes()
    {
        // Mock PDO
        $stmt = $this->createMock(PDOStatement::class);
        $stmt->method('fetchAll')->willReturn([
            ['id' => 1, 'nombre' => 'Cliente 1'],
            ['id' => 2, 'nombre' => 'Cliente 2']
        ]);

        $this->conn->method('query')->willReturn($stmt);

        $results = $this->model->getAll();

        $this->assertCount(2, $results);
    }

    // ❌ NO testear JOINs aquí
    // Los JOINs se testean en Query Classes
}

Testing de JoinSpec (Specs)

php

<?php

use PHPUnit\Framework\TestCase;

class JoinSpecTest extends TestCase
{
    public function test_auto_mode_creates_valid_join_spec()
    {
        $spec = JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT');

        $this->assertInstanceOf(JoinSpec::class, $spec);
        $this->assertEquals('c', $spec->leftAlias);
        $this->assertEquals('ordenes', $spec->rightTable);
        $this->assertEquals('o', $spec->rightAlias);
        $this->assertEquals('LEFT', strtoupper($spec->type));
    }

    public function test_auto_mode_generates_correct_sql()
    {
        $spec = JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT');
        $sql = $spec->toSQL();

        $expected = 'LEFT JOIN ordenes o ON o.cliente_id = c.id';
        $this->assertEquals($expected, $sql);
    }

    public function test_auto_mode_with_schema_generates_correct_sql()
    {
        $spec = JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT');
        $sql = $spec->toSQLWithSchema('suc0001');

        $expected = 'LEFT JOIN suc0001.ordenes o ON o.cliente_id = c.id';
        $this->assertEquals($expected, $sql);
    }

    public function test_auto_mode_generates_correct_on_clause()
    {
        $spec = JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT');

        $this->assertStringContainsString('o.cliente_id = c.id', $spec->on);
    }

    public function test_autoWithSchema_mode_marks_multi_schema()
    {
        $spec = JoinSpec::autoWithSchema(
            'cm',
            CajaMovimientoModel::class,
            MovimientoBancarioModel::class,
            'LEFT'
        );

        $this->assertTrue($spec->requiresMultiSchema());
    }
}

Testing de Queries (Integración)

php

<?php

use PHPUnit\Framework\TestCase;

class ClienteOrdenesQueryTest extends TestCase
{
    private PDO $conn;

    protected function setUp(): void
    {
        // Setup DB real con Docker
        $this->conn = new PDO(
            'pgsql:host=localhost;dbname=testdb',
            'testuser',
            'testpass'
        );

        // Seed data
        $this->seedDatabase();
    }

    private function seedDatabase(): void
    {
        $this->conn->exec("TRUNCATE clientes, ordenes CASCADE");

        $this->conn->exec("
            INSERT INTO clientes (id, nombre, email) VALUES
            (1, 'Cliente 1', 'cliente1@test.com'),
            (2, 'Cliente 2', 'cliente2@test.com'),
            (3, 'Cliente 3', 'cliente3@test.com')
        ");

        $this->conn->exec("
            INSERT INTO ordenes (id, cliente_id, total, fecha) VALUES
            (1, 1, 100.00, '2026-01-01'),
            (2, 1, 200.00, '2026-01-02'),
            (3, 2, 150.00, '2026-01-03')
        ");
    }

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

        $this->assertNotEmpty($results);
        $this->assertIsArray($results);

        // Verificar estructura
        $first = $results[0];
        $this->assertArrayHasKey('id', $first);
        $this->assertArrayHasKey('nombre', $first);
        $this->assertArrayHasKey('orden_id', $first);
        $this->assertArrayHasKey('total', $first);
    }

    public function test_execute_excludes_soft_deleted()
    {
        // Soft delete un cliente
        $this->conn->exec("UPDATE clientes SET deleted_at = NOW() WHERE id = 1");

        $query = new ClienteOrdenesQuery($this->conn);
        $results = $query->execute();

        // Verificar que no aparece
        $ids = array_column($results, 'id');
        $this->assertNotContains(1, $ids);
    }

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

        // Cliente 3 no tiene órdenes pero debe aparecer (LEFT JOIN)
        $clienteIds = array_unique(array_column($results, 'id'));
        $this->assertContains(3, $clienteIds);
    }

    protected function tearDown(): void
    {
        $this->conn->exec("TRUNCATE clientes, ordenes CASCADE");
    }
}

Performance y Optimización

Índices Requeridos

Para que los JOINs sean eficientes, crear índices en columnas de FK:

sql

-- Tabla: ordenes
CREATE INDEX idx_ordenes_cliente_id ON ordenes(cliente_id);
CREATE INDEX idx_ordenes_fecha ON ordenes(fecha);

-- Tabla: orden_items
CREATE INDEX idx_orden_items_orden_id ON orden_items(orden_id);
CREATE INDEX idx_orden_items_producto_id ON orden_items(producto_id);

-- Índices compuestos para filtros frecuentes
CREATE INDEX idx_ordenes_cliente_estado ON ordenes(cliente_id, estado);
CREATE INDEX idx_ordenes_cliente_fecha ON ordenes(cliente_id, fecha DESC);

-- Multi-column para cobertura
CREATE INDEX idx_ordenes_covering ON ordenes(cliente_id, estado) INCLUDE (total, fecha);

Análisis de Queries

Usar EXPLAIN ANALYZE para verificar performance:

php

class ClienteOrdenesQuery extends BaseQuery
{
    // Método para debugging (solo desarrollo)
    public function explainQuery(): string
    {
        $sql = $this->buildSQL(); // Método helper que retorna SQL final

        $stmt = $this->conn->query("EXPLAIN ANALYZE " . $sql);
        return $stmt->fetchAll(PDO::FETCH_COLUMN)[0];
    }
}

// Uso
$query = new ClienteOrdenesQuery($conn);
echo $query->explainQuery();

Optimización UNION ALL

PostgreSQL optimiza UNION ALL automáticamente:

Parallel execution: Queries en paralelo si es posible
Index usage: Usa índices en cada sub-query
Sort optimization: Ordenamiento global eficiente

Recomendaciones:

Índices en cada schema: Asegurar índices consistentes
LIMIT después del UNION: No dentro de cada sub-query
Filtros en sub-queries: Reducir datos antes del UNION
Evitar DISTINCT: UNION ALL (no UNION) para mejor performance

Evitar N+1 Queries

Anti-pattern:

php

// ❌ N+1 problem
$clientes = $clienteModel->getAll();
foreach ($clientes as $cliente) {
    $ordenes = $ordenModel->getByClienteId($cliente['id']); // N queries
}

Solución:

php

// ✅ Single query con JOIN
$query = new ClienteOrdenesQuery($conn);
$results = $query->execute(); // 1 query

// Post-procesamiento en PHP si necesitas agrupar
$clientesConOrdenes = [];
foreach ($results as $row) {
    $clienteId = $row['id'];
    if (!isset($clientesConOrdenes[$clienteId])) {
        $clientesConOrdenes[$clienteId] = [
            'id' => $row['id'],
            'nombre' => $row['nombre'],
            'ordenes' => []
        ];
    }
    if ($row['orden_id']) {
        $clientesConOrdenes[$clienteId]['ordenes'][] = [
            'id' => $row['orden_id'],
            'total' => $row['total']
        ];
    }
}

Recursos Relacionados

Documentación Complementaria

Casos de Uso Simple: Ejemplos sin multi-schema
Casos de Uso Multi-Schema: Ejemplos multi-tenant
Criterios de Abstracción: Cuándo abstraer
Inmutabilidad en Servicios: Servicios inmutables

Patrones Arquitectónicos

5-Layer DDD: Ver bautista-backend/CLAUDE.md
Multi-Tenancy: Ver docs/architecture/multi-schema.md
Service Layer: Ver docs/backend/service-layer.md

Skills de Claude Code

bautista-backend-architecture: Arquitectura 5-layer DDD
php-slim-api: Patrones de Slim Framework
technical-backend-documentation: Documentar arquitectura backend

Última actualización: 2026-02-04 Versión: 2.0.0 Autor: Sistema Bautista - Arquitectura Backend Revisor: php-architecture-expert agent

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

Guía Completa: Patrón Unificado de JOINs Declarativos ​

Tabla de Contenidos ​

Introducción ​

Conceptos Fundamentales ​

Principio de Diseño ​

Problema que Resuelve ​

Anti-Pattern: JOINs Hardcodeados ​

Solución: Patrón Unificado ​

Componentes Principales ​

1. ModelMetadata Interface ​

Auto-Resolución de Schema Level ​

Implementación en Model ​

2. JoinSpec Class ​

Ejemplos de los 3 Modos ​

3. BaseQuery Builder ​

Modos de Construcción Detallados ​

Modo 1: Manual ON ​

Modo 2: Auto ON ​

Modo 3: Auto con Cross-Schema Resolution ​

Edge Cases y Soluciones ​

1. FK No Convencional ​

2. Composite Primary Key ​

3. Múltiples JOINs a Misma Tabla ​

4. JOIN con Condiciones Adicionales ​

5. Tres o Más Tablas en Secuencia ​

Guía de Adopción ​

Checklist de Implementación (3 Fases) ​

Migración Paso a Paso ​

Paso 1: Implementar ModelMetadata ​

Paso 2: Crear Query Class con JoinSpec Directo ​

Paso 3: Actualizar Service ​

Estrategia de Testing ​

Testing de Models (Sin JOINs) ​

Testing de JoinSpec (Specs) ​

Testing de Queries (Integración) ​

Performance y Optimización ​

Índices Requeridos ​

Análisis de Queries ​

Optimización UNION ALL ​

Evitar N+1 Queries ​

Recursos Relacionados ​

Documentación Complementaria ​

Patrones Arquitectónicos ​

Skills de Claude Code ​

Guía Completa: Patrón Unificado de JOINs Declarativos

Tabla de Contenidos

Introducción

Conceptos Fundamentales

Principio de Diseño

Problema que Resuelve

Anti-Pattern: JOINs Hardcodeados

Solución: Patrón Unificado

Componentes Principales

1. ModelMetadata Interface

Auto-Resolución de Schema Level

Implementación en Model

2. JoinSpec Class

Ejemplos de los 3 Modos

3. BaseQuery Builder

Modos de Construcción Detallados

Modo 1: Manual ON

Modo 2: Auto ON

Modo 3: Auto con Cross-Schema Resolution

Edge Cases y Soluciones

1. FK No Convencional

2. Composite Primary Key

3. Múltiples JOINs a Misma Tabla

4. JOIN con Condiciones Adicionales

5. Tres o Más Tablas en Secuencia

Guía de Adopción

Checklist de Implementación (3 Fases)

Migración Paso a Paso

Paso 1: Implementar ModelMetadata

Paso 2: Crear Query Class con JoinSpec Directo

Paso 3: Actualizar Service

Estrategia de Testing

Testing de Models (Sin JOINs)

Testing de JoinSpec (Specs)

Testing de Queries (Integración)

Performance y Optimización

Índices Requeridos

Análisis de Queries

Optimización UNION ALL

Evitar N+1 Queries

Recursos Relacionados

Documentación Complementaria

Patrones Arquitectónicos

Skills de Claude Code