Selecto VALUES and Inline Lookup Workbook

Setup 1) Install Dependencies

Run this cell first. It installs Selecto and related packages from Hex for this Livebook session.

selecto_dep = {:selecto, "~> 0.3.5"}

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)}")

Overview

This workbook demonstrates with_values/3 for inline lookup tables and ad hoc reference data.

1. Add a literal lookup table using row lists
2. Join inline lookup to base query rows
3. Use map rows with inferred columns
4. Treat VALUES tables as priority metadata without physical schema changes

flowchart LR
  base[orders]
  values[WITH ... VALUES inline table]
  join[join inline table]
  select[select mapped fields]
  output[result rows]

  base --> join
  values --> join --> select --> output

graph TD
  status[order status]
  labels[status_labels VALUES]
  priority[priority VALUES]

  status --> labels
  status --> priority

Before running, initialize the sample database:

cd selecto_examples
mix setup

Setup 2) Connect to Repo and Build Order Domain

This cell defines a local Repo and order domain for VALUES join examples.

defmodule ValuesLab.Repo do
  use Ecto.Repo,
    otp_app: :values_lab_livebook,
    adapter: Ecto.Adapters.Postgres
end

defmodule ValuesLab.OrderDomain do
  @moduledoc false

  def domain do
    %{
      name: "Orders",
      source: %{
        source_table: "orders",
        primary_key: :id,
        fields: [
          :id,
          :order_number,
          :status,
          :total,
          :shipping_country,
          :inserted_at,
          :customer_id
        ],
        columns: %{
          id: %{type: :integer},
          order_number: %{type: :string},
          status: %{type: :string},
          total: %{type: :decimal},
          shipping_country: %{type: :string},
          inserted_at: %{type: :utc_datetime},
          customer_id: %{type: :integer}
        },
        associations: %{}
      },
      schemas: %{},
      joins: %{},
      default_selected: ["order_number", "status", "total"],
      default_order_by: [{"total", :desc}]
    }
  end
end

repo_config = [
  database: System.get_env("SELECTO_EXAMPLES_DB", "selecto_examples_dev"),
  username: System.get_env("SELECTO_EXAMPLES_DB_USER", "postgres"),
  password: System.get_env("SELECTO_EXAMPLES_DB_PASS", "postgres"),
  hostname: System.get_env("SELECTO_EXAMPLES_DB_HOST", "localhost"),
  port: String.to_integer(System.get_env("SELECTO_EXAMPLES_DB_PORT", "5432")),
  pool_size: 5
]

case Process.whereis(ValuesLab.Repo) do
  nil -> {:ok, _pid} = ValuesLab.Repo.start_link(repo_config)
  _pid -> :ok
end

config = %{
  repo: ValuesLab.Repo,
  order_domain: ValuesLab.OrderDomain.domain()
}

{:ok, result} = Ecto.Adapters.SQL.query(config.repo, "select count(*) from orders", [])
[[order_count]] = result.rows
IO.puts("Connected. Orders in dataset: #{order_count}")

config

Setup 3) Define VALUES Helpers

This helper cell prints SQL/params and executes a query with a compact preview.

defmodule ValuesLab.Helpers do
  @moduledoc false

  def explain(label, query) do
    Selecto.Livebook.explain(label, query)
  end

  def run(label, query, preview_count \\ 10) do
    Selecto.Livebook.run(label, query, preview_count: preview_count)
  end

  def run_shape(label, query, preview_count \\ 10) do
    IO.inspect(query.set.selection_shape, label: "Compiled shape", pretty: true)
    Selecto.Livebook.run_shape(label, query, preview_count: preview_count)
  end
end

1) Basic Inline Status Label Mapping

Define a small status-to-label table inline and join it to orders.

status_lookup_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(15)

ValuesLab.Helpers.run("Inline status labels", status_lookup_query)

2) Map-Row VALUES with Inferred Columns

Provide values as maps and let Selecto infer the columns.

status_priority_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",
    "total"
  ])
  |> Selecto.order_by({"status_priority.priority_rank", :asc})
  |> Selecto.limit(15)

ValuesLab.Helpers.run("Map-row VALUES with inferred columns", status_priority_query)

3) Inline Lookup for Shipping Region Buckets

Create an inline table that classifies shipping countries into coarse geographic buckets.

