Ecto.Query.API based solution
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.
Enum based solution
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.
