Selecto (Selecto v0.4.3)

Selecto is a query builder for Elixir that uses Postgrex to execute queries. It is designed to be a flexible and powerful tool for building complex SQL queries without writing SQL by hand.

domain-configuration
Domain Configuration

Selecto is configured using a domain map. This map defines the database schema, including tables, columns, and associations. Here is an example of a domain map:

%{
  source: %{
    source_table: "users",
    primary_key: :id,
    fields: [:id, :name, :email, :age, :active, :created_at, :updated_at],
    redact_fields: [],
    columns: %{
      id: %{type: :integer},
      name: %{type: :string},
      email: %{type: :string},
      age: %{type: :integer},
      active: %{type: :boolean},
      created_at: %{type: :utc_datetime},
      updated_at: %{type: :utc_datetime}
    },
    associations: %{
      posts: %{
        queryable: :posts,
        field: :posts,
        owner_key: :id,
        related_key: :user_id
      }
    }
  },
  schemas: %{
    posts: %{
      source_table: "posts",
      primary_key: :id,
      fields: [:id, :title, :body, :user_id, :created_at, :updated_at],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        title: %{type: :string},
        body: %{type: :string},
        user_id: %{type: :integer},
        created_at: %{type: :utc_datetime},
        updated_at: %{type: :utc_datetime}
      },
      associations: %{
        tags: %{
          queryable: :post_tags,
          field: :tags,
          owner_key: :id,
          related_key: :post_id
        }
      }
    },
    post_tags: %{
      source_table: "post_tags",
      primary_key: :id,
      fields: [:id, :name, :post_id],
      redact_fields: [],
      columns: %{
        id: %{type: :integer},
        name: %{type: :string},
        post_id: %{type: :integer}
      }
    }
  },
  name: "User",
  default_selected: ["name", "email"],
  default_aggregate: [{"id", %{"format" => "count"}}],
  required_filters: [{"active", true}],
  joins: %{
    posts: %{
      type: :left,
      name: "posts",
      parameters: [
        {:tag, :name}
      ],
      joins: %{
        tags: %{
          type: :left,
          name: "tags"
        }
      }
    }
  },
  filters: %{
    "active" => %{
      name: "Active",
      type: "boolean",
      default: true
    }
  }
}

query-execution
Query Execution

Selecto provides two execution patterns for better error handling and control flow:

safe-execution-non-raising
Safe Execution (Non-raising)

Use execute/2 and execute_one/2 for applications that prefer explicit error handling:

# Multiple rows
case Selecto.execute(selecto) do
  {:ok, {rows, columns, aliases}} ->
    # Process successful results
    Enum.map(rows, &process_row/1)

  {:error, %Selecto.Error{} = error} ->
    # Handle database errors gracefully
    Logger.error("Query failed: #{inspect(error)}")
    {:error, :database_error}
end

# Single row (useful for COUNT, aggregate queries, or lookups)
case Selecto.execute_one(selecto) do
  {:ok, {row, aliases}} ->
    # Process single row
    extract_values(row, aliases)

  {:error, :no_results} ->
    # Handle empty result set
    {:error, :not_found}

  {:error, :multiple_results} ->
    # Handle unexpected multiple rows
    {:error, :ambiguous_result}
end

error-types
Error Types

All execution functions return structured Selecto.Error for consistent error handling:

{:error, %Selecto.Error{type: :connection_error}} - Database connection failures
{:error, %Selecto.Error{type: :query_error}} - SQL execution errors
{:error, %Selecto.Error{type: :no_results}} - execute_one/2 when 0 rows returned
{:error, %Selecto.Error{type: :multiple_results}} - execute_one/2 when >1 rows returned
{:error, %Selecto.Error{type: :timeout_error}} - Query timeout failures

Link to this section Summary

Functions

apply_tenant_scope(selecto, opts \\ [])

Apply tenant scope as required filters.

array_filter(selecto, array_filters, opts \\ [])

Add array filtering operations to WHERE clauses.

array_manipulate(selecto, array_operations, opts \\ [])

Add array manipulation operations to select fields.

array_select(selecto, array_operations, opts \\ [])

Add array aggregation operations to select fields.

available_fields(selecto)

Get all available fields across all joins and the source table.

case_select(selecto, column, when_clauses, opts \\ [])

Add a simple CASE expression to the select fields.

case_when_select(selecto, when_clauses, opts \\ [])

Add a searched CASE expression to the select fields.

columns(selecto)

See Selecto.Fields.columns/1.

configure(domain, postgrex_opts, opts \\ [])

Generate a selecto structure from a domain configuration and connection input.

detail_actions(selecto)

See Selecto.Fields.detail_actions/1.

domain(selecto)

See Selecto.Fields.domain/1.

domain_data(selecto)

See Selecto.Fields.domain_data/1.

ensure_tenant_scope!(selecto, opts \\ [])

Raise if tenant scope is required and missing.

except(left_query, right_query, opts \\ [])

Create an EXCEPT set operation between two queries.

execute(selecto, opts \\ [])

Generate and run the query, returning {:ok, result} or {:error, reason}.

execute_one(selecto, opts \\ [])

Execute a query expecting exactly one row, returning {:ok, row} or {:error, reason}.

execute_shape(selecto, opts \\ [])

Execute a query configured with select_shape/2 and return shaped rows.

execute_stream(selecto, opts \\ [])

Execute a query as a database-backed stream.

execute_with_metadata(selecto, opts \\ [])

Execute a query and return results with metadata including SQL, params, and execution time.

explain(selecto, opts \\ [])

Run EXPLAIN for a query and return plan details.

explain_analyze(selecto, opts \\ [])

Run EXPLAIN ANALYZE for a query and return plan details.

extensions(selecto)

See Selecto.Fields.extensions/1.

field(selecto, field_name)

See Selecto.Fields.field/2.

field_suggestions(selecto, partial_name)

Get field suggestions for autocomplete or error recovery.

filter(selecto, filters_or_filter)

Add filters to the query.

filters(selecto)

See Selecto.Fields.filters/1.

format_sql(sql, opts \\ [])

Format SQL output for readability.

from_ecto(repo, schema, opts \\ [])

Configure Selecto from an Ecto repository and schema.

gen_sql(selecto, opts)

group_by(selecto, groups)

Add to the Group By clause.

highlight_sql(sql, style)

Apply optional highlighting to SQL (:ansi or :markdown).

infer_type(selecto, expression)

Infer the SQL type of an expression.

intersect(left_query, right_query, opts \\ [])

Create an INTERSECT set operation between two queries.

join(selecto, join_id, options \\ [])

Enable a join from the domain configuration or add a custom join dynamically.

join_parameterize(selecto, join_id, parameter, options \\ [])

Create a parameterized instance of an existing join.

join_subquery(selecto, join_id, join_selecto, options \\ [])

Join with another Selecto query as a subquery.

joins(selecto)

See Selecto.Fields.joins/1.

json_filter(selecto, json_filters, opts \\ [])

