Well I don’t have a lot of context on OP’s task but if they are worried about load spikes – or generally high load – then I’d definitely resort to “get what you need by a clever complex SQL / Ecto query and reshape it with Elixir to match your business code needs”.
Until we hear something more this is what it looks like they need.
Yes, I believe this is what I need and that’s why I even started thinking about refactoring my queries. In your opinion, would using Enum.group_by be a better choice then using Enum.filter
query |> Enum.filter(fn x -> x.created_by == "user" end) |> Enum.take(3)
Well, I don’t really worry about load spikes right now but I want to understand those things now so I will be able to make better decisions when I will face those problems.
@sbuttgereit Thanks for taking the time to write such a thoughtful response, I will definitely take the time to read it carefully, try to fully understand it and learn from it.
I am still not very clear on what you exactly do you need but no, don’t use Enum.take, use Ecto’s limit.
Here goes an example script that shows how you can filter by associations. It should be the most optimal way.
Mix.install([:ecto_sql, :postgrex])
defmodule Repo do
use Ecto.Repo, adapter: Ecto.Adapters.Postgres, otp_app: :my_app
end
defmodule Migration do
use Ecto.Migration
def change do
create table("users") do
add(:name, :string)
timestamps()
end
create table("projects") do
add(:creator_id, references(:users))
add(:name, :string)
timestamps()
end
create table("user_projects", primary_key: false) do
add(:project_id, references(:projects))
add(:user_id, references(:users))
end
end
end
defmodule User do
use Ecto.Schema
schema "users" do
field(:name)
timestamps()
end
end
defmodule Project do
use Ecto.Schema
schema "projects" do
belongs_to(:creator, User)
field(:name, :string)
many_to_many(:contributors, User, join_through: "user_projects")
timestamps()
end
end
defmodule Example do
alias Ecto.Query
require Query
def cleanup do
Repo.stop()
end
def prepare do
Application.put_env(:my_app, Repo,
database: "example",
password: "postgres",
pool_size: 10,
show_sensitive_data_on_connection_error: true,
username: "postgres"
)
Application.ensure_all_started(:ecto_sql)
Application.ensure_all_started(:ecto_sqlite3)
Repo.__adapter__().storage_down(Repo.config())
Repo.__adapter__().storage_up(Repo.config())
Repo.start_link()
Ecto.Migrator.up(Repo, 1, Migration)
end
def sample do
Project
|> Query.from(as: :project)
# prevent duplicates if creator is also a contributor
|> Query.distinct([project: project], project.name)
# join assocs
|> Query.join(:inner, [project: project], assoc(project, :creator), as: :creator)
|> Query.join(:inner, [project: project], assoc(project, :contributors), as: :contributors)
# filter projects based on its assocs
|> Query.where(
[contributors: contributors, creator: creator],
contributors.name == "Foo" or creator.name == "Foo"
)
# limit number of projects
|> Query.limit(2)
|> Repo.all()
|> IO.inspect()
end
def seed do
foo = Repo.insert!(%User{name: "Foo"})
bar = Repo.insert!(%User{name: "Bar"})
Repo.insert(%Project{contributors: [foo, bar], creator: foo, name: "both"})
Repo.insert(%Project{contributors: [bar], creator: foo, name: "creator"})
Repo.insert(%Project{contributors: [foo, bar], creator: bar, name: "contributor"})
Repo.insert(%Project{contributors: [bar], creator: bar, name: "none"})
end
end
Example.prepare()
Example.seed()
Example.sample()
Example.cleanup()
Please keep in mind that not all databases support distinct like it was used in code above, for example SQLite. I send a PostgreSQL based example as this is a default choice for an Elixir/Phoenix database.
If for some reason you can’t use ecto’s query API like mentioned by others already then my example may be interesting for you:
defmodule Example do
def sample(list, func \\ &Function.identity/1, max \\ :infinity)
def sample(list, func, :infinity) when is_list(list) and is_function(func, 1) do
Enum.filter(list, func)
end
def sample(list, func, max)
when is_list(list) and is_function(func, 1) and is_integer(max) and max > 0 do
list
|> Enum.reduce_while({0, []}, fn element, {count, acc} ->
case {count + 1, func.(element)} do
{_count, result} when result in [nil, false] -> {:cont, {count, acc}}
{^max, _result} -> {:halt, {count, [element | acc]}}
{count, _result} -> {:cont, {count, [element | acc]}}
end
end)
|> then(fn {_count, acc} -> Enum.reverse(acc) end)
end
end
The code above is a bit big, but that’s because it’s really flexible. In short it’s a combination of Enum.filter/2 + Enum.take/2 in one function. With it you can filter only first n matching elements which is especially useful when working with big lists.
Users can create many projects, also, they can contribute to many projects. Each project can have only one creator and only one contributor so there are two separate table fields (:created_by and :contributed_by).
project.ex
schema "projects" do
fields ...
belongs_to :created_by, User
belongs_to :contributed_by, User
timestamps()
end
Here I’m getting all user projects with one query and then use Enum functions to filter them
account_controller.ex
def index(conn, _params, current_user) do
profile = Profiles.get_profile!(current_user.id)
projects = Projects.list_user_projects(current_user)
created_projects = Enum.filter(projects, fn x -> x.created_by_id == current_user.id end) |> Enum.sort_by(&(&1.inserted_at), Date) |> Enum.take(3)
contributed_projects = Enum.filter(projects, fn x -> x.contributed_by_id == current_user.id end) |> Enum.sort_by(&(&1.inserted_at), Date) |> Enum.take(3)
render(conn, "index.html", created_projects: created_projects, contributed_projects: contributed_projects)
end
projects.ex
def list_user_projects(user) do
query = from p in Project, where: p.created_by_id == ^user.id, or_where: p.contributed_by_id == ^user.id, select: p
Repo.all(query)
end
Alternative which I’ve created first is that I have two queries, one for created projects and one for contributed projects.
account_controller.ex
def index(conn, _params, current_user) do
profile = Profiles.get_profile!(current_user.id)
created_projects = Projects.list_created_projects(current_user)
contributed_projects = Projects.list_contributed_projects(current_user)
render(conn, "index.html", created_projects: created_projects, contributed_projects: contributed_projects)
end
projects.ex
def list_created_projects(user) do
query = from p in Project, where: p.created_by_id == ^user.id, order_by: [desc: p.inserted_at], limit: 3, select: p
Repo.all(query)
end
def list_contributed_projects(user) do
query = from p in Project, where: p.contributed_by_id == ^user.id, order_by: [desc: p.inserted_at], limit: 3, select: p
Repo.all(query)
end
In my template, I want to show 3 latest created projects and 3 latest contributed projects.
I see. I’d go for your first approach plus add sorting by inserted_at in list_user_projects so you don’t have to sort in the Elixir code.
Wait no, you should also add Ecto’s limit.
Hm, it seems you just need a rather more specialized function for this and not the generic list_user_projects:
def list_created_projects(user) do
query =
from p in Project,
where: p.created_by_id == ^user.id,
order_by: [asc: :inserted at],
limit: 3,
select: p
Repo.all(query)
end
def list_contributed_projects(user) do
query =
from p in Project,
where: p.contributed_by_id == ^user.id,
order_by: [asc: :inserted at],
limit: 3,
select: p
Repo.all(query)
end
This can probably be made with only one Ecto/SQL query but right now I can’t figure out how (just got up from a nap, lol).
What if I create list_user projects() function where I make two queries, one for created projects, one for contributed projects and use union_all to combine the results?
Problem is that I don’t remember now. I’d advise you to try really hard to do it with one query in general, yes.
This is a good option and the first thing that comes to mind given all the info in the thread to this point. There are other things that could work too, but I don’t think you’d gain anything over the union. This results in a single database query and you get the benefits of keeping the filtering in the database.