# Linking many tables with conditions? **URL:** \ **Category:** Rails\ **Created:** [August 1, 2007, 4:20pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971 "2007-08-01T16:20:26Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 1, 2007, 4:20pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/1 "2007-08-01T16:20:26Z")
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 ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 1, 2007, 7:26pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/2 "2007-08-01T19:26:59Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 2, 2007, 2:31pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/3 "2007-08-02T14:31:44Z")
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 🙂 – haven’t done a “:through” relation before for example. Thanks so much for the response! Rog ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 2, 2007, 2:44am UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/4 "2007-08-02T02:44:43Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 2, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/5 "2007-08-02T18:54:41Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 2, 2007, 7:58pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/6 "2007-08-02T19:58:41Z")
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 ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 3, 2007, 3:03pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/7 "2007-08-03T15:03:26Z")
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 \
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 ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 2, 2007, 7:14pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/8 "2007-08-02T19:14:49Z")
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 ---
**Author:** ![rogboone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e0b2c6/32.png) [@rogboone](https://www.ruby-forum.com/u/rogboone)\ **Post date:** [August 3, 2007, 5:31pm UTC](https://www.ruby-forum.com/t/linking-many-tables-with-conditions/110971/9 "2007-08-03T17:31:38Z")
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