Selecto Guide - Interactive Examples

find_local_selecto = fn ->
  cwd = File.cwd!()
  ancestors = [cwd | Path.split(cwd) |> Enum.scan("/", &Path.join(&2, &1)) |> Enum.reverse()]

  candidate_paths =
    ancestors
    |> Enum.flat_map(fn base ->
      [
        Path.join(base, "vendor/selecto"),
        Path.join(base, "selecto")
      ]
    end)
    |> Enum.uniq()

  Enum.find(candidate_paths, &(File.exists?(Path.join(&1, "mix.exs"))))
end

local_selecto_path = find_local_selecto.()

selecto_dep =
  if local_selecto_path do
    {:selecto, path: local_selecto_path}
  else
    {:selecto, github: "seeken/selecto", branch: "main"}
  end

Mix.install([
  selecto_dep,
  {:postgrex, "~> 0.17"},
  {:ecto_sql, "~> 3.11"},
  {:jason, "~> 1.4"},
  {:kino, "~> 0.12"}
])

IO.puts("Using Selecto dependency: #{inspect(selecto_dep)}")

if match?({:selecto, github: _, branch: _}, selecto_dep) do
  IO.puts("""
  WARNING: using GitHub Selecto fallback.
  Hierarchy join examples may fail if the selected branch does not include legacy CTE compatibility.
  """)
end

Introduction

This Livebook demonstrates all the examples from the Selecto Guide. Selecto is a powerful, composable SQL query builder for Elixir that generates parameterized queries from declarative domain configurations.

Before running these examples, make sure you have:

1. Set up the selecto_examples database
2. Run the migrations and seeds

cd selecto_examples
mix setup

Setup: Database Connection and Domain Configurations

Run this cell first - it sets up everything needed for the examples below.

# Configure and start the Repo
defmodule SelectoExamples.Repo do
  use Ecto.Repo,
    otp_app: :selecto_examples,
    adapter: Ecto.Adapters.Postgres
end

# Start the repo with connection settings
{:ok, _pid} = SelectoExamples.Repo.start_link(
  database: "selecto_examples_dev",
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  pool_size: 5
)

# Product Domain
product_domain = %{
  name: "Products",
  source: %{
    source_table: "products",
    primary_key: :id,
    fields: [:id, :name, :sku, :description, :price, :cost, :stock_quantity,
             :reorder_level, :active, :featured, :tags, :metadata, :category_id, :supplier_id,
             :inserted_at, :updated_at],
    columns: %{
      id: %{type: :integer},
      name: %{type: :string, label: "Product Name"},
      sku: %{type: :string, label: "SKU"},
      description: %{type: :text},
      price: %{type: :decimal, label: "Price"},
      cost: %{type: :decimal, label: "Cost"},
      stock_quantity: %{type: :integer, label: "Stock"},
      reorder_level: %{type: :integer},
      active: %{type: :boolean, label: "Active?"},
      featured: %{type: :boolean, label: "Featured?"},
      tags: %{type: {:array, :string}},
      metadata: %{type: :jsonb, label: "Metadata"},
      category_id: %{type: :integer},
      supplier_id: %{type: :integer},
      inserted_at: %{type: :utc_datetime},
      updated_at: %{type: :utc_datetime}
    },
    associations: %{
      category: %{field: :category, queryable: :categories, owner_key: :category_id, related_key: :id},
      supplier: %{field: :supplier, queryable: :suppliers, owner_key: :supplier_id, related_key: :id}
    }
  },
  schemas: %{
    categories: %{
      source_table: "categories",
      primary_key: :id,
      fields: [:id, :name, :description, :slug, :active],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        name: %{type: :string},
        description: %{type: :text},
        slug: %{type: :string},
        active: %{type: :boolean}
      }
    },
    suppliers: %{
      source_table: "suppliers",
      primary_key: :id,
      fields: [:id, :company_name, :contact_name, :email, :city, :country, :active],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        company_name: %{type: :string},
        contact_name: %{type: :string},
        email: %{type: :string},
        city: %{type: :string},
        country: %{type: :string},
        active: %{type: :boolean}
      }
    }
  },
  joins: %{
    category: %{
      name: "Category",
      type: :left,
      source: "categories",
      on: [%{left: "category_id", right: "id"}],
      fields: %{
        name: %{type: :string, label: "Category Name"},
        description: %{type: :text}
      }
    },
    supplier: %{
      name: "Supplier",
      type: :left,
      source: "suppliers",
      on: [%{left: "supplier_id", right: "id"}],
      fields: %{
        company_name: %{type: :string, label: "Supplier Name"},
        contact_name: %{type: :string}
      }
    }
  }
}

# Order Domain
order_domain = %{
  name: "Orders",
  source: %{
    source_table: "orders",
    primary_key: :id,
    fields: [:id, :order_number, :status, :subtotal, :tax, :shipping, :discount,
             :total, :shipping_city, :shipping_country, :customer_id,
             :shipped_at, :delivered_at, :inserted_at, :updated_at],
    columns: %{
      id: %{type: :integer},
      order_number: %{type: :string, label: "Order #"},
      status: %{type: :string, label: "Status"},
      subtotal: %{type: :decimal},
      tax: %{type: :decimal},
      shipping: %{type: :decimal},
      discount: %{type: :decimal},
      total: %{type: :decimal, label: "Total"},
      shipping_city: %{type: :string},
      shipping_country: %{type: :string},
      customer_id: %{type: :integer},
      shipped_at: %{type: :utc_datetime},
      delivered_at: %{type: :utc_datetime},
      inserted_at: %{type: :utc_datetime, label: "Order Date"},
      updated_at: %{type: :utc_datetime}
    },
    associations: %{
      customer: %{field: :customer, queryable: :customers, owner_key: :customer_id, related_key: :id},
      order_items: %{field: :order_items, queryable: :order_items, owner_key: :id, related_key: :order_id}
    }
  },
  schemas: %{
    customers: %{
      source_table: "customers",
      primary_key: :id,
      fields: [:id, :name, :email, :phone, :tier, :city, :country, :active],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        name: %{type: :string},
        email: %{type: :string},
        phone: %{type: :string},
        tier: %{type: :string},
        city: %{type: :string},
        country: %{type: :string},
        active: %{type: :boolean}
      }
    },
    order_items: %{
      source_table: "order_items",
      primary_key: :id,
      fields: [:id, :quantity, :unit_price, :discount, :line_total, :order_id, :product_id],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        quantity: %{type: :integer},
        unit_price: %{type: :decimal},
        discount: %{type: :decimal},
        line_total: %{type: :decimal},
        order_id: %{type: :integer},
        product_id: %{type: :integer}
      }
    }
  },
  joins: %{
    customer: %{
      name: "Customer",
      type: :left,
      source: "customers",
      on: [%{left: "customer_id", right: "id"}],
      fields: %{
        name: %{type: :string, label: "Customer Name"},
        email: %{type: :string},
        tier: %{type: :string, label: "Customer Tier"}
      }
    },
    order_items: %{
      name: "Order Items",
      type: :left,
      source: "order_items",
      on: [%{left: "id", right: "order_id"}],
      fields: %{
        quantity: %{type: :integer},
        unit_price: %{type: :decimal},
        line_total: %{type: :decimal}
      }
    }
  }
}

# Customer Domain
customer_domain = %{
  name: "Customers",
  source: %{
    source_table: "customers",
    primary_key: :id,
    fields: [:id, :name, :email, :phone, :tier, :company_name, :city, :country,
             :active, :verified_at, :inserted_at, :updated_at],
    columns: %{
      id: %{type: :integer},
      name: %{type: :string, label: "Customer Name"},
      email: %{type: :string},
      phone: %{type: :string},
      tier: %{type: :string, label: "Tier"},
      company_name: %{type: :string},
      city: %{type: :string},
      country: %{type: :string},
      active: %{type: :boolean},
      verified_at: %{type: :utc_datetime},
      inserted_at: %{type: :utc_datetime},
      updated_at: %{type: :utc_datetime}
    }
  },
  schemas: %{},
  joins: %{}
}

# Employee Domain (for hierarchical examples)
employee_domain = %{
  name: "Employees",
  source: %{
    source_table: "employees",
    primary_key: :id,
    fields: [:id, :first_name, :last_name, :email, :title, :department,
             :hire_date, :salary, :active, :manager_id, :inserted_at, :updated_at],
    columns: %{
      id: %{type: :integer},
      first_name: %{type: :string},
      last_name: %{type: :string},
      email: %{type: :string},
      title: %{type: :string},
      department: %{type: :string},
      hire_date: %{type: :date},
      salary: %{type: :decimal},
      active: %{type: :boolean},
      manager_id: %{type: :integer},
      inserted_at: %{type: :utc_datetime},
      updated_at: %{type: :utc_datetime}
    },
    associations: %{
      manager: %{field: :manager, queryable: :employees, owner_key: :manager_id, related_key: :id}
    }
  },
  schemas: %{
    employees: %{
      source_table: "employees",
      primary_key: :id,
      fields: [:id, :first_name, :last_name, :email, :title, :department,
               :hire_date, :salary, :active, :manager_id],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        first_name: %{type: :string},
        last_name: %{type: :string},
        email: %{type: :string},
        title: %{type: :string},
        department: %{type: :string},
        hire_date: %{type: :date},
        salary: %{type: :decimal},
        active: %{type: :boolean},
        manager_id: %{type: :integer}
      }
    }
  },
  joins: %{
    manager: %{
      name: "Manager",
      type: :left,
      source: "employees",
      on: [%{left: "manager_id", right: "id"}],
      fields: %{
        first_name: %{type: :string, label: "Manager First Name"},
        last_name: %{type: :string, label: "Manager Last Name"}
      }
    }
  }
}

# Order Items Domain (for product sales analysis)
order_items_domain = %{
  name: "OrderItems",
  source: %{
    source_table: "order_items",
    primary_key: :id,
    fields: [:id, :quantity, :unit_price, :line_total, :order_id, :product_id],
    columns: %{
      id: %{type: :integer},
      quantity: %{type: :integer},
      unit_price: %{type: :decimal},
      line_total: %{type: :decimal},
      order_id: %{type: :integer},
      product_id: %{type: :integer}
    },
    associations: %{
      product: %{field: :product, queryable: :products, owner_key: :product_id, related_key: :id}
    }
  },
  schemas: %{
    products: %{
      source_table: "products",
      primary_key: :id,
      fields: [:id, :name, :sku, :price],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        name: %{type: :string},
        sku: %{type: :string},
        price: %{type: :decimal}
      }
    }
  },
  joins: %{
    product: %{
      name: "Product",
      type: :left,
      source: "products",
      on: [%{left: "product_id", right: "id"}],
      fields: %{
        name: %{type: :string},
        sku: %{type: :string}
      }
    }
  }
}

# Store everything in a map for easy access
config = %{
  repo: SelectoExamples.Repo,
  product_domain: product_domain,
  order_domain: order_domain,
  customer_domain: customer_domain,
  employee_domain: employee_domain,
  order_items_domain: order_items_domain
}

IO.puts("✓ Connected to database!")
IO.puts("✓ All domains configured!")
IO.puts("\nAvailable: config.repo, config.product_domain, config.order_domain, etc.")

config

defmodule SelectoExamples.LivebookHelpers do
  @moduledoc false

  def execute_with_checks(query, opts \\ []) do
    label = Keyword.get(opts, :example, "Query")
    {sql, params} = Selecto.to_sql(query)

    IO.puts("\n[Checks] #{label}")
    print_check("SQL generated", String.trim(sql) != "")
    print_check("FROM clause present", valid_from_clause?(sql))
    print_check("params available", is_list(params))
    print_check("CTE appears consumed (if present)", cte_consumed?(sql))

    Selecto.execute(query)
  end

  defp valid_from_clause?(sql) do
    has_from = Regex.match?(~r/\bfrom\s+\S/m, sql)
    bad_from = Regex.match?(~r/\bfrom\s*(\n|\r\n)\s*where\b/mi, sql)
    has_from and not bad_from
  end

  defp cte_consumed?(sql) do
    case Regex.run(~r/\bWITH(?:\s+RECURSIVE)?\s+([a-zA-Z_][a-zA-Z0-9_]*)\s+AS\b/m, sql) do
      nil ->
        true

      [_, cte_name] ->
        count = Regex.scan(~r/\b#{Regex.escape(cte_name)}\b/, sql) |> length()
        count > 1
    end
  end

  defp print_check(name, true), do: IO.puts("  [PASS] #{name}")
  defp print_check(name, false), do: IO.puts("  [FAIL] #{name}")
end

Section 1: Basic Query Building

Selecting Fields

# Simple field selection
selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "sku", "price", "stock_quantity"])
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("Columns: #{inspect(columns)}")
    IO.puts("\nFirst 10 Products:")
    Enum.each(rows, fn row ->
      IO.puts("  #{inspect(row)}")
    end)
  {:error, error} ->
    IO.puts("Error: #{inspect(error)}")
