# `Selecto.Advanced.CTE`

Common Table Expression (CTE) support for PostgreSQL WITH clauses.

Provides comprehensive support for non-recursive and recursive CTEs, enabling
hierarchical queries, query modularity, and complex data processing patterns.

## Examples

    # Non-recursive CTE
    selecto
    |> Selecto.with_cte("high_value_customers", fn ->
        Selecto.configure(customer_domain, connection)
        |> Selecto.select(["customer_id", "first_name", "last_name"])
        |> Selecto.aggregate([{"payment.amount", :sum, as: "total_spent"}])
        |> Selecto.join(:inner, "payment", on: "customer.customer_id = payment.customer_id")
        |> Selecto.group_by(["customer.customer_id", "customer.first_name", "customer.last_name"])
        |> Selecto.having([{"total_spent", {:>, 100}}])
      end,
      columns: ["customer_id", "first_name", "last_name", "total_spent"],
      join: [type: :inner, owner_key: :customer_id, related_key: :customer_id, fields: :infer]
    )
    |> Selecto.select(["film.title", "high_value_customers.first_name"])
    
    # Recursive CTE for hierarchical data
    selecto
    |> Selecto.with_recursive_cte("org_hierarchy",
        base_query: fn ->
          # Anchor: top-level managers
          Selecto.configure(employee_domain, connection)
          |> Selecto.select(["employee_id", "name", "manager_id", {:literal, 0, as: "level"}])
          |> Selecto.filter([{"manager_id", nil}])
        end,
        recursive_query: fn cte ->
          # Recursive: employees under each manager
          Selecto.configure(employee_domain, connection)
          |> Selecto.select(["employee.employee_id", "employee.name", "employee.manager_id", 
                            {:func, "org_hierarchy.level + 1", as: "level"}])
          |> Selecto.join(:inner, cte, on: "employee.manager_id = org_hierarchy.employee_id")
      end,
      columns: ["employee_id", "name", "manager_id", "level"],
      join: [type: :inner, owner_key: :employee_id, related_key: :employee_id, fields: :infer]
    )

# `create_cte`

Create a non-recursive CTE specification.

## Parameters

- `name` - CTE name for the WITH clause
- `query_builder` - Function that returns a Selecto query
- `opts` - Options including :columns, :dependencies

## Examples

    # Simple CTE
    CTE.create_cte("active_customers", fn ->
      Selecto.configure(customer_domain, connection)
      |> Selecto.filter([{"active", true}])
    end)
    
    # CTE with explicit columns
    CTE.create_cte("customer_stats", 
      fn ->
        Selecto.configure(customer_domain, connection)
        |> Selecto.select(["customer_id", {:func, "COUNT", ["rental_id"], as: "rental_count"}])
        |> Selecto.join(:left, "rental", on: "customer.customer_id = rental.customer_id")
        |> Selecto.group_by(["customer_id"])
      end,
      columns: ["customer_id", "rental_count"]
    )

# `create_recursive_cte`

Create a recursive CTE specification.

## Parameters

- `name` - CTE name for the WITH clause
- `base_query` - Function that returns the anchor query
- `recursive_query` - Function that takes the CTE reference and returns recursive query
- `opts` - Options including :columns, :dependencies

## Examples

    # Hierarchical employee structure
    CTE.create_recursive_cte("employee_hierarchy",
      base_query: fn ->
        # Anchor: top-level managers
        Selecto.configure(employee_domain, connection)
        |> Selecto.select(["employee_id", "name", "manager_id", {:literal, 0, as: "level"}])
        |> Selecto.filter([{"manager_id", nil}])
      end,
      recursive_query: fn cte_ref ->
        # Recursive: subordinates
        Selecto.configure(employee_domain, connection)
        |> Selecto.select(["employee.employee_id", "employee.name", "employee.manager_id",
                          {:func, "employee_hierarchy.level + 1", as: "level"}])
        |> Selecto.join(:inner, cte_ref, on: "employee.manager_id = employee_hierarchy.employee_id")
      end
    )

# `detect_circular_dependencies`

Detect circular dependencies in a list of CTEs.

Returns {:error, cycle} if a circular dependency is found,
{:ok, ordered_ctes} if CTEs can be ordered for execution.

# `validate_cte`

Validate a CTE specification.

Ensures the CTE name is valid, queries are properly formed,
and dependencies don't create circular references.

---

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