Hey all. I have an organization question. How do you structure your contexts in relation to queries. Let’s say I have a User module and he has friends, images, profile. There are places I don’t need to preload all of those relationships. I might only need the user without preloads, or with only the friends and profile, or only the user with profile. It’s also possible I might need to modify my queries for things like ordering, filtering…
How do you overcome this issue.
2 Likes
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
1 Like
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.
4 Likes
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…).
4 Likes
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.
5 Likes
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)
4 Likes
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.
5 Likes
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.
8 Likes
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
1 Like
The with_friends method would stand in the User schema? or in the context? Thanks for the idea
I have it in the context because I would use it in controllers and liveviews.
1 Like
Not something that is often recommended (because it is not very explicit and requires model knowledge), but I have been doing something like this for a project (it is an API only project that exposes a GQL API for a SPA).
def list_users(opts \\ []) do
preload = Keyword.get(opts, :preload, [])
User
|> Repo.base_query(opts)
|> Repo.all()
|> Repo.preload(preload)
end
# ...
list_users(where: %{first_name: "John"}, order_by: %{verified_at: :desc}, preload: [:friends])
And then magic happens inside Repo.base_query that sends appends the where and orders to the query. Works well and is very concise considering this has to be done for several different schemas and the orders and where clauses could be very different depending on the GQL API usage.
2 Likes
I like solutions proposed here but I’d like to suggest one more.
I’ve read an article somewhere that promoted the following idea:
group your functions into module by return type.
E.g. one module returns a User or a list of Users and other module returns Users with Friends.
For note, I would go with the composable versions nowadays, I only used my more complicated/powerful builder because back when it was made you couldn’t name a join in Ecto like you can now, they were only positional back then, so I had to build up the joins first then encode the positions into a dispatched code flow since I couldn’t dynamically select, it was a pain, but powerful. Nowadays with named joins it is so much easier!
2 Likes
For more complicated usecase, pattern that i frequently use is to write composeable query with a decent amount of named join. It produce readable code, with nice behavior of join only when necessary.
It looks like
defmodule UserQuery do
def has_minimum_rating(query, rating) do
query = query_require(query, :user_profile)
from(query, [user_profile: up], up.rating > ^ rating)
end
def name_like(query, name) do
query = query_require(query, :user_profile)
from(query, [user_profile: up], ilike(up.name, ^"#{name}*")
end
...
defp query_require(query, identifiers) when is_list(identifiers) do
Enum.reduce(identifiers, query, fn identifier, q ->
query_require(q, identifier)
end)
end
defp query_require(query, identifier) do
if has_named_binding?(query, identifier) do
query
else
implement_query_require(query, identifier)
end
end
defp implement_query_require(query, :user_profile) do
join(query, :left, [u], up in assoc(u, :user_profile), as: :user_profile)
end
end
and then used like
User
|> UserQuery.has_minimum_rating(4)
|> UserQuery.name_like("john")
|> Repo.all()
2 Likes
