incrementing the value of one column based on the value of another column

Question

Hey all, This code does not upload the intended value to the database. I was expecting that if 2001 already exists as a student_number value for a record which has another column site 1 (represented by site_id), then we increment student_number by 1 so next value of student_number for that site would be 2002 and next value 2003 and so on. However, for some reason here, if 2001 exists, it just returns a value of 1, and then for next record created, it returns a value of 1 again and so on:

Student model:

def test!
update_attributes :updater_id => User.current_user.id,
:student_number =>
sitenum = self.site_id
count = Student.count_by_sql("SELECT MAX(student_number) FROM students WHERE site_id = #{sitenum}")
if count >= 2001
Student(:condition => { :site_id => sitenum },
  :order => "student_number DESC").student_number + 1
 else
 2001
end
end

Students controller:

def test_finalize
if @student.update_attributes(params[:student]) && @student.test! 
@student.save
end
end

Any idea of how to increment it by 1 if 2001 exists? Thanks for any suggestions.

Answer 1

MySQL does this automatically if you use a composite primary key and only one term auto increments. I'm not sure how other databases handle it though, so you will have to look into it if you're not using MySQL.

Unfortunately Rails doesn't do composite keys out of the box. There is a composite_primary_keys plugin. I have no experience with this plugin, so your mileage may vary.

Edit: Explanation of why code in the question doesn't work:

It looks like you expect the result of the if condition to be the student number. But this is how the code is evaluated.

def test!
  update_attributes :updater_id => User.current_user.id,
    :student_number => sitenum = self.site_id

  count = Student.count_by_sql("SELECT MAX(student_number) FROM students WHERE site_id = #{sitenum}")
  if count >= 2001
    Student(:condition => { :site_id => sitenum },
      :order => "student_number DESC").student_number + 1
  else
    2001
  end
end

The result is that test! returns the student number you want to assign.

This code does what you described:

def test!
  new_number = Student.count_by_sql("SELECT MAX(student_number) FROM students WHERE site_id = #{sitenum}") + 1
  new_number = new_number > 2000 ? new_number : 2001
  update_attributes :updater_id => User.current_user.id,
    :student_number => new_number    
end