Today I Learned

I was writing a unit test recently to test the return of a RAND() mySQL call and wanted to be able to expect one value or the other. I found out that RSpec has this ability built-in, but I’d never heard of it before.

From the RSpec documentation:

class StopLight
  def color
    %w[green yellow red].shuffle.first
  end
end

RSpec.describe StopLight, "#color" do
  let(:light) { StopLight.new }

  it "is green, yellow or red" do
    expect(light.color).to eq("green").or eq("yellow").or eq("red")
  end

  it "passes when using boolean OR | alias" do
    expect(light.color).to eq("green") | eq("yellow") | eq("red")
  end
end

Similar to my previous post, I have a high_scores table that tracks users’ high scores for several games. I needed to return the current user’s global rank for a specified game.

Here’s the table structure:

CREATE TABLE `high_scores` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `game_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `score` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_high_scores_on_game_id` (`game_id`),
  KEY `index_high_scores_on_user_id` (`user_id`),
  KEY `index_high_scores_on_game_id_and_user_id` (`game_id`,`user_id`),
  KEY `index_high_scores_on_game_id_and_score` (`game_id`,`score`)
);

And here’s the query I use to get the user’s global rank for a single game:

SELECT h1.user_id, h1.score, (
  SELECT COUNT(*) FROM high_scores h2 WHERE h2.score > h1.score AND h2.game_id = 3
) + 1 AS rank
FROM high_scores h1
WHERE h1.game_id = 3 AND h1.user_id = 57;

Note that I needed to add 1 to the count because it only return how many other scores are higher, and we want the user’s rank.

I recently needed to add a uniqueness validation for compound columns in a Rails project.

I have a high_scores table that tracks users’ high scores for several games. I wanted to ensure that each user only had one record per game.

class HighScore < ApplicationRecord
  belongs_to :game
  belongs_to :user

  validates :game_id, presence: true
  validates :user_id, presence: true, uniqueness: { scope: :game_id }
end

Adding the scope to the uniqueness validation ensures that only one combination of user_id/game_id is allowed in the table.

Bonus: Add a db-level constraint as well

class AddGameUserIndexToHighScores < ActiveRecord::Migration
  def change
    add_index :high_scores, [:game_id, :user_id], unique: true
  end
end