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)`

This strikes me not so much as an Ecto thing or even as an issue of database constraints, but something that should be applied via the application business logic: i.e. boils down to don't allow more than x rows to be inserted. — Everett, Jun 25 '22 at 18:04
I tried going this direction first, but it felt wrong to have to query de DB in the changeset. I'm also a little bit afraid of race conditions (the specific case of 2 players being added at different requests at the same time when there's only one more space left) — GuiMendel, Jun 26 '22 at 12:13

Everett · Accepted Answer · 2022-06-27T16:53:18.603

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:

begin the transaction
perform a SELECT query counting the number of players in the given game; if the game is already full, abort the transaction, otherwise, continue
perform an INSERT query to add the player to the game
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