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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Boone <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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 > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Boone
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Boone
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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
>
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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
On Aug 3, 12:53 am, Kip <kipco...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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 Boone
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
>
> > 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
>
> > --
> > Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---