Oh, also, something I started doing is to use Ecto.Query.dynamic/2 instead of actual queries to extract reusable query snippets, like this:

defmodule UserQueries do
  def from_workspace(workspace_id), do: dynamic([u], u.workspace_id == ^workspace_id)
  def by_role(role), do: dynamic([u], u.role == ^role)
  def confirmed(true), do: dynamic([u], not is_nil(u.confirmed_at))
  def confirmed(false), do: dynamic([u], is_nil(u.confirmed_at))
end

So you can use that as building blocks to build complex queries like:

import Ecto.Query
import UserQueries

User
|> where(^dynamic(
  ^from_workspace(1) and 
  ^confirmed(true) and 
  (^by_role("admin") or ^by_role("owner"))
)) 
|> Repo.all()

Which would not be possible on a chain of |> with functions that build new queries using wheres, for example. I know or_wheres exist, but note that you can’t fully customize its precedence on those pipe chains.

PS.: I also know my or example could be handle with an in, but hopefully you got my point.

Might not be a popular opinion but the way I read module and function names made me go for this:

defmodule MyApp.UserQuery do
  def get(queryable, id), do: ...
  def list(queryable), do: ...
  def with_profile(queryable), do: ...
  def with_projects(queryable), do: ...
end

So when I need to compose a query I do this:

import MyApp.UserQuery

users =
  MyApp.Accounts.User
  |> get(123)
  |> with_projects()

Pretty opinionated and not entirely in line with general recommended Phoenix practices but it’s how reading code flows in my head and it’s IMO quite readable and quick to parse for a human.

Not really something I do every time, so I would not call is “how I structure my queries”, but one pattern I used sometimes for filters for example is something like this:

defmodule Accounts do
  def list(filter \\ []) do
    User
    |> apply_filter(filter)
    |> Repo.all()
  end

  defp apply_filter(q, []), do: q
  defp apply_filter(q, [{:ids, ids} | rest]), do: q |> where([u], u.id in ^ids) |> apply_filter(rest)
  defp apply_filter(q, [{:role, role} | rest]), do: q |> where([u], u.role in ^ids) |> apply_filter(rest)
  defp apply_filter(q, [{:confirmed, true} | rest]), do: q |> where([u], not is_nil(u.confirmed_at)) |> apply_filter(rest)
  defp apply_filter(q, [{:confirmed, false} | rest]), do: q |> where([u], is_nil(u.confirmed_at)) |> apply_filter(rest)
  ...
end

I sometimes extract functions on a UserQuery module too, like @dimitarvp does, but I normally just do that for queries that are actually going to be reused on a new place. So I start adding the wheres on the contexts, and when I realize that the query could be reused, I start extracting functions for composing them.