Self-referencing many to many

In this guide we will model a social network where a User can be friends with other Users. There is no second table here: friendship relates users to users. This is the classic self-referencing many-to-many, and the only thing that makes it special is that the “parent” and the “child” of the association are the same schema. Everything else — a join table, two foreign keys, and a preload — is the ordinary many-to-many you have seen before, pointed back at itself.

We need two tables: users, and a join table — call it user_relationships — that pairs a user with a friend. Migrations in lode are ordinary values built with the lode/migration module from the lode_sql package (see Getting Started for running them with the migrator):

import lode/migration
import lode/migration/ddl

pub fn add_users_and_relationships() -> migration.Migration {
  migration.new(20_260_613_120_000)
  |> migration.create_table("users", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("name", ddl.Text) |> ddl.not_null,
  ])
  |> migration.create_table("user_relationships", [
    ddl.column("user_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("users")),
    ddl.column("friend_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("users")),
  ])
}

Both user_id and friend_id reference users.id — the same table. That is the whole trick of the self-reference at the database level: ddl.references(ddl.reference("users")) on each column points both foreign keys at users.

The schema

Schemas in lode are plain values — there are no macros and no reflection (see DESIGN.md in the repository), so an association is declared by handing the library the functions it cannot infer: how to read each join key, and how to attach loaded friends back onto a user. The self-reference shows up in two places. First, the User record carries a friends: List(User) field, so its own type appears in its own definition. Second, the related thunk we pass to many_to_many is user_schema itself.

import lode/association
import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/result

pub type User {
  User(id: Int, name: String, friends: List(User))
}

pub fn user_schema() -> Schema(User) {
  schema.new(
    source: "users",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "name", of: primitive.string()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use name <- result.try(schema.require_string(row, "name"))
      Ok(User(id:, name:, friends: []))
    },
    dump: fn(u: User) {
      dict.from_list([#("id", VInt(u.id)), #("name", VString(u.name))])
    },
  )
  |> association.many_to_many(
    name: "friends",
    related: user_schema,
    join_through: "user_relationships",
    join_owner_key: "user_id",
    join_related_key: "friend_id",
    owner_key: fn(u: User) { VInt(u.id) },
    child_key: fn(u: User) { VInt(u.id) },
    put: fn(u, friends) { User(..u, friends: friends) },
    opts: association.options()
      |> association.on_replace(association.ReplaceDelete),
  )
}

A few things are doing real work here. related: user_schema passes the schema as a thunk — a fn() -> Schema(User), not a Schema(User). That is not a stylistic choice: user_schema refers to itself, and without the thunk building the schema would recurse forever. Ecto sidesteps this with compile-time macro expansion; lode defers it to a function call (see DESIGN.md in the repository). The two key functions, owner_key and child_key, are identical here — both read u.id — because the owner and the child are both users. They match against different join columns, though: owner_key against user_id (join_owner_key), child_key against friend_id (join_related_key). The put closure is what attaches the loaded friends onto a user once they have been fetched.

The on_replace(ReplaceDelete) option says that when we write a new friends list for a user, any join rows for friends missing from the new list are deleted — the friends themselves (other users) survive; only the user_relationships link is removed.

Adding a friend

The friendship lives in the join table, so creating one is a matter of writing a user_relationships row. We do that with changeset.put_assoc, exactly as for any other many-to-many: we hand the changeset the friends that should be linked, and the association write reconciles the join rows to match.

import lode/changeset
import lode/error.{type LodeError}
import lode/preload
import lode/repo.{type Repo}
import gleam/list
import gleam/result

pub fn add_friend(
  r: Repo,
  user user: User,
  friend friend: User,
) -> Result(User, LodeError) {
  let cs =
    changeset.change(user, user_schema())
    |> changeset.put_assoc("friends", [
      changeset.change(friend, user_schema()),
    ])
  repo.insert(r, user_schema(), cs)
}

The friend we pass to put_assoc is a changeset over a User row that already carries its primary key (changeset.change(friend, user_schema())), so the association write does not try to re-insert the friend as a new user — it only reconciles the user_relationships join rows, honoring on_replace. The other user must already exist; if you are creating both people at once, insert the friend first, then link.

Because ReplaceDelete is in effect, put_assoc writes the complete friends list, not a delta. To add a friend to a user who already has some, preload the current friends, append the new one, and write the whole list back:

pub fn befriend(
  r: Repo,
  user user: User,
  friend friend: User,
) -> Result(User, LodeError) {
  use loaded <- result.try(
    repo.preload(repo: r, schema: user_schema(), parents: [user], preloads: [
      preload.one("friends"),
    ]),
  )
  let current = case loaded {
    [u, ..] -> u.friends
    [] -> user.friends
  }
  let cs =
    changeset.change(user, user_schema())
    |> changeset.put_assoc(
      "friends",
      list.map([friend, ..current], fn(f) { changeset.change(f, user_schema()) }),
    )
  repo.insert(r, user_schema(), cs)
}

Reading friends back

Reading is the everyday preload. The association batch-loads through the join table: given a set of users, it fetches their user_relationships rows, then loads the friend_id users in one further query, and attaches them with put. You can attach the preload to a query and let repo.all apply it:

import lode/preload
import lode/query

pub fn list_users_with_friends(r: Repo) -> Result(List(User), LodeError) {
  let q =
    query.from(source: "users", alias: "u")
    |> query.preload([preload.one("friends")])
  repo.all(r, user_schema(), q)
}

or preload onto records you already have in hand:

repo.preload(repo: r, schema: user_schema(), parents: users, preloads: [
  preload.one("friends"),
])

Because friends are themselves users with a friends association, you can nest the preload to pull in friends-of-friends with preload.nest:

repo.preload(repo: r, schema: user_schema(), parents: [user], preloads: [
  preload.nest("friends", [preload.one("friends")]),
])

Directionality

There is a subtlety the join table forces us to confront: a row (user_id: 1, friend_id: 2) records that user 1 considers user 2 a friend, but it says nothing about whether user 2 considers user 1 a friend. The association we declared is directional — preloading user 1’s friends follows user_id -> friend_id and finds user 2; preloading user 2’s friends finds nothing, because no row has user_id: 2.

If friendship in your domain is symmetric — if 1 being friends with 2 always means 2 is friends with 1 — you have the same two choices Ecto’s guide describes:

• Insert both directions. When 1 befriends 2, write (1, 2) and (2, 1). Then the directional preload above gives the right answer from either side, at the cost of two join rows per friendship and the discipline to keep them in sync (deleting a friendship means deleting both). With put_assoc this means running add_friend once in each direction.

• Query both columns at read time. Keep a single (1, 2) row and, when reading, gather friends from both user_id and friend_id. In Ecto this is typically a UNION of two queries (the friends where you are the user_id, plus the friends where you are the friend_id).

That second option runs into a current limitation of lode’s typed query builder: it has no union combinator (only from, where, join, select, order_by, and friends). To read a symmetric friendship from a single join row, drop to raw SQL with repo.query_raw (from lode_sql) and a UNION, or — more simply — adopt the insert-both-directions approach above and let the ordinary typed preload do the work. The insert-both-rows model is what the rest of this guide assumes, and it keeps every read on the type-checked path.

The read-both-columns approach to symmetric friendship can use query.union/query.union_all to combine the two directions (wrap the union in query.from_subquery if you need to order the result). The insert-both-directions approach needs no union at all.

Recap

A self-referencing many-to-many is just a many-to-many whose related schema is itself: the User record holds a friends: List(User), the related thunk is user_schema, and owner_key/child_key both read id while matching against the two distinct join columns user_id and friend_id. Writing a friendship is put_assoc over the join table; reading it is a preload. The only thing the self-reference adds is the directionality question — pick “store both rows” or “union at read time” to taste.

For the full read-side picture and the preload cheat sheet, see Associations; for constraints, upserts, and the related many-to-many tagging example, see Constraints and Upserts; and for the complete map of what differs from Elixir’s Ecto, see Divergences from Ecto.