Wrap multiple arguments for use with from_opts(..., mode: :full).

from_opts passes each {operation, value} as a single argument to the operation (i.e. it calls operation(query, value)). Use args/* when you need to call an operation with multiple arguments (like order_by/3, select/3, where/3, or custom extension functions).

Examples:

QueryBuilder.from_opts(User, [order_by: QueryBuilder.args(:role, asc: :name@role)], mode: :full)
QueryBuilder.from_opts(User, [where: QueryBuilder.args(:role, [name@role: "admin"])], mode: :full)

Aggregate helper: array_agg (Postgres-only).

This returns an aggregate expression that can be used in select/*, order_by/*, and having/* (typically with group_by/*).

Options:

• :distinct? (optional) - boolean (default: false)
• :order_by (optional) - a keyword list like order_by/* (default: []). When distinct?: true, order_by is restricted to ordering by the aggregated token itself (Postgres restriction for DISTINCT aggregates). Supports up to 5 order terms.
• :filter (optional) - a filter DSL value (like where/*), an Ecto dynamic, or a 1-arity function that returns a dynamic. This is compiled into FILTER (WHERE ...) (Postgres). The DSL form is AND-only; for OR logic, use a dynamic/function.

Examples:

Article
|> QueryBuilder.group_by(:author_id)
|> QueryBuilder.select(%{
  author_id: :author_id,
  publisher_ids: QueryBuilder.array_agg(:publisher_id, distinct?: true, order_by: [asc: :publisher_id])
})
|> Repo.all()

Aggregate helpers for grouped queries.

These return aggregate expressions that can be used in select/*, order_by/*, and having/*.

Examples:

QueryBuilder.count(:id)
QueryBuilder.count(:id, :distinct)
QueryBuilder.sum(:amount)

A distinct query expression.

When passed true/false, this sets DISTINCT for the current select expression.

You can also pass order_by-like expressions (tokens/directions) to generate DISTINCT ON (...) on databases that support it (e.g. Postgres).

Ensures the query returns unique root rows (dedupe by root primary key).

This is primarily useful when you must join a to-many association (e.g. for filtering/order_by) but still want a unique list of root structs.

Postgres-only: uses DISTINCT ON (root_pk...) (via Ecto distinct expressions). In join-multiplying queries, order_by determines which joined row “wins” for each root.

Notes:

• Requires the root schema to have a primary key.
• Requires a database that supports DISTINCT ON (Postgres).
• Cannot be combined with preload_through_join on to-many associations (it would drop association rows).

A shorthand for top_n_per/2 with n: 1.

Accepts the same options as top_n_per/2 (except :n is fixed to 1).

Postgres-only.

Example:

# one latest post per subreddit
Post
|> QueryBuilder.first_per(partition_by: [:subreddit_id], order_by: [desc: :score, desc: :id])
|> Repo.all()

Applies a keyword list of operations to a query.

By default, from_opts/2 runs in boundary mode: it only allows a small join-independent subset of operations so callers don’t need to know whether the base query happens to join/preload anything.

To opt into the full power of from_opts, use mode: :full.

Examples:

# boundary (default)
QueryBuilder.from_opts(query, [
  where: [name: "John"],
  order_by: [desc: :inserted_at],
  limit: 50
])

# full (trusted internal usage)
QueryBuilder.from_opts(query, [
  where: QueryBuilder.args(:role, [name@role: "admin"]),
  preload_separate: :role
], mode: :full)

A group by query expression.

Example:

QueryBuilder.group_by(query, :category)

An AND having query expression.

Like where, but applied after grouping.

An OR having query expression (an OR of AND groups).

An inner join query expression.

This emits INNER JOINs for the given association path. It is “just join”: it does not apply filters.

Example:

QueryBuilder.inner_join(query, [authored_articles: :comments])

A join query expression.

Example:

QueryBuilder.left_join(query, :articles, title@articles: "Foo", or: [title@articles: "Bar"])

Notes:

• left_join/4 only supports leaf associations (no nested assoc paths). For nested paths, use left_join_leaf/4 or left_join_path/4.

Left-joins the latest row of a has_many association and selects {root, assoc}.

This is a helper for “parent rows + latest child row joined” without multiplying parent rows.

Postgres-only: emits LEFT JOIN LATERAL (...) LIMIT 1.

Supported options:

• order_by: (required) - a keyword list like order_by/2 (applied in the assoc subquery)
• where: (optional) - filters like where/2 (applied in the assoc subquery)
• child_assoc_fields: (optional) - assoc tree to join inside the assoc subquery (to support tokens like field@assoc)

Notes:

• Only supports a single, direct has_many association (no nested paths).
• The order_by: must include the assoc schema primary key fields as a tie-breaker.
• This sets a custom select ({root, assoc}), so it cannot be used with paginate_cursor/3 or paginate_offset/3.

Example:

User
|> QueryBuilder.order_by(asc: :id)
|> QueryBuilder.left_join_latest(:authored_articles, order_by: [desc: :inserted_at, desc: :id])
|> Repo.all()
# => [{%User{}, %Article{} | nil}, ...]

Left-joins the leaf association and uses inner joins to traverse intermediate associations in a nested path.

This is the explicit version of the historical nested left_join/4 behavior.

Example (INNER authored_articles, LEFT comments):

User
|> QueryBuilder.left_join_leaf([authored_articles: :comments])
|> Repo.all()

Left-joins every hop in a nested association path (a full left-joined chain).

Example (LEFT authored_articles, LEFT comments):

User
|> QueryBuilder.left_join_path([authored_articles: :comments])
|> Repo.all()

Left-joins the top N rows of a has_many association and selects {root, assoc}.

This is a helper for “parent rows + top N child rows joined”, returning multiple {parent, child} rows per parent.

Postgres-only: emits LEFT JOIN LATERAL (...) LIMIT n.

Supported options:

• n: (required) - a positive integer (applied as a LIMIT in the assoc subquery)
• order_by: (required) - a keyword list like order_by/2 (applied in the assoc subquery)
• where: (optional) - filters like where/2 (applied in the assoc subquery)
• child_assoc_fields: (optional) - assoc tree to join inside the assoc subquery (to support tokens like field@assoc)

Notes:

• Only supports a single, direct has_many association (no nested paths).
• The order_by: must include the assoc schema primary key fields as a tie-breaker.
• This multiplies parent rows (up to n rows per parent).
• This sets a custom select ({root, assoc}), so it cannot be used with paginate_cursor/3 or paginate_offset/3.

Example:

User
|> QueryBuilder.left_join_top_n(:authored_articles, n: 3, order_by: [desc: :inserted_at, desc: :id])
|> Repo.all()
# => [{%User{}, %Article{} | nil}, ...]

A limit query expression. If multiple limit expressions are provided, the last expression is evaluated

Example:

QueryBuilder.limit(query, 10)

Run QueryBuilder.order_by/2 only if given condition is met.

Run QueryBuilder.order_by/3 only if given condition is met.

Run QueryBuilder.where/2 only if given condition is met.

Run QueryBuilder.where/4 only if given condition is met.

A offset query expression. If multiple offset expressions are provided, the last expression is evaluated

Example:

QueryBuilder.offset(query, 10)

An order by query expression.

Example:

QueryBuilder.order_by(query, asc: :lastname, asc: :firstname)

An order by query expression.

For more about the second argument, see where/3.

Example:

QueryBuilder.order_by(query, :articles, asc: :title@articles)

Preloads associations using separate queries (Ecto's default preload behavior).

This always performs query-preload, even if the association is joined in SQL.

Example:

QueryBuilder.preload_separate(query, [role: :permissions, articles: [:stars, comments: :user]])

Preloads a (direct) association using a separate query with an explicit scope.

This is the QueryBuilder equivalent of Ecto’s query-based separate preload:

User
|> preload([u],
  authored_articles:
    ^from(a in assoc(u, :authored_articles),
      where: a.published == true,
      order_by: [desc: a.inserted_at]
    )
)

Supported options:

• where: filters (QueryBuilder where/2 filter shape)
• order_by: keyword list (QueryBuilder order_by/2 shape)

Restrictions (fail-fast):

• Only supports a single, direct association (no nested paths).
• Filters/order_by must reference fields on the association schema (no @assoc tokens).
• Does not accept custom filter/order_by functions. Use an Ecto preload query for advanced cases.
• Cannot be combined with nested preloads under the same association; use an explicit Ecto query-based preload query instead.

Preloads associations through join bindings (join-preload).

This requires the association to already be joined (for example because you filtered through it, ordered by it, or explicitly joined it with left_join/2). If the association isn't joined, this raises ArgumentError.

Example:

User
|> QueryBuilder.left_join(:role)
|> QueryBuilder.preload_through_join(:role)

A select query expression.

Selection supports:

• a single field token (:name or :name@role) → selects a single value
• a tuple of tokens/values → selects a tuple
• a list of field tokens → selects a map keyed by the tokens
• a map of output keys to field tokens → selects a map with your keys
• a custom 1-arity function escape hatch (receives a token resolver)

Examples:

User |> QueryBuilder.select(:name) |> Repo.all()

User |> QueryBuilder.select([:id, :name]) |> Repo.all()
# => [%{id: 100, name: "Alice"}, ...]

User |> QueryBuilder.select(:role, %{role_name: :name@role}) |> Repo.all()

User |> QueryBuilder.select({:id, :name}) |> Repo.all()
# => [{100, "Alice"}, ...]

Like Ecto, only one select expression is allowed. Calling select/* more than once (or calling select/* after select_merge/*) raises. Use select_merge/* to accumulate fields into the selection.

Note: paginate_cursor/3 / paginate_offset/3 require selecting the root struct; using select/* will make pagination raise (fail-fast).

A select_merge query expression.

This merges a map into the existing selection (Ecto select_merge semantics).

Notes:

• If there is no prior select, Ecto merges into the root struct by default.
• select_merge requires explicit keys for field@assoc values (use a map).
• paginate_cursor/3 / paginate_offset/3 require selecting the root struct; any custom select expression (including select_merge) will make pagination raise (fail-fast).

Examples:

User
|> QueryBuilder.select_merge(%{name: :name})
|> Repo.all()

User
|> QueryBuilder.select_merge(:role, %{role_name: :name@role})
|> Repo.all()

Builds an Ecto.SubQuery using QueryBuilder operations.

This is a convenience wrapper around from_opts/3 (mode: :full) + Ecto.Query.subquery/1.

Example:

user_ids =
  QueryBuilder.subquery(User,
    where: [deleted: false],
    select: :id
  )

Article
|> QueryBuilder.where({:author_id, :in, user_ids})
|> Repo.all()

Keeps the top N rows per group (window-function helper).

This ranks rows with row_number() OVER (PARTITION BY ... ORDER BY ...) and filters to rn <= n, while returning the original root rows.

Postgres-only: uses window functions for n > 1; uses DISTINCT ON for n: 1 when the query has no distinct.

Options:

• :partition_by (required) - a token or list of tokens/expressions
• :order_by (required) - a keyword list like order_by/*
• :n (required) - a positive integer
• :disable_distinct_on? (optional) - when true and n: 1, forces the window-function plan (no DISTINCT ON)

Notes:

• Requires the root schema to have a primary key (used as a deterministic tie-breaker, and to join back for window-function ranking).
• order_by must include the root primary key fields as a tie-breaker.
• Must be applied before order_by/* (apply the final ordering after top_n_per/*).
• Must be applied before limit/2 and offset/2.

Examples:

# latest order per user
Order
|> QueryBuilder.top_n_per(partition_by: [:user_id], order_by: [desc: :created_at, desc: :id], n: 1)
|> Repo.all()

An AND where query expression.

Example:

QueryBuilder.where(query, firstname: "John")

An AND where query expression.

Associations are passed in second argument; fields from these associations can then be referenced by writing the field name, followed by the "@" character and the association name, as an atom. For example: :name@users.

Example:

QueryBuilder.where(query, [role: :permissions], name@permissions: :write)

OR clauses may be passed through last argument opts. For example:

QueryBuilder.where(query, [], [firstname: "John"], or: [firstname: "Alice", lastname: "Doe"], or: [firstname: "Bob"])

An OR where query expression (an OR of AND groups).

Examples:

QueryBuilder.where_any(query, [[firstname: "John"], [firstname: "Alice", lastname: "Doe"]])

QueryBuilder.where_any(query, :role, [[name@role: "admin"], [name@role: "author"]])

A correlated EXISTS(...) subquery filter.

This is the explicit alternative to where/4 when filtering through to-many associations would otherwise duplicate root rows (SQL join multiplication).

Example:

User
|> QueryBuilder.where_exists_subquery(
  [authored_articles: :comments],
  where: [title@comments: "It's great!"],
  scope: []
)
|> Repo.all()

scope: is required to make the “new query block” boundary explicit. It is applied inside the EXISTS(...) subquery (and is not inferred from outer joins). Pass scope: [] to explicitly declare “no extra scoping”.

Tuple filters inside the subquery must target association fields (use field@assoc). Root-field filters belong on the outer query. For per-parent comparisons, use field-to-field values via the @self marker (e.g. {:inserted_at@articles, :gt, :inserted_at@self}).

where: adds AND filters inside the subquery. To express OR groups, use where_any: [[...], ...].

A shorthand for where_exists_subquery/3 (“has associated rows”).

It applies a correlated EXISTS(...) filter for the given association path. filters are AND-ed inside the subquery. Filter fields must be explicit association tokens (contain @), because the predicate runs inside the association subquery.

Examples:

# Equivalent to where_exists_subquery(:authored_articles, where: [published@authored_articles: true], scope: [])
User |> QueryBuilder.where_has(:authored_articles, published@authored_articles: true)

User |> QueryBuilder.where_has([authored_articles: :comments], title@comments: "It's great!")

A shorthand for where_not_exists_subquery/3 (“missing associated rows”).

This is the NOT EXISTS(...) counterpart to where_has/3. Filter fields must be explicit association tokens (contain @), because the predicate runs inside the association subquery.

A correlated NOT EXISTS(...) subquery filter.

Example:

User
|> QueryBuilder.where_not_exists_subquery(:authored_articles, where: [], scope: [])
|> Repo.all()

where: adds AND filters inside the subquery. To express OR groups, use where_any: [[...], ...].