# `Selecto.Builder.Sql.Hierarchy`

Hierarchical SQL pattern generation for self-referencing relationships.

Supports adjacency lists, materialized paths, and closure table patterns
using recursive CTEs and specialized SQL constructs.

The module exposes concrete builders for each hierarchy strategy and a
deterministic fallback join for unrecognized patterns.

# `build_adjacency_list_cte`

Build recursive CTE for adjacency list pattern.

Generates a recursive CTE that traverses parent-child relationships to build
hierarchical paths and levels. Uses Selecto-powered CTE generation for safety.

## Examples

    # Build CTE for category hierarchy
    {hierarchy_cte, params} = build_adjacency_list_cte(selecto, :categories, config)
    
    # Generates CTE like:
    # WITH RECURSIVE category_hierarchy AS (
    #   -- Base case: root nodes
    #   SELECT id, name, parent_id, 0 as level, CAST(id AS TEXT) as path
    #   FROM categories WHERE parent_id IS NULL
    #   UNION ALL
    #   -- Recursive case: child nodes  
    #   SELECT c.id, c.name, c.parent_id, h.level + 1, h.path || '/' || c.id
    #   FROM categories c JOIN category_hierarchy h ON c.parent_id = h.id
    #   WHERE h.level < 5
    # )

Returns: {cte_iodata, params}

# `build_closure_table_query`

Build closure table join patterns.

Generates queries for closure table pattern with ancestor-descendant relationships.
Closure tables maintain all ancestor-descendant relationships explicitly.

## Example

    # For a closure table with ancestor_id, descendant_id, depth columns
    config = %{
      source: "categories",
      closure_table: "category_closure", 
      ancestor_field: "ancestor_id",
      descendant_field: "descendant_id",
      depth_field: "depth"
    }
    
Returns: {query_iodata, params}

# `build_hierarchy_join_with_cte`

Build hierarchical join with appropriate CTE pattern.

Returns: `{from_clause_iodata, params, ctes}`.

# `build_materialized_path_query`

Build materialized path query patterns.

Generates SQL for path-based hierarchy queries using LIKE patterns and depth calculations.

## Example

    # For a path like "root/electronics/computers"
    config = %{
      source: "categories", 
      path_field: "path",
      path_separator: "/",
      root_path: "root"
    }
    
    # Generates SQL that calculates depth and filters by path patterns

Returns: {query_iodata, params}

# `example_selecto_hierarchy_usage`

Example of hierarchical join integration using Selecto-powered CTEs.

This demonstrates the integration pattern for teams wiring these CTE helpers
into higher-level query flows.

    # Instead of raw SQL generation, we'll use Selecto queries:
    
    def build_adjacency_cte_with_selecto(selecto, join, config) do
      domain = build_hierarchy_domain(selecto, join, config)
      connection = selecto.postgrex_opts
      
      # Base case using Selecto
      base_case = Selecto.configure(domain, connection)
        |> Selecto.select([
          "id", 
          "name", 
          "parent_id",
          {:literal, 0, "level"},
          {:literal, "id", "path"}
        ])
        |> Selecto.filter([{"parent_id", nil}])
      
      # Recursive case using Selecto  
      recursive_case = Selecto.configure(domain, connection)
        |> Selecto.select([
          "c.id",
          "c.name",
          "c.parent_id", 
          "h.level + 1",
          {:func, "concat", ["h.path", {:literal, "/"}, "c.id"]}
        ])
        |> Selecto.filter([{"h.level", {:lt, 5}}])
        # Special handling for CTE JOIN would be added here
      
      # Use Selecto public recursive CTE API
      Selecto.with_recursive_cte(
        selecto,
        hierarchy_name(join),
        fn -> base_case end,
        fn _cte_ref -> recursive_case end
      )
    end

# `hierarchy_cte_name`

---

*Consult [api-reference.md](api-reference.md) for complete listing*