CODEWITHSUNDEEP
ruby-on-railspg
MZaragoza
MZaragoza

Reputation: 10111

SQL How to get only 1 true value

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

Upvotes: 0

Views: 233

Answers (2)

Bijendra
Bijendra

Reputation: 10015

As per the migration, attributes for the model are ClassroomTeacher: classroom_id, teacher_id, lead

Considering teachers are being added to class:

/controller file

def create
  ClassroomTeacher.create(teacher_id: data1, classroom_id: data2, lead: data3)
end

Possible sample data with ideal values would be:

id classroom_id teacher_id lead
1     1             3       false
2     2             4       true
3     1             2       false
4     1             5       true

Now you need to avoid any new teachers being added to the classroom as lead. Model validation code could be

validate :only_one_is_lead_teacher

def only_one_is_lead_teacher
  if self.lead
    class_obj = ClassroomTeacher.where(classroom_id: self.classroom_id, lead: true).first
    if class_obj.present?
      self.errors.add(:lead, "There can only be one (1) lead teacher per classroom")
      return false
    end
  end
end

Upvotes: 0

Clemens Kofler
Clemens Kofler

Reputation: 1968

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

Upvotes: 2

Related Questions