Casos de Uso: JOINs en Mismo Schema

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

Tabla de Contenidos

Introducción

Este documento presenta casos de uso prácticos del patrón unificado de JOINs en mismo schema (multi-tenant normal, donde cada request trabaja en 1 schema).

Contexto Arquitectónico:

Sistema Bautista usa arquitectura multi-tenant (cada request trabaja en 1 schema para aislamiento). Estos casos de uso son queries que:

Operan dentro del schema actual del request
NO necesitan consolidación cross-schema
Usan el search_path establecido por ConnectionMiddleware

Diferencia con multi-schema:

Este documento: JOINs en 1 schema (ej: Cliente → Órdenes en suc0001)
Multi-schema: JOINs consolidando N schemas (ej: Todas las cajas de sucursal) → Ver casos-uso-multi-schema.md

Prerrequisitos:

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

Caso 1: JOIN Simple 1:1

Descripción del Problema

Listar órdenes con información del cliente asociado. Cada orden tiene exactamente un cliente (relación 1:1).

Estructura de Tablas

sql

-- Tabla: clientes
CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    telefono VARCHAR(20),
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: ordenes
CREATE TABLE ordenes (
    id SERIAL PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id),
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    estado VARCHAR(20) NOT NULL DEFAULT 'pendiente',
    fecha DATE NOT NULL,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Índices
CREATE INDEX idx_ordenes_cliente_id ON ordenes(cliente_id);
CREATE INDEX idx_ordenes_fecha ON ordenes(fecha);

Implementación

Models

php

<?php

namespace App\Models\Ventas;

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

final class ClienteModel implements ModelMetadata
{
    private PDO $conn;

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

    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
    {
        $sql = "SELECT * FROM " . self::table() . " WHERE deleted_at IS NULL";
        return $this->conn->query($sql)->fetchAll();
    }
}

final class OrdenModel implements ModelMetadata
{
    private PDO $conn;

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

    public static function table(): string { return 'ordenes'; }
    public static function alias(): string { return 'o'; }
    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();
    }
}

Query Class (con JoinSpec directo)

php

<?php

namespace App\Models\Queries\Ventas;

use App\Models\Queries\BaseQuery;
use App\Models\Contracts\JoinSpec;
use App\Models\Ventas\OrdenModel;

class OrdenConClienteQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                o.id,
                o.numero,
                o.total,
                o.estado,
                o.fecha,
                c.nombre AS cliente_nombre,
                c.email AS cliente_email,
                c.telefono AS cliente_telefono
            FROM %s %s",
            OrdenModel::table(),
            OrdenModel::alias()
        );

        // JoinSpec creado directamente
        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('o', OrdenModel::class, ClienteModel::class, 'INNER')
        ]);
        $sql = $this->applyFilters($sql);
        $sql .= " ORDER BY o.fecha DESC, o.id DESC";

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

SQL Generado

sql

SELECT
    o.id,
    o.numero,
    o.total,
    o.estado,
    o.fecha,
    c.nombre AS cliente_nombre,
    c.email AS cliente_email,
    c.telefono AS cliente_telefono
FROM ordenes o
INNER JOIN clientes c ON o.cliente_id = c.id
WHERE deleted_at IS NULL
ORDER BY o.fecha DESC, o.id DESC

Test Unitario

php

<?php

use PHPUnit\Framework\TestCase;

class OrdenConClienteQueryTest extends TestCase
{
    private PDO $conn;

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

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

        $this->conn->exec("
            INSERT INTO clientes (id, nombre, email) VALUES
            (1, 'Juan Pérez', 'juan@test.com'),
            (2, 'María García', 'maria@test.com')
        ");

        $this->conn->exec("
            INSERT INTO ordenes (id, numero, cliente_id, total, fecha) VALUES
            (1, 'ORD-001', 1, 100.00, '2026-01-15'),
            (2, 'ORD-002', 2, 200.00, '2026-01-16'),
            (3, 'ORD-003', 1, 150.00, '2026-01-17')
        ");
    }

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

        $this->assertCount(3, $results);

        $first = $results[0];
        $this->assertArrayHasKey('numero', $first);
        $this->assertArrayHasKey('cliente_nombre', $first);
        $this->assertArrayHasKey('cliente_email', $first);
    }

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

        $this->assertEquals('ORD-003', $results[0]['numero']); // Más reciente
        $this->assertEquals('ORD-002', $results[1]['numero']);
        $this->assertEquals('ORD-001', $results[2]['numero']);
    }

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

