Working with Elixir and Ecto for several years now, I keep running into the same patterns when building dynamic queries. API endpoints that need optional filtering, search functionality that should gracefully handle empty parameters, and pagination that works consistently across different contexts.

While Ecto's query DSL is powerful, I find myself writing repetitive conditional logic to handle these common scenarios. That's what led me to create EctoQueryPlus—a small library that makes these patterns more expressive and reduces boilerplate.

The Problem: Conditional Query Logic

Consider a typical user search endpoint. Users might want to filter by name, email, status, or any combination of these. The naive approach leads to deeply nested conditional logic:

query = from(u in User)

query = if params[:name] do
  where(query, [u], ilike(u.name, ^"%#{params[:name]}%"))
else
  query
end

query = if params[:email] do
  where(query, [u], u.email == ^params[:email])
else
  query
end

query = if params[:active] != nil do
  where(query, [u], u.active == ^params[:active])
else
  query
end

This pattern is verbose, repetitive, and error-prone. Each conditional follows the same structure, but we're writing it out every time. As queries become more complex, this approach becomes unwieldy.

A More Expressive Approach

What I want is a way to express conditional queries more naturally. The same logic should read like this:

import EctoQueryPlus

User
|> wherep(name: params[:name])
|> wherep(email: params[:email])
|> wherep(active: params[:active])

The wherep/2 function is the core of EctoQueryPlus. It takes a query and a keyword list or list of tuples, applying conditions only when values are present (not nil). This eliminates the repetitive if-else pattern while maintaining the same functionality.

Beyond Simple Conditionals

Real-world queries often need more sophisticated conditional logic. Sometimes we want to apply different transformations based on the type or value of parameters. EctoQueryPlus provides several helpers for these scenarios.

Filter By with Operators

You can also use list syntax with operators for more complex filtering:

User
|> wherep([{:name, :ilike, params[:name]}])
|> wherep([{:age, :gte, params[:min_age]}])
|> wherep([{:created_at, :gte, params[:since]}])

This is much cleaner than writing out each conditional with its specific operator logic.

Dynamic Ordering

Sorting is another area where conditional logic gets messy. EctoQueryPlus provides the order_byp/2 function for conditional ordering:

User
|> order_byp([{params[:sort_by] || :name, params[:sort_direction] || :asc}])

If sort parameters are provided, they're used. Otherwise, it defaults to sorting by name. This handles the common pattern of optional sorting with fallbacks.

Pagination Made Simple

Pagination is another repetitive pattern. EctoQueryPlus provides offsetp/2 and limitp/2 functions to handle this cleanly:

User
|> wherep(name: params[:name])
|> order_byp([{params[:sort_by] || :name, params[:sort_direction] || :asc}])
|> offsetp(params[:offset])
|> limitp(params[:limit])

The offsetp/2 and limitp/2 functions handle nil inputs gracefully, only applying pagination when values are present.

Composability and Pipeline Thinking

What I love about this approach is how naturally it fits into Elixir's pipeline style. Query building becomes a series of transformations, each adding a piece of functionality:

defmodule MyApp.Users do
  import EctoQueryPlus
  
  def search_users(params) do
    User
    |> wherep([
      {:name, :ilike, params[:name]},
      {:email, params[:email]},
      {:department_id, params[:department_id]},
      {:active, params[:active]},
      {:created_at, :gte, params[:created_after]},
      {:created_at, :lte, params[:created_before]}
    ])
    |> order_byp([{params[:sort_by] || :name, params[:sort_direction] || :asc}])
    |> offsetp(params[:offset])
    |> limitp(params[:limit])
    |> Repo.all()
  end
end

Each line is clear about what it's doing. The query reads like a description of the filtering logic rather than a complex conditional structure.

Handling Edge Cases

One of the benefits of centralizing these patterns is consistent edge case handling. The library deals with common issues that crop up in dynamic queries:

Nil vs Empty String: API parameters often come as empty strings rather than nil. EctoQueryPlus treats empty strings as nil for conditional logic.

Type Coercion: Pagination parameters from URLs are strings, but need to be integers. The library handles this conversion with sensible fallbacks.

