20200716142101_add_unique_project_qualification.rb

class AddUniqueProjectQualification < ActiveRecord::Migration[6.0]
  def up
    # Stores before removing repeated
    sql = <<~SQL
      CREATE TEMPORARY TABLE t_project_qualifications AS (
        SELECT project_id, qualification_id
        FROM project_qualifications pq
        GROUP BY project_id, qualification_id HAVING count(*) > 1
      )
    SQL

    execute(sql)

    # removes all repeated but we have to insert the repeated ones again
    sql = <<~SQL
      DELETE FROM project_qualifications uq
      WHERE EXISTS (
        SELECT project_id, qualification_id FROM (
          SELECT project_id, qualification_id
          FROM project_qualifications pq
          GROUP BY project_id, qualification_id HAVING count(*) > 1
        ) AS pq
        WHERE uq.project_id=pq.project_id AND uq.qualification_id=pq.qualification_id
      );
    SQL
    execute(sql)

    # insert the repeated that have been deleted
    sql = <<~SQL
      INSERT INTO project_qualifications (project_id, qualification_id)
      SELECT project_id, qualification_id FROM t_project_qualifications
    SQL

    execute(sql)

    # remove temp table
    execute("DROP TABLE IF EXISTS t_project_qualifications")

    add_index :project_qualifications, %i(project_id qualification_id), unique: true
  end

  def down
    remove_index :project_qualifications, %i(project_id qualification_id)
  end
end