ecto 3.12.5: Compose dynamic filters, merge select projections, exclude AST clauses, reverse ordering, and attach CTEs and windows with Ecto.Query

[
  inputs: ["{mix,.formatter}.exs", "{config,lib,test}/**/*.{ex,exs}"]
]

/_build/
/cover/
/deps/
/doc/
/.fetch
erl_crash.dump
*.ez
csx_ecto_query_composition-*.tar
/tmp/

# Ecto Query Composition and AST Manipulation

## Known Solutions Search

A search via `search_known_solution` for `pkg:hex/ecto@3.12.5` returned existing samples covering optimistic locking (`sha256:1e2a19ac37ba6adfa3f491b086bfc7d9a193727d9a750b88d8379b12a21249fb`), `Ecto.Multi` transaction pipelines (`sha256:410bddea995d11fd7cd016edb8d816954a28483bf3ba96f8594332e0cbd59730`), `Ecto.Enum` and parameterized types (`sha256:09bd86ace16d87b9a1aee811d766956654989863c84cb0a9960d07ac7c5a7b0f`, `sha256:6c6fb613f0a59efec76da1c4c4b630f16524102c374f9718430edeb1abd1742e`), embedded schemas with lifecycle traps (`sha256:ae752db5d7927ae9032a038840a9dc4c05edb909194c15efd4a16dd03309aff5`), and query ellipsis binding syntax (`sha256:95a0b23d5d25c5fc2a24c9a208120b31c751ddb24af09fd6068501821e9682db`). None verified pure query composition, AST projection merges, clause exclusion, reverse ordering semantics, and CTE/window registration.

## Pinned Release

This sample pins `ecto 3.12.5` on the Hex ecosystem.

## Observed Behaviour

1. **Dynamic Boolean Filters (`Ecto.Query.dynamic/2`)**:
   - `dynamic/2` generates an `%Ecto.Query.DynamicExpr{}` struct representing composable query conditions.
   - Dynamic expressions can be nested and combined using boolean operators (`and`, `or`, `not`).
   - Starting an accumulator from `dynamic(true)` enables clean reduction over arbitrary runtime filter parameters.

2. **Projection Augmentation (`Ecto.Query.select_merge/3`)**:
   - Merging into an existing map projection (`select: %{...}`) produces a unified compile-time map AST `{:%{}, [], [...]}` containing both original and newly added keys.
   - Merging into a schema struct projection (`select: %User{...}`) preserves the struct schema wrapper `{:%, [], [User, ...]}`.
   - Calling `select_merge/3` on a query without a prior `select` expression wraps the root binding and new fields in a `{:merge, [], [...]}` expression AST.

3. **Clause Exclusion (`Ecto.Query.exclude/2`)**:
   - `exclude(query, clause)` strips specific query clauses (`:where`, `:order_by`, `:select`, `:join`) while preserving all other clauses, bindings, and options.

4. **Reverse Ordering (`Ecto.Query.reverse_order/1`)**:
   - Reverses all explicit ordering directions: `:asc` becomes `:desc`, `:desc` becomes `:asc`, `:asc_nulls_first` becomes `:desc_nulls_last`, and `:desc_nulls_first` becomes `:asc_nulls_last`.
   - When invoked on an unordered schema query, it defaults to ordering descending by the schema primary key (`[desc: u.id]`).
   - When invoked on an unordered schemaless query (`from u in "users"`), it raises `Ecto.QueryError` requiring a schema from expression.

5. **Common Table Expressions & Windows (`Ecto.Query.with_cte/3` and `Ecto.Query.windows/3`)**:
   - `with_cte/3` registers CTE subqueries in `%Ecto.Query.WithExpr{}` with options (`materialized: true`, `operation: :all`).
   - `windows/3` and `over/2` attach named window definitions in `%Ecto.Query.ByExpr{}` and construct window function expressions.

