SQL How to get only 1 true value

Question

I am building an application and I need to be able to sing a lead teacher I need to prevent that 2 teachers share the title of lead for a particular class

class CreateClassroomTeachers < ActiveRecord::Migration[5.2]
  def change
    create_table :classroom_teachers do |t|
      t.belongs_to :classroom
      t.belongs_to :teacher
      t.boolean    :lead, default: false
    end
    add_index :household_people, [:classroom_id, :teacher_id], unique: true
    # Only one teacher in a classroom can be lead 
  end
end

I have this in my model

class ClassroomTeacher < ApplicationRecord
  belongs_to :classroom
  belongs_to :teacher


  validate :only_one_is_lead_teacher

  def only_one_is_lead_teacher
    if lead
      if ClassroomTeacher.where(classroom_id: classroom_id, lead: true).count > 0
        errors.add(:lead, "There can only be one (1) lead teacher per classroom")
      end
    end
  end
end

The problem on this is that on Create I can have 2 or more teachers be lead

Thanks for the help

Answer 1

There's several ways for achieving this with constraints, triggers etc. – depending on what your respective database server supports.

What should work at least in Postgres (even though it might be slightly hacky) is to set a unique index on %i[classroom_id lead] and make sure that lead is either true or NULL. This should work because Postgres treats NULL values as distinct, meaning that it doesn't complain if multiple NULL values are stored in a column that has a uniqueness constraint on it.

---

If you want to solve it in code (which personally I would not recommend, because your database might be access by things other than your code and even your code can work around it, e.g. by directly writing to the database instead of using ActiveRecord's higher level methods), here's how I've done this in the past:

class ClassroomTeacher < ActiveRecord::Base
  before_save :ensure_only_one_lead_teacher

  private

  def ensure_only_one_lead_teacher
    # We don't have to do this unless the record is the one who should be the (new) lead.
    return unless lead?

    # Set all other records for the class room to lead = false.
    self.class.where(classroom_id: classroom_id).update_all(lead: false)

    # Now if the record gets persisted, it will be the only one with lead = true.
  end
end

A probably slightly more "correct" approach would be to ensure the uniqueness after the record has been persisted:

class ClassroomTeacher < ActiveRecord::Base
  after_commit :ensure_only_one_lead_teacher

  private

  def ensure_only_one_lead_teacher
    # We don't have to do this unless the record is the one who should be the (new) lead.
    return unless lead?

    # Set all other records for the class room to lead = false. Note that we now have to exclude
    # the record itself by id because it has already been saved.
    self.class.where.not(id: id).where(classroom_id: classroom_id).update_all(lead: false)
  end
end