Validating a unique record

I'm building a stat tracking site for my local basketball league using a Rails API. I started with a post about creating custom names (or aliases) in Rails. In this post I'll be sharing my experience creating a customized uniqueness validation.

Validations in general are an important piece of keeping a tidy database. Fortunately Rails comes with plenty built in. There's a very thorough guide on implementing validations.

My use case

I keep track of the data for a game by the Player, instead of storing all that information on the Game table. To find the winner I tally up the points scored by each Player on the home_team and away_team mentioned in my last post.

The validation I am creating will ensure that there is only one record (GameStat) per Player, per Game. This custom validation ensures that a Player only has one entry on each Game. Without this check user error could cause problems for my front end (i.e. displaying an incorrect winner).

The code below is simple. If you're familiar with ActiveRecord it may be easy to understand.

class GameStat < ApplicationRecord
  belongs_to :player
  belongs_to :game
  validates :game_id, uniqueness: { scope: :player_id, message: 'record already exists' }
end

On the 4th line I declare that a Game should only have one GameStat per Player using the scope: option. I also provide a custom message to give myself my users a clear idea of why their attempt at posting statistics is failing. The message is not necessary as there is a provided default.

To explain in it a nontechnical way: every time I create a GameStat I check to see if one exists with the same ID as the current game and player. If there is, an error is sent to the user.

Explaining it technically (pretty much the same): Before the new GameStat record is created, Rails runs a SQL query on the GameStat table checking to see if a record of the same value (game_id, and player_id thanks to the scope addition) already exists. If the record exists, throw an error.

There is one more step mentioned by the Rails guide.

Should you wish to create a database constraint to prevent possible violations of a uniqueness validation using the :scope option, you must create a unique index on both columns in your database.

I have to add an index to my player_id and game_id on my GameStat table. I don't fully understand indexing yet. So I had to find some help. Thanks to google, I found an answer on Stack Overflow.

class AddUniqueIndexForGameStat < ActiveRecord::Migration[6.0]
  def change
    add_index :game_stats, [:player_id, :game_id], unique: true
  end
end

Just like that I am no longer able to create duplicate records for a game, or player.

Thanks for reading, please comment any questions or suggestions for future blog posts. If you have any good resources on learning more about index's I'd love to see them!