region_lookup_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.with_values(
    [
      ["USA", "North America"],
      ["Canada", "North America"],
      ["Mexico", "North America"],
      ["UK", "Europe"],
      ["Germany", "Europe"],
      ["France", "Europe"]
    ],
    columns: ["shipping_country", "region_bucket"],
    as: "country_region_map"
  )
  |> Selecto.join(:country_region_map,
    source: "country_region_map",
    type: :left,
    owner_key: :shipping_country,
    related_key: :shipping_country,
    fields: %{
      shipping_country: %{type: :string},
      region_bucket: %{type: :string}
    }
  )
  |> Selecto.select([
    "order_number",
    "shipping_country",
    "country_region_map.region_bucket",
    "total"
  ])
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(20)

ValuesLab.Helpers.run("Inline country-region lookup", region_lookup_query)

4) Compare Two VALUES Strategies Side-by-Side

Run list-row and map-row styles in one pass to compare SQL and output readability.

comparison_queries = [
  {"List-row VALUES", status_lookup_query},
  {"Map-row VALUES", status_priority_query}
]

Enum.each(comparison_queries, fn {label, query} ->
  ValuesLab.Helpers.run(label, query, 5)
end)

:ok

5) Staged Lookup Logic with with_values/3 + with_cte/4

Build a high-value-order CTE, then enrich those rows with inline status-group metadata.

staged_lookup_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.with_cte("high_value_orders", fn ->
    Selecto.configure(config.order_domain, config.repo)
    |> Selecto.select(["id", "status", "total"])
    |> Selecto.filter({"total", {:>=, 150}})
  end, columns: ["id", "status", "total"])
  |> Selecto.with_values(
    [
      ["processing", "In-flight"],
      ["shipped", "In-flight"],
      ["delivered", "Closed"],
      ["cancelled", "Closed"]
    ],
    columns: ["status", "status_group"],
    as: "status_group_lookup"
  )
  |> Selecto.join(:high_value_orders,
    source: "high_value_orders",
    type: :inner,
    owner_key: :id,
    related_key: :id,
    fields: %{
      id: %{type: :integer},
      status: %{type: :string},
      total: %{type: :decimal}
    }
  )
  |> Selecto.join(:status_group_lookup,
    source: "status_group_lookup",
    type: :left,
    owner_key: :status,
    related_key: :status,
    fields: %{
      status: %{type: :string},
      status_group: %{type: :string}
    }
  )
  |> Selecto.select([
    "order_number",
    "status",
    "status_group_lookup.status_group",
    "high_value_orders.total"
  ])
  |> Selecto.order_by({"high_value_orders.total", :desc})
  |> Selecto.limit(15)

ValuesLab.Helpers.run("CTE + VALUES staged lookup", staged_lookup_query, 10)

6) Priority-Scored Business Sorting

Inline lookup rank controls display order first, then monetary value breaks ties.

priority_scored_query =
  Selecto.configure(config.order_domain, config.repo)
  |> Selecto.with_values(
    [
      %{status: "processing", business_rank: 1, board_bucket: "Needs attention"},
      %{status: "shipped", business_rank: 2, board_bucket: "In transit"},
      %{status: "delivered", business_rank: 3, board_bucket: "Completed"},
      %{status: "cancelled", business_rank: 4, board_bucket: "Closed exception"}
    ],
    as: "status_business_priority"
  )
  |> Selecto.join(:status_business_priority,
    source: "status_business_priority",
    type: :left,
    owner_key: :status,
    related_key: :status,
    fields: %{
      status: %{type: :string},
      business_rank: %{type: :integer},
      board_bucket: %{type: :string}
    }
  )
  |> Selecto.select([
    "order_number",
    "status",
    "status_business_priority.business_rank",
    "status_business_priority.board_bucket",
    "total"
  ])
  |> Selecto.order_by({"status_business_priority.business_rank", :asc})
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(20)

ValuesLab.Helpers.run("Business-priority ordering via VALUES", priority_scored_query, 10)

7) Shaped Lookup Payloads with select_shape/2

Package lookup-enriched values into nested tuples for API-ready output structures.

shaped_lookup_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_shape"
  )
  |> Selecto.join(:status_priority_shape,
    source: "status_priority_shape",
    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", "total"])
  |> Selecto.select_shape([
    "order_number",
    {"status", "status_priority_shape.customer_label"},
    {"status_priority_shape.priority_rank", "total"}
  ])
  |> Selecto.order_by({"status_priority_shape.priority_rank", :asc})
  |> Selecto.order_by({"total", :desc})
  |> Selecto.limit(15)

ValuesLab.Helpers.run_shape("Shaped VALUES lookup output", shaped_lookup_query, 10)

Next Steps

To extend this workbook, add:

1. Inline lookup versioning patterns (swap lookup sets by environment)
2. A workbook cell comparing VALUES lookup versus persisted lookup-table joins
3. A reusable helper that composes with_values + join definitions from a single metadata map