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

Self-Joins

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

Lateral Joins

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

Cross Joins

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

Full Outer Joins

Complete outer join that returns all rows from both tables.

Conditional Joins

Dynamic join conditions based on field values or runtime parameters.

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