Invalid Values: Page numbers below 1, negative per_page values, and other invalid inputs are normalized to reasonable defaults.

The Power of the `list/2` Function

While individual functions like wherep/2 and order_byp/2 are useful, I quickly realized they can feel cumbersome for complex queries. The real power of EctoQueryPlus comes from implementing a base query module that provides the list/2 function, which can be reused everywhere:

defmodule MyApp.Query do
  use EctoQueryPlus
end

Now I can use the much more elegant list/2 syntax:

User
|> MyApp.Query.list(
  [
    {:name, :not_nil},
    {:email, ["foo@example.com", "bar@example.com"]},
    {:last_login_at, :gt, DateTime.utc_now()},
    {:locked_at, nil}
  ],
  %{
    select: [:id, :name, :email],
    order_by: {:last_login_at, :asc},
    limit: 5,
    offset: 10
  }
)

This single call handles multiple filtering conditions, selection, ordering, and pagination in a declarative way.

Coexistence with Standard Ecto

One of the best aspects of EctoQueryPlus is that it plays nicely with standard Ecto functions. You can mix and match based on your needs:

User
|> MyApp.Query.list([{:active, true}], %{select: [:id, :name]})
|> join(:left, [u], p in assoc(u, :profile))
|> where([u, p], not is_nil(p.avatar))
|> preload([:profile, :posts])
|> Repo.all()

Here I'm using EctoQueryPlus for the initial filtering and selection, then standard Ecto for joins, additional where clauses, and preloading. This flexibility means I can adopt the library incrementally without rewriting existing queries.

Performance Considerations

Since EctoQueryPlus works at the query building level (before database execution), there's no runtime performance impact. The conditionals are resolved when building the query, not when executing it.

The library generates the same SQL as manually written conditional logic would. It's purely a developer experience improvement—making code more readable and maintainable without affecting database performance.

A Complete Example

Let me put it all together with a complete example function that we can then test:

defmodule MyApp.Users do
  def search_users(params) do
    User
    |> MyApp.Query.list(
      [
        {:name, :ilike, params[:name]},
        {:email, params[:email]},
        {:active, params[:active]},
        {:age, :gte, params[:min_age]}
      ],
      %{
        order_by: {params[:sort_by] || :name, params[:sort_direction] || :asc},
        offset: params[:offset],
        limit: params[:limit]
      }
    )
    |> Repo.all()
  end
end

This function handles all the conditional logic automatically. If `params[:name]` is nil, that condition is ignored. If `params[:active]` is present, it's applied. The beauty is in the simplicity.

Testing Dynamic Queries

Now that we have our `search_users/1` function, testing becomes straightforward. Since EctoQueryPlus handles nil values automatically, I can focus on testing the actual business logic rather than conditional query building:

test "search_users returns filtered results" do
  active_user = insert(:user, active: true, name: "John")
  inactive_user = insert(:user, active: false, name: "Jane")
  
  results = MyApp.Users.search_users(%{active: true})
  
  assert length(results) == 1
  assert hd(results).id == active_user.id
end

test "search_users handles empty params gracefully" do
  user = insert(:user)
  
  results = MyApp.Users.search_users(%{})
  
  assert length(results) == 1
end

The testing becomes more focused on verifying the actual results rather than testing query construction logic.

Looking Forward

EctoQueryPlus is solving real problems in my daily development work. Future enhancements I'm considering include:

• More sophisticated filtering operators (in, between, regex matching)

• Integration with Phoenix params casting for better type handling

• Helpers for common aggregate query patterns

• Support for more complex dynamic joins

Building Better Abstractions

The goal with EctoQueryPlus isn't to replace Ecto's query DSL—it's to build on top of it. By identifying common patterns and extracting them into reusable helpers, we can write more expressive code while maintaining all of Ecto's power and flexibility.

Sometimes the best libraries are the ones that make common tasks feel natural rather than trying to solve complex problems. EctoQueryPlus is my attempt to make conditional querying feel as natural as Elixir's pipeline operator.

Check out the complete implementation and examples: EctoQueryPlus on GitHub