Here you go! 
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(:contributed_by_id, references(:users))
add(:created_by_id, references(:users))
add(:name, :string)
timestamps()
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(:contributed_by, User)
belongs_to(:created_by, User)
field(:name, :string)
field(:type, :string, virtual: true)
timestamps()
end
def new(tuple) do
list = Tuple.to_list(tuple)
:fields |> __schema__() |> Enum.zip(list) |> then(&struct(__MODULE__, &1))
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: System.get_env("USER")
# 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
%{id: foo_id} = Repo.get_by!(User, name: "Foo")
Project
|> Query.from(as: :project)
|> Query.join(:inner, [project: p], u in User,
on: u.id == ^foo_id and (u.id == p.contributed_by_id or u.id == p.created_by_id),
as: :user
)
|> Query.select(
[project: project, user: user],
{selected_as(
fragment(
"case ? when true then ? else ? end",
project.created_by_id == user.id,
"created projects",
"contributed projects"
),
:type
), fragment("array_agg(?)", project)}
)
|> Query.group_by(selected_as(:type))
|> Repo.all()
|> Map.new(fn {key, list} -> {key, Enum.map(list, &Project.new/1)} end)
|> IO.inspect()
end
def seed do
foo = Repo.insert!(%User{name: "Foo"})
bar = Repo.insert!(%User{name: "Bar"})
Repo.insert(%Project{contributed_by: foo, created_by: foo, name: "both"})
Repo.insert(%Project{contributed_by: bar, created_by: foo, name: "creator"})
Repo.insert(%Project{contributed_by: foo, created_by: bar, name: "contributor"})
Repo.insert(%Project{contributed_by: bar, created_by: bar, name: "none"})
end
end
Example.prepare()
Example.seed()
Example.sample()
Example.cleanup()
Above code prints:
%{
"contributed projects" => [
%Project{
__meta__: #Ecto.Schema.Metadata<:built, "projects">,
id: 3,
contributed_by_id: 1,
contributed_by: #Ecto.Association.NotLoaded<association :contributed_by is not loaded>,
created_by_id: 2,
created_by: #Ecto.Association.NotLoaded<association :created_by is not loaded>,
name: "contributor",
type: nil,
inserted_at: ~N[2022-10-27 16:11:34.000000],
updated_at: ~N[2022-10-27 16:11:34.000000]
}
],
"created projects" => [
%Project{
__meta__: #Ecto.Schema.Metadata<:built, "projects">,
id: 1,
contributed_by_id: 1,
contributed_by: #Ecto.Association.NotLoaded<association :contributed_by is not loaded>,
created_by_id: 1,
created_by: #Ecto.Association.NotLoaded<association :created_by is not loaded>,
name: "both",
type: nil,
inserted_at: ~N[2022-10-27 16:11:34.000000],
updated_at: ~N[2022-10-27 16:11:34.000000]
},
%Project{
__meta__: #Ecto.Schema.Metadata<:built, "projects">,
id: 2,
contributed_by_id: 2,
contributed_by: #Ecto.Association.NotLoaded<association :contributed_by is not loaded>,
created_by_id: 1,
created_by: #Ecto.Association.NotLoaded<association :created_by is not loaded>,
name: "creator",
type: nil,
inserted_at: ~N[2022-10-27 16:11:34.000000],
updated_at: ~N[2022-10-27 16:11:34.000000]
}
]
}