Add JSON operations to WHERE clauses for filtering with PostgreSQL JSON/JSONB functionality.

json_order_by(selecto, json_sorts, opts \\ [])

Add JSON operations to ORDER BY clauses for sorting with PostgreSQL JSON/JSONB functionality.

json_rowset(selecto, source_field, opts \\ [])

Add a SQLite JSON rowset expansion using json_each or json_tree.

json_select(selecto, json_operations, opts \\ [])

Add JSON operations to SELECT clauses for PostgreSQL JSON/JSONB functionality.

json_table(selecto, source_field, opts \\ [])

Add a MySQL JSON_TABLE rowset expansion to the query.

lateral_join(selecto, arg2, arg3 \\ [], arg4 \\ nil, arg5 \\ [])

Add a LATERAL join to the query.

limit(selecto, limit_value)

Limit the number of rows returned by the query.

offset(selecto, offset_value)

Set the offset for the query results.

order_by(selecto, orders)

Add to the Order By clause.

post_retarget_filter(selecto, filters_or_filter)

Append filters explicitly to the post-retarget filter list.

post_retarget_filters(selecto)

Read post-retarget filters from the query set (set.post_retarget_filters).

pre_retarget_filter(selecto, filters_or_filter)

Append filters explicitly to the pre-retarget filter list.

pre_retarget_filters(selecto)

Read pre-retarget filters from the query set (set.filtered).

query_filters(selecto, opts \\ [])

Return query filters from current filter buckets.

require_tenant_filter(selecto, filter)

Add a required tenant filter to query state.

require_tenant_filter(selecto, tenant_field, tenant_id)

required_filters(selecto)

Return required filters currently attached to the query.

resolve_field(selecto, field)

Enhanced field resolution with disambiguation and error handling.

retarget(selecto, target_schema, opts \\ [])

Retarget the query to focus on a different table while preserving existing context.

select(selecto, fields_or_field)

Add fields to the select list.

select_shape(selecto, shape)

Compile a nested selection shape and attach it to the query.

set(selecto)

See Selecto.Fields.set/1.

source_table(selecto)

See Selecto.Fields.source_table/1.

sqlite_fts_rank(selecto, fields, opts \\ [])

Add a SQLite FTS5 ranking selector using bm25(...).

subselect(selecto, field_specs, opts \\ [])

Add subselect fields to return related data as aggregated arrays.

tenant(selecto)

Read tenant context from query state.

tenant_required?(selecto, opts \\ [])

Return whether tenant scope is required for this query.

text_search_rank(selecto, fields, opts \\ [])

Add an adapter-aware text-search rank selector.

to_sql(selecto, opts \\ [])

Generate SQL without executing - useful for debugging and caching.

type_category(type)

Get the type category for a given SQL type.

types_compatible?(type1, type2)

Check if two SQL types are compatible for comparisons or assignments.

udf(function_id, args \\ [])

Build a registered scalar or predicate UDF expression.

udf_table(function_id, args \\ [])

Build a registered table-UDF expression for lateral joins and named laterals.

union(left_query, right_query, opts \\ [])

Create a UNION set operation between two queries.

unnest(selecto, array_field, opts \\ [])

Add an UNNEST operation to expand array columns into rows.

validate_tenant_scope(selecto, opts \\ [])

Validate tenant scope and return :ok or structured validation error.

window_function(selecto, function, arguments \\ [], options)

Add a window function to the query.

with_cte(selecto, member_id)

Add a Common Table Expression (CTE) to the query using WITH clause.

with_cte(selecto, member_id, opts)

with_cte(selecto, name, query_builder, opts \\ [])

with_ctes(selecto, cte_specs, opts \\ [])

Add multiple CTEs to the query in a single operation.

with_lateral(selecto, member_id, opts \\ [])

Apply a LATERAL source directly or resolve a named LATERAL preset from domain.query_members.laterals.

with_recursive_cte(selecto, arg2, arg3, arg4 \\ nil, arg5 \\ [])

Add a recursive Common Table Expression (CTE) to the query.

with_subquery(selecto, member_id, opts \\ [])

Apply a named subquery preset from domain.query_members.subqueries.

with_tenant(selecto, tenant_context)

Attach tenant context to the query state.

with_unnest(selecto, member_id, opts \\ [])

Apply a named UNNEST preset from domain.query_members.unnests.

with_values(selecto, data_or_member, opts \\ [])

Add a VALUES clause to create an inline table from literal data.

Link to this section Types

t()

@type t() :: Selecto.Types.t()

Link to this section Functions

apply_tenant_scope(selecto, opts \\ [])

@spec apply_tenant_scope(
  t(),
  keyword()
) :: t()

Apply tenant scope as required filters.

array_filter(selecto, array_filters, opts \\ [])

Add array filtering operations to WHERE clauses.

Supports array containment, overlap, and equality operations.

parameters
Parameters

selecto - The Selecto instance
array_filters - List of array filter tuples or single filter
opts - Additional options

examples
Examples

# Array contains
selecto
|> Selecto.array_filter({:array_contains, "tags", ["featured", "new"]})

# Array overlap (has any of the elements)
selecto
|> Selecto.array_filter({:array_overlap, "categories", ["electronics", "computers"]})

# Array contained by
selecto
|> Selecto.array_filter({:array_contained, "permissions", ["read", "write", "admin"]})

# Multiple filters
selecto
|> Selecto.array_filter([
    {:array_contains, "special_features", ["Trailers"]},
    {:array_overlap, "languages", ["English", "Spanish"]}
  ])

array_manipulate(selecto, array_operations, opts \\ [])

Add array manipulation operations to select fields.

Supports array construction, modification, and transformation operations.

parameters
Parameters

selecto - The Selecto instance
array_operations - List of array manipulation operations
opts - Additional options

examples
Examples

# Array append
selecto
|> Selecto.array_manipulate({:array_append, "tags", "new-tag", as: "updated_tags"})

# Array remove
selecto
|> Selecto.array_manipulate({:array_remove, "tags", "deprecated", as: "cleaned_tags"})

# Array to string
selecto
|> Selecto.array_manipulate({:array_to_string, "tags", ", ", as: "tag_string"})

array_select(selecto, array_operations, opts \\ [])

Add array aggregation operations to select fields.

Supports ARRAY_AGG, STRING_AGG, and other array aggregation functions with optional DISTINCT, ORDER BY, and filtering.

parameters
Parameters

selecto - The Selecto instance
array_operations - List of array operation tuples or single operation
opts - Additional options

examples
Examples

# Simple array aggregation
selecto
|> Selecto.array_select({:array_agg, "film.title", as: "film_titles"})

# Array aggregation with DISTINCT and ORDER BY
selecto
|> Selecto.array_select({:array_agg, "actor.name",
    distinct: true,
    order_by: [{"actor.last_name", :asc}],
    as: "unique_actors"})

# String aggregation with custom delimiter
selecto
|> Selecto.array_select({:string_agg, "tag.name",
    delimiter: ", ",
    as: "tag_list"})