Resultado Esperado

json

[
  {
    "id": 3,
    "numero": "ORD-003",
    "total": "150.00",
    "estado": "pendiente",
    "fecha": "2026-01-17",
    "cliente_nombre": "Juan Pérez",
    "cliente_email": "juan@test.com",
    "cliente_telefono": null
  },
  {
    "id": 2,
    "numero": "ORD-002",
    "total": "200.00",
    "estado": "pendiente",
    "fecha": "2026-01-16",
    "cliente_nombre": "María García",
    "cliente_email": "maria@test.com",
    "cliente_telefono": null
  },
  {
    "id": 1,
    "numero": "ORD-001",
    "total": "100.00",
    "estado": "pendiente",
    "fecha": "2026-01-15",
    "cliente_nombre": "Juan Pérez",
    "cliente_email": "juan@test.com",
    "cliente_telefono": null
  }
]

Caso 2: JOIN Simple 1:N (LEFT)

Descripción del Problema

Listar clientes con sus órdenes (si las tienen). Un cliente puede tener 0 o N órdenes (relación 1:N). Usar LEFT JOIN para incluir clientes sin órdenes.

Implementación

Query Class (con JoinSpec directo)

php

<?php

namespace App\Models\Queries\Ventas;

use App\Models\Queries\BaseQuery;
use App\Models\Contracts\JoinSpec;
use App\Models\Ventas\ClienteModel;

class ClienteConOrdenesQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                c.id AS cliente_id,
                c.nombre AS cliente_nombre,
                c.email,
                o.id AS orden_id,
                o.numero AS orden_numero,
                o.total AS orden_total,
                o.fecha AS orden_fecha
            FROM %s %s",
            ClienteModel::table(),
            ClienteModel::alias()
        );

        // JoinSpec creado directamente
        $sql = $this->applyJoins($sql, [
            JoinSpec::auto('c', ClienteModel::class, OrdenModel::class, 'LEFT')
        ]);
        $sql = $this->applyFilters($sql);
        $sql .= " ORDER BY c.nombre ASC, o.fecha DESC";

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

SQL Generado

sql

SELECT
    c.id AS cliente_id,
    c.nombre AS cliente_nombre,
    c.email,
    o.id AS orden_id,
    o.numero AS orden_numero,
    o.total AS orden_total,
    o.fecha AS orden_fecha
FROM clientes c
LEFT JOIN ordenes o ON o.cliente_id = c.id
WHERE deleted_at IS NULL
ORDER BY c.nombre ASC, o.fecha DESC

Test Unitario

php

public function test_execute_includes_clientes_without_ordenes()
{
    // Crear cliente sin órdenes
    $this->conn->exec("
        INSERT INTO clientes (id, nombre, email) VALUES
        (3, 'Cliente Sin Órdenes', 'sin@test.com')
    ");

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

    // Verificar que aparece el cliente
    $nombres = array_column($results, 'cliente_nombre');
    $this->assertContains('Cliente Sin Órdenes', $nombres);

    // Verificar que orden_id es NULL
    $cliente = array_filter($results, fn($r) => $r['cliente_id'] === 3);
    $this->assertNull($cliente[0]['orden_id']);
}

Resultado Esperado

json

[
  {
    "cliente_id": 3,
    "cliente_nombre": "Cliente Sin Órdenes",
    "email": "sin@test.com",
    "orden_id": null,
    "orden_numero": null,
    "orden_total": null,
    "orden_fecha": null
  },
  {
    "cliente_id": 1,
    "cliente_nombre": "Juan Pérez",
    "email": "juan@test.com",
    "orden_id": 3,
    "orden_numero": "ORD-003",
    "orden_total": "150.00",
    "orden_fecha": "2026-01-17"
  },
  {
    "cliente_id": 1,
    "cliente_nombre": "Juan Pérez",
    "email": "juan@test.com",
    "orden_id": 1,
    "orden_numero": "ORD-001",
    "orden_total": "100.00",
    "orden_fecha": "2026-01-15"
  }
]

Caso 3: Self-JOIN

Descripción del Problema

Listar empleados con información de su jefe directo (ambos en la misma tabla empleados).

Estructura de Tabla

sql

CREATE TABLE empleados (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    jefe_id INTEGER NULL REFERENCES empleados(id),
    cargo VARCHAR(50),
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_empleados_jefe_id ON empleados(jefe_id);

Implementación

Model

php

<?php

namespace App\Models\RRHH;

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

final class EmpleadoModel implements ModelMetadata
{
    private PDO $conn;

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

    public static function table(): string { return 'empleados'; }
    public static function alias(): string { return 'e'; }
    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();
    }
}

Query Class (con JoinSpec directo)

php

<?php

namespace App\Models\Queries\RRHH;

use App\Models\Queries\BaseQuery;
use App\Models\Contracts\JoinSpec;
use App\Models\RRHH\EmpleadoModel;

class EmpleadoJerarquiaQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                e.id,
                e.nombre AS empleado_nombre,
                e.email AS empleado_email,
                e.cargo,
                jefe.id AS jefe_id,
                jefe.nombre AS jefe_nombre,
                jefe.email AS jefe_email
            FROM %s %s",
            EmpleadoModel::table(),
            EmpleadoModel::alias()
        );

        // JoinSpec creado directamente (Self-JOIN)
        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                leftAlias: 'e',
                rightTable: 'empleados',
                rightAlias: 'jefe', // ← Alias diferente para evitar ambigüedad
                on: 'jefe.id = e.jefe_id',
                type: 'LEFT' // LEFT para incluir empleados sin jefe (CEO)
            )
        ]);
        $sql = $this->applyFilters($sql);
        $sql .= " ORDER BY e.nombre ASC";

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

