Many to Many joins with Ecto and Elixir

Oct 3, 2014 09:48 · 240 words · 2 minute read

Ecto is an DSL for writing queries and interacting with databases in Elixir.  It has a style similar to ActiveRecord, but since there aren’t objects, you can’t really call it an ORM.

Like ActiveRecord, it has has_many and belongs_to, but it doesn’t have has_many :through yet. Here is how I implemented a DAG using a many-to-many join in Ecto anyways

The basic idea is I have a Nodes table.  And each Node can have multiple parents and multiple children.  The most straightforward implementation of this is to have a many-to-many join table and then use that with INNER JOIN to query for parents or children.

First, I needed to create the tables using Ecto’s migration feature.

defmodule Repo.Migrations.CreateNodes do
use Ecto.Migration
def up do
[
"CREATE TABLE IF NOT EXISTS nodes(id SERIAL PRIMARY KEY, name TEXT NOT NULL, script TEXT)",
"CREATE TABLE IF NOT EXISTS node_to_node(parent_id INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE, child_id INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE)"
]
end
def down do
[
"DROP TABLE node_to_node",
"DROP TABLE nodes"
]
end
end
view raw gistfile1.ex hosted with ❤ by GitHub

Then I define the models using the Ecto model DSL.

defmodule Node do
use Ecto.Model
schema "nodes" do
has_many :children, NodeToNode, foreign_key: :parent_id
has_many :parents, NodeToNode, foreign_key: :child_id
end
end
defmodule NodeToNode do
use Ecto.Model
schema "node_to_node", primary_key: false do
belongs_to :parent, Node, foreign_key: :parent_id
belongs_to :child, Node, foreign_key: :child_id
end
end
view raw ecto_models.ex hosted with ❤ by GitHub

This wasn’t quite enough though. I ran into trouble getting Node to preload its children and parents via NodeToNode. I ended up adding help functions to the Node module.

defmodule Node do
import Ecto.Query, only: [from: 2]
def get_children(node) do
from(n in Node,
join: n2n in NodeToNode, on: n.id == n2n.parent_id,
inner_join: n1 in Node, on: n1.id == n2n.child_id,
select: n1,
where: n.id == ^node.id)
|> Repo.all
end
def get_parents(node) do
from(n in Node,
join: n2n in NodeToNode, on: n.id == n2n.child_id,
inner_join: n1 in Node, on: n1.id == n2n.parent_id,
select: n1,
where: n.id == ^node.id)
|> Repo.all
end
end
view raw gistfile1.ex hosted with ❤ by GitHub

This now lets me write an association.

defmodule NodeTest do
use ExUnit.Case
test "can have childen and parents" do
node1 = %Node{name: "parent"} |> Repo.insert
node2 = %Node{name: "child2"} |> Repo.insert
%NodeToNode{parent_id: node1.id, child_id: node.id} |> Repo.insert
assert Node.get_children(node1) == [node2]
assert Node.get_parents(node2) == [node1]
end
emd
view raw nodetest.ex hosted with ❤ by GitHub

This works as far as I can tell with my unittests. I’m not completely comfortable with Ecto’s association proxy, so there might be a better way to do this that is more idiomatic. I hope that has_many :through support is added soon so I can concentrate on my business logic instead of mundane joins. I think more documentation of reading and writing associations is necessary.

If you know a better way to express this, please let me know.