{"buildCommand":["mix","deps.compile","--no-deps-check"],"case":{"caseId":"case:sha256:aa9bd99e063914b021903d74a3c07c4b351b44cf8b686774a2e967db8ef1ad54","contract":["Ecto.Query.dynamic constructs composable boolean AST expressions that interpolate into query filter clauses and combine via boolean operators","select_merge statically combines map or struct projections into a unified select AST while wrapping unprojected queries in a merge expression","exclude strips individual AST clauses such as where, order_by, select, and join while preserving remaining query composition","reverse_order inverts ordering directions and null positioning on ordered queries and defaults to descending primary key order for unordered schema queries","with_cte and windows register Common Table Expression subqueries and window specifications into the query AST"],"goal":"Compose dynamic filters, merge select projections, exclude AST clauses, reverse ordering, and attach CTEs and windows with Ecto.Query","kind":"HOW","packages":["pkg:hex/ecto@3.12.5"],"schemaVersion":1,"symbols":["Ecto.Query.dynamic","Ecto.Query.select_merge","Ecto.Query.exclude","Ecto.Query.reverse_order","Ecto.Query.with_cte","Ecto.Query.windows"]},"contractCommand":["mix","test","--no-deps-check"],"environment":{"arch":"x64","ecosystem":"hex","executionContext":"elixir","language":"elixir","os":"linux","packageManager":"mix","runtime":"elixir","schemaVersion":1},"license":"MIT-0","packages":["pkg:hex/ecto@3.12.5"],"schemaVersion":1,"symbols":["Ecto.Query.dynamic","Ecto.Query.select_merge","Ecto.Query.exclude","Ecto.Query.reverse_order","Ecto.Query.with_cte","Ecto.Query.windows"],"verifierAdapter":"hex@1"}

defmodule CsxEctoQueryComposition do
  @moduledoc """
  Proves Ecto.Query composition, dynamic expressions, select_merge, exclude, reverse_order,
  with_cte, and window functions in Ecto 3.12.5.
  """
end

defmodule CsxEctoQueryComposition.Post do
  @moduledoc """
  Post schema for demonstrating joins and CTEs.
  """
  use Ecto.Schema

  schema "posts" do
    field(:title, :string)
    field(:views, :integer, default: 0)
    field(:published, :boolean, default: false)
    belongs_to(:user, CsxEctoQueryComposition.User)

    timestamps(type: :utc_datetime)
  end
end

defmodule CsxEctoQueryComposition.QueryBuilder do
  @moduledoc """
  Reusable query composition patterns using Ecto.Query.
  """
  import Ecto.Query

  alias CsxEctoQueryComposition.User

  @doc """
  Builds dynamic filters conditionally combining multiple criteria.
  """
  def filter_users(base_query \\ User, filters) do
    dynamic_condition =
      Enum.reduce(filters, dynamic(true), fn
        {:role, role}, dynamic_acc ->
          dynamic([u], ^dynamic_acc and u.role == ^role)

        {:min_score, min_score}, dynamic_acc ->
          dynamic([u], ^dynamic_acc and u.score >= ^min_score)

        {:search, term}, dynamic_acc ->
          pattern = "%#{term}%"
          dynamic([u], ^dynamic_acc and (like(u.name, ^pattern) or like(u.email, ^pattern)))

        {:active, true}, dynamic_acc ->
          dynamic([u], ^dynamic_acc and not is_nil(u.email))

        _, dynamic_acc ->
          dynamic_acc
      end)

    from(u in base_query, where: ^dynamic_condition)
  end

  @doc """
  Applies window function to compute rank within each role.
  """
  def with_role_ranking(base_query \\ User) do
    base_query
    |> windows(w: [partition_by: :role, order_by: [desc: :score]])
    |> select_merge([u], %{rank: over(row_number(), :w)})
  end
end

defmodule CsxEctoQueryComposition.User do
  @moduledoc """
  User schema for demonstrating Ecto query composition.
  """
  use Ecto.Schema

  schema "users" do
    field(:name, :string)
    field(:email, :string)
    field(:role, :string, default: "member")
    field(:score, :integer, default: 0)
    has_many(:posts, CsxEctoQueryComposition.Post)

    timestamps(type: :utc_datetime)
  end
end

defmodule CsxEctoQueryComposition.MixProject do
  use Mix.Project

  def project do
    [
      app: :csx_ecto_query_composition,
      version: "0.1.0",
      elixir: "~> 1.17",
      start_permanent: Mix.env() == :prod,
      deps: deps()
    ]
  end

  def application do
    [
      extra_applications: [:logger]
    ]
  end

  defp deps do
    [
      {:ecto, "3.12.5"}
    ]
  end
end