SQL Generado

sql

SELECT
    e.id,
    e.nombre AS empleado_nombre,
    e.email AS empleado_email,
    e.cargo,
    jefe.id AS jefe_id,
    jefe.nombre AS jefe_nombre,
    jefe.email AS jefe_email
FROM empleados e
LEFT JOIN empleados jefe ON jefe.id = e.jefe_id
WHERE deleted_at IS NULL
ORDER BY e.nombre ASC

Datos de Prueba

sql

INSERT INTO empleados (id, nombre, email, jefe_id, cargo) VALUES
(1, 'CEO', 'ceo@empresa.com', NULL, 'CEO'), -- Sin jefe
(2, 'Gerente Ventas', 'ventas@empresa.com', 1, 'Gerente'),
(3, 'Vendedor 1', 'vend1@empresa.com', 2, 'Vendedor'),
(4, 'Vendedor 2', 'vend2@empresa.com', 2, 'Vendedor');

Resultado Esperado

json

[
  {
    "id": 1,
    "empleado_nombre": "CEO",
    "empleado_email": "ceo@empresa.com",
    "cargo": "CEO",
    "jefe_id": null,
    "jefe_nombre": null,
    "jefe_email": null
  },
  {
    "id": 2,
    "empleado_nombre": "Gerente Ventas",
    "empleado_email": "ventas@empresa.com",
    "cargo": "Gerente",
    "jefe_id": 1,
    "jefe_nombre": "CEO",
    "jefe_email": "ceo@empresa.com"
  },
  {
    "id": 3,
    "empleado_nombre": "Vendedor 1",
    "empleado_email": "vend1@empresa.com",
    "cargo": "Vendedor",
    "jefe_id": 2,
    "jefe_nombre": "Gerente Ventas",
    "jefe_email": "ventas@empresa.com"
  },
  {
    "id": 4,
    "empleado_nombre": "Vendedor 2",
    "empleado_email": "vend2@empresa.com",
    "cargo": "Vendedor",
    "jefe_id": 2,
    "jefe_nombre": "Gerente Ventas",
    "jefe_email": "ventas@empresa.com"
  }
]

Caso 4: Múltiples JOINs Secuenciales

Descripción del Problema

Listar órdenes con cliente Y productos (N:M a través de tabla intermedia orden_items).

Estructura de Tablas

sql

CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE orden_items (
    id SERIAL PRIMARY KEY,
    orden_id INTEGER NOT NULL REFERENCES ordenes(id),
    producto_id INTEGER NOT NULL REFERENCES productos(id),
    cantidad INTEGER NOT NULL DEFAULT 1,
    precio_unitario DECIMAL(10,2) NOT NULL,
    deleted_at TIMESTAMP NULL
);

CREATE INDEX idx_orden_items_orden_id ON orden_items(orden_id);
CREATE INDEX idx_orden_items_producto_id ON orden_items(producto_id);

Implementación

Models

php

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'; }
}

final class OrdenItemModel implements ModelMetadata
{
    public static function table(): string { return 'orden_items'; }
    public static function alias(): string { return 'oi'; }
    public static function primaryKey(): string { return 'id'; }
}