# Array length operation
selecto
|> Selecto.array_select({:array_length, "tags", 1, as: "tag_count"})

available_fields(selecto)

@spec available_fields(t()) :: [String.t()]

Get all available fields across all joins and the source table.

case_select(selecto, column, when_clauses, opts \\ [])

Add a simple CASE expression to the select fields.

Simple CASE expressions test a column against specific values and return corresponding results. This is useful for data transformation and categorization.

parameters
Parameters

selecto - The Selecto instance
column - Column to test against
when_clauses - List of {value, result} tuples for WHEN conditions
opts - Options including :else and :as

examples
Examples

# Simple CASE for film ratings
selecto
|> Selecto.case_select("film.rating", [
    {"G", "General Audience"},
    {"PG", "Parental Guidance"},
    {"PG-13", "Parents Strongly Cautioned"},
    {"R", "Restricted"}
  ], else: "Not Rated", as: "rating_description")
|> Selecto.select(["film.title", "rating_description"])

# Generated SQL:
# SELECT film.title,
#        CASE film.rating
#          WHEN 'G' THEN 'General Audience'
#          WHEN 'PG' THEN 'Parental Guidance'
#          WHEN 'PG-13' THEN 'Parents Strongly Cautioned'
#          WHEN 'R' THEN 'Restricted'
#          ELSE 'Not Rated'
#        END AS rating_description

case_when_select(selecto, when_clauses, opts \\ [])

Add a searched CASE expression to the select fields.

Searched CASE expressions evaluate multiple conditions and return results based on the first true condition. This enables complex conditional logic.

parameters
Parameters

selecto - The Selecto instance
when_clauses - List of {conditions, result} tuples
opts - Options including :else and :as

examples
Examples

# Customer tier based on payment totals
selecto
|> Selecto.case_when_select([
    {[{"payment_total", {:>, 100}}], "Premium"},
    {[{"payment_total", {:between, 50, 100}}], "Standard"},
    {[{"payment_total", {:>, 0}}], "Basic"}
  ], else: "No Purchases", as: "customer_tier")
|> Selecto.select(["customer.first_name", "customer_tier"])

# Multiple conditions per WHEN clause
selecto
|> Selecto.case_when_select([
    {[{"film.rating", "R"}, {"film.length", {:>, 120}}], "Long Adult Film"},
    {[{"film.rating", "G"}, {"film.special_features", {:like, "%Family%"}}], "Family Film"}
  ], else: "Regular Film", as: "film_category")

# Generated SQL:
# SELECT customer.first_name,
#        CASE
#          WHEN payment_total > $1 THEN $2
#          WHEN payment_total BETWEEN $3 AND $4 THEN $5
#          WHEN payment_total > $6 THEN $7
#          ELSE $8
#        END AS customer_tier

columns(selecto)

@spec columns(t()) :: map()

See Selecto.Fields.columns/1.

configure(domain, postgrex_opts, opts \\ [])

@spec configure(Selecto.Types.domain(), term(), keyword()) :: t()

Generate a selecto structure from a domain configuration and connection input.

## Parameters

domain - Domain configuration map (see domain configuration docs)
postgrex_opts - Connection input retained for backward compatibility. This may be adapter-specific connection options, an Ecto repo, a live connection pid/name, or a pooled connection reference.
opts - Configuration options

## Options

:validate - (boolean, default: true) Whether to validate the domain configuration before processing. When true, will raise Selecto.DomainValidator.ValidationError if the domain has structural issues like missing schemas, circular join dependencies, or invalid advanced join configurations.
:pool - (boolean, default: false) Whether to enable connection pooling
:pool_options - Connection pool configuration options
:adapter - (module, default: SelectoDBPostgreSQL.Adapter) Database adapter module

## Validation

Domain validation checks for:

Required top-level keys (source, schemas)
Schema structural integrity (required keys, column definitions)
Association references to valid schemas
Join references to existing associations
Join dependency cycles that would cause infinite recursion
Advanced join type requirements (dimension keys, hierarchy parameters, etc.)
Field reference validity in filters and selectors

## Examples

  # Basic usage (validation enabled by default)
  selecto = Selecto.configure(domain, connection_input)

  # With connection pooling
  selecto = Selecto.configure(domain, connection_input, pool: true)

  # Custom pool configuration
  pool_opts = [pool_size: 20, max_overflow: 10]
  selecto = Selecto.configure(domain, connection_input, pool: true, pool_options: pool_opts)

  # Using existing pooled connection
  {:ok, pool} = Selecto.ConnectionPool.start_pool(connection_input)
  selecto = Selecto.configure(domain, {:pool, pool})

  # Disable validation for performance-critical scenarios
  selecto = Selecto.configure(domain, connection_input, validate: false)

  # With Ecto repository and schema
  selecto = Selecto.from_ecto(MyApp.Repo, MyApp.User)

  # Validation can also be called explicitly
  :ok = Selecto.DomainValidator.validate_domain!(domain)
  selecto = Selecto.configure(domain, connection_input)

detail_actions(selecto)

@spec detail_actions(t()) :: %{optional(atom() | String.t()) => map()}

See Selecto.Fields.detail_actions/1.

domain(selecto)

@spec domain(t()) :: Selecto.Types.domain()

See Selecto.Fields.domain/1.

domain_data(selecto)

@spec domain_data(t()) :: term()

See Selecto.Fields.domain_data/1.

ensure_tenant_scope!(selecto, opts \\ [])

@spec ensure_tenant_scope!(
  t(),
  keyword()
) :: :ok

Raise if tenant scope is required and missing.

except(left_query, right_query, opts \\ [])

Create an EXCEPT set operation between two queries.

Returns rows from the first query that don't appear in the second query.

options
Options

:all - Use EXCEPT ALL to include duplicates in difference (default: false)
:column_mapping - Map columns between incompatible schemas

examples
Examples

# Find free users (all users except premium)
all_users |> Selecto.except(premium_users)

# Include duplicates in difference
query1 |> Selecto.except(query2, all: true)

execute(selecto, opts \\ [])

@spec execute(Selecto.Types.t(), Selecto.Types.execute_options()) ::
  Selecto.Types.safe_execute_result()

Generate and run the query, returning {:ok, result} or {:error, reason}.

Non-raising version that returns tagged tuples for better error handling. Result format: {:ok, {rows, columns, aliases}} | {:error, reason}

## Examples

  case Selecto.execute(selecto) do
    {:ok, {rows, columns, aliases}} ->
      # Handle successful query
      process_results(rows, columns)
    {:error, reason} ->
      # Handle database error
      Logger.error("Query failed: #{inspect(reason)}")
  end

execute_one(selecto, opts \\ [])

@spec execute_one(Selecto.Types.t(), Selecto.Types.execute_options()) ::
  Selecto.Types.safe_execute_one_result()

Execute a query expecting exactly one row, returning {:ok, row} or {:error, reason}.

Useful for queries that should return a single record (e.g., with LIMIT 1 or aggregate functions). Returns an error if zero rows or multiple rows are returned.

