title: Best way to setup an Ecto Schema for a two-way, many-to-many relationship with itself
body: <p>For the <code>tasks</code> schema below, I'm trying to add linking tasks to one another with a <code>many_to_many</code> relationship joined through a <code>task_links</code> table.</p>
  <pre><code>schema &quot;tasks&quot; do
    field :title, :integer
    field :description, :string
    belongs_to :assignee, User
    timestamps()
  end
  </code></pre>
  <p>The <code>task_links</code> table is setup as follows:</p>
  <pre><code>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: &quot;left_task_id &lt; right_task_id&quot;)
    create unique_index(:task_links, [:left_task_id, :right_task_id])
  end
  </code></pre>
  <p>The check constraint and unique index are used to ensure links aren't duplicated.</p>
  <p>My question is, what is the best way to alter the <code>tasks</code> schema (or my approach to linking tasks) so queries preloading the <code>task_links</code> association return a list of linked tasks?</p>
  <p>The issues is a task's <code>id</code> can be in the <code>:left_task_id</code> or <code>:right_task_id</code> column of the <code>task_links</code> table. I've almost achieved my goal with two many-to-many relationships:</p>
  <pre><code>schema &quot;tasks&quot; 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
  </code></pre>
  <p>This setup is close to what I'm aiming for, but the query below returns links separately depending on whether the target task's <code>id</code> was in the <code>:left_task_id</code> or <code>right_task_id</code> column of the <code>task_links</code> table:</p>
  <pre><code>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
    ) |&gt; Repo.one
  -----------------
  iex(1)&gt; Tasks.get_task_and_links(6)
  %Task{
    id: 6,
    ...,
    left_links: [
      %Task{id: 4, ...},
      %Task{id: 5, ...}
    ],
    right_links: [
      %Task{id: 32, ...}
    ]
  </code></pre>
  <p>Is there a way I could setup my <code>tasks</code> 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?</p>