How to limit amount of associations in Elixir Ecto

Question

I have this app where there is a Games table and a Players table, and they share an n:n association.

This association is mapped in Phoenix through a GamesPlayers schema.

What I'm wondering how to do is actually quite simple: I'd like there to be an adjustable limit of how many players are allowed per game.

If you need more details, carry on reading, but if you already know an answer feel free to skip the rest!

---

What I've Tried

I've taken a look at adding check constraints, but without much success. Here's what the check constraint would have to look something like:

create constraint("games_players", :limit_players, check: "count(players) <= player_limit")

Problem here is, the check syntax is very much invalid and I don't think there actually is a valid way to achieve this using this call.

I've also looked into adding a trigger to the Postgres database directly in order to enforce this (something very similar to what this answer proposes), but I am very wary of directly fiddling with the DB since I should only be using ecto's interface.

---

Table Schemas

For the purposes of this question, let's assume this is what the tables look like:

Games

Property	Type
id	integer
player_limit	integer

Players

Property	Type
id	integer

GamesPlayers

Property	Type
game_id	references(Games)
player_id	references(Players)

Answer 1

As I mentioned in my comment, I think the cleanest way to enforce this is via business logic inside the code, not via a database constraint. I would approach this using a database transaction, which Ecto supports via Ecto.Repo.transaction/2. This will prevent any race conditions.

In this case I would do something like the following:

1. begin the transaction
2. perform a SELECT query counting the number of players in the given game; if the game is already full, abort the transaction, otherwise, continue
3. perform an INSERT query to add the player to the game
4. complete the transaction

In code, this would boil down to something like this (untested):

import Ecto.Query

alias MyApp.Repo
alias MyApp.GamesPlayers

@max_allowed_players 10

def add_player_to_game(player_id, game_id, opts \\ []) do
  max_allowed_players = Keyword.get(opts, :max_allowed_players, @max_allowed_players)

  case is_game_full?(game_id, max_allowed_players) do
    false -> %GamesPlayers{
        game_id: game_id,
        player_id: player_id
      }
      |> Repo.insert!()

    # Raising an error causes the transaction to fail
    true -> raise "Game #{inspect(game_id)} full; cannot add player #{inspect(player_id)}"
  end
end


defp is_game_full?(game_id, max_allowed_players) do
   current_players = from(r in GamesPlayers, 
       where: r.game_id == game_id, 
       select: count(r.id)
     ) 
     |> Repo.one()

   current_players >= max_allowed_players
end