## Examples

  case Selecto.execute_one(selecto) do
    {:ok, row} ->
      # Handle single row result
      process_single_result(row)
    {:error, :no_results} ->
      # Handle case where no rows were found
    {:error, :multiple_results} ->
      # Handle case where multiple rows were found
    {:error, error} ->
      # Handle database or other errors
  end

execute_shape(selecto, opts \\ [])

@spec execute_shape(t(), Selecto.Types.execute_options()) ::
  {:ok, list()} | {:error, Selecto.Error.t()}

Execute a query configured with select_shape/2 and return shaped rows.

Returns {:ok, shaped_rows} where each row mirrors the selection shape.

execute_stream(selecto, opts \\ [])

@spec execute_stream(
  Selecto.Types.t(),
  keyword()
) :: Selecto.Types.safe_execute_stream_result()

Execute a query as a database-backed stream.

Returns a stream of {row, columns, aliases} tuples for incremental result consumption.

options
Options

:max_rows - PostgreSQL cursor batch size (default 500)
:receive_timeout - stream consumer wait timeout in ms (default 60000)
:queue_timeout - internal task yield timeout in ms (default 100)
:stream_timeout - transaction timeout for cursor execution (default 30000)

notes
Notes

Direct PostgreSQL connections use cursor-backed streaming.
Adapter-backed streaming requires adapter.stream/4 support.
Ecto repo and pooled PostgreSQL stream paths currently return structured :validation_error responses.

execute_with_metadata(selecto, opts \\ [])

@spec execute_with_metadata(Selecto.Types.t(), Selecto.Types.execute_options()) ::
  {:ok, Selecto.Types.execute_result(), map()} | {:error, Selecto.Error.t()}

Execute a query and return results with metadata including SQL, params, and execution time.

parameters
Parameters

selecto - The Selecto struct containing connection and query info
opts - Execution options

returns
Returns

{:ok, result, metadata} - Successful execution with results and metadata
{:error, error} - Execution failure with detailed error

The metadata map includes:

:sql - The generated SQL query string
:params - The query parameters
:execution_time - Query execution time in milliseconds

examples
Examples

case Selecto.execute_with_metadata(selecto) do
  {:ok, {rows, columns, aliases}, _metadata} ->
    # Process successful results with metadata
    handle_results(rows, columns, aliases)
  {:error, error} ->
    # Handle database error
    Logger.error("Query failed: #{inspect(error)}")
end

explain(selecto, opts \\ [])

@spec explain(
  t(),
  keyword()
) :: {:ok, map()} | {:error, Selecto.Error.t()}

Run EXPLAIN for a query and return plan details.

explain_analyze(selecto, opts \\ [])

@spec explain_analyze(
  t(),
  keyword()
) :: {:ok, map()} | {:error, Selecto.Error.t()}

Run EXPLAIN ANALYZE for a query and return plan details.

extensions(selecto)

@spec extensions(t()) :: [{module(), keyword()}]

See Selecto.Fields.extensions/1.

field(selecto, field_name)

@spec field(t(), Selecto.Types.field_name()) :: map() | nil

See Selecto.Fields.field/2.

field_suggestions(selecto, partial_name)

@spec field_suggestions(t(), String.t()) :: [String.t()]

Get field suggestions for autocomplete or error recovery.

filter(selecto, filters_or_filter)

@spec filter(t(), [Selecto.Types.filter()]) :: t()

@spec filter(t(), Selecto.Types.filter()) :: t()

Add filters to the query.

For macro-free query composition, prefer importing Selecto.Expr and using runtime filter helpers.

examples
Examples

import Selecto.Expr

selecto
|> Selecto.filter(eq("status", "delivered"))
|> Selecto.filter(compact_and([not_null("customer.id"), gte("total", 100)]))

filters(selecto)

@spec filters(t()) :: %{required(String.t()) => term()}

See Selecto.Fields.filters/1.

format_sql(sql, opts \\ [])

@spec format_sql(
  String.t(),
  keyword()
) :: String.t()

Format SQL output for readability.

from_ecto(repo, schema, opts \\ [])

Configure Selecto from an Ecto repository and schema.

This convenience function automatically introspects the Ecto schema and configures Selecto with the appropriate domain and database connection.

## Parameters

repo - The Ecto repository module (e.g., MyApp.Repo)
schema - The Ecto schema module to use as the source table
opts - Configuration options (passed to EctoAdapter.configure/3)

## Examples

  # Basic usage
  selecto = Selecto.from_ecto(MyApp.Repo, MyApp.User)

  # With joins and options
  selecto = Selecto.from_ecto(MyApp.Repo, MyApp.User,
    joins: [:posts, :profile],
    redact_fields: [:password_hash]
  )

  # With validation
  selecto = Selecto.from_ecto(MyApp.Repo, MyApp.User, validate: true)

gen_sql(selecto, opts)

@spec gen_sql(
  t(),
  keyword()
) :: {String.t(), map(), list()}

group_by(selecto, groups)

@spec group_by(t(), [Selecto.Types.field_name()]) :: t()

@spec group_by(t(), Selecto.Types.field_name()) :: t()

Add to the Group By clause.

examples
Examples

import Selecto.Expr

selecto
|> Selecto.group_by(["customer.name"])
|> Selecto.group_by(rollup(["status"]))

highlight_sql(sql, style)

@spec highlight_sql(String.t(), :ansi | :markdown | nil) :: String.t()

Apply optional highlighting to SQL (:ansi or :markdown).

infer_type(selecto, expression)

@spec infer_type(t(), term()) ::
  {:ok, Selecto.TypeSystem.sql_type()} | {:error, term()}

Infer the SQL type of an expression.

Returns the type of a field, function, literal, or complex expression. Useful for type checking, validation, and UI components that need type information.

examples
Examples

# Field type lookup
{:ok, :string} = Selecto.infer_type(selecto, "product_name")

# Aggregate function
{:ok, :bigint} = Selecto.infer_type(selecto, {:count, "*"})

# Numeric aggregate
{:ok, :decimal} = Selecto.infer_type(selecto, {:sum, "price"})

# Literal
{:ok, :integer} = Selecto.infer_type(selecto, {:literal, 42})

intersect(left_query, right_query, opts \\ [])

Create an INTERSECT set operation between two queries.

Returns only rows that appear in both queries.

options
Options

:all - Use INTERSECT ALL to include duplicate intersections (default: false)
:column_mapping - Map columns between incompatible schemas

examples
Examples

# Find users who are both active and premium
active_users |> Selecto.intersect(premium_users)

# Include duplicate intersections
query1 |> Selecto.intersect(query2, all: true)

join(selecto, join_id, options \\ [])

@spec join(t(), atom(), keyword()) :: t()

Enable a join from the domain configuration or add a custom join dynamically.

This allows adding joins at runtime that either:

Enable predefined joins from the domain configuration
Add completely custom joins not in the domain

parameters
Parameters

join_id - The join identifier (atom)
options - Optional configuration overrides

options
Options

