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 |
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 |
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 |
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 |
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.