linking many tables with conditions?

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

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 = ?

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