:type - Join type (:left, :inner, :right, :full). Default: :left
:source - Source table name (required for custom joins)
:on - Join conditions as list of maps with :left and :right keys
:owner_key - The key on the parent table
:related_key - The key on the joined table
:fields - Map of field configurations to expose from the joined table

examples
Examples

# Enable domain-configured join
selecto |> Selecto.join(:category)

# Custom join with explicit configuration
selecto |> Selecto.join(:audit_log,
  source: "audit_logs",
  on: [%{left: "id", right: "record_id"}],
  type: :left,
  fields: %{
    action: %{type: :string},
    created_at: %{type: :naive_datetime}
  }
)

join_parameterize(selecto, join_id, parameter, options \\ [])

@spec join_parameterize(t(), atom(), String.t() | atom(), keyword()) :: t()

Create a parameterized instance of an existing join.

Parameterized joins allow the same association to be joined multiple times with different filter conditions. The parameter creates a unique instance that can be referenced using dot notation: join_name:parameter.field_name

parameters
Parameters

join_id - Base join identifier to parameterize
parameter - Unique parameter value to identify this instance
options - Filter conditions and options

examples
Examples

# Create parameterized join for electronics products
selecto
|> Selecto.join_parameterize(:products, "electronics", category_id: 1)
|> Selecto.select(["products:electronics.product_name"])

# Multiple parameterized instances for comparison
selecto
|> Selecto.join_parameterize(:orders, "active", status: "active")
|> Selecto.join_parameterize(:orders, "completed", status: "completed")
|> Selecto.select([
    "orders:active.total as active_total",
    "orders:completed.total as completed_total"
  ])

join_subquery(selecto, join_id, join_selecto, options \\ [])

@spec join_subquery(t(), atom(), t(), keyword()) :: t()

Join with another Selecto query as a subquery.

This creates a join using a separate Selecto query as the right side, enabling complex subquery joins for aggregations and derived tables.

parameters
Parameters

join_id - Identifier for this join
join_selecto - The Selecto struct to use as subquery
options - Join configuration

options
Options

:type - Join type (:left, :inner, :right, :full). Default: :left
:on - Join conditions referencing the subquery alias

examples
Examples

# Create a subquery for aggregated data
order_totals = Selecto.configure(order_domain, connection)
|> Selecto.select(["customer_id", {:sum, "total", as: "total_spent"}])
|> Selecto.group_by(["customer_id"])

# Join aggregated subquery to main query
selecto
|> Selecto.join_subquery(:customer_totals, order_totals,
    on: [%{left: "customer_id", right: "customer_id"}]
  )
|> Selecto.select(["name", "customer_totals.total_spent"])

joins(selecto)

@spec joins(t()) :: map()

See Selecto.Fields.joins/1.

json_filter(selecto, json_filters, opts \\ [])

Add JSON operations to WHERE clauses for filtering with PostgreSQL JSON/JSONB functionality.

Supports JSON containment, existence, and comparison operations.

parameters
Parameters

selecto - The Selecto instance
json_filters - List of JSON filter tuples or single filter
opts - Options (reserved for future use)

examples
Examples

# JSON containment and existence
selecto
|> Selecto.json_filter([
    {:json_contains, "metadata", %{"category" => "electronics"}},
    {:json_path_exists, "metadata", "$.specs.warranty"}
  ])

# JSON path comparison
selecto
|> Selecto.json_filter([
    {:json_extract_text, "settings", "$.theme", {:=, "dark"}},
    {:json_extract, "data", "$.priority", {:>, 5}}
  ])

# Single JSON filter
selecto
|> Selecto.json_filter({:json_exists, "tags", "electronics"})

json_order_by(selecto, json_sorts, opts \\ [])

Add JSON operations to ORDER BY clauses for sorting with PostgreSQL JSON/JSONB functionality.

parameters
Parameters

selecto - The Selecto instance
json_sorts - List of JSON sort tuples or single sort
opts - Options (reserved for future use)

examples
Examples

# Sort by JSON path values
selecto
|> Selecto.json_order_by([
    {:json_extract, "metadata", "$.priority", :desc},
    {:json_extract_text, "data", "$.created_at", :asc}
  ])

# Single JSON sort
selecto
|> Selecto.json_order_by({:json_extract, "settings", "$.sort_order"})

json_rowset(selecto, source_field, opts \\ [])

@spec json_rowset(t(), atom() | String.t(), keyword() | map()) :: t()

Add a SQLite JSON rowset expansion using json_each or json_tree.

This helper registers the standard SQLite JSON rowset columns so they can be selected and filtered with qualified references such as item_rows.value.

json_select(selecto, json_operations, opts \\ [])

Add JSON operations to SELECT clauses for PostgreSQL JSON/JSONB functionality.

Supports JSON path extraction, aggregation, construction, and manipulation operations.

parameters
Parameters

selecto - The Selecto instance
json_operations - List of JSON operation tuples or single operation
opts - Options (reserved for future use)

examples
Examples

# JSON path extraction
selecto
|> Selecto.json_select([
    {:json_extract, "metadata", "$.category", as: "category"},
    {:json_extract, "metadata", "$.specs.weight", as: "weight"}
  ])

# JSON aggregation
selecto
|> Selecto.json_select([
    {:json_agg, "product_name", as: "products"},
    {:json_object_agg, "product_id", "price", as: "price_map"}
  ])
|> Selecto.group_by(["category"])

# Single JSON operation
selecto
|> Selecto.json_select({:json_extract, "data", "$.status", as: "status"})

json_table(selecto, source_field, opts \\ [])

@spec json_table(t(), atom() | String.t(), keyword() | map()) :: t()

Add a MySQL JSON_TABLE rowset expansion to the query.

This helper projects JSON-derived rows as a joinable rowset and registers the projected columns into the query config so they can be selected and filtered with qualified field references such as item_rows.sku.

lateral_join(selecto, arg2, arg3 \\ [], arg4 \\ nil, arg5 \\ [])

Add a LATERAL join to the query.

LATERAL joins allow the right side of the join to reference columns from the left side, enabling powerful correlated subquery patterns.

parameters
Parameters

join_type - Type of join (:left, :inner, :right, :full)
subquery_builder_or_function - Function that builds correlated subquery or table function tuple
alias_name - Alias for the LATERAL join results
opts - Additional options

examples
Examples

# LATERAL join with correlated subquery
selecto
|> Selecto.lateral_join(
  :left,
  fn base_query ->
    Selecto.configure(rental_domain, connection)
    |> Selecto.select([{:func, "COUNT", ["*"], as: "rental_count"}])
    |> Selecto.filter([{"customer_id", {:ref, "customer.customer_id"}}])
    |> Selecto.limit(5)
  end,
  "recent_rentals"
)

# LATERAL join with table function
selecto
|> Selecto.lateral_join(
  :inner,
  {:unnest, "film.special_features"},
  "features"
)

# LATERAL join with generate_series
selecto
|> Selecto.lateral_join(
  :inner,
  {:function, :generate_series, [1, 10]},
  "numbers"
)

