Avoid exception when trying to add a entry with a duplicate index in rails

Question

I have 3 models:

• User
• Puzzle
• Solution

A solution represents the first time that a user solved a given puzzle. I only want to record the first time - subsequent attempts don't count. The solution has an index on the user-puzzle combination.

My migration looks like this:

class CreateSolutions < ActiveRecord::Migration[6.0]
  def change
    create_table :solutions do |t|
      t.integer :time
      t.integer :attempts
      t.references :user
      t.references :puzzle

      t.timestamps
    end
    add_index :solutions, [:user_id, :puzzle_id], unique: true
  end
end

I'm using MySQL. The Puzzle and User models both have: has_many :solutions.

In the solutions_controller.rb, I have:

  def create
    @solution = Solution.new(solution_params)
    @solution.user = current_user
    @solution.puzzle = Puzzle.find(params["puzzle"])

    respond_to do |format|
      if @solution.save
        format.json { render :show, status: :created }
      else
        format.json { render json: @solution.errors, status: :unprocessable_entity }
      end
    end
  end

The problem is that the second time I try to save a puzzle, I get an exception:

ActiveRecord::RecordNotUnique in SolutionsController#create
Mysql2::Error: Duplicate entry '28-13' for key 'index_solutions_on_user_id_and_puzzle_id'

Goal: I would like for it to just silently ignore subsequent saves, rather than throw an exception.

Here's what I've tried - all in the solutions_controller.rb:

• if @solution.save(validate: false)...
• if @solution.save(:validate => false)...
• if @solution.save(false)...

Ideally, I only want to skip validation for the duplicate index - I would want to catch other errors.

Answer 1

In the Solution model, you could add

validates_uniqueness_of :user_id, scope: :puzzle_id

It will run a select query every time you try to save a solution record. If a record exists for the select query solution.save will give error.

Or you could use first_or_initialize

Solution.where(user_id: current_user.id, puzzle_id: params[:puzzle][:id]).first_or_initialize(solution_params)

This will check if a record exists for the given query if it doesn't exist, it will assign the attributes in the where condition and the solution_params.

update

def create
    @solution = Solution.where(user_id: current_user.id, puzzle_id: params[:puzzle][:id]).first_or_initialize(solution_params)

    respond_to do |format|
      if @solution.save
        format.json { render :show, status: :created }
      else
        format.json { render json: @solution.errors, status: :unprocessable_entity }
      end
    end
  end

if you do it this way no need to check for the error message, if the record already exists in the table it will be assigned to the @solution and the data in solution params won't be assigned. If the record does not exist @solution will be assigned with the data in where condition and solution_params(user_id and puzzle_id values will be taken from what is given in where condition and others from solution_params). If for an attribute data is given in where condition and solution_params, then solution_params value will be taken.