Query Class (con JoinSpecs directos)

php

<?php

namespace App\Models\Queries\Ventas;

use App\Models\Queries\BaseQuery;
use App\Models\Contracts\JoinSpec;
use App\Models\Ventas\OrdenModel;

class OrdenDetalladaQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                o.id AS orden_id,
                o.numero,
                o.total AS orden_total,
                o.fecha,
                c.nombre AS cliente_nombre,
                c.email AS cliente_email,
                p.id AS producto_id,
                p.nombre AS producto_nombre,
                oi.cantidad,
                oi.precio_unitario,
                (oi.cantidad * oi.precio_unitario) AS subtotal
            FROM %s %s",
            OrdenModel::table(),
            OrdenModel::alias()
        );

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

        $sql = $this->applyFilters($sql);
        $sql .= " ORDER BY o.fecha DESC, o.id ASC, oi.id ASC";

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

SQL Generado

sql

SELECT
    o.id AS orden_id,
    o.numero,
    o.total AS orden_total,
    o.fecha,
    c.nombre AS cliente_nombre,
    c.email AS cliente_email,
    p.id AS producto_id,
    p.nombre AS producto_nombre,
    oi.cantidad,
    oi.precio_unitario,
    (oi.cantidad * oi.precio_unitario) AS subtotal
FROM ordenes o
INNER JOIN clientes c ON c.orden_id = o.id
INNER JOIN orden_items oi ON oi.orden_id = o.id
INNER JOIN productos p ON p.orden_item_id = oi.id
WHERE deleted_at IS NULL
ORDER BY o.fecha DESC, o.id ASC, oi.id ASC

Datos de Prueba

sql

INSERT INTO productos (id, nombre, precio) VALUES
(1, 'Producto A', 50.00),
(2, 'Producto B', 75.00);

INSERT INTO orden_items (orden_id, producto_id, cantidad, precio_unitario) VALUES
(1, 1, 2, 50.00), -- Orden 1: 2x Producto A
(1, 2, 1, 75.00), -- Orden 1: 1x Producto B
(2, 1, 3, 50.00); -- Orden 2: 3x Producto A

Resultado Esperado

json

[
  {
    "orden_id": 2,
    "numero": "ORD-002",
    "orden_total": "200.00",
    "fecha": "2026-01-16",
    "cliente_nombre": "María García",
    "cliente_email": "maria@test.com",
    "producto_id": 1,
    "producto_nombre": "Producto A",
    "cantidad": 3,
    "precio_unitario": "50.00",
    "subtotal": "150.00"
  },
  {
    "orden_id": 1,
    "numero": "ORD-001",
    "orden_total": "100.00",
    "fecha": "2026-01-15",
    "cliente_nombre": "Juan Pérez",
    "cliente_email": "juan@test.com",
    "producto_id": 1,
    "producto_nombre": "Producto A",
    "cantidad": 2,
    "precio_unitario": "50.00",
    "subtotal": "100.00"
  },
  {
    "orden_id": 1,
    "numero": "ORD-001",
    "orden_total": "100.00",
    "fecha": "2026-01-15",
    "cliente_nombre": "Juan Pérez",
    "cliente_email": "juan@test.com",
    "producto_id": 2,
    "producto_nombre": "Producto B",
    "cantidad": 1,
    "precio_unitario": "75.00",
    "subtotal": "75.00"
  }
]

Caso 5: JOIN con Filtros Adicionales

Descripción del Problema

Listar clientes con sus órdenes activas del último mes (filtro temporal en el JOIN).

Implementación

Query Class (con JoinSpec directo)

php

class ClienteActividadRecienteQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                c.id,
                c.nombre,
                COUNT(o.id) AS ordenes_activas,
                SUM(o.total) AS total_activo
            FROM %s %s",
            ClienteModel::table(),
            ClienteModel::alias()
        );

        // JoinSpec directo con filtro temporal
        $sql = $this->applyJoins($sql, [
            new JoinSpec(
                leftAlias: 'c',
                rightTable: 'ordenes',
                rightAlias: 'o',
                on: "o.cliente_id = c.id
                     AND o.estado = 'activo'
                     AND o.fecha >= CURRENT_DATE - INTERVAL '30 days'",
                type: 'LEFT'
            )
        ]);
        $sql = $this->applyFilters($sql);
        $sql .= " GROUP BY c.id, c.nombre";
        $sql .= " ORDER BY ordenes_activas DESC, total_activo DESC";

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