limit(selecto, limit_value)

@spec limit(t(), non_neg_integer()) :: t()

Limit the number of rows returned by the query.

examples
Examples

# Limit to 10 rows
selecto |> Selecto.limit(10)

# Limit with offset for pagination
selecto |> Selecto.limit(10) |> Selecto.offset(20)

offset(selecto, offset_value)

@spec offset(t(), non_neg_integer()) :: t()

Set the offset for the query results.

examples
Examples

# Skip first 20 rows
selecto |> Selecto.offset(20)

# Pagination: page 3 with 10 items per page
selecto |> Selecto.limit(10) |> Selecto.offset(20)

order_by(selecto, orders)

@spec order_by(t(), [Selecto.Types.order_spec()]) :: t()

@spec order_by(t(), Selecto.Types.order_spec()) :: t()

Add to the Order By clause.

examples
Examples

import Selecto.Expr

selecto
|> Selecto.order_by([asc("inserted_at"), desc("priority")])

post_retarget_filter(selecto, filters_or_filter)

@spec post_retarget_filter(t(), [Selecto.Types.filter()]) :: t()

@spec post_retarget_filter(t(), Selecto.Types.filter()) :: t()

Append filters explicitly to the post-retarget filter list.

These filters apply to the retargeted target root.

post_retarget_filters(selecto)

@spec post_retarget_filters(t()) :: [Selecto.Types.filter()]

Read post-retarget filters from the query set (set.post_retarget_filters).

pre_retarget_filter(selecto, filters_or_filter)

@spec pre_retarget_filter(t(), [Selecto.Types.filter()]) :: t()

@spec pre_retarget_filter(t(), Selecto.Types.filter()) :: t()

Append filters explicitly to the pre-retarget filter list.

These filters stay attached to the original root side when using retarget/3.

pre_retarget_filters(selecto)

@spec pre_retarget_filters(t()) :: [Selecto.Types.filter()]

Read pre-retarget filters from the query set (set.filtered).

query_filters(selecto, opts \\ [])

@spec query_filters(
  t(),
  keyword()
) :: [Selecto.Types.filter()]

Return query filters from current filter buckets.

Options:

:include_post_retarget (default: true)

require_tenant_filter(selecto, filter)

@spec require_tenant_filter(t(), Selecto.Types.filter()) :: t()

Add a required tenant filter to query state.

require_tenant_filter(selecto, tenant_field, tenant_id)

@spec require_tenant_filter(t(), atom() | String.t(), term()) :: t()

required_filters(selecto)

@spec required_filters(t()) :: [Selecto.Types.filter()]

Return required filters currently attached to the query.

This includes domain-level required filters and tenant-scope required filters attached at runtime.

resolve_field(selecto, field)

@spec resolve_field(t(), Selecto.Types.field_name()) ::
  {:ok, map()} | {:error, term()}

Enhanced field resolution with disambiguation and error handling.

Provides detailed field information and helpful error messages.

retarget(selecto, target_schema, opts \\ [])

Retarget the query to focus on a different table while preserving existing context.

This allows you to retarget a Selecto query from the source table to any joined table, while preserving existing filters through subqueries.

examples
Examples

# Retarget from events to orders while preserving event filters
selecto
|> Selecto.filter([{"event_id", 123}])
|> Selecto.retarget(:orders)
|> Selecto.select(["product_name", "quantity"])

options
Options

:preserve_filters - Whether to preserve existing filters (default: true)
:subquery_strategy - How to generate the subquery (:in, :exists, :join)

See Selecto.Retarget module for more details.

select(selecto, fields_or_field)

@spec select(t(), [Selecto.Types.selector()]) :: t()

@spec select(t(), Selecto.Types.selector()) :: t()

Add fields to the select list.

For macro-free query composition, prefer importing Selecto.Expr and using string field paths plus runtime helper constructors.

examples
Examples

import Selecto.Expr

selecto
|> Selecto.select(["order_number", "customer.name", as(sum("total"), "customer_total")])
|> Selecto.select(count_distinct("customer.id"))

select_shape(selecto, shape)

@spec select_shape(t(), list() | tuple()) :: t()

Compile a nested selection shape and attach it to the query.

This is an opt-in structured selection API. Use execute_shape/2 to materialize results into the same list/tuple structure.

Nested lists/tuples that only reference a single joined schema are treated as subselect nodes.

set(selecto)

@spec set(t()) :: Selecto.Types.query_set()

See Selecto.Fields.set/1.

source_table(selecto)

@spec source_table(t()) :: Selecto.Types.table_name() | nil

See Selecto.Fields.source_table/1.

sqlite_fts_rank(selecto, fields, opts \\ [])

@spec sqlite_fts_rank(
  t(),
  atom() | String.t() | [atom() | String.t()],
  keyword() | map()
) :: t()

Add a SQLite FTS5 ranking selector using bm25(...).

This helper is intentionally narrow: all referenced fields must be configured as SQLite FTS5 fields on the same source alias.

subselect(selecto, field_specs, opts \\ [])

Add subselect fields to return related data as aggregated arrays.

This prevents result set denormalization while maintaining relational context by returning related data as JSON arrays, PostgreSQL arrays, or other formats.

examples
Examples

# Basic subselect - get orders as JSON for each attendee
selecto
|> Selecto.select(["attendee.name"])
|> Selecto.subselect(["order.product_name", "order.quantity"])

# With custom configuration
selecto
|> Selecto.subselect([
     %{
       fields: ["product_name", "quantity"],
       target_schema: :order,
       format: :json_agg,
       alias: "order_items"
     }
   ])

options
Options

:format - Aggregation format (:json_agg, :array_agg, :string_agg, :count)
:alias_prefix - Prefix for generated field aliases

See Selecto.Subselect module for more details.

tenant(selecto)

@spec tenant(t()) :: map() | nil

Read tenant context from query state.

tenant_required?(selecto, opts \\ [])

@spec tenant_required?(
  t(),
  keyword()
) :: boolean()

Return whether tenant scope is required for this query.

text_search_rank(selecto, fields, opts \\ [])

@spec text_search_rank(
  t(),
  atom() | String.t() | [atom() | String.t()],
  keyword() | map()
) :: t()

Add an adapter-aware text-search rank selector.

This is the shared entry point for ranking support. Adapters can map it onto their native ranking primitives where available.

to_sql(selecto, opts \\ [])

@spec to_sql(
  t(),
  keyword()
) :: {String.t(), list()}

Generate SQL without executing - useful for debugging and caching.

Supports optional readability controls:

pretty: true
highlight: :ansi | :markdown

type_category(type)

@spec type_category(Selecto.TypeSystem.sql_type()) ::
  Selecto.TypeSystem.type_category()

Get the type category for a given SQL type.

Categories: :numeric, :string, :boolean, :datetime, :json, :array, :binary, :uuid, :unknown

types_compatible?(type1, type2)

@spec types_compatible?(Selecto.TypeSystem.sql_type(), Selecto.TypeSystem.sql_type()) ::
  boolean()

