Best way to setup an Ecto Schema for a two-way, many-to-many relationship with itself

Question

For the tasks schema below, I'm trying to add linking tasks to one another with a many_to_many relationship joined through a task_links table.

schema "tasks" do
  field :title, :integer
  field :description, :string
  belongs_to :assignee, User
  timestamps()
end

The task_links table is setup as follows:

def change do
  create table(:task_links) do
    add :left_task_id, references(:tasks)
    add :right_task_id, references(:tasks)
    add :creator_id, references(:users)
    timestamps()
  end

  create constraint(:task_links, :left_less_than_right_check, check: "left_task_id < right_task_id")
  create unique_index(:task_links, [:left_task_id, :right_task_id])
end

The check constraint and unique index are used to ensure links aren't duplicated.

My question is, what is the best way to alter the tasks schema (or my approach to linking tasks) so queries preloading the task_links association return a list of linked tasks?

The issues is a task's id can be in the :left_task_id or :right_task_id column of the task_links table. I've almost achieved my goal with two many-to-many relationships:

schema "tasks" do
  ...
  many_to_many :right_links, Task, join_through: TaskLinks, join_keys: [left_task_id: :id, right_task_id: :id]
  many_to_many :left_links, Task, join_through: TaskLinks, join_keys: [right_task_id: :id, left_task_id: :id]
end

This setup is close to what I'm aiming for, but the query below returns links separately depending on whether the target task's id was in the :left_task_id or right_task_id column of the task_links table:

def get_task_and_links(task_id) do
  from(t in Task,
    left_join: right in assoc(t, :right_links),
    left_join: left in assoc(t, :left_links),
    preload [right_links: right, left_links: left]
    where: t.id == ^task_id
  ) |> Repo.one
-----------------
iex(1)> Tasks.get_task_and_links(6)
%Task{
  id: 6,
  ...,
  left_links: [
    %Task{id: 4, ...},
    %Task{id: 5, ...}
  ],
  right_links: [
    %Task{id: 32, ...}
  ]

Is there a way I could setup my tasks schema (or change my method of linking tasks) so a query would return a list of every task linked to a task under a single field?

Answer 1

For anyone interested, I simplified things by representing an undirected link between tasks A and B as two directed links, A → B and B → A. For my use case, duplicating data is a perfectly acceptable tradeoff. Now I just join on the :left_task_id (renamed to :task_id) column.

# TaskLinks Migration
def change do
  create table(:task_links) do
    add :task_id, references(:tasks)
    add :linked_task_id, references(:tasks)
    add :creator_id, references(:users)
    timestamps()
  end

  create unique_index(:task_links, [:left_task_id, :right_task_id])
end
-----------------
# Tasks Schema
schema "tasks" do
  ...
  has_many :task_links, TaskLinks, foreign_key: :task_id
end
-----------------
def get_task_and_links(task_id) do
  from(t in Task,
  left_join: link in assoc(t, :task_links),
  preload: [task_links: link],
  where: t.id == ^task_id
) |> Repo.one

iex(1)> Tasks.get_task_and_links(6)
%Task{
  id: 6,
  ...,
  task_links: [
    %Task{id: 4, ...},
    %Task{id: 5, ...},
    %Task{id: 32, ...}
  ]
}