linking many tables with conditions?

rubyonrails-talk
1

Roggie,

Try:

@m = Score.find(:first, :conditions => ["student_id = ?", params[:id]] , :order => "recorded_on desc", :limit => 1)

The idea is to get all records for that student, sort them in descening order by date and get the first one. Which will be the most recent.

In general though, I imagine you already know which student you're working with. Lets say you have a Student instance in @student. Then you could do this:

student.scores.find(:conditions => "task_id = 3", :order => "recorded_on desc", :limit => 1)

Now lets assume you have a student, and you already know which task you are interested in. Assuming your model describes this:

student.tasks[some_task].scores(order => "recorded_on desc", :limit => 1)

All of these are a bit too "intimate" with the database mechanism so would be better off in the model. A bit like this (not literally this code)

class Student < ActiveRecord::Base   has_many :tasks   has_many :scores, :through => :tasks do      def recent_score        find(:first, :order "recorded_on desc", :limit => 1)      end    end end

Then you can just do: @student = Student.find(:first) @recent_score = @student.tasks[@task_index].scores.recent_score

Note that @task_index in these cases is not the task number, but the index into the array of tasks on the association proxy. You would need to work out which entry you need, or to resolve the correct task in advance.

Lastly, you could then

@student.tasks.each do |task|     puts "Recent score for task #{task.name}"     puts "Score: ", task.scores.recent_score.score     puts "Recorded on: ", task.scores.recent_score.recorded_on end

and you are all done (I hope!)

Cheers, --Kip

Cheers, --Kip

2

Rog, that's a whole different prospect that is easy to describe and hard to implement. Let me explain (and then suggest a solution)

Recall that max() is an aggregate function, not a selector. So it works on sets (rows) of data. When you do GROUP BY then you can only select aggregates like max, min, count, sum, ....

So to work out if a simple select can do this you have to ask " is there some aggregate function that would return the latest score from that set of scores for a User and Task". The answer is no in this case.

All is not lost - we could try a correlated subquery. Now we are well out of the ORM model and up to our armpits in hard to read SQL. It goes something like this:

  SELECT scores.* from users join tasks on users.id = tasks.user_id                                              join scores on scores.id = tasks.scores_id                 WHERE users.id = ?

3

Arrgggh, keyboard problems. As I was saying....

  SELECT scores.* from users join tasks on users.id = tasks.user_id                                    join scores on scores.id = tasks.scores_id                 WHERE users.id = ?                       AND scores.id = (select maximum(latest_scores.id)                              from scores lastest_scores                              where latest_scores.user_id = scores.user_id                              and latest_scores.task_id = scores.task_id)

This is hard to format for this forum with narrow lines, but you can tell its not easy to understand. It doesn't map well to ORM, its not in the Rails spirit and its also very very expensive in the database.

Assuming that you make this query frequently, compared to the number of updates to the database then I think the idea would be to make the updates a little more expensive so the queries can be less expensive.

To do that I would suggest adding a column to the scores table called     add_column :scores, :most_recent, :boolean

In your application, set :most_recent = true when you are storing a new score. In your model, add an after_update filter a bit like this:

class Score < ActiveRecord::Base   after_update :clear_recent_flag

  def clear_recent_flag     # clear the recent_flag on all rows with     # the same user.id and task.id except this one     update_all("most_recent = 0", ["user_id = ? and task_id = ? and id <> ?", self.user_id, self.task_id, self.id]   end

Then, finally! you can select what you want in Rails:   most_recent_scores = user.tasks.scores.find(:conditions => ['most_recent = ?', true])

Which will return a list of all most recent scores for all tasks for a given user.

Hope this helps,

--Kip