SQL Generado

sql

SELECT
    c.id,
    c.nombre,
    COUNT(o.id) AS ordenes_activas,
    SUM(o.total) AS total_activo
FROM clientes c
LEFT JOIN ordenes o
    ON o.cliente_id = c.id
    AND o.estado = 'activo'
    AND o.fecha >= CURRENT_DATE - INTERVAL '30 days'
WHERE deleted_at IS NULL
GROUP BY c.id, c.nombre
ORDER BY ordenes_activas DESC, total_activo DESC

Caso 6: Agregaciones con GROUP BY

Descripción del Problema

Calcular total de ventas por cliente (agregación con GROUP BY).

Implementación

Query Class

php

<?php

namespace App\Models\Queries\Ventas;

use App\Models\Queries\BaseQuery;
use App\Models\Contracts\JoinSpec;
use App\Models\Ventas\ClienteModel;

class ClienteTotalVentasQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                c.id,
                c.nombre,
                c.email,
                COUNT(o.id) AS total_ordenes,
                SUM(o.total) AS suma_ventas,
                AVG(o.total) AS promedio_venta,
                MAX(o.fecha) AS ultima_compra
            FROM %s %s",
            ClienteModel::table(),
            ClienteModel::alias()
        );

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

        $sql .= " GROUP BY c.id, c.nombre, c.email";
        $sql .= " HAVING COUNT(o.id) > 0"; // Solo clientes con órdenes
        $sql .= " ORDER BY suma_ventas DESC";

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

SQL Generado

sql

SELECT
    c.id,
    c.nombre,
    c.email,
    COUNT(o.id) AS total_ordenes,
    SUM(o.total) AS suma_ventas,
    AVG(o.total) AS promedio_venta,
    MAX(o.fecha) AS ultima_compra
FROM clientes c
LEFT JOIN ordenes o ON o.cliente_id = c.id
WHERE deleted_at IS NULL
GROUP BY c.id, c.nombre, c.email
HAVING COUNT(o.id) > 0
ORDER BY suma_ventas DESC

Test Unitario

php

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

    $this->assertCount(2, $results); // Juan y María

    // Juan tiene 2 órdenes: 100 + 150 = 250
    $juan = array_filter($results, fn($r) => $r['nombre'] === 'Juan Pérez')[0];
    $this->assertEquals(2, $juan['total_ordenes']);
    $this->assertEquals('250.00', $juan['suma_ventas']);
    $this->assertEquals('125.00', $juan['promedio_venta']);

    // María tiene 1 orden: 200
    $maria = array_filter($results, fn($r) => $r['nombre'] === 'María García')[0];
    $this->assertEquals(1, $maria['total_ordenes']);
    $this->assertEquals('200.00', $maria['suma_ventas']);
}

Caso 7: Múltiples JOINs a Misma Tabla

Descripción del Problema

Orden con dos direcciones (envío y facturación), ambas en la tabla direcciones.

Estructura de Tablas

sql

CREATE TABLE direcciones (
    id SERIAL PRIMARY KEY,
    calle VARCHAR(200) NOT NULL,
    ciudad VARCHAR(100) NOT NULL,
    codigo_postal VARCHAR(10),
    deleted_at TIMESTAMP NULL
);

ALTER TABLE ordenes
ADD COLUMN direccion_envio_id INTEGER REFERENCES direcciones(id),
ADD COLUMN direccion_facturacion_id INTEGER REFERENCES direcciones(id);

CREATE INDEX idx_ordenes_dir_envio ON ordenes(direccion_envio_id);
CREATE INDEX idx_ordenes_dir_fact ON ordenes(direccion_facturacion_id);

Implementación

Query Class (con JoinSpecs directos)

php

class OrdenConDireccionesQuery extends BaseQuery
{
    public function execute(): array
    {
        $sql = sprintf(
            "SELECT
                o.id,
                o.numero,
                dir_envio.calle AS envio_calle,
                dir_envio.ciudad AS envio_ciudad,
                dir_fact.calle AS fact_calle,
                dir_fact.ciudad AS fact_ciudad
            FROM %s %s",
            OrdenModel::table(),
            OrdenModel::alias()
        );

        // JoinSpecs directos con alias diferentes
        $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'
            )
        ]);

        $sql = $this->applyFilters($sql);

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

SQL Generado

sql

