Enhanced join types and patterns for Selecto.

This module extends the base join functionality with additional join types and enhanced field resolution capabilities.

new-join-types

New Join Types

self-joins

Self-Joins

Join a table to itself with different aliases for comparison or hierarchical relationships.

lateral-joins

Lateral Joins

Correlated subqueries that can reference columns from preceding tables in the FROM clause.

cross-joins

Cross Joins

Cartesian product between tables (use with caution for performance).

full-outer-joins

Full Outer Joins

Complete outer join that returns all rows from both tables.

conditional-joins

Conditional Joins

Dynamic join conditions based on field values or runtime parameters.

enhanced-join-configuration

Enhanced Join Configuration

joins: %{
  # Self-join for manager relationships
  manager: %{
    type: :self_join,
    self_key: :manager_id,
    target_key: :id,
    alias: "mgr",
    condition_type: :left
  },

  # Lateral join for complex correlated queries
  recent_orders: %{
    type: :lateral_join,
    lateral_query: "SELECT * FROM orders o WHERE o.customer_id = customers.id ORDER BY o.created_at DESC LIMIT 5",
    alias: "recent"
  },

  # Cross join for combinations (use carefully)
  product_variants: %{
    type: :cross_join,
    source: "product_options",
    alias: "variants"
  },

  # Full outer join
  all_transactions: %{
    type: :full_outer_join,
    source: "transactions",
    left_key: :account_id,
    right_key: :account_id,
    alias: "trans"
  },

  # Conditional join with runtime conditions
  applicable_discounts: %{
    type: :conditional_join,
    source: "discounts",
    conditions: [
      {:field_comparison, "orders.total", :gte, "discounts.minimum_amount"},
      {:date_range, "orders.created_at", "discounts.valid_from", "discounts.valid_to"}
    ],
    condition_type: :left
  }
}