Selecto v0.3.15

Selecto.Advanced.CTE (Selecto v0.3.15)

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

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

Link to this section Summary

Functions

create_cte(name, query_builder, opts \\ [])

Create a non-recursive CTE specification.

create_recursive_cte(name, opts)

Create a recursive CTE specification.

detect_circular_dependencies(ctes)

Detect circular dependencies in a list of CTEs.

validate_cte(spec)

Validate a CTE specification.

Link to this section Functions

Link to this function

create_cte(name, query_builder, opts \\ [])

Create a non-recursive CTE specification.

parameters

 Parameters

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

examples

 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"]
)
Link to this function

create_recursive_cte(name, opts)

Create a recursive CTE specification.

parameters

 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

 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
)
Link to this function

detect_circular_dependencies(ctes)

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.

Link to this function

validate_cte(spec)

Validate a CTE specification.

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