SELECT
    o.id,
    o.numero,
    dir_envio.calle AS envio_calle,
    dir_envio.ciudad AS envio_ciudad,
    dir_fact.calle AS fact_calle,
    dir_fact.ciudad AS fact_ciudad
FROM ordenes o
LEFT JOIN direcciones dir_envio ON dir_envio.id = o.direccion_envio_id
LEFT JOIN direcciones dir_fact ON dir_fact.id = o.direccion_facturacion_id
WHERE deleted_at IS NULL

Resumen de Casos

Caso	Tipo JOIN	Complejidad	Uso Principal
1. Simple 1:1	INNER	Baja	Orden → Cliente
2. Simple 1:N	LEFT	Baja	Cliente → Órdenes
3. Self-JOIN	LEFT	Media	Empleado → Jefe
4. Múltiples Secuenciales	INNER	Alta	Orden → Items → Productos
5. Filtros en JOIN	LEFT	Media	Órdenes del último mes
6. Agregaciones	LEFT + GROUP BY	Media	Total ventas por cliente
7. Múltiples a Misma Tabla	LEFT	Media	Orden con 2 direcciones

Recursos Relacionados

Documentación de JOINs

Guía Completa: Componentes fundamentales
Casos Multi-Schema: JOINs con consolidación cross-schema
Index: Resumen ejecutivo

Documentación de Arquitectura de Base de Datos

docs/architecture/database/multi-tenant.md: Schema-based tenancy (contexto de estos casos)

Última actualización: 2026-02-04 Versión: 2.0.0 Autor: Sistema Bautista - Arquitectura Backend Nota: Aclarada diferencia con 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 en Mismo Schema ​

Tabla de Contenidos ​

Introducción ​

Caso 1: JOIN Simple 1:1 ​

Descripción del Problema ​

Estructura de Tablas ​

Implementación ​

Models ​

Query Class (con JoinSpec directo) ​

SQL Generado ​

Test Unitario ​

Resultado Esperado ​

Caso 2: JOIN Simple 1:N (LEFT) ​

Descripción del Problema ​

Implementación ​

Query Class (con JoinSpec directo) ​

SQL Generado ​

Test Unitario ​

Resultado Esperado ​

Caso 3: Self-JOIN ​

Descripción del Problema ​

Estructura de Tabla ​

Implementación ​

Model ​

Query Class (con JoinSpec directo) ​

SQL Generado ​

Datos de Prueba ​

Resultado Esperado ​

Caso 4: Múltiples JOINs Secuenciales ​

Descripción del Problema ​

Estructura de Tablas ​

Implementación ​

Models ​

Query Class (con JoinSpecs directos) ​

SQL Generado ​

Datos de Prueba ​

Resultado Esperado ​

Caso 5: JOIN con Filtros Adicionales ​

Descripción del Problema ​

Implementación ​

Query Class (con JoinSpec directo) ​

SQL Generado ​

Caso 6: Agregaciones con GROUP BY ​

Descripción del Problema ​

Implementación ​

Query Class ​

SQL Generado ​

Test Unitario ​

Casos de Uso: JOINs en Mismo Schema

Tabla de Contenidos

Introducción

Caso 1: JOIN Simple 1:1

Descripción del Problema

Estructura de Tablas

Implementación

Models

Query Class (con JoinSpec directo)

SQL Generado

Test Unitario

Resultado Esperado

Caso 2: JOIN Simple 1:N (LEFT)

Descripción del Problema

Implementación

Query Class (con JoinSpec directo)

SQL Generado

Test Unitario

Resultado Esperado

Caso 3: Self-JOIN

Descripción del Problema

Estructura de Tabla

Implementación

Model

Query Class (con JoinSpec directo)

SQL Generado

Datos de Prueba

Resultado Esperado

Caso 4: Múltiples JOINs Secuenciales

Descripción del Problema

Estructura de Tablas

Implementación

Models

Query Class (con JoinSpecs directos)

SQL Generado

Datos de Prueba

Resultado Esperado

Caso 5: JOIN con Filtros Adicionales

Descripción del Problema

Implementación

Query Class (con JoinSpec directo)

SQL Generado

Caso 6: Agregaciones con GROUP BY

Descripción del Problema

Implementación

Query Class

SQL Generado

Test Unitario

Caso 7: Múltiples JOINs a Misma Tabla

Descripción del Problema

Estructura de Tablas

Implementación

Query Class (con JoinSpecs directos)

SQL Generado

Resumen de Casos

Recursos Relacionados

Documentación de JOINs

Documentación de Arquitectura de Base de Datos