Check if two SQL types are compatible for comparisons or assignments.

examples
Examples

true = Selecto.types_compatible?(:integer, :decimal)
false = Selecto.types_compatible?(:string, :boolean)

udf(function_id, args \\ [])

@spec udf(atom() | String.t(), [term()] | term()) :: tuple()

Build a registered scalar or predicate UDF expression.

This is a convenience wrapper around the normalized {:udf, function_id, args} shape used by selectors and filters.

examples
Examples

Selecto.udf("similarity", ["name", "Acme"])
Selecto.udf(:matches_name, ["name", "Acme%"])

udf_table(function_id, args \\ [])

@spec udf_table(atom() | String.t(), [term()] | term()) :: tuple()

Build a registered table-UDF expression for lateral joins and named laterals.

examples
Examples

Selecto.udf_table("nearby_points", ["location", 500])
Selecto.udf_table(:nearby_points, ["location", 500])

union(left_query, right_query, opts \\ [])

Create a UNION set operation between two queries.

Combines results from multiple queries using UNION or UNION ALL. All queries must have compatible column counts and types.

options
Options

:all - Use UNION ALL to include duplicates (default: false)
:column_mapping - Map columns between incompatible schemas

examples
Examples

# Basic UNION (removes duplicates)
query1 |> Selecto.union(query2)

# UNION ALL (includes duplicates, faster)
query1 |> Selecto.union(query2, all: true)

# UNION with column mapping
customers |> Selecto.union(vendors,
  column_mapping: [
    {"name", "company_name"},
    {"email", "contact_email"}
  ]
)

unnest(selecto, array_field, opts \\ [])

Add an UNNEST operation to expand array columns into rows.

UNNEST transforms array values into a set of rows, one for each array element. Can optionally include ordinality to track position in the array.

examples
Examples

# Basic unnest
selecto |> Selecto.unnest("tags", as: "tag")

# Unnest with ordinality (includes position)
selecto |> Selecto.unnest("tags", as: "tag", ordinality: "tag_position")

# Multiple unnests (will be cross-joined)
selecto
|> Selecto.unnest("tags", as: "tag")
|> Selecto.unnest("categories", as: "category")

validate_tenant_scope(selecto, opts \\ [])

@spec validate_tenant_scope(
  t(),
  keyword()
) :: :ok | {:error, Selecto.Error.t()}

Validate tenant scope and return :ok or structured validation error.

window_function(selecto, function, arguments \\ [], options)

Add a window function to the query.

Window functions provide analytical capabilities over a set of rows related to the current row, without grouping rows into a single result.

examples
Examples

# Add row numbers within each category
selecto |> Selecto.window_function(:row_number,
  over: [partition_by: ["category"], order_by: ["created_at"]])

# Calculate running total
selecto |> Selecto.window_function(:sum, ["amount"],
  over: [partition_by: ["user_id"], order_by: ["date"]],
  as: "running_total")

# Get previous value for comparison
selecto |> Selecto.window_function(:lag, ["amount", 1],
  over: [partition_by: ["user_id"], order_by: ["date"]],
  as: "prev_amount")

with_cte(selecto, member_id)

Add a Common Table Expression (CTE) to the query using WITH clause.

CTEs provide a way to create temporary named result sets that can be referenced within the main query, enabling query modularity and readability.

parameters
Parameters

selecto - The Selecto instance
name - CTE name (must be valid SQL identifier)
query_builder - Function that returns a Selecto query for the CTE
opts - Options including :columns, :dependencies, and optional :join

Join shortcut options (:join):

true - infer join keys from first declared CTE column
keyword/map - any Selecto.join/3 options (:type, :owner_key, :related_key, :on, :fields)
fields: :infer - infer join fields from declared CTE columns

examples
Examples

# Simple CTE for filtering
selecto
|> Selecto.with_cte("active_customers", fn ->
    Selecto.configure(customer_domain, connection)
    |> Selecto.filter([{"active", true}])
  end,
  join: [owner_key: :customer_id, related_key: :customer_id]
)
|> Selecto.select(["film.title", "active_customers.first_name"])

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

# Named CTE member from domain.query_members.ctes
selecto
|> Selecto.with_cte(:active_customers)
|> Selecto.select(["film.title", "active_customers.first_name"])

# Generated SQL:
# WITH active_customers AS (
#   SELECT * FROM customer WHERE active = true
# )
# SELECT film.title, active_customers.first_name
# FROM film
# INNER JOIN active_customers ON rental.customer_id = active_customers.customer_id

with_cte(selecto, member_id, opts)

with_cte(selecto, name, query_builder, opts \\ [])

with_ctes(selecto, cte_specs, opts \\ [])

Add multiple CTEs to the query in a single operation.

Useful for complex queries that require multiple temporary result sets. CTEs will be automatically ordered based on their dependencies.

parameters
Parameters

selecto - The Selecto instance
cte_specs - List of CTE specifications created with create_cte/3
opts - Options including :joins for auto-joining one or more CTEs

Batch join shortcut options (:joins):

true - auto-join every provided CTE (default key inference)
list of entries where each entry is one of:
- cte name ("my_cte" or :my_cte) to use inferred defaults
- {name, join_opts} tuple
- keyword/map with :name plus join options

examples
Examples

# Multiple related CTEs
active_customers_cte = Selecto.Advanced.CTE.create_cte("active_customers", fn ->
  Selecto.configure(customer_domain, connection)
  |> Selecto.filter([{"active", true}])
end)

high_value_cte = Selecto.Advanced.CTE.create_cte("high_value_customers", fn ->
  Selecto.configure(customer_domain, connection)
  |> Selecto.aggregate([{"payment.amount", :sum, as: "total_spent"}])
  |> Selecto.join(:inner, "payment", on: "customer.customer_id = payment.customer_id")
  |> Selecto.group_by(["customer.customer_id"])
  |> Selecto.having([{"total_spent", {:>, 100}}])
end, dependencies: ["active_customers"])

selecto
|> Selecto.with_ctes([active_customers_cte, high_value_cte],
  joins: [
    [name: "active_customers", owner_key: :customer_id, related_key: :customer_id],
    [name: "high_value_customers", owner_key: :customer_id, related_key: :customer_id]
  ]
)
|> Selecto.select(["film.title", "high_value_customers.total_spent"])

with_lateral(selecto, member_id, opts \\ [])

@spec with_lateral(t(), atom() | String.t(), keyword() | map()) :: t()

Apply a LATERAL source directly or resolve a named LATERAL preset from domain.query_members.laterals.

examples
Examples

selecto
|> Selecto.with_lateral(:recent_rentals)

selecto
|> Selecto.with_lateral(:recent_rentals, join_type: :inner)

selecto
|> Selecto.with_lateral(Selecto.udf_table("nearby_points", ["location", 500]),
  as: "nearby_points",
  join_type: :left
)

with_recursive_cte(selecto, arg2, arg3, arg4 \\ nil, arg5 \\ [])

