How do you structure your queries?

This is what I do, probably there are better approaches. Ecto.Query is very flexible and you can have multiple where statements, compose order, pagination etc much later. I take advantage of that.

I start with one use case, and implement the methods as necessary going forwards. (TDD, XP)

When I see commonality, I abstract into private methods to update my queriees.

This is pseudocode

defp get_user_query(id) do
from(u in User where u.id== ^id and u.active == true)
end

defp list_users_by_criteria_query() do
from(u in User, where: u.active == true, join: Profile ...)
end

def get_user_with_profile(id) do
get_user_query(id) |> Repo.prelod(:profile) |> Repo.get(..)
end

def search_users(query) do
from(u in list_users_by_criteria_query(), select: u.email, where: u.name == ^query, order: [asc: :inserted_at])
|> Repo.all()
end

I would usually to something like the normal

def list_users do
  ...
end

and also have a function that does the preload

def with_friends(user_or_users) do
  Repo.preload(user_or_users, :friends)
end

And then optionally pipe list_users |> with_friends where needed.

Just as an alternative way I use single functions that create specialized queries based on precisely what is wanted. So I may do something like Account.get_account(pidms: pidms, with_image: "profile", with_legal_name: true, etc..), which then creates the specialized query based on the keyword arguments passed in, and it gets pretty huge in the capabilities, but it means it is trivial to join only what is needed in the ways that is specifically needed, can refine what is returned, what it queries on, etc… But I build up basically a set of structures describing what is needed, then I build up the elixir query, etc…

I would not necessarily recommend this for most systems, I do it because I had to interact with a pre-existing database that is… oddly designed. The “original queries” I were given to do various things would query literally dozens of tables and take seconds to run at best to minutes overall, where my method can build up a perfect query every time and all my queries take milliseconds to run (while still having to join like 5-20 tables on average, I’m not sure any query joins less than 4 or so tables…).

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.

I use a pattern which takes an optional query function that can be injected in the list_* and get_* functions to build up the query prior to hitting the database.

def list_users(queries \\ & &1) do
  from(User)
  |> queries.()
  |> Repo.all()
end

Composable query functions are exposed on the context and can be referenced externally.

Accounts.list_users(fn query ->
  query
  |> Accounts.include_user_profile()
  |> Accounts.filter_non_executive_users()
  |> Accounts.filter_users_by_company(company)
  |> Accounts.order_users_by_first_name()
end)

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.

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.

I’d like to add that sometimes depends on use case it makes sense to create a separate context with their own schemas that map to the same tables, and, perhaps, lists different set of columns.

As always, there are trade-offs.. like on one hand you have clear separate declarations you need for different cases with better naming etc.. on the other hand it seems repetitive and if schemas change you might need to update code in multiple places (though in my experience usually one of the contexts drive the change of the table schema, while others remain working with their set of data)

I created a library that allows context functions to receive filters, ordering, preloads, etc. as argument.

Blog.list_articles(preload: :comments, order_by: [title: :asc])

def list_articles(opts \\ []) do
  Article
  |> QueryBuilder.from_list(opts)
  |> Repo.all()
end

The with_friends method would stand in the User schema? or in the context? Thanks for the idea