Counter cache with a many_to_many association


I wanted to share with you my work concerning a counter cache on a many_to_many association (User <-> Team) and some questions that came to my mind while coding it.

Here is the code:


defmodule MyApp.Accounts.Team do
  use Ecto.Schema
  schema "teams" do
    field :name, :string
    field :users_count, :integer, default: 0

    many_to_many :users, User, join_through: TeamUser
defmodule MyApp.Accounts.User do
  use Ecto.Schema

  schema "users" do
    field :email, :string
    field :name, :string

    many_to_many :teams, Team, join_through: TeamUser

The join schema:

defmodule MyApp.Accounts.TeamUser do
  use Ecto.Schema

  @primary_key false
  schema "teams_users" do
    belongs_to :team, Team
    belongs_to :user, User

  def changeset(%TeamUser{} = team_user, attrs) do
    |> cast(attrs, [:team_id, :user_id])
    |> validate_required([:team_id, :user_id])
    |> unique_constraint(:team_id, name: "teams_users_team_id_user_id_index")

Some quick Repo helpers

defmodule Palapa.Repo do
  use Ecto.Repo, otp_app: :my_app
  require Ecto.Query

  # Not sure about that
  def increment(struct, field, value \\ 1) do
    primary_key = Ecto.primary_key(struct)

    |> Ecto.Query.where(^primary_key)
    |> update_all([inc: [{field, value}]], returning: true)
    |> case do
      {1, updated_struct} -> {:ok, updated_struct}
      _ -> {:error, struct}

  def decrement(struct, field, value \\ -1) do
    increment(struct, field, value)

Adding a User to a Team

Here I use Ecto.Changeset.prepare_changes/2 when incrementing the counter, as advised in the Ecto documentation

def add_user_to_team(%User{} = user, %Team{} = team) do
  TeamUser.changeset(%TeamUser{}, %{user_id:, team_id:})
  |> increment_counter_cache(team, :users_count)
  |> Repo.insert

defp increment_counter_cache(changeset, struct, counter_name, value \\ 1) do
  prepare_changes(changeset, fn prepared_changeset ->
    prepared_changeset.repo.increment(struct, counter_name, value)

Removing a User from a Team

Here’s the kicker. To add/remove a user from a team, I work directly on the join schema. From what I understood, I can’t use a changeset here as the join table has no id primary key to work with. Therefore, I have to use Repo.delete_all/2 or Ecto.Multi.delete_all/4 to delete the association directly in the join table. And since I can’t use a changeset, I can’t use Ecto.Changeset.prepare_changes/2 again to decrement the counter.

So here is my version using Ecto.Multi this time:

  def remove_user_from_team(%User{} = user, %Team{} = team) do
    team_user_query = 
      from tu in TeamUser, 
      where: tu.user_id == ^ and tu.team_id == ^
    |> Ecto.Multi.delete_all(:team_user, team_user_query)
    |>, fn changes_so_far ->
      # Avoids having a negative counter by checking if the row in the join table has actually been deleted
      %{team_user: {deleted_entries_count, nil}} = changes_so_far
      if deleted_entries_count > 0 do
        Repo.decrement(team, :users_count)
        {:ok, team}
    |> Repo.transaction

OK. There are probably some things to improve here, but that’s the global idea.

Newbie thoughts

I’m relatively new to Elixir. I know Ecto favors explicitness and that it shouldn’t be seen as an ORM. Coming from a Rails/ActiveRecord background, I’m probably biased. But still, having this counter cache working was not as straightforward/elegant as I thought.

Q1) It seems that the “official” way to implement a counter cache with Ecto is to do it manually through Ecto.Changeset.prepare_changes/2. In the Rails world, I think I only had to do something like has_many :users, through: :team_memberships, counter_cache: true and that’s it (yes, magic). Is a counter cache feature like this would be considered too opinionated or abstract to be part of Ecto?

Q2) I’ve read several blog posts and Ecto documentation where Ecto.Changeset.put_assoc/4 and the on_replace option are used to associate two existing records in a many_to_many association. But it doesn’t feel very appropriate for this use case as you have to preload the whole association first (which may be very big!) and then re-submit all the desired associated records. Therefore I decided to work directly on the join table to add/remove a user to a team. As @josevalim said in this Github issue :

Going through the join table is the way to go! put_assoc is meant to compute the diffs between values and keep transactional guarantees.

Maybe I’ve misread the docs but it feels like some functions are missing to Ecto for doing just a simple association on the join table, without preloading, and without having to maintain a join schema and having to deal directly with the user_id and team_id fields.

Q3) Why common Repo helpers like Repo.increment(struct, field, value), Repo.exists? aren’t available directly in Ecto?

Q4) What’s the real difference between a has_many through: association and a many_to_many association with a join schema (join_through option)?

Many thanks for your time and suggestions!

1 Like

Honestly, that I would have first written as SQL. ^.^

I don’t use Ecto’s association functions as I find them very limiting, I just make it explicit instead. :slight_smile:

Yes, I think Ecto pushes us to work closer to the DB, to avoid useless abstractions, and embrace all the great features the RDBMS gives us (starting with SQL, triggers…).

It may be puzzling for newcomers like me (especially those coming from Rails/ActiveRecord).
I guess people are like: “I know exactly the explicit SQL query I would have to write, but… should I get my fingers dirty and deal explicitly with a user_id and the join table ? Is that OK? Is that the proper Ecto way?”

José’s comment and this kind of blog post made me realize that: You don’t have to use :put_assoc or :cast_assoc

Also, for those who are interested, here is a postgres trigger-based way of doing counter caching (I haven’t tried it yet).