Add a recursive Common Table Expression (CTE) to the query.

Recursive CTEs enable hierarchical queries by combining an anchor query with a recursive query that references the CTE itself.

parameters
Parameters

selecto - The Selecto instance
name - CTE name (must be valid SQL identifier)
opts - Options with :base_query, :recursive_query, and optional :join

Join shortcut options (:join) follow with_cte/4.

examples
Examples

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

# Generated SQL:
# WITH RECURSIVE employee_hierarchy AS (
#   SELECT employee_id, name, manager_id, 0 as level
#   FROM employee
#   WHERE manager_id IS NULL
#   UNION ALL
#   SELECT employee.employee_id, employee.name, employee.manager_id, employee_hierarchy.level + 1
#   FROM employee
#   INNER JOIN employee_hierarchy ON employee.manager_id = employee_hierarchy.employee_id
# )
# SELECT employee_hierarchy.employee_id, employee_hierarchy.name, employee_hierarchy.level
# FROM employee
# INNER JOIN employee_hierarchy ON employee.employee_id = employee_hierarchy.employee_id
# ORDER BY employee_hierarchy.level ASC, employee_hierarchy.name ASC

with_subquery(selecto, member_id, opts \\ [])

@spec with_subquery(t(), atom() | String.t(), keyword() | map()) :: t()

Apply a named subquery preset from domain.query_members.subqueries.

This resolves a preconfigured subquery member and applies Selecto.join_subquery/4 with optional overrides.

examples
Examples

selecto
|> Selecto.with_subquery(:high_value_orders)

selecto
|> Selecto.with_subquery(:high_value_orders, type: :left)

with_tenant(selecto, tenant_context)

@spec with_tenant(t(), map() | keyword() | String.t() | atom() | nil) :: t()

Attach tenant context to the query state.

with_unnest(selecto, member_id, opts \\ [])

@spec with_unnest(t(), atom() | String.t(), keyword() | map()) :: t()

Apply a named UNNEST preset from domain.query_members.unnests.

examples
Examples

selecto
|> Selecto.with_unnest(:product_tags)

with_values(selecto, data_or_member, opts \\ [])

Add a VALUES clause to create an inline table from literal data.

VALUES clauses allow creating inline tables from literal values, useful for data transformations, lookup tables, and testing scenarios.

parameters
Parameters

selecto - The Selecto struct
data - List of data rows (lists or maps)
opts - Options including :columns (explicit column names), :as (table alias), and optional :join configuration to auto-join the VALUES table

examples
Examples

# Basic VALUES table with explicit columns
selecto
|> Selecto.with_values([
    ["PG", "Family Friendly", 1],
    ["PG-13", "Teen", 2],
    ["R", "Adult", 3]
  ],
  columns: ["rating_code", "description", "sort_order"],
  as: "rating_lookup"
)

# Map-based VALUES (columns inferred from keys)
selecto
|> Selecto.with_values([
    %{month: 1, name: "January", days: 31},
    %{month: 2, name: "February", days: 28},
    %{month: 3, name: "March", days: 31}
  ], as: "months")

# VALUES with auto-join (fields inferred from VALUES columns)
selecto
|> Selecto.with_values([
    ["processing", "In Progress"],
    ["shipped", "In Transit"]
  ],
  columns: ["status", "status_label"],
  as: "status_labels",
  join: [owner_key: :status, related_key: :status]
)
|> Selecto.select(["order_number", "status_labels.status_label"])

# Named VALUES member from domain.query_members.values
selecto
|> Selecto.with_values(:status_labels)
|> Selecto.select(["order_number", "status_labels.status_label"])

# Generated SQL:
# WITH rating_lookup (rating_code, description, sort_order) AS (
#   VALUES ('PG', 'Family Friendly', 1),
#          ('PG-13', 'Teen', 2),
#          ('R', 'Adult', 3)
# )

Settings Selecto (Selecto v0.4.3)

domain-configuration Domain Configuration

query-execution Query Execution

safe-execution-non-raising Safe Execution (Non-raising)

error-types Error Types

Link to this section Summary

Types

Functions

Link to this section Types

t()

Link to this section Functions

apply_tenant_scope(selecto, opts \\ [])

array_filter(selecto, array_filters, opts \\ [])

parameters Parameters

examples Examples

array_manipulate(selecto, array_operations, opts \\ [])

parameters Parameters

examples Examples

array_select(selecto, array_operations, opts \\ [])

parameters Parameters

examples Examples

available_fields(selecto)

case_select(selecto, column, when_clauses, opts \\ [])

parameters Parameters

examples Examples

case_when_select(selecto, when_clauses, opts \\ [])

parameters Parameters

examples Examples

columns(selecto)

configure(domain, postgrex_opts, opts \\ [])

detail_actions(selecto)

domain(selecto)

domain_data(selecto)

ensure_tenant_scope!(selecto, opts \\ [])

except(left_query, right_query, opts \\ [])

options Options

examples Examples

execute(selecto, opts \\ [])

execute_one(selecto, opts \\ [])

execute_shape(selecto, opts \\ [])

execute_stream(selecto, opts \\ [])

options Options

notes Notes

execute_with_metadata(selecto, opts \\ [])

parameters Parameters

returns Returns

examples Examples

explain(selecto, opts \\ [])

explain_analyze(selecto, opts \\ [])

extensions(selecto)

field(selecto, field_name)

field_suggestions(selecto, partial_name)

filter(selecto, filters_or_filter)

examples Examples

filters(selecto)

format_sql(sql, opts \\ [])

from_ecto(repo, schema, opts \\ [])

gen_sql(selecto, opts)

group_by(selecto, groups)

examples Examples

highlight_sql(sql, style)

infer_type(selecto, expression)

examples Examples

intersect(left_query, right_query, opts \\ [])

options Options

examples Examples

join(selecto, join_id, options \\ [])

parameters Parameters

options Options

examples Examples

join_parameterize(selecto, join_id, parameter, options \\ [])

parameters Parameters

examples Examples

join_subquery(selecto, join_id, join_selecto, options \\ [])

parameters Parameters

options Options

examples Examples

joins(selecto)

json_filter(selecto, json_filters, opts \\ [])

parameters Parameters

Selecto (Selecto v0.4.3)

domain-configuration
Domain Configuration

query-execution
Query Execution

safe-execution-non-raising
Safe Execution (Non-raising)

error-types
Error Types

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

options
Options

examples
Examples

options
Options

notes
Notes

parameters
Parameters

returns
Returns

examples
Examples

examples
Examples

examples
Examples

examples
Examples

options
Options

examples
Examples

parameters
Parameters

options
Options

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

options
Options

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

examples
Examples

examples
Examples

examples
Examples

examples
Examples

options
Options

examples
Examples

examples
Examples

options
Options

examples
Examples

examples
Examples

examples
Examples

options
Options

examples
Examples

examples
Examples

examples
Examples

parameters
Parameters

examples
Examples

parameters
Parameters

examples
Examples

examples
Examples

parameters
Parameters