My app has some students that have attendances. I'm trying to find students who have not attended class in the last 2 weeks. Here is my code that is not working:
students = @school.students.active @missed_attnd = students.each do |student| unless student.attendances.empty? @missed_attnd << student if student.attendances.first.date <= Date.today - 2.weeks end end
An attendance has a date column. Any idea how I can get this to work?
jko,
Databases can't be assumed to retrieve rows in any specific order, so student.attendances.first cannot be assumed to be the most recent attendance.
In your loop, something like student.attendances.maximum(:date) should give you the last date they attended. Then you can compae in your loop.
However, note that this kind of loop is very expensive in database terms (a lookup for each student). I'm guessing you'll be asking this question (ie. this query) more frequently than you're updating the database. So it might make sense to optimise a little.
One approach is to add a column into attendances table called "most_recent". And in your model do this add filters to set "most_recent" to true when adding a new record, and then setting the previous most_recent to false.
Its more work on an insert, but a lot easier to manage a lookup.
missed_students = Student.attendances.find(:conditions => 'students.active = true and attendances.most_recent = true and attendances.date <= ?', Date.today - 2.weeks)
So one database operation and you have a list of students missing attendance.
Hope that helps a little,
Cheers, --Kip
Thank you very much! I decided to go with the maximum(:date) because I will only use this functionality on the dashboard. If the performance is to slow then I'll switch to your optimized suggestion.