%{
  "decimal": {:hex, :decimal, "2.4.1", "6c0fbede12fb122ba685e9ab41c6a40c129e322b3aa192f9e072e61f3a6ffaf2", [:mix], [], "hexpm", "7e618897933a8455f19a727d7c5e50a2c071a544b700e5e724298ecb4340187f"},
  "ecto": {:hex, :ecto, "3.12.5", "4a312960ce612e17337e7cefcf9be45b95a3be6b36b6f94dfb3d8c361d631866", [:mix], [{:decimal, "~> 2.0", [hex: :decimal, repo: "hexpm", optional: false]}, {:jason, "~> 1.0", [hex: :jason, repo: "hexpm", optional: true]}, {:telemetry, "~> 0.4 or ~> 1.0", [hex: :telemetry, repo: "hexpm", optional: false]}], "hexpm", "6eb18e80bef8bb57e17f5a7f068a1719fbda384d40fc37acb8eb8aeca493b6ea"},
  "telemetry": {:hex, :telemetry, "1.4.2", "a0cb522801dffb1c49fe6e30561badffc7b6d0e180db1300df759faa22062855", [:rebar3], [], "hexpm", "928f6495066506077862c0d1646609eed891a4326bee3126ba54b60af61febb1"},
}

defmodule CsxEctoQueryComposition.ContractTest do
  use ExUnit.Case, async: true

  import Ecto.Query

  alias CsxEctoQueryComposition.Post
  alias CsxEctoQueryComposition.QueryBuilder
  alias CsxEctoQueryComposition.User

  test "Ecto.Query.dynamic builds composable boolean AST expressions that interpolate into where clauses and combine with and, or, and not" do
    # 1. Base dynamic condition
    role_dyn = dynamic([u], u.role == ^"admin")
    assert %Ecto.Query.DynamicExpr{} = role_dyn

    # 2. Boolean composition with `and`
    score_dyn = dynamic([u], u.score >= ^100)
    combined_and = dynamic([u], ^role_dyn and ^score_dyn)
    assert %Ecto.Query.DynamicExpr{} = combined_and

    # 3. Boolean composition with `or` and `not`
    status_dyn = dynamic([u], u.role == ^"moderator")
    combined_or_not = dynamic([u], (^role_dyn or ^status_dyn) and not is_nil(u.email))
    assert %Ecto.Query.DynamicExpr{} = combined_or_not

    # 4. Interpolate dynamic expression into query where clause
    query = from(u in User, where: ^combined_or_not)
    assert %Ecto.Query{wheres: [%Ecto.Query.BooleanExpr{expr: expr, op: :and}]} = query
    assert expr != nil

    # 5. QueryBuilder reduce over dynamic(true) identity
    filtered =
      QueryBuilder.filter_users(User, [
        {:role, "admin"},
        {:min_score, 50},
        {:search, "elixir"},
        {:active, true}
      ])

    assert %Ecto.Query{wheres: [%Ecto.Query.BooleanExpr{}]} = filtered
  end

  test "Ecto.Query.select_merge statically merges map and struct projections and dynamically wraps unprojected queries in merge expressions" do
    # 1. Merging into map projection compiles to a single unified map AST
    base_map_query = from(u in User, select: %{id: u.id, name: u.name})
    merged_map_query = select_merge(base_map_query, [u], %{role: u.role, score: u.score})

    assert %Ecto.Query{select: %Ecto.Query.SelectExpr{expr: select_ast}} = merged_map_query
    {:%{}, [], fields} = select_ast
    field_keys = Enum.map(fields, fn {k, _v} -> k end)
    assert Enum.sort(field_keys) == [:id, :name, :role, :score]

    # 2. Merging into struct projection preserves struct schema type and adds fields
    base_struct_query = from(u in User, select: %User{name: u.name})
    merged_struct_query = select_merge(base_struct_query, [u], %{role: u.role})

    assert %Ecto.Query{
             select: %Ecto.Query.SelectExpr{expr: {:%, [], [User, {:%{}, [], struct_fields}]}}
           } =
             merged_struct_query

    struct_keys = Enum.map(struct_fields, fn {k, _v} -> k end)
    assert Enum.sort(struct_keys) == [:name, :role]

    # 3. select_merge on a query without explicit select creates a dynamic {:merge, ...} AST expression
    plain_query = from(u in User)
    merged_plain = select_merge(plain_query, [u], %{user_name: u.name})

    assert %Ecto.Query{
             select: %Ecto.Query.SelectExpr{
               expr: {:merge, [], [{:&, [], [0]}, {:%{}, [], [{:user_name, _}]}]}
             }
           } = merged_plain
  end

  test "Ecto.Query.exclude strips specified AST clauses while preserving remaining query composition" do
    full_query =
      from(u in User,
        join: p in Post,
        on: p.user_id == u.id,
        where: u.role == "admin",
        order_by: [asc: u.name],
        limit: 10,
        offset: 5,
        select: %{id: u.id, title: p.title}
      )

    # Exclude :where preserves joins, order_by, limit, offset, and select
    no_where = exclude(full_query, :where)
    assert no_where.wheres == []
    assert length(no_where.joins) == 1
    assert length(no_where.order_bys) == 1
    assert no_where.limit != nil
    assert no_where.offset != nil
    assert no_where.select != nil

    # Exclude :order_by removes ordering
    no_order = exclude(full_query, :order_by)
    assert no_order.order_bys == []
    assert length(no_order.wheres) == 1

    # Exclude :select resets select to nil
    no_select = exclude(full_query, :select)
    assert no_select.select == nil

    # Exclude :join strips join clauses
    no_joins = exclude(full_query, :join)
    assert no_joins.joins == []
  end

  test "Ecto.Query.reverse_order inverts order_by directions and defaults to schema primary key descending when unordered" do
    # 1. Reversing explicit order_by inverts directions and null positioning
    ordered_query =
      from(u in User,
        order_by: [asc: u.name, desc: u.score, asc_nulls_first: u.inserted_at]
      )

    reversed = reverse_order(ordered_query)

    assert %Ecto.Query{order_bys: [%Ecto.Query.ByExpr{expr: reversed_expr}]} = reversed
    directions = Enum.map(reversed_expr, fn {dir, _field} -> dir end)

    # :asc -> :desc, :desc -> :asc, :asc_nulls_first -> :desc_nulls_last
    assert directions == [:desc, :asc, :desc_nulls_last]

    # 2. Reversing an unordered schema query defaults to descending primary key order
    unordered_schema_query = from(u in User)
    reversed_default = reverse_order(unordered_schema_query)

    assert %Ecto.Query{
             order_bys: [
               %Ecto.Query.ByExpr{expr: [desc: {{:., [], [{:&, [], [0]}, :id]}, [], []}]}
             ]
           } = reversed_default

    # 3. Reversing an unordered schemaless query raises Ecto.QueryError
    schemaless_query = from(u in "users")

    assert_raise Ecto.QueryError, ~r/expected a from expression with a schema in query/, fn ->
      reverse_order(schemaless_query)
    end
  end

  test "Ecto.Query.with_cte and windows register Common Table Expressions and window definitions in the query struct" do
    # 1. with_cte attaching subquery CTE
    recent_posts = from(p in Post, where: p.views > 100)

    query_with_cte =
      User
      |> with_cte("popular_posts", as: ^recent_posts, materialized: true)
      |> join(:inner, [u], p in "popular_posts", on: p.user_id == u.id)
      |> select([u, p], %{user_id: u.id, post_id: p.id})

    assert %Ecto.Query{
             with_ctes: %Ecto.Query.WithExpr{queries: [{"popular_posts", cte_opts, cte_query}]}
           } =
             query_with_cte

    assert cte_opts == %{materialized: true, operation: :all}
    assert %Ecto.Query{} = cte_query

    # 2. Window function definitions with windows/3 and over/2
    windowed_query =
      from(u in User,
        windows: [role_window: [partition_by: u.role, order_by: [desc: u.score]]],
        select: %{
          id: u.id,
          role: u.role,
          rank: over(row_number(), :role_window),
          avg_score: over(avg(u.score), :role_window)
        }
      )

    assert %Ecto.Query{windows: [role_window: %Ecto.Query.ByExpr{}]} = windowed_query

    assert %Ecto.Query{select: %Ecto.Query.SelectExpr{expr: {:%{}, [], window_select_fields}}} =
             windowed_query

    keys = Enum.map(window_select_fields, fn {k, _v} -> k end)
    assert Enum.sort(keys) == [:avg_score, :id, :rank, :role]
  end
end

ExUnit.start()