Linking many tables with conditions?

Rails
1

Rails newbie here struggling to understand ActiveRecord.

I have 6 tables:

  1. Class -> has many subjects
  2. Subject -> has many projects
  3. Project -> has many tasks
  4. Task -> has many scores
  5. Score (records a score for each task and a time stamp)
  6. Student -> has many scores

A student may redo a task many times in order to get a better score,
but I am (via the timestamp) recording multiple scores per task for
each student to keep track of progress over time. For example,
some score records might look like:

student_id, task_id, score, recorded_on
1 , 37 , C , 07-01-2007
2 , 28, , D , 07-01-2007
…
1 , 37 , B , 07-25-2007

Notice how student #1 has two grades for task #37 (over time, this
student
could have 4 or 5 grades for that task – and there are 150 tasks!).
I’ve got two problems:

  1. I need to be able to see the most recent scores for
    a student on all 150 tasks (e.g. I’d want the 07-25-2007 score for
    student
    #1 rather than the 07-01-2007 older score).

  2. In order to make the webpage, I need to be able to link task_id back
    to the Project and then back to Subject and then back to Class and
    also back to Student so that I can get for example, the student name
    rather than just his “primary key number” or the Project description
    or the Class name, etc.

I can almost get the first one with something like this:

@m = Score.maximum :recorded_on, :group => “task_id”, :conditions =>
[“student_id = ?”, params[:id]]

which gives [element_id, :recorded_on] combinations with the most
recent “recorded_on” value for that task… How can I
include the score, timestamp and even student_id field for that
most recent record?
How can I then link all the other tables back in to get access to all
their fields?

Thanks in advance,
Rog

2

  1. The easy solution for this is use acts_as_list.

  2. Use has_many and belongs_to. Then you can do
    task.project.project_name and such.

On Aug 1, 10:20 am, Roggie B. [email protected]

3

Thanks Kip. Being new to Rails, I’m still trying to understand
your code a little, but it looks like it might work. If I’m
reading your first solution (at top of your response) correctly,
I think this is only getting the most recent score for a single task.
It may not have been clear in my original posting, but there will
be 150 (maybe more) tasks per student and I want the most recent
by task. Hopefully that could be made in one query, not 150 individual
queries. I think maybe your final solution handles that but I’ve
got to study the code a little more :slight_smile: – haven’t done a
“:through” relation before for example. Thanks so much for the
response!

Rog

4

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

On Aug 1, 10:20 pm, Roggie B. [email protected]

5

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

On Aug 2, 8:31 pm, Roggie B. [email protected]

6

Excellent idea about adding a “most recent” column. I had already
come up with a straight SQL query kind of like what you listed, but
have still be trying to find a more ORM way of doing it and this
looks promising.

You’ve been most helpful.
Rog

7

This should perhaps be a different thread, but I’ll put it here for now.
My Score table (and the remaining tables) are linked via has_many
and belongs_to relations as shown in the original posting above.
For all the tables, it is the (automatic) primary key that provides
the linkage between the tables. Thus when I find a score by
“id=5”, I can get it’s “task_id” which in turn let’s me get the
“project_id”, etc.

Because my database is new, it turns out that all my task_ids are
numbered in order 1 to 150. At this point, I happen to know that
tasks 1-15 are from the Subject “Math” and tasks 16-28 are from
Subject “Science”, etc. So if I want to generate a web page with
task scores by subject, I could (perhaps naively) do a for-loop
and say “for x=1 to 15 do list Math scores in a <div “Math”> section”.
Suppose that a year from now, I add one more task to the task table
(which happens to be for subject Math).
This task will get a primary key of “151”. So now “Math” has task
ids of 1,2,…15,151, but when I get “most_recent_score” ordered by
task_id, task 151 will be the very last record not up by tasks 1…15
because I’m ordering by task_id, not by subject_id (which is two tables
up in the tree) and then by element_id.

How do people handle ordering issues when one of the order fields
comes from a parent or parent.parent table?

Thanks (again) in advance,
Rog

8

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

9

I may have solved the ordering dilemma with this:

@scores = Scores.find(:all, :conditions => [“scores.student_id = ? and
assessments.most_recent=1”, params[:id]], :order => “subjects.id ASC,
projects.id ASC, scores.task_id ASC”, :include => [{:tasks => {:project
=> :subject}}])

It appears to work in some simple tests.

Rog