end

Select with Joins (dot notation)

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "price", "category.name", "supplier.company_name"])
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("Products with Category and Supplier:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn row ->
      IO.puts("  #{inspect(row)}")
    end)
  {:error, error} ->
    IO.puts("Error: #{inspect(error)}")
end

Section 2: Filtering Data

Simple Equality Filter

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "price", "active"])
|> Selecto.filter({"active", true})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _}} ->
    IO.puts("Active Products Only:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Comparison Operators

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "price", "stock_quantity"])
|> Selecto.filter({"price", {:gt, 50}})      # price > 50
|> Selecto.filter({"stock_quantity", {:gte, 100}})  # stock >= 100
|> Selecto.order_by({"price", :desc})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _}} ->
    IO.puts("Products with price > 50 and stock >= 100:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Pattern Matching with LIKE/ILIKE

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "sku", "price"])
|> Selecto.filter({"name", {:ilike, "%wireless%"}})  # Case-insensitive
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Products with 'wireless' in name:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

IN Operator

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select(["order_number", "status", "total"])
|> Selecto.filter({"status", {:in, ["pending", "processing"]}})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Pending and Processing Orders:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

BETWEEN Filter

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "price"])
|> Selecto.filter({"price", {:between, 25, 75}})
|> Selecto.order_by({"price", :asc})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Products priced between $25 and $75:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

NULL Checks

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select(["order_number", "status", "shipped_at"])
|> Selecto.filter({"shipped_at", :not_null})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Orders that have been shipped:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

OR Conditions

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select(["order_number", "status", "total"])
|> Selecto.filter({:or, [
  {"status", "pending"},
  {"status", "cancelled"}
]})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Pending OR Cancelled Orders:")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Section 3: Sorting and Pagination

Sorting Results

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "category.name", "price"])
|> Selecto.order_by([
  {"category.name", :asc},
  {"price", :desc}
])
|> Selecto.limit(15)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _}} ->
    IO.puts("Products sorted by Category (asc), then Price (desc):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Pagination

# Page 2, 10 items per page
page = 2
per_page = 10

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select(["name", "sku", "price"])
|> Selecto.order_by({"name", :asc})
|> Selecto.limit(per_page)
|> Selecto.offset((page - 1) * per_page)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Page #{page} (#{per_page} items per page):")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Section 4: Aggregates and Grouping

Simple Aggregates

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select([
  {:count, "*"},
  {:avg, "price"},
  {:min, "price"},
  {:max, "price"},
  {:sum, "stock_quantity"}
])
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {[[count, avg, min, max, sum]], columns, _}} ->
    IO.puts("Product Statistics:")
    IO.puts("  Count: #{count}")
    IO.puts("  Average Price: $#{Decimal.round(avg || Decimal.new(0), 2)}")
    IO.puts("  Min Price: $#{min}")
    IO.puts("  Max Price: $#{max}")
    IO.puts("  Total Stock: #{sum}")
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Group By with Aggregates

selecto = Selecto.configure(config.product_domain, config.repo)

result = selecto
|> Selecto.select([
  "category.name",
  {:count, "*"},
  {:avg, "price"},
  {:sum, "stock_quantity"}
])
|> Selecto.group_by(["category.name"])
|> Selecto.order_by({{:count, "*"}, :desc})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _}} ->
    IO.puts("Products by Category:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn row ->
      [cat, count, avg, stock] = row
      IO.puts("  #{cat}: #{count} products, avg $#{Decimal.round(avg || Decimal.new(0), 2)}, #{stock} in stock")
    end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Count Distinct

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select([
  {:count, "*"},
  {:count_distinct, "customer_id"},
  {:sum, "total"},
  {:avg, "total"}
])
|> Selecto.filter({"status", {:in, ["delivered", "shipped"]}})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {[[orders, customers, revenue, avg_order]], _, _}} ->
    IO.puts("Sales Summary (Shipped/Delivered Orders):")
    IO.puts("  Total Orders: #{orders}")
    IO.puts("  Unique Customers: #{customers}")
    IO.puts("  Total Revenue: $#{Decimal.round(revenue || Decimal.new(0), 2)}")
    IO.puts("  Average Order Value: $#{Decimal.round(avg_order || Decimal.new(0), 2)}")
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Section 5: Joins and Associations

Basic Joins (automatic via dot notation)

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select([
  "order_number",
  "status",
  "total",
  "customer.name",
  "customer.tier"
])
|> Selecto.filter({"status", "delivered"})
|> Selecto.order_by({"total", :desc})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _}} ->
    IO.puts("Top 10 Delivered Orders with Customer Info:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Orders by Customer Tier

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select([
  "customer.tier",
  {:count, "*"},
  {:sum, "total"},
  {:avg, "total"}
])
|> Selecto.group_by(["customer.tier"])
|> Selecto.order_by({{:sum, "total"}, :desc})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Order Summary by Customer Tier:")
    Enum.each(rows, fn [tier, count, sum, avg] ->
      IO.puts("  #{tier || "unknown"}: #{count} orders, $#{Decimal.round(sum || Decimal.new(0), 2)} total, $#{Decimal.round(avg || Decimal.new(0), 2)} avg")
    end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Section 6: Employees with Manager (Self-Join)

selecto = Selecto.configure(config.employee_domain, config.repo)

result = selecto
|> Selecto.select([
  "first_name",
  "last_name",
  "title",
  "department",
  "manager.first_name",
  "manager.last_name"
])
|> Selecto.order_by([{"department", :asc}, {"last_name", :asc}])
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, columns, _}} ->
    IO.puts("Employees with their Managers:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [first, last, title, dept, mgr_first, mgr_last] ->
      manager = if mgr_first, do: "#{mgr_first} #{mgr_last}", else: "(No Manager)"
      IO.puts("  #{first} #{last} - #{title} (#{dept}) - Reports to: #{manager}")
    end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Section 7: Advanced Queries

Sales by Status

selecto = Selecto.configure(config.order_domain, config.repo)

result = selecto
|> Selecto.select([
  "status",
  {:count, "*"},
  {:sum, "total"},
  {:avg, "total"}
])
|> Selecto.group_by(["status"])
|> Selecto.order_by({{:count, "*"}, :desc})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Order Status Breakdown:")
    Enum.each(rows, fn [status, count, sum, avg] ->
      IO.puts("  #{status}: #{count} orders, $#{Decimal.round(sum || Decimal.new(0), 2)} total revenue")
    end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Top Selling Products (via Order Items)

selecto = Selecto.configure(config.order_items_domain, config.repo)

result = selecto
|> Selecto.select([
  "product.name",
  "product.sku",
  {:sum, "quantity"},
  {:sum, "line_total"}
])
|> Selecto.group_by(["product.name", "product.sku"])
|> Selecto.order_by({{:sum, "line_total"}, :desc})
|> Selecto.limit(10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Top 10 Products by Revenue:")
    Enum.each(rows, fn [name, sku, qty, revenue] ->
      IO.puts("  #{name} (#{sku}): #{qty} units, $#{Decimal.round(revenue || Decimal.new(0), 2)} revenue")
    end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Department Salary Summary

selecto = Selecto.configure(config.employee_domain, config.repo)

result = selecto
|> Selecto.select([
  "department",
  {:count, "*"},
  {:sum, "salary"},
  {:avg, "salary"},
  {:min, "salary"},
  {:max, "salary"}
])
|> Selecto.filter({"active", true})
|> Selecto.group_by(["department"])
|> Selecto.order_by({{:sum, "salary"}, :desc})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Salary Summary by Department:")
    Enum.each(rows, fn [dept, count, sum, avg, min, max] ->
      IO.puts("\n  #{dept}:")
      IO.puts("    Employees: #{count}")
      IO.puts("    Total Payroll: $#{Decimal.round(sum || Decimal.new(0), 2)}")
      IO.puts("    Average Salary: $#{Decimal.round(avg || Decimal.new(0), 2)}")
      IO.puts("    Range: $#{min} - $#{max}")
    end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Pivot Example: Retarget Delivered Orders to Line Items

pivot_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.filter({"status", "delivered"})
  |> Selecto.pivot(:order_items, subquery_strategy: :exists)
  |> Selecto.select([
    {:field, "order_items.product_id", "product_id"},
    {:field, "order_items.quantity", "quantity"},
    {:field, "order_items.line_total", "line_total"}
  ])

{pivot_sql, pivot_params} = Selecto.to_sql(pivot_query)
IO.puts("Generated SQL for Pivot query:\n")
IO.puts(pivot_sql)
IO.puts("\nParams: #{inspect(pivot_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(pivot_query, example: "Pivot Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nLine Items from Delivered Orders (pivoted view):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(Enum.take(rows, 10), fn [product_id, quantity, line_total] ->
      IO.puts("  product_id=#{product_id}, qty=#{quantity}, line_total=$#{Decimal.round(line_total || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Subselect Example: Attach Order Items as JSON per Order

subselect_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "status", "total"])
  |> Selecto.filter({"status", {:in, ["processing", "shipped"]}})
  |> Selecto.subselect(
    ["order_items[quantity, line_total]"],
    format: :json_agg,
    alias_prefix: "items"
  )
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(5)

{subselect_sql, subselect_params} = Selecto.to_sql(subselect_query)
IO.puts("Generated SQL for Subselect query:\n")
IO.puts(subselect_sql)
IO.puts("\nParams: #{inspect(subselect_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(subselect_query, example: "Subselect Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nTop processing/shipped orders with embedded line items:")
    IO.puts("Columns: #{inspect(columns)}")

    Enum.each(rows, fn [order_number, status, total, items] ->
      item_count = if is_list(items), do: length(items), else: 0
      IO.puts("  #{order_number} (#{status}) total=$#{Decimal.round(total || Decimal.new(0), 2)} | item_count=#{item_count}")

      items
      |> List.wrap()
      |> Enum.take(2)
      |> Enum.each(fn item ->
        IO.puts("    item: quantity=#{item["quantity"]}, line_total=#{item["line_total"]}")
      end)
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Section 8: Composable Query Patterns

Building Reusable Query Functions

defmodule ProductQueries do
  def base_query(repo, domain) do
    selecto = Selecto.configure(domain, repo)
    selecto
  end

  def active_only(selecto) do
    selecto |> Selecto.filter({"active", true})
  end

  def in_stock(selecto) do
    selecto |> Selecto.filter({"stock_quantity", {:gt, 0}})
  end

  def price_range(selecto, min, max) do
    selecto |> Selecto.filter({"price", {:between, min, max}})
  end

  def paginated(selecto, page, per_page) do
    selecto
    |> Selecto.limit(per_page)
    |> Selecto.offset((page - 1) * per_page)
  end
end

# Use the composable functions
result = ProductQueries.base_query(config.repo, config.product_domain)
|> ProductQueries.active_only()
|> ProductQueries.in_stock()
|> ProductQueries.price_range(20, 100)
|> Selecto.select(["name", "price", "stock_quantity", "category.name"])
|> Selecto.order_by({"price", :asc})
|> ProductQueries.paginated(1, 10)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

case result do
  {:ok, {rows, _columns, _}} ->
    IO.puts("Active, In-Stock Products ($20-$100):")
    Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)
  {:error, e} -> IO.puts("Error: #{inspect(e)}")
end

Dynamic Search Function

defmodule ProductSearch do
  def search(repo, domain, params \\ %{}) do
    selecto = Selecto.configure(domain, repo)

    selecto
    |> Selecto.select(["id", "name", "sku", "price", "stock_quantity", "category.name"])
    |> apply_search(params[:q])
    |> apply_category_filter(params[:category])
    |> apply_price_filter(params[:min_price], params[:max_price])
    |> apply_active_filter(params[:active])
    |> apply_sorting(params[:sort], params[:direction])
    |> Selecto.limit(params[:limit] || 25)
    |> SelectoExamples.LivebookHelpers.execute_with_checks()
  end

  defp apply_search(selecto, nil), do: selecto
  defp apply_search(selecto, ""), do: selecto
  defp apply_search(selecto, query) do
    selecto |> Selecto.filter({"name", {:ilike, "%#{query}%"}})
  end

  defp apply_category_filter(selecto, nil), do: selecto
  defp apply_category_filter(selecto, category) do
    selecto |> Selecto.filter({"category.name", {:ilike, "%#{category}%"}})
  end

  defp apply_price_filter(selecto, nil, nil), do: selecto
  defp apply_price_filter(selecto, min, nil) when not is_nil(min) do
    selecto |> Selecto.filter({"price", {:gte, min}})
  end
  defp apply_price_filter(selecto, nil, max) when not is_nil(max) do
    selecto |> Selecto.filter({"price", {:lte, max}})
  end
  defp apply_price_filter(selecto, min, max) do
    selecto |> Selecto.filter({"price", {:between, min, max}})
  end

  defp apply_active_filter(selecto, nil), do: selecto
  defp apply_active_filter(selecto, active) do
    selecto |> Selecto.filter({"active", active})
  end

  defp apply_sorting(selecto, nil, _), do: selecto |> Selecto.order_by({"name", :asc})
  defp apply_sorting(selecto, field, direction) do
    dir = if direction == "desc", do: :desc, else: :asc
    selecto |> Selecto.order_by({field, dir})
  end
end

# Example searches
IO.puts("\n--- Search: 'wireless' products ---")
{:ok, {rows, _, _}} = ProductSearch.search(config.repo, config.product_domain, %{q: "wireless"})
Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)

IO.puts("\n--- Search: Electronics under $100 ---")
{:ok, {rows, _, _}} = ProductSearch.search(config.repo, config.product_domain, %{
  category: "electronics",
  max_price: 100,
  sort: "price",
  direction: "asc"
})
Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)

IO.puts("\n--- Search: Active products $30-$60 ---")
{:ok, {rows, _, _}} = ProductSearch.search(config.repo, config.product_domain, %{
  active: true,
  min_price: 30,
  max_price: 60,
  limit: 10
})
Enum.each(rows, fn row -> IO.puts("  #{inspect(row)}") end)

Section 9: Output Formats

Transform to Maps

selecto = Selecto.configure(config.product_domain, config.repo)

{:ok, {rows, columns, aliases}} = selecto
|> Selecto.select(["name", "sku", "price", "stock_quantity"])
|> Selecto.filter({"featured", true})
|> SelectoExamples.LivebookHelpers.execute_with_checks()

# Transform to maps
maps = Enum.map(rows, fn row ->
  Enum.zip(columns, row) |> Map.new()
end)

IO.puts("Featured Products as Maps:")
Enum.each(maps, fn map ->
  IO.puts("  #{map["name"]} (#{map["sku"]}): $#{map["price"]}, #{map["stock_quantity"]} in stock")
end)

Create a Summary Table with Kino

selecto = Selecto.configure(config.product_domain, config.repo)

{:ok, {rows, _columns, aliases}} = selecto
|> Selecto.select([
  {:field, "name", "product_name"},
  {:field, "sku", "sku"},
  {:field, "price", "price"},
  {:field, "stock_quantity", "stock_quantity"},
  {:field, "category.name", "category_name"}
])
|> Selecto.filter({"active", true})
|> Selecto.order_by({"price", :desc})
|> Selecto.limit(15)
|> SelectoExamples.LivebookHelpers.execute_with_checks()

# Transform for Kino DataTable
data = Enum.map(rows, fn row ->
  Enum.zip(aliases, row) |> Map.new()
end)

Kino.DataTable.new(data)

Section 10: Sales Dashboard Example

Let’s create a comprehensive sales dashboard using multiple queries:

defmodule SalesDashboard do
  def generate(config) do
    IO.puts("=" |> String.duplicate(60))
    IO.puts("           SALES DASHBOARD")
    IO.puts("=" |> String.duplicate(60))

    # Overall stats
    IO.puts("\n## OVERALL STATISTICS")
    show_overall_stats(config)

    # Status breakdown
    IO.puts("\n## ORDER STATUS BREAKDOWN")
    show_status_breakdown(config)

    # Customer tier analysis
    IO.puts("\n## REVENUE BY CUSTOMER TIER")
    show_tier_analysis(config)

    # Top customers
    IO.puts("\n## TOP 5 CUSTOMERS BY REVENUE")
    show_top_customers(config)

    # Product category performance
    IO.puts("\n## PRODUCT CATEGORY SUMMARY")
    show_category_summary(config)

    IO.puts("\n" <> String.duplicate("=", 60))
  end

  defp show_overall_stats(config) do
    selecto = Selecto.configure(config.order_domain, config.repo)

    {:ok, {[[orders, customers, revenue, avg]], _, _}} = selecto
    |> Selecto.select([
      {:count, "*"},
      {:count_distinct, "customer_id"},
      {:sum, "total"},
      {:avg, "total"}
    ])
    |> SelectoExamples.LivebookHelpers.execute_with_checks()

    IO.puts("  Total Orders: #{orders}")
    IO.puts("  Unique Customers: #{customers}")
    IO.puts("  Total Revenue: $#{format_decimal(revenue)}")
    IO.puts("  Average Order Value: $#{format_decimal(avg)}")
  end

  defp show_status_breakdown(config) do
    selecto = Selecto.configure(config.order_domain, config.repo)

    {:ok, {rows, _, _}} = selecto
    |> Selecto.select([
      "status",
      {:count, "*"},
      {:sum, "total"}
    ])
    |> Selecto.group_by(["status"])
    |> Selecto.order_by({{:count, "*"}, :desc})
    |> SelectoExamples.LivebookHelpers.execute_with_checks()

    Enum.each(rows, fn [status, count, revenue] ->
      IO.puts("  #{String.pad_trailing(status, 12)}: #{String.pad_leading(to_string(count), 4)} orders, $#{format_decimal(revenue)}")
    end)
  end

  defp show_tier_analysis(config) do
    selecto = Selecto.configure(config.order_domain, config.repo)

    {:ok, {rows, _, _}} = selecto
    |> Selecto.select([
      "customer.tier",
      {:count, "*"},
      {:sum, "total"},
      {:avg, "total"}
    ])
    |> Selecto.group_by(["customer.tier"])
    |> Selecto.order_by({{:sum, "total"}, :desc})
    |> SelectoExamples.LivebookHelpers.execute_with_checks()

    Enum.each(rows, fn [tier, count, revenue, avg] ->
      IO.puts("  #{String.pad_trailing(tier || "unknown", 10)}: #{count} orders, $#{format_decimal(revenue)} total, $#{format_decimal(avg)} avg")
    end)
  end

  defp show_top_customers(config) do
    selecto = Selecto.configure(config.order_domain, config.repo)

    {:ok, {rows, _, _}} = selecto
    |> Selecto.select([
      "customer.name",
      "customer.tier",
      {:count, "*"},
      {:sum, "total"}
    ])
    |> Selecto.group_by(["customer.name", "customer.tier"])
    |> Selecto.order_by({{:sum, "total"}, :desc})
    |> Selecto.limit(5)
    |> SelectoExamples.LivebookHelpers.execute_with_checks()

    Enum.with_index(rows, 1) |> Enum.each(fn {[name, tier, orders, revenue], rank} ->
      IO.puts("  #{rank}. #{name} (#{tier}): #{orders} orders, $#{format_decimal(revenue)}")
    end)
  end

  defp show_category_summary(config) do
    selecto = Selecto.configure(config.product_domain, config.repo)

    {:ok, {rows, _, _}} = selecto
    |> Selecto.select([
      "category.name",
      {:count, "*"},
      {:avg, "price"},
      {:sum, "stock_quantity"}
    ])
    |> Selecto.group_by(["category.name"])
    |> Selecto.order_by({{:count, "*"}, :desc})
    |> SelectoExamples.LivebookHelpers.execute_with_checks()

    Enum.each(rows, fn [category, count, avg_price, stock] ->
      IO.puts("  #{String.pad_trailing(category || "Unknown", 20)}: #{count} products, $#{format_decimal(avg_price)} avg, #{stock} units")
    end)
  end

  defp format_decimal(nil), do: "0.00"
  defp format_decimal(d) when is_struct(d, Decimal), do: Decimal.round(d, 2) |> Decimal.to_string()
  defp format_decimal(n), do: :erlang.float_to_binary(n / 1, decimals: 2)
end

# Generate the dashboard
SalesDashboard.generate(config)

Section 11: Set Operations (UNION / INTERSECT / EXCEPT)

Set operations are most useful when comparing meaningful cohorts. In this example we compare two employee cohorts:

• Cohort A: employees who report to someone (manager_id IS NOT NULL)
• Cohort B: employees who manage at least one direct report

Each query projects the same columns so they are compatible for set operations.

employees_with_manager =
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.select(["id", "first_name", "last_name", "title"])
  |> Selecto.filter({"manager_id", :not_null})

employees_with_direct_reports =
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.join(:direct_report,
    source: "employees",
    type: :inner,
    owner_key: :id,
    related_key: :manager_id,
    fields: %{
      id: %{type: :integer}
    }
  )
  |> Selecto.filter({"direct_report.id", :not_null})
  |> Selecto.select(["id", "first_name", "last_name", "title"])
  |> Selecto.group_by(["id", "first_name", "last_name", "title"])

set_queries = [
  {
    "UNION",
    "All employees who either have a manager or manage someone.",
    Selecto.union(employees_with_manager, employees_with_direct_reports)
  },
  {
    "UNION ALL",
    "Same as UNION but keeps duplicates (middle managers appear twice).",
    Selecto.union(employees_with_manager, employees_with_direct_reports, all: true)
  },
  {
    "INTERSECT",
    "Employees in both cohorts (middle managers: report to someone and manage others).",
    Selecto.intersect(employees_with_manager, employees_with_direct_reports)
  },
  {
    "EXCEPT (A - B)",
    "Employees who report to someone but do NOT manage others (individual contributors).",
    Selecto.except(employees_with_manager, employees_with_direct_reports)
  },
  {
    "EXCEPT (B - A)",
    "Employees who manage others but do NOT report to anyone (top-level leadership).",
    Selecto.except(employees_with_direct_reports, employees_with_manager)
  }
]

Enum.each(set_queries, fn {label, meaning, query} ->
  IO.puts("\n=== #{label} ===")
  IO.puts("Meaning: #{meaning}")

  {sql, params} = Selecto.to_sql(query)
  IO.puts(sql)
  IO.puts("Params: #{inspect(params)}")

  case SelectoExamples.LivebookHelpers.execute_with_checks(query) do
    {:ok, {rows, columns, _aliases}} ->
      IO.puts("Columns: #{inspect(columns)}")
      IO.puts("Row count: #{length(rows)}")
      Enum.each(Enum.take(rows, 10), fn row ->
        case row do
          [id, first_name, last_name, title] ->
            IO.puts("  ##{id} #{first_name} #{last_name} (#{title})")

          _ ->
            IO.puts("  #{inspect(row)}")
        end
      end)

    {:error, error} ->
      IO.puts("Execution error: #{inspect(error)}")
  end
end)

Section 12: CTEs (WITH and WITH RECURSIVE)

Non-Recursive CTE

Business question: Which managers have the largest direct teams, and what is each team’s total salary?

Equivalent plain SQL (for intuition):

WITH manager_team_metrics AS (
  SELECT manager_id, COUNT(*) AS direct_report_count, SUM(salary) AS team_salary_total
  FROM employees
  WHERE manager_id IS NOT NULL
  GROUP BY manager_id
)
SELECT e.first_name, e.last_name, e.title,
       m.direct_report_count, m.team_salary_total
FROM employees e
JOIN manager_team_metrics m ON m.manager_id = e.id
WHERE m.direct_report_count >= 2
ORDER BY m.direct_report_count DESC
LIMIT 10;

cte_query =
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.with_cte("manager_team_metrics", fn ->
    Selecto.configure(config.employee_domain, config.repo)
    |> Selecto.select([
      "manager_id",
      {:count, "*"},
      {:sum, "salary"}
    ])
    |> Selecto.filter({"manager_id", :not_null})
    |> Selecto.group_by(["manager_id"])
  end, columns: ["manager_id", "direct_report_count", "team_salary_total"])
  |> Selecto.join(:manager_team_metrics,
    source: "manager_team_metrics",
    type: :inner,
    owner_key: :id,
    related_key: :manager_id,
    fields: %{
      manager_id: %{type: :integer},
      direct_report_count: %{type: :integer},
      team_salary_total: %{type: :decimal}
    }
  )
  |> Selecto.select([
    "first_name",
    "last_name",
    "title",
    "manager_team_metrics.direct_report_count",
    "manager_team_metrics.team_salary_total"
  ])
  |> Selecto.filter({"manager_team_metrics.direct_report_count", {:>=, 2}})
  |> Selecto.order_by({"manager_team_metrics.direct_report_count", :desc})
  |> Selecto.limit(10)

{cte_sql, cte_params} = Selecto.to_sql(cte_query)
IO.puts("Generated SQL with WITH clause:\n")
IO.puts(cte_sql)
IO.puts("\nParams: #{inspect(cte_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(cte_query) do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nManager Team Metrics (from non-recursive CTE):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [mgr_first, mgr_last, title, report_count, team_salary_total] ->
      IO.puts(
        "  #{mgr_first} #{mgr_last} (#{title}) -> " <>
          "#{report_count} direct reports, total team salary $#{Decimal.round(team_salary_total || Decimal.new(0), 2)}"
      )
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Non-Recursive CTE):

• SQL includes WITH manager_team_metrics AS ( ... )
• SQL CTE body includes count(*) and sum(salary) grouped by manager_id
• SQL FROM is not empty
• SQL references manager_team_metrics in the main query (not only in the CTE definition)
• Params should include the team-size threshold (for this sample: [2])
• Rows should read as manager + direct report count + team salary

Recursive CTE

Business question: Starting from the VP of Sales, show every person in that reporting tree and their depth.

Equivalent plain SQL (for intuition):

WITH RECURSIVE employee_tree_sales (id, first_name, last_name, manager_id, depth) AS (
  SELECT id, first_name, last_name, manager_id, 0
  FROM employees
  WHERE email = 'sarah.connor@company.com'
  UNION ALL
  SELECT e.id, e.first_name, e.last_name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN employee_tree_sales t ON e.manager_id = t.id
)
SELECT t.depth, t.first_name, t.last_name, t.title, m.first_name, m.last_name
FROM employees e
JOIN employee_tree_sales t ON t.id = e.id
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY t.depth, t.last_name
LIMIT 15;

base_query = fn ->
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.select([
    "id",
    "first_name",
    "last_name",
    "manager_id",
    {:literal, 0}
  ])
  |> Selecto.filter({"email", "sarah.connor@company.com"})
end

recursive_query = fn _cte_ref ->
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.join(:employee_tree_sales,
    source: "employee_tree_sales",
    type: :inner,
    owner_key: :manager_id,
    related_key: :id,
    fields: %{
      id: %{type: :integer},
      first_name: %{type: :string},
      last_name: %{type: :string},
      manager_id: %{type: :integer},
      depth: %{type: :integer}
    }
  )
  |> Selecto.select([
    "id",
    "first_name",
    "last_name",
    "manager_id",
    {:raw_sql, "employee_tree_sales.depth + 1"}
  ])
  # Required to make recursive expansion depend on prior CTE rows.
  |> Selecto.filter({"employee_tree_sales.id", :not_null})
end

recursive_cte_query =
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.with_recursive_cte("employee_tree_sales",
    base_query: base_query,
    recursive_query: recursive_query,
    columns: ["id", "first_name", "last_name", "manager_id", "depth"]
  )
  |> Selecto.join(:employee_tree_sales,
    source: "employee_tree_sales",
    type: :inner,
    owner_key: :id,
    related_key: :id,
    fields: %{
      id: %{type: :integer},
      first_name: %{type: :string},
      last_name: %{type: :string},
      manager_id: %{type: :integer},
      depth: %{type: :integer}
    }
  )
  |> Selecto.select([
    "employee_tree_sales.depth",
    "employee_tree_sales.first_name",
    "employee_tree_sales.last_name",
    "title",
    "manager.first_name",
    "manager.last_name"
  ])
  |> Selecto.order_by({"employee_tree_sales.depth", :asc})
  |> Selecto.order_by({"employee_tree_sales.last_name", :asc})
  |> Selecto.limit(15)

{recursive_sql, recursive_params} = Selecto.to_sql(recursive_cte_query)
IO.puts("Generated SQL with WITH RECURSIVE:\n")
IO.puts(recursive_sql)
IO.puts("\nParams: #{inspect(recursive_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(recursive_cte_query) do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nSales Reporting Tree (recursive CTE):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [depth, emp_first, emp_last, title, mgr_first, mgr_last] ->
      manager_name = if mgr_first, do: "#{mgr_first} #{mgr_last}", else: "(No Manager)"
      IO.puts("  depth=#{depth} | #{emp_first} #{emp_last} (#{title}) -> Manager: #{manager_name}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Recursive CTE):

• SQL starts with WITH RECURSIVE employee_tree_sales AS (
• Recursive branch references the CTE via join: join employee_tree_sales ... on ...manager_id = ...id
• Main query selects from/joins employee_tree_sales (not just plain from employees)
• SQL FROM is not empty
• Params should include the anchor email (for this sample: ["sarah.connor@company.com"])
• Output should show depth=0 for Sarah Connor, then higher depths for her reports

Section 13: Window Functions

Rank Employees Within Their Department

Business question: Within each department, who are the top earners and what is the department average salary next to each row?

window_query =
  Selecto.configure(config.employee_domain, config.repo)
  |> Selecto.filter({"active", true})
  |> Selecto.select([
    "first_name",
    "last_name",
    "department",
    "salary"
  ])
  |> Selecto.window_function(:row_number, [],
    over: [partition_by: ["department"], order_by: [{"salary", :desc}]],
    as: "department_salary_rank"
  )
  |> Selecto.window_function(:avg, ["salary"],
    over: [partition_by: ["department"]],
    as: "department_avg_salary"
  )
  |> Selecto.order_by({"department", :asc})
  |> Selecto.order_by({"salary", :desc})
  |> Selecto.limit(15)

{window_sql, window_params} = Selecto.to_sql(window_query)
IO.puts("Generated SQL with window functions:\n")
IO.puts(window_sql)
IO.puts("\nParams: #{inspect(window_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(window_query) do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nDepartment Salary Ranking:")
    IO.puts("Columns: #{inspect(columns)}")

    Enum.each(rows, fn [first, last, dept, salary, rank, dept_avg] ->
      IO.puts(
        "  #{dept} | ##{rank} #{first} #{last} - " <>
          "salary=$#{Decimal.round(salary || Decimal.new(0), 2)} " <>
          "(dept avg=$#{Decimal.round(dept_avg || Decimal.new(0), 2)})"
      )
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Window Functions):

• SQL includes ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) for ranking
• SQL includes AVG(salary) OVER (PARTITION BY department) for per-department average
• Rank resets to 1 for the top salary in each department

Section 14: JSON Operations

Filter and Project Product Metadata (JSONB)

Business question: Which active products are in the premium metadata band, and what JSON metadata values drive that classification?

# Ensure metadata has meaningful JSON values for demonstration.
seed_result =
  config.repo.query!("""
  UPDATE products
  SET metadata = jsonb_build_object(
    'price_band',
    CASE
      WHEN price >= 100 THEN 'premium'
      WHEN price >= 50 THEN 'mid'
      ELSE 'budget'
    END,
    'warehouse',
    jsonb_build_object(
      'zone',
      CASE WHEN stock_quantity <= reorder_level THEN 'restock' ELSE 'in_stock' END,
      'bin',
      concat('A-', lpad((id % 25)::text, 2, '0'))
    ),
    'flags',
    jsonb_build_array(
      CASE WHEN active THEN 'active' ELSE 'inactive' END,
      CASE WHEN featured THEN 'featured' ELSE 'standard' END
    )
  )
  """)

IO.puts("Metadata rows refreshed for JSON example: #{seed_result.num_rows}")

json_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.price_band", as: "price_band"},
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"},
    {:json_extract_text, "metadata", "$.flags[0]", as: "primary_flag"}
  ])
  |> Selecto.json_filter({:json_contains, "metadata", %{"price_band" => "premium"}})
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"price", :desc})
  |> Selecto.json_order_by({:json_extract_text, "metadata", "$.warehouse.zone", :asc})
  |> Selecto.limit(10)

{json_sql, json_params} = Selecto.to_sql(json_query)
IO.puts("Generated SQL with JSON operations:\n")
IO.puts(json_sql)
IO.puts("\nParams: #{inspect(json_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(json_query) do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nPremium Products from JSON metadata:")
    IO.puts("Columns: #{inspect(columns)}")

    Enum.each(rows, fn [name, sku, price, price_band, warehouse_zone, primary_flag] ->
      IO.puts(
        "  #{name} (#{sku}) - $#{Decimal.round(price || Decimal.new(0), 2)} " <>
          "| price_band=#{price_band} | zone=#{warehouse_zone} | flag=#{primary_flag}"
      )
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (JSON Operations):

• SQL includes JSON extraction operators (-> / ->>) for metadata paths
• SQL includes JSON containment filter (metadata @> ...) for premium band
• Output rows show regular columns plus JSON-derived columns (price_band, warehouse_zone, primary_flag)

Section 15: Array Operations and UNNEST

Array Functions on Product Tags

Business question: Which products match target tag groups, and how many tags does each carry?

array_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "tags"])
  |> Selecto.array_select([
    {:array_length, "tags", 1, as: "tag_count"},
    {:array_to_string, "tags", ", ", as: "tag_list"}
  ])
  |> Selecto.array_filter({:array_overlap, "tags", ["electronics", "outdoor"]})
  |> Selecto.order_by({"name", :asc})
  |> Selecto.limit(10)

{array_sql, array_params} = Selecto.to_sql(array_query)
IO.puts("Generated SQL for Array Operations query:\n")
IO.puts(array_sql)
IO.puts("\nParams: #{inspect(array_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(array_query, example: "Array Operations Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nProducts matching array overlap filter:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, sku, tags, tag_count, tag_list] ->
      IO.puts("  #{name} (#{sku}) tags=#{inspect(tags)} | tag_count=#{tag_count} | tag_list=#{tag_list}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

UNNEST Product Tags into Row-Level Output

unnest_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select([
    "name",
    {:field, {:raw_sql, "product_tag"}, "product_tag"}
  ])
  |> Selecto.unnest("tags", as: "product_tag")
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"name", :asc})
  |> Selecto.limit(12)

{unnest_sql, unnest_params} = Selecto.to_sql(unnest_query)
IO.puts("Generated SQL for UNNEST query:\n")
IO.puts(unnest_sql)
IO.puts("\nParams: #{inspect(unnest_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(unnest_query, example: "UNNEST Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nProduct tags expanded to one row per tag:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [product_name, product_tag] ->
      IO.puts("  #{product_name} -> tag: #{product_tag}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Array + UNNEST):

• SQL includes ARRAY_LENGTH(...) and ARRAY_TO_STRING(...) in the array query
• SQL includes array overlap filter (&&) for tags
• UNNEST SQL includes CROSS JOIN LATERAL UNNEST(...)
• UNNEST output should show repeated product names with different tag values

Section 16: LATERAL Join

Explicit LATERAL Table Function

Business question: Show active products and each tag as a row, using explicit lateral_join/5.

lateral_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select([
    "name",
    {:field, {:raw_sql, "tag_expanded"}, "product_tag"}
  ])
  |> Selecto.filter({"active", true})
  |> Selecto.lateral_join(:inner, {:unnest, "selecto_root.tags"}, "tag_expanded")
  |> Selecto.order_by({"name", :asc})
  |> Selecto.limit(12)

{lateral_sql, lateral_params} = Selecto.to_sql(lateral_query)
IO.puts("Generated SQL with explicit LATERAL join:\n")
IO.puts(lateral_sql)
IO.puts("\nParams: #{inspect(lateral_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(lateral_query, example: "LATERAL Join Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nLATERAL result (product -> tag):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [product_name, product_tag] ->
      IO.puts("  #{product_name} -> #{product_tag}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (LATERAL Join):

• SQL includes JOIN LATERAL UNNEST(selecto_root.tags) AS tag_expanded ON true
• Main query FROM remains the base table while lateral rows are expanded per base row
• Output should mirror row-expansion behavior (multiple tags per product)

Section 17: CASE Expressions

Classify Order Size Tiers in SQL

Business question: Label each order as high_value, medium_value, or low_value from total amount.

case_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "status", "total"])
  |> Selecto.case_when_select([
    {[{"total", {:>=, Decimal.new("1000")}}], "high_value"},
    {[{"total", {:>=, Decimal.new("500")}}], "medium_value"},
    {[{"total", {:<, Decimal.new("500")}}], "low_value"}
  ], else: "unknown", as: "order_size")
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(10)

{case_sql, case_params} = Selecto.to_sql(case_query)
IO.puts("Generated SQL with CASE expression:\n")
IO.puts(case_sql)
IO.puts("\nParams: #{inspect(case_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(case_query, example: "CASE Expression Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrder size classification:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, status, total, order_size] ->
      IO.puts("  #{order_number} (#{status}) total=$#{Decimal.round(total || Decimal.new(0), 2)} -> #{order_size}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (CASE Expressions):

• SQL includes CASE WHEN ... THEN ... ELSE ... END AS order_size
• Params include threshold values (1000 and 500) and labels
• Output shows computed order_size alongside each order row

Section 18: VALUES Clause (Inline Lookup Table)

Join Orders to an Inline Status Mapping

Business question: Can we map raw order status values to display labels without creating a physical lookup table?

values_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.with_values(
    [
      ["processing", "In Progress"],
      ["shipped", "In Transit"],
      ["delivered", "Completed"],
      ["cancelled", "Cancelled"]
    ],
    columns: ["status", "status_label"],
    as: "status_labels"
  )
  |> Selecto.join(:status_labels,
    source: "status_labels",
    type: :left,
    owner_key: :status,
    related_key: :status,
    fields: %{
      status: %{type: :string},
      status_label: %{type: :string}
    }
  )
  |> Selecto.select(["order_number", "status", "status_labels.status_label", "total"])
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(10)

{values_sql, values_params} = Selecto.to_sql(values_query)
IO.puts("Generated SQL with VALUES clause:\n")
IO.puts(values_sql)
IO.puts("\nParams: #{inspect(values_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(values_query, example: "VALUES Clause Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrders with inline status labels:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, status, status_label, total] ->
      IO.puts("  #{order_number}: status=#{status} (#{status_label}) total=$#{Decimal.round(total || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (VALUES Clause):

• SQL starts with WITH status_labels ("status", "status_label") AS (VALUES ...)
• Main query joins the inline CTE via left join status_labels ... on status_labels.status = selecto_root.status
• Params should be [] for this literal lookup example
• Output includes status plus the mapped status_label

Section 19: Multiple CTEs with with_ctes/2

Combine High-Value Orders with Customer Order Frequency

Business question: Which high-value orders come from customers with repeat purchase activity?

alias Selecto.Advanced.CTE

high_value_orders_cte =
  CTE.create_cte("high_value_orders", fn ->
    Selecto.configure(config.order_domain, config.repo)
    |> Selecto.select(["id", "customer_id", "total"])
    |> Selecto.filter({"total", {:>, 500}})
  end)

customer_order_counts_cte =
  CTE.create_cte("customer_order_counts", fn ->
    Selecto.configure(config.order_domain, config.repo)
    |> Selecto.select(["customer_id", {:count, "*"}])
    |> Selecto.group_by(["customer_id"])
  end)

multi_cte_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.with_ctes([high_value_orders_cte, customer_order_counts_cte])
  |> Selecto.join(:high_value_orders,
    source: "high_value_orders",
    type: :inner,
    owner_key: :id,
    related_key: :id,
    fields: %{
      total: %{type: :decimal},
      customer_id: %{type: :integer}
    }
  )
  |> Selecto.join(:customer_order_counts,
    source: "customer_order_counts",
    type: :inner,
    owner_key: :customer_id,
    related_key: :customer_id,
    fields: %{
      customer_id: %{type: :integer},
      count: %{type: :integer}
    }
  )
  |> Selecto.select([
    "order_number",
    "status",
    "high_value_orders.total",
    "customer_order_counts.count"
  ])
  |> Selecto.filter({"customer_order_counts.count", {:>=, 2}})
  |> Selecto.order_by({"high_value_orders.total", :desc})
  |> Selecto.limit(10)

{multi_cte_sql, multi_cte_params} = Selecto.to_sql(multi_cte_query)
IO.puts("Generated SQL with multiple CTEs:\n")
IO.puts(multi_cte_sql)
IO.puts("\nParams: #{inspect(multi_cte_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(multi_cte_query, example: "Multiple CTE Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nHigh-value orders from repeat customers:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, status, total, repeat_count] ->
      IO.puts("  #{order_number} (#{status}) total=$#{Decimal.round(total || Decimal.new(0), 2)} | customer_orders=#{repeat_count}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Multiple CTEs):

• SQL starts with WITH ... and contains both high_value_orders AS (...) and customer_order_counts AS (...)
• Main query consumes both CTEs through explicit joins
• Output includes order amount and per-customer order frequency

Section 20: Array Manipulation (array_manipulate/3)

Transform Product Tags for Display and Downstream Use

Business question: How can we derive multiple array-based tag projections in one query?

array_manipulate_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "tags"])
  |> Selecto.array_manipulate([
    {:array_append, "tags", "featured_livebook", [as: "tags_plus_featured"]},
    {:array_remove, "tags", "outdoor", [as: "tags_without_outdoor"]},
    {:array_to_string, "tags", " | ", [as: "tags_pipe"]}
  ])
  |> Selecto.filter({"active", true})
  |> Selecto.limit(10)

{array_manipulate_sql, array_manipulate_params} = Selecto.to_sql(array_manipulate_query)
IO.puts("Generated SQL with array manipulation:\n")
IO.puts(array_manipulate_sql)
IO.puts("\nParams: #{inspect(array_manipulate_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(array_manipulate_query, example: "Array Manipulation Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nArray manipulation sample rows:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, tags, tags_plus, tags_without_outdoor, tags_pipe] ->
      IO.puts("  #{name}")
      IO.puts("    tags=#{inspect(tags)}")
      IO.puts("    tags_plus_featured=#{inspect(tags_plus)}")
      IO.puts("    tags_without_outdoor=#{inspect(tags_without_outdoor)}")
      IO.puts("    tags_pipe=#{tags_pipe}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Array Manipulation):

• SQL includes ARRAY_APPEND, ARRAY_REMOVE, and ARRAY_TO_STRING
• Params contain appended value, removed value, delimiter, and active filter
• Output shows original and transformed tag projections side by side

Section 21: Execution Helpers (execute_with_metadata/2, execute_one/2)

Get SQL/Timing Metadata and Enforce Single-Row Semantics

Business question: How do we inspect runtime SQL details and safely assert one-row results?

metadata_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "status", "total"])
  |> Selecto.filter({"status", "delivered"})
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(3)

IO.puts("execute_with_metadata/2 demo:\n")
case Selecto.execute_with_metadata(metadata_query) do
  {:ok, {rows, columns, _aliases}, metadata} ->
    IO.puts("Columns: #{inspect(columns)}")
    IO.puts("Row count: #{length(rows)}")
    IO.puts("Metadata keys: #{inspect(Map.keys(metadata))}")
    IO.puts("Execution time (ms): #{inspect(metadata[:execution_time])}")
    IO.puts("Params: #{inspect(metadata[:params])}")
    IO.puts("SQL preview:\n#{metadata[:sql]}")

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

one_row_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "status", "total"])
  |> Selecto.filter({"status", "delivered"})
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(1)

IO.puts("\nexecute_one/2 success case:\n")
case Selecto.execute_one(one_row_query) do
  {:ok, {row, aliases}} ->
    IO.puts("Single row: #{inspect(row)}")
    IO.puts("Aliases: #{inspect(aliases)}")

  {:error, error} ->
    IO.puts("Unexpected error: #{inspect(error)}")
end

many_rows_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "status", "total"])
  |> Selecto.filter({"status", "delivered"})
  |> Selecto.limit(2)

IO.puts("\nexecute_one/2 multiple-rows guard:\n")
case Selecto.execute_one(many_rows_query) do
  {:ok, {row, _aliases}} ->
    IO.puts("Unexpected single row: #{inspect(row)}")

  {:error, error} ->
    IO.puts("Expected error: #{inspect(error)}")
end

Expected output checks (Execution Helpers):

• execute_with_metadata/2 returns query result plus metadata with :sql, :params, and :execution_time
• execute_one/2 returns {:ok, {row, aliases}} when exactly one row is returned
• execute_one/2 returns an error when multiple rows are returned

Section 22: Dynamic Subquery Joins (join_subquery/4)

Join Customers to a Filtered Order Subquery

Business question: Which customers have delivered orders above $1000, and which order rows are those?

high_value_delivered_orders =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["customer_id", "order_number", "total"])
  |> Selecto.filter({:and, [
    {"status", "delivered"},
    {"total", {:>, 1000}}
  ]})

customer_domain = Map.put_new(config.customer_domain, :schemas, %{})

join_subquery_query =
  Selecto.configure(customer_domain, config.repo)
  |> Selecto.join_subquery(:high_value_delivered, high_value_delivered_orders,
    type: :inner,
    on: [%{left: "id", right: "customer_id"}]
  )
  |> Selecto.select([
    "name",
    "tier",
    "high_value_delivered.order_number",
    "high_value_delivered.total"
  ])
  |> Selecto.limit(10)

{join_subquery_sql, join_subquery_params} = Selecto.to_sql(join_subquery_query)
IO.puts("Generated SQL with dynamic subquery join:\n")
IO.puts(join_subquery_sql)
IO.puts("\nParams: #{inspect(join_subquery_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(join_subquery_query, example: "Dynamic Subquery Join Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nCustomers with high-value delivered orders:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, tier, order_number, total] ->
      IO.puts("  #{name} (#{tier}) -> #{order_number} total=$#{Decimal.round(total || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Dynamic Subquery Join):

• SQL contains inner join ( ...subquery... ) high_value_delivered on selecto_root.id = high_value_delivered.customer_id
• Subquery filters are parameterized and appear in Params (expected: "delivered" and 1000)
• Output includes customer identity plus the matching high-value order row

Section 23: Parameterized Join Instances (join_parameterize/4)

Materialize the Same Join Twice with Independent Aliases

Business question: Can we project the same relationship twice with separate join aliases?

parameterized_join_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.join_parameterize(:customer, "alias_a")
  |> Selecto.join_parameterize(:customer, "alias_b")
  |> Selecto.select([
    "order_number",
    "customer:alias_a.name",
    "customer:alias_b.tier"
  ])
  |> Selecto.limit(50)

{parameterized_join_sql, parameterized_join_params} = Selecto.to_sql(parameterized_join_query)
IO.puts("Generated SQL with parameterized join aliases:\n")
IO.puts(parameterized_join_sql)
IO.puts("\nParams: #{inspect(parameterized_join_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(parameterized_join_query, example: "Parameterized Join Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrders with independently aliased customer projections:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, name_alias_a, tier_alias_b] ->
      IO.puts("  #{order_number} -> alias_a.name=#{name_alias_a}, alias_b.tier=#{tier_alias_b}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Parameterized Join Instances):

• SQL includes two aliased joins, e.g. "customer:alias_a" and "customer:alias_b"
• Dot-notation fields customer:alias_a.name and customer:alias_b.tier resolve successfully
• Output shows both projections selected from the independently aliased join instances

Same-Join Filtered Aliases (lf1/lf2-style pattern)

Business question: Can we generate two filtered aliases from the same base join (equivalent to lf1.type = 1 and lf2.type = 2)?

# Enable parameterized filtering on the base join by declaring filterable fields.
order_domain_with_customer_filters =
  put_in(config.order_domain, [:joins, :customer, :filters], %{
    "tier" => %{type: "string"}
  })

tier_parameterized_query =
  Selecto.configure(order_domain_with_customer_filters, config.repo)
  |> Selecto.join_parameterize(:customer, "tier_premium", tier: "premium")
  |> Selecto.join_parameterize(:customer, "tier_standard", tier: "standard")
  |> Selecto.select([
    "order_number",
    "customer:tier_premium.name",
    "customer:tier_standard.name"
  ])
  |> Selecto.limit(25)

{tier_parameterized_sql, tier_parameterized_params} = Selecto.to_sql(tier_parameterized_query)

IO.puts("Generated SQL with filtered parameterized aliases:\n")
IO.puts(tier_parameterized_sql)
IO.puts("\nParams: #{inspect(tier_parameterized_params)}")

premium_on? = String.contains?(tier_parameterized_sql, "\"customer:tier_premium\".tier = $1")
standard_on? = String.contains?(tier_parameterized_sql, "\"customer:tier_standard\".tier = $2")

IO.puts("\nON-clause checks:")
IO.puts("  premium alias filter present? #{premium_on?}")
IO.puts("  standard alias filter present? #{standard_on?}")

case SelectoExamples.LivebookHelpers.execute_with_checks(tier_parameterized_query, example: "Parameterized Join Filter Wiring Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrders with tier-filtered customer aliases:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, premium_name, standard_name] ->
      IO.puts("  #{order_number} -> premium_alias=#{inspect(premium_name)}, standard_alias=#{inspect(standard_name)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Same-Join Filtered Aliases):

• SQL includes "customer:tier_premium" and "customer:tier_standard" aliases
• SQL ON clause contains alias-specific filters:
  • "customer:tier_premium".tier = $1
  • "customer:tier_standard".tier = $2
• Params are ordered as ["premium", "standard"]

Section 24: Dynamic Custom Join (join/3 with non-association source)

Attach Product Reviews Without Predeclaring a Domain Join

Business question: Can we ad-hoc join products to reviews even if that relationship is not preconfigured in the domain joins?

custom_join_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.join(:reviews,
    source: "reviews",
    type: :left,
    owner_key: :id,
    related_key: :product_id,
    fields: %{
      rating: %{type: :integer},
      title: %{type: :string},
      helpful_count: %{type: :integer}
    }
  )
  |> Selecto.select([
    "name",
    "reviews.rating",
    "reviews.title",
    "reviews.helpful_count"
  ])
  |> Selecto.filter({:and, [
    {"active", true},
    {"reviews.rating", {:>=, 4}}
  ]})
  |> Selecto.order_by({"reviews.rating", :desc})
  |> Selecto.limit(10)

{custom_join_sql, custom_join_params} = Selecto.to_sql(custom_join_query)
IO.puts("Generated SQL with dynamic custom join:\n")
IO.puts(custom_join_sql)
IO.puts("\nParams: #{inspect(custom_join_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(custom_join_query, example: "Dynamic Custom Join Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nHighly-rated product reviews (dynamic join):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, rating, title, helpful_count] ->
      IO.puts("  #{name} -> rating=#{rating}, title=#{inspect(title)}, helpful=#{helpful_count}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Dynamic Custom Join):

• SQL includes left join reviews reviews on reviews.product_id = selecto_root.id
• Params include both base filter (active = true) and joined-table filter (reviews.rating >= 4)
• Output includes product fields plus review columns from the ad-hoc join

Section 25: Output Formats via Execution Options

Return Maps, JSON, and CSV Directly from Selecto.execute/2

Business question: Can we get formatted payloads directly from execution instead of manually transforming {rows, columns, aliases}?

format_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"name", :asc})
  |> Selecto.limit(5)

IO.puts("Maps format (expected: one map per row, keyed by selected columns):")
case Selecto.execute(format_query, format: :maps) do
  {:ok, maps} ->
    Enum.each(maps, fn row_map ->
      IO.puts("  #{row_map["name"]} (#{row_map["sku"]}) -> $#{row_map["price"]}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

IO.puts("\nJSON format (expected: JSON array with name/sku/price fields):")
case Selecto.execute(format_query, format: {:json, [pretty: true]}) do
  {:ok, json_string} ->
    IO.puts(json_string)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

IO.puts("\nCSV format (expected: header row plus five product rows):")
case Selecto.execute(format_query, format: :csv) do
  {:ok, csv_string} ->
    IO.puts(csv_string)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

IO.puts("\nMaps format + metadata (expected: maps + sql/params/execution_time):")
case Selecto.execute_with_metadata(format_query, format: :maps) do
  {:ok, maps, metadata} ->
    IO.puts("Row count: #{length(maps)}")
    IO.puts("Execution time (ms): #{metadata[:execution_time]}")
    IO.puts("Params: #{inspect(metadata[:params])}")
    IO.puts("SQL:\n#{metadata[:sql]}")

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Execution Format Options):

• format: :maps returns a list of maps keyed by selected columns
• format: {:json, ...} returns a JSON string payload
• format: :csv returns CSV text with headers
• execute_with_metadata/2 still returns :sql, :params, and :execution_time with formatted output

Section 26: Additional LATERAL Join Variants

Subquery Builder and Table Function Forms

Business question: How do we use lateral_join/5 beyond UNNEST?

# Variant A: Subquery builder form
lateral_subquery_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select([
    "name",
    "category.name",
    {:field, {:raw_sql, "category_stats.count"}, "active_products_in_category"}
  ])
  |> Selecto.filter({"active", true})
  |> Selecto.lateral_join(:left,
    fn _base_query ->
      Selecto.configure(config.product_domain, config.repo)
      |> Selecto.select([{:count, "*"}])
      |> Selecto.filter({"active", true})
      |> Selecto.filter({:raw_sql_filter, "selecto_root.category_id = category.id"})
    end,
    "category_stats"
  )
  |> Selecto.order_by({"name", :asc})
  |> Selecto.limit(5)

{lateral_subquery_sql, lateral_subquery_params} = Selecto.to_sql(lateral_subquery_query)
IO.puts("Generated SQL (LATERAL subquery form):\n")
IO.puts(lateral_subquery_sql)
IO.puts("\nParams: #{inspect(lateral_subquery_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(lateral_subquery_query, example: "LATERAL Subquery Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nProducts with active-product counts for their category:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, category_name, active_products_in_category] ->
      IO.puts("  #{name} (#{category_name}) -> active_products_in_category=#{active_products_in_category}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

# Variant B: Table function form (generate_series)
generate_series_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select([
    "id",
    "name",
    {:field, {:raw_sql, "period_slot"}, "period_slot"}
  ])
  |> Selecto.filter({"active", true})
  |> Selecto.filter({"id", {:<=, 3}})
  |> Selecto.lateral_join(:inner,
    {:function, :generate_series, [1, "selecto_root.id"]},
    "period_slot"
  )
  |> Selecto.order_by({"id", :asc})
  |> Selecto.limit(12)

{generate_series_sql, generate_series_params} = Selecto.to_sql(generate_series_query)
IO.puts("\nGenerated SQL (LATERAL function form):\n")
IO.puts(generate_series_sql)
IO.puts("\nParams: #{inspect(generate_series_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(generate_series_query, example: "LATERAL Function Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nLATERAL generate_series rows (values from 1..product_id for id <= 3):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [id, name, period_slot] ->
      IO.puts("  id=#{id} #{name} -> period_slot=#{period_slot}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Additional LATERAL Variants):

• Subquery form emits LEFT JOIN LATERAL ( ... ) AS category_stats ON true and references category.id in the subquery filter
• Function form emits INNER JOIN LATERAL GENERATE_SERIES(...) AS period_slot ON true
• Params include lateral inputs and outer filters in stable order

Section 27: Bootstrap from Ecto (from_ecto/3)

Generate a Selecto Domain from an Ecto Schema

Business question: Can we start from Ecto schemas instead of hand-authoring a full Selecto domain map?

defmodule SelectoExamples.LivebookSchemas.Category do
  use Ecto.Schema

  @primary_key {:id, :id, autogenerate: false}
  schema "categories" do
    field :name, :string
  end
end

defmodule SelectoExamples.LivebookSchemas.Supplier do
  use Ecto.Schema

  @primary_key {:id, :id, autogenerate: false}
  schema "suppliers" do
    field :company_name, :string
  end
end

defmodule SelectoExamples.LivebookSchemas.Product do
  use Ecto.Schema

  @primary_key {:id, :id, autogenerate: false}
  schema "products" do
    field :name, :string
    field :sku, :string
    field :price, :decimal
    field :active, :boolean

    belongs_to :category, SelectoExamples.LivebookSchemas.Category
    belongs_to :supplier, SelectoExamples.LivebookSchemas.Supplier
  end
end

ecto_selecto =
  Selecto.from_ecto(
    config.repo,
    SelectoExamples.LivebookSchemas.Product,
    joins: [:category, :supplier]
  )

IO.puts("Available fields sample from Ecto bootstrap:")
ecto_selecto
|> Selecto.available_fields()
|> Enum.map(fn
  {field_name, _meta} when is_binary(field_name) -> field_name
  {field_name, _meta} when is_atom(field_name) -> Atom.to_string(field_name)
  field_name when is_binary(field_name) -> field_name
  field_name when is_atom(field_name) -> Atom.to_string(field_name)
  other -> inspect(other)
end)
|> Enum.sort()
|> Enum.take(20)
|> Enum.each(fn field -> IO.puts("  #{field}") end)

from_ecto_query =
  ecto_selecto
  |> Selecto.select(["name", "sku", "price", "category.name", "supplier.company_name"])
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(10)

{from_ecto_sql, from_ecto_params} = Selecto.to_sql(from_ecto_query)
IO.puts("\nGenerated SQL from from_ecto/3 domain:\n")
IO.puts(from_ecto_sql)
IO.puts("\nParams: #{inspect(from_ecto_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(from_ecto_query, example: "from_ecto Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nTop products from Ecto-bootstrapped Selecto:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, sku, price, category_name, supplier_name] ->
      IO.puts("  #{name} (#{sku}) $#{Decimal.round(price || Decimal.new(0), 2)} | #{category_name} | #{supplier_name}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (from_ecto Bootstrap):

• Selecto.from_ecto/3 creates a usable Selecto instance from Product schema metadata
• Joined fields from Ecto associations (category.name, supplier.company_name) resolve
• Generated SQL includes the expected joins and filter/order/limit clauses

Section 28: Simple CASE (case_select/4)

Bucket Order Status with Value-to-Label Mapping

Business question: Can we map one column’s discrete values to labels using a simple CASE WHEN ... THEN ... expression?

simple_case_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "status", "total"])
  |> Selecto.case_select("status", [
    {"processing", "Open"},
    {"shipped", "In Transit"},
    {"delivered", "Closed"},
    {"cancelled", "Closed"}
  ], else: "Other", as: "status_bucket")
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(10)

{simple_case_sql, simple_case_params} = Selecto.to_sql(simple_case_query)
IO.puts("Generated SQL with simple CASE expression:\n")
IO.puts(simple_case_sql)
IO.puts("\nParams: #{inspect(simple_case_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(simple_case_query, example: "Simple CASE Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nSimple CASE results (status -> status_bucket):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, status, total, status_bucket] ->
      IO.puts("  #{order_number} status=#{status} total=$#{Decimal.round(total || Decimal.new(0), 2)} -> #{status_bucket}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Simple CASE):

• SQL includes CASE status WHEN ... THEN ... ELSE ... END AS status_bucket
• Params include value/result pairs from the mapping list plus else value
• Output includes original status and computed status_bucket

Section 29: with_values/3 with Map Rows

Inline Status Metadata Using Inferred Columns

Business question: Can we provide VALUES data as maps and let Selecto infer the column list automatically?

values_map_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.with_values(
    [
      %{status: "processing", priority_rank: 3, customer_label: "Open"},
      %{status: "shipped", priority_rank: 2, customer_label: "On The Way"},
      %{status: "delivered", priority_rank: 1, customer_label: "Complete"},
      %{status: "cancelled", priority_rank: 4, customer_label: "Cancelled"}
    ],
    as: "status_priority"
  )
  |> Selecto.join(:status_priority,
    source: "status_priority",
    type: :left,
    owner_key: :status,
    related_key: :status,
    fields: %{
      status: %{type: :string},
      priority_rank: %{type: :integer},
      customer_label: %{type: :string}
    }
  )
  |> Selecto.select([
    "order_number",
    "status",
    "status_priority.priority_rank",
    "status_priority.customer_label"
  ])
  |> Selecto.order_by({"status_priority.priority_rank", :asc})
  |> Selecto.limit(10)

{values_map_sql, values_map_params} = Selecto.to_sql(values_map_query)
IO.puts("Generated SQL with map-row VALUES clause:\n")
IO.puts(values_map_sql)
IO.puts("\nParams: #{inspect(values_map_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(values_map_query, example: "VALUES Map-Row Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrders with inferred map-row status metadata:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, status, priority_rank, customer_label] ->
      IO.puts("  #{order_number}: status=#{status} priority=#{priority_rank} label=#{customer_label}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Map-Row VALUES):

• SQL starts with WITH status_priority (...) AS (VALUES ...)
• Column names are inferred from map keys (deterministic sorted order in generated SQL)
• Output includes joined fields from the inferred VALUES table

Section 30: Explicit join/3 with on: Conditions

Custom Join Conditions Without Owner/Related Key Pairing

Business question: Can we define join predicates explicitly with on: [%{left: ..., right: ...}] when adding custom joins?

join_on_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.join(:customer_lookup,
    source: "customers",
    type: :inner,
    on: [%{left: "customer_id", right: "id"}],
    fields: %{
      name: %{type: :string},
      tier: %{type: :string}
    }
  )
  |> Selecto.select(["order_number", "customer_lookup.name", "customer_lookup.tier", "total"])
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(10)

{join_on_sql, join_on_params} = Selecto.to_sql(join_on_query)
IO.puts("Generated SQL with explicit on: join conditions:\n")
IO.puts(join_on_sql)
IO.puts("\nParams: #{inspect(join_on_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(join_on_query, example: "Explicit on: Join Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrders with explicit on: customer lookup join:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, customer_name, customer_tier, total] ->
      IO.puts("  #{order_number} -> #{customer_name} (#{customer_tier}) total=$#{Decimal.round(total || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Explicit on:):

• SQL includes ... join customers customer_lookup on selecto_root.customer_id = customer_lookup.id
• The join is built from on: conditions without relying on inferred key names
• Dot-notation fields from the custom join (customer_lookup.name, customer_lookup.tier) resolve

Section 31: Advanced Execution Formats (typed_maps, stream)

Type-Coerced Maps and Streaming Results

Business question: Beyond maps/json/csv, can we return typed maps and lazily-consumed streams from execution?

advanced_format_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "price", "active"])
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"name", :asc})
  |> Selecto.limit(5)

IO.puts("Typed maps format:")
case Selecto.execute(advanced_format_query, format: {:typed_maps, [keys: :strings, coerce: :safe]}) do
  {:ok, typed_maps} ->
    Enum.each(typed_maps, fn row_map ->
      IO.puts("  #{row_map["name"]} -> price=#{inspect(row_map["price"])} active=#{inspect(row_map["active"])}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

IO.puts("\nStream format (maps):")
case Selecto.execute(advanced_format_query, format: {:stream, :maps}) do
  {:ok, row_stream} ->
    first_rows = Enum.take(row_stream, 3)
    IO.puts("First 3 streamed rows:")
    Enum.each(first_rows, fn row_map ->
      IO.puts("  #{inspect(row_map)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (Advanced Formats):

• format: {:typed_maps, ...} returns maps with configured coercion behavior
• format: {:stream, :maps} returns a stream that can be consumed lazily (Enum.take/2)
• Streamed map keys resolve to selected field names (not synthetic alias UUIDs)

Section 32: Domain-Level Join Patterns (join_type)

Configure OLAP and Hierarchical Behavior in Domain Joins

Business question: How do we express advanced join intent at the domain layer (OLAP and hierarchy), separate from raw recursive SQL?

# Variant A: OLAP star-dimension join type (runnable query)
olap_domain =
  put_in(config.order_domain, [:joins, :customer, :type], :star_dimension)

olap_query =
  Selecto.configure(olap_domain, config.repo)
  |> Selecto.select(["customer.name", {:count, "*"}, {:sum, "total"}])
  |> Selecto.group_by(["customer.name"])
  |> Selecto.order_by({{:sum, "total"}, :desc})
  |> Selecto.limit(5)

{olap_sql, olap_params} = Selecto.to_sql(olap_query)
IO.puts("Generated SQL with star_dimension join type:\n")
IO.puts(olap_sql)
IO.puts("\nParams: #{inspect(olap_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(olap_query, example: "OLAP join_type Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nTop customers by revenue (star_dimension join type):")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [customer_name, order_count, total_revenue] ->
      IO.puts("  #{customer_name}: orders=#{order_count}, total=$#{Decimal.round(total_revenue || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

# Variant B: Hierarchical join type metadata (domain-level configuration inspection)
hierarchy_domain =
  config.employee_domain
  |> put_in([:source, :associations, :manager_tree], %{
    field: :manager_tree,
    queryable: :employees,
    owner_key: :manager_id,
    related_key: :id
  })
  |> put_in([:joins, :manager_tree], %{
    type: :hierarchical,
    hierarchy_type: :adjacency_list,
    depth_limit: 6,
    parent_field: "manager_id",
    name_field: "first_name"
  })

hierarchy_selecto = Selecto.configure(hierarchy_domain, config.repo)
manager_tree_join = Selecto.joins(hierarchy_selecto)[:manager_tree]

IO.puts("\nHierarchical join metadata preview:")
IO.puts("  join_type: #{inspect(manager_tree_join.join_type)}")
IO.puts("  source: #{inspect(manager_tree_join.source)}")
IO.puts("  hierarchy_depth: #{inspect(Map.get(manager_tree_join, :hierarchy_depth))}")

hierarchy_fields =
  hierarchy_selecto
  |> Selecto.available_fields()
  |> Enum.map(fn
    {field_name, _meta} when is_binary(field_name) -> field_name
    {field_name, _meta} when is_atom(field_name) -> Atom.to_string(field_name)
    field_name when is_binary(field_name) -> field_name
    field_name when is_atom(field_name) -> Atom.to_string(field_name)
    other -> inspect(other)
  end)
  |> Enum.filter(&String.contains?(&1, "manager_tree"))
  |> Enum.take(12)

IO.puts("  Sample hierarchical fields:")
Enum.each(hierarchy_fields, fn field -> IO.puts("    #{field}") end)

Expected output checks (Domain-Level Join Patterns):

• :star_dimension join type generates and executes an OLAP-friendly aggregate query
• Join metadata reflects the configured advanced marker (:hierarchical_adjacency) for hierarchy joins
• Hierarchical field set is discoverable from the configured domain (without hand-written recursive SQL in this section)

Section 33: Domain and Field Introspection Helpers

Inspect Domain Metadata and Resolve Fields Programmatically

Business question: How can a UI or service layer discover fields/joins/types without hardcoding schema details?

introspection_selecto = Selecto.configure(config.product_domain, config.repo)

source_table = Selecto.source_table(introspection_selecto)
join_ids = introspection_selecto |> Selecto.joins() |> Map.keys()
column_count = introspection_selecto |> Selecto.columns() |> map_size()
filter_count =
  case Selecto.filters(introspection_selecto) do
    filters when is_map(filters) -> map_size(filters)
    _ -> 0
  end

price_field = Selecto.field(introspection_selecto, "price")
resolved_category_name = Selecto.resolve_field(introspection_selecto, "category.name")
suggested_fields = Selecto.field_suggestions(introspection_selecto, "cat")

available_field_preview =
  introspection_selecto
  |> Selecto.available_fields()
  |> Enum.take(8)

domain_data = Selecto.domain_data(introspection_selecto)

domain_name =
  case domain_data do
    %{name: name} -> name
    %{"name" => name} -> name
    _ -> "N/A"
  end

query_set_keys =
  case Selecto.set(introspection_selecto) do
    query_set when is_map(query_set) ->
      query_set
      |> Map.keys()
      |> Enum.take(10)

    _ ->
      []
  end

IO.puts("Domain name: #{domain_name}")
IO.puts("Source table: #{source_table}")
IO.puts("Join ids: #{inspect(join_ids)}")
IO.puts("Column count: #{column_count}")
IO.puts("Filter count: #{filter_count}")
IO.puts("Field config for `price`: #{inspect(price_field)}")
IO.puts("Resolved `category.name`: #{inspect(resolved_category_name)}")
IO.puts("Field suggestions for `cat`: #{inspect(suggested_fields)}")
IO.puts("Available fields preview: #{inspect(available_field_preview)}")
IO.puts("Query set keys preview: #{inspect(query_set_keys)}")

Expected output checks (Introspection Helpers):

• Domain/source metadata is discoverable via Selecto helper APIs
• resolve_field/2 returns a structured success/error tuple
• field_suggestions/2 and available_fields/1 provide UX-friendly discovery data

Section 34: Type Inference and Compatibility Helpers

Validate Expression Types Before Executing Queries

Business question: How can we preflight types for dynamic query builders (for example, UI condition builders)?

type_selecto = Selecto.configure(config.product_domain, config.repo)

{:ok, price_type} = Selecto.infer_type(type_selecto, "price")
{:ok, count_type} = Selecto.infer_type(type_selecto, {:count, "*"})
{:ok, sum_type} = Selecto.infer_type(type_selecto, {:sum, "price"})
{:ok, literal_type} = Selecto.infer_type(type_selecto, {:literal, true})

numeric_compat = Selecto.types_compatible?(:integer, :decimal)
string_bool_compat = Selecto.types_compatible?(:string, :boolean)

price_category = Selecto.type_category(price_type)
count_category = Selecto.type_category(count_type)

IO.puts("Inferred type of `price`: #{inspect(price_type)} (category: #{inspect(price_category)})")
IO.puts("Inferred type of `count(*)`: #{inspect(count_type)} (category: #{inspect(count_category)})")
IO.puts("Inferred type of `sum(price)`: #{inspect(sum_type)}")
IO.puts("Inferred type of literal `true`: #{inspect(literal_type)}")
IO.puts("Type compatible? integer vs decimal -> #{inspect(numeric_compat)}")
IO.puts("Type compatible? string vs boolean -> #{inspect(string_bool_compat)}")

Expected output checks (Type Helpers):

• infer_type/2 returns concrete SQL-oriented types for fields and expressions
• types_compatible?/2 supports safe comparison checks for dynamic filter builders
• type_category/1 classifies inferred types for UI/operator selection

Section 35: SQL Builder API (gen_sql/2) vs to_sql/1

Access SQL, Params, and Alias Metadata Directly

Business question: When should we use gen_sql/2 instead of to_sql/1?

gen_sql_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "total", "customer.tier"])
  |> Selecto.filter({"status", "delivered"})
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(5)

{gen_sql_sql, gen_sql_aliases, gen_sql_params} = Selecto.gen_sql(gen_sql_query, [])
{to_sql_sql, to_sql_params} = Selecto.to_sql(gen_sql_query)

normalize_sql = fn sql -> sql |> String.replace(~r/\s+/, " ") |> String.trim() end

alias_count =
  cond do
    is_map(gen_sql_aliases) -> map_size(gen_sql_aliases)
    is_list(gen_sql_aliases) -> length(gen_sql_aliases)
    true -> 0
  end

IO.puts("gen_sql SQL:\n#{gen_sql_sql}")
IO.puts("\ngen_sql params: #{inspect(gen_sql_params)}")
IO.puts("gen_sql alias count: #{alias_count}")
IO.puts("to_sql params: #{inspect(to_sql_params)}")
IO.puts("SQL text equivalent? #{normalize_sql.(gen_sql_sql) == normalize_sql.(to_sql_sql)}")
IO.puts("Params equivalent? #{gen_sql_params == to_sql_params}")

case SelectoExamples.LivebookHelpers.execute_with_checks(gen_sql_query, example: "gen_sql vs to_sql Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nExecuted rows:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, total, tier] ->
      IO.puts("  #{order_number} -> total=$#{Decimal.round(total || Decimal.new(0), 2)} tier=#{tier}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (gen_sql/2 vs to_sql/1):

• gen_sql/2 returns SQL + params + alias metadata
• to_sql/1 returns SQL + params for direct inspection and debugging
• SQL and params are equivalent for the same query definition

Section 36: Advanced Filter Operators (:subquery, :text_search)

Use SQL Subqueries and Full-Text Search from Selecto.filter/2

Business question: How do we express subquery membership and text search using typed filter operators?

subquery_filter_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.select(["order_number", "customer_id", "status", "total"])
  # Keep parentheses in the raw subquery for compatibility with older Selecto builds.
  |> Selecto.filter({"customer_id", {:subquery, :in, "(SELECT id FROM customers WHERE tier = 'premium')", []}})
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(10)

{subquery_filter_sql, subquery_filter_params} = Selecto.to_sql(subquery_filter_query)
IO.puts("Subquery filter SQL:\n#{subquery_filter_sql}")
IO.puts("\nSubquery filter params: #{inspect(subquery_filter_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(subquery_filter_query, example: "Subquery Filter Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nOrders limited to customers returned by subquery:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [order_number, customer_id, status, total] ->
      IO.puts("  #{order_number} -> customer_id=#{customer_id}, status=#{status}, total=$#{Decimal.round(total || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error (subquery): #{inspect(error)}")
end

text_search_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "description"])
  |> Selecto.filter({"name", {:text_search, "wireless"}})
  |> Selecto.limit(10)

{text_search_sql, text_search_params} = Selecto.to_sql(text_search_query)
IO.puts("\nText-search SQL:\n#{text_search_sql}")
IO.puts("\nText-search params: #{inspect(text_search_params)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(text_search_query, example: "Text Search Filter Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nProducts matching text search:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [name, description] ->
      preview = (description || "") |> String.slice(0, 60)
      IO.puts("  #{name} -> #{preview}")
    end)

  {:error, error} ->
    IO.puts("Execution error (text search): #{inspect(error)}")
end

Expected output checks (Advanced Filters):

• Subquery filter emits IN (SELECT ...) style SQL via {:subquery, ...}
• Text-search filter emits PostgreSQL full-text search expression
• Both filter forms can be composed with standard select/order/limit

Section 37: Additional Domain Join Marker (:snowflake_dimension)

Preview Snowflake Join Intent at the Domain Layer

Business question: Can we mark a join as snowflake-oriented while keeping the query API unchanged?

snowflake_domain =
  put_in(config.order_domain, [:joins, :customer],
    config.order_domain.joins.customer
    |> Map.merge(%{
      type: :snowflake_dimension,
      display_field: :name,
      # Non-empty normalization chain is required by Selecto.DomainValidator
      normalization_joins: [
        %{table: "customers", key: "id", foreign_key: "id", alias: "customer_customers"}
      ]
    })
  )

snowflake_selecto = Selecto.configure(snowflake_domain, config.repo)
snowflake_join = Selecto.joins(snowflake_selecto)[:customer]

snowflake_query =
  snowflake_selecto
  |> Selecto.select(["customer.name", {:count, "*"}, {:sum, "total"}])
  |> Selecto.group_by(["customer.name"])
  |> Selecto.order_by({{:sum, "total"}, :desc})
  |> Selecto.limit(5)

{snowflake_sql, snowflake_params} = Selecto.to_sql(snowflake_query)
IO.puts("Snowflake marker SQL:\n#{snowflake_sql}")
IO.puts("\nParams: #{inspect(snowflake_params)}")
IO.puts("Join metadata marker: #{inspect(snowflake_join.join_type)}")

case SelectoExamples.LivebookHelpers.execute_with_checks(snowflake_query, example: "Snowflake join_type Query") do
  {:ok, {rows, columns, _aliases}} ->
    IO.puts("\nSnowflake-marked customer aggregates:")
    IO.puts("Columns: #{inspect(columns)}")
    Enum.each(rows, fn [customer_name, order_count, total_revenue] ->
      IO.puts("  #{customer_name}: orders=#{order_count}, total=$#{Decimal.round(total_revenue || Decimal.new(0), 2)}")
    end)

  {:error, error} ->
    IO.puts("Execution error: #{inspect(error)}")
end

Expected output checks (:snowflake_dimension marker):

• Join metadata includes the snowflake join marker
• Query API remains unchanged (select/group_by/order_by still works)
• SQL remains parameterized and executable

Section 38: Experimental Subfilter Modules (Preview)

Inspect Availability of Subfilter Components

Business question: Which subfilter modules are present today for teams evaluating experimental subfilter workflows?

subfilter_modules = [
  Selecto.Subfilter,
  Selecto.Subfilter.Parser,
  Selecto.Subfilter.Registry,
  Selecto.Subfilter.SQL
]

IO.puts("Subfilter module availability:")
Enum.each(subfilter_modules, fn mod ->
  IO.puts("  #{inspect(mod)} loaded? #{Code.ensure_loaded?(mod)}")
end)

IO.puts("\nNote: Advanced subfilter workflows are currently marked experimental in Selecto.")

Expected output checks (Subfilter Preview):

• Core subfilter modules are discoverable and loadable
• Teams can evaluate module surface area before adopting advanced subfilter flows
• Experimental status is explicit in guide narrative

Section 39: Side-by-Side Join SQL Comparison (:left vs :star_dimension vs :snowflake_dimension)

Compare Join Intent Using the Same Base Query

Business question: What changes in generated SQL and metadata when we switch only join intent?

base_customer_join = config.order_domain.joins.customer

regular_domain =
  put_in(config.order_domain, [:joins, :customer],
    base_customer_join
    |> Map.put(:type, :left)
  )

star_domain =
  put_in(config.order_domain, [:joins, :customer],
    base_customer_join
    |> Map.merge(%{
      type: :star_dimension,
      display_field: :name
    })
  )

snowflake_domain =
  put_in(config.order_domain, [:joins, :customer],
    base_customer_join
    |> Map.merge(%{
      type: :snowflake_dimension,
      display_field: :name,
      normalization_joins: [
        %{table: "customers", key: "id", foreign_key: "id", alias: "customer_customers"}
      ]
    })
  )

build_query = fn selecto ->
  selecto
  |> Selecto.select(["order_number", "customer.name", "total"])
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(3)
end

Enum.each(
  [
    {"Regular (:left)", regular_domain},
    {"Star (:star_dimension)", star_domain},
    {"Snowflake (:snowflake_dimension)", snowflake_domain}
  ],
  fn {label, domain} ->
    selecto = Selecto.configure(domain, config.repo)
    query = build_query.(selecto)
    {sql, params} = Selecto.to_sql(query)
    join_meta = Selecto.joins(selecto)[:customer]
    join_type = Map.get(join_meta, :join_type, :basic)

    IO.puts("\n=== #{label} ===")
    IO.puts("join_type metadata: #{inspect(join_type)}")
    IO.puts(sql)
    IO.puts("params: #{inspect(params)}")
  end
)

Expected output checks (Join Syntax Comparison):

• Regular join shows a single customer join and basic join metadata
• Star join preserves one-hop join shape but marks join metadata as :star_dimension
• Snowflake join adds at least one extra normalization-chain join

Section 40: Hierarchical Join Type Examples (:adjacency_list, :materialized_path, :closure_table)

Preview Hierarchical Join Metadata and Generated Field Surfaces

Business question: How do the three hierarchical join types differ at configuration-time in Selecto?

hierarchy_base =
  config.employee_domain
  |> put_in([:source, :associations, :manager_tree], %{
    field: :manager_tree,
    queryable: :employees,
    owner_key: :manager_id,
    related_key: :id
  })

hierarchy_variants = [
  {
    :adjacency_list,
    %{
      name: "Manager Tree",
      type: :hierarchical,
      hierarchy_type: :adjacency_list,
      depth_limit: 5,
      id_field: :id,
      parent_field: :manager_id,
      name_field: :first_name,
      fields: %{first_name: %{type: :string}, last_name: %{type: :string}}
    }
  },
  {
    :materialized_path,
    %{
      name: "Manager Tree",
      type: :hierarchical,
      hierarchy_type: :materialized_path,
      path_field: :manager_path,
      path_separator: "/",
      fields: %{first_name: %{type: :string}, last_name: %{type: :string}}
    }
  },
  {
    :closure_table,
    %{
      name: "Manager Tree",
      type: :hierarchical,
      hierarchy_type: :closure_table,
      closure_table: "employee_closure",
      ancestor_field: :ancestor_id,
      descendant_field: :descendant_id,
      depth_field: :depth,
      fields: %{first_name: %{type: :string}, last_name: %{type: :string}}
    }
  }
]

Enum.each(hierarchy_variants, fn {label, join_config} ->
  domain = put_in(hierarchy_base, [:joins, :manager_tree], join_config)
  selecto = Selecto.configure(domain, config.repo)
  join_meta = Selecto.joins(selecto)[:manager_tree]

  hierarchy_fields =
    selecto
    |> Selecto.columns()
    |> Map.keys()
    |> Enum.filter(&String.contains?(&1, "manager_tree"))
    |> Enum.sort()
    |> Enum.take(12)

  IO.puts("\n=== #{label} ===")
  IO.puts("join_type: #{inspect(join_meta.join_type)}")
  IO.puts("hierarchy_depth: #{inspect(Map.get(join_meta, :hierarchy_depth))}")
  IO.puts("path_field: #{inspect(Map.get(join_meta, :path_field))}")
  IO.puts("closure_table: #{inspect(Map.get(join_meta, :closure_table))}")
  IO.puts("field preview: #{inspect(hierarchy_fields)}")
end)

Execute a Hierarchy Query (:adjacency_list)

Business question: Can the hierarchy join intent produce a runnable SQL query for employee-manager traversal?

adjacency_domain =
  hierarchy_base
  |> put_in([:joins, :manager_tree], %{
    name: "Manager Tree",
    type: :hierarchical,
    hierarchy_type: :adjacency_list,
    depth_limit: 5,
    id_field: :id,
    parent_field: :manager_id,
    name_field: :first_name,
    fields: %{first_name: %{type: :string}, last_name: %{type: :string}}
  })

adjacency_query =
  Selecto.configure(adjacency_domain, config.repo)
  |> Selecto.select([
    "id",
    "first_name",
    "last_name",
    "manager_tree.first_name",
    "manager_tree_level",
    "manager_tree_path"
  ])
  |> Selecto.limit(15)

try do
  {sql, params} = Selecto.to_sql(adjacency_query)
  IO.puts("Adjacency hierarchy SQL:\n")
  IO.puts(sql)
  IO.puts("\nparams: #{inspect(params)}")

  case SelectoExamples.LivebookHelpers.execute_with_checks(adjacency_query, example: "Adjacency Hierarchy Query") do
    {:ok, {rows, columns, _aliases}} ->
      IO.puts("\nAdjacency hierarchy rows:")
      IO.puts("Columns: #{inspect(columns)}")

      rows
      |> Enum.take(10)
      |> Enum.each(&IO.inspect/1)

    {:error, error} ->
      IO.puts("Execution error: #{inspect(error)}")
  end
rescue
  e ->
    IO.puts("Hierarchy query generation is unavailable in this Selecto build:")
    IO.puts(Exception.message(e))
    IO.puts("Tip: use local `vendor/selecto` or update to a build with hierarchy CTE compatibility fixes.")
end

Expected output checks (Hierarchical Join Types + Runtime):

• :adjacency_list resolves to join metadata :hierarchical_adjacency and includes path/level helper fields
• :materialized_path resolves to :hierarchical_materialized_path and includes path/depth helper fields
• :closure_table resolves to :hierarchical_closure_table and includes depth/descendant helper fields
• Adjacency runtime query emits WITH RECURSIVE ... and returns rows (or prints a compatibility note when running against an older selecto build)
• :materialized_path and :closure_table metadata can be previewed without adding manager_path / employee_closure schema objects to the sample DB

Conclusion

This Livebook has demonstrated the core features of Selecto:

1. Domain Configuration - Defining your data model with sources, columns, and joins
2. Query Building - Using the fluent API to build queries
3. Filtering - Applying various filter conditions (equality, comparison, pattern matching, etc.)
4. Sorting & Pagination - Ordering results and implementing pagination
5. Aggregates - Using COUNT, SUM, AVG, MIN, MAX with GROUP BY
6. Joins - Automatic joins via dot notation
7. Composable Patterns - Building reusable query functions
8. Output Formats - Transforming results for different use cases
9. Pivoting - Retargeting query focus to related schemas while preserving filter context
10. Subselects - Embedding related rows as JSON arrays in each parent row
11. Set Operations - Combining results with UNION / INTERSECT / EXCEPT
12. CTEs - Using WITH and WITH RECURSIVE for modular query construction
13. Window Functions - Ranking and analytics across related rows without collapsing detail
14. JSON Operations - Extracting and filtering JSONB document data directly in SQL
15. Array Operations - Filtering, sizing, and formatting PostgreSQL arrays
16. UNNEST + LATERAL - Expanding array values into row-level results
17. CASE Expressions - Conditional classification directly in SQL
18. VALUES Clauses - Inline lookup tables via WITH ... AS (VALUES ...)
19. Multi-CTE Composition - Attach and consume multiple CTEs with with_ctes/2
20. Array Manipulation - Transform arrays with append/remove/string conversion in one query
21. Execution Helpers - Inspect SQL/timing metadata and enforce single-row semantics
22. Dynamic Subquery Joins - Join a query to an ad-hoc subquery built with Selecto
23. Parameterized Join Instances - Materialize the same join multiple times with distinct aliases
24. Dynamic Custom Joins - Attach non-association tables at runtime with explicit join keys
25. Execution Format Options - Return maps/JSON/CSV directly from execute/2
26. Advanced LATERAL Variants - Use both subquery-builder and table-function forms
27. Ecto Bootstrap - Derive Selecto domains from Ecto schemas with from_ecto/3
28. Simple CASE - Map discrete column values with case_select/4
29. Map-Row VALUES - Infer VALUES columns directly from map keys
30. Explicit on: Joins - Define custom join predicates with join/3
31. Advanced Formats - Use {:typed_maps, ...} and {:stream, ...} execution formats
32. Domain-Level Join Patterns - Configure OLAP and hierarchical intent via join_type
33. Introspection Helpers - Inspect domain/query metadata and resolve fields dynamically
34. Type Helpers - Infer expression types and validate compatibility
35. SQL Builder API - Compare gen_sql/2 alias metadata with to_sql/1
36. Advanced Filters - Use subquery and full-text-search filter operators
37. Snowflake Marker Example - Apply :snowflake_dimension without changing query shape
38. Subfilter Module Preview - Inspect experimental subfilter components
39. Join Syntax Comparison - Compare regular, star, and snowflake SQL side-by-side
40. Hierarchical Join Type Examples - Compare adjacency-list, materialized-path, and closure-table join metadata

For more advanced feature updates, follow the selecto repository directly.