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

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 join patterns.

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

example

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 hierarchical join with appropriate CTE pattern.

Returns: {from_clause_iodata, params, ctes}.

Build materialized path query patterns.

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

example

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