User has_many Students Student has_one ReportCard ReportCard has_many Metrics Metric has_many Numbers Given the above schema, if i want to get all the numbers for a particular user ... @user = User.first # Some User @user.collect(&:students).flatten.collect(&:report_card).collect(&:metrics).collect(&:numbers)
Refactorings
No refactoring yet !
Aaron Scruggs
October 24, 2009, October 24, 2009 02:05, permalink
The problem with the above approach is that it requires a lot of database calls with what can be accomplished with one. Their may be a clever way to do this with one "has_many :through", but if so, I have never seen it. I would do it like this:
class User
def numbers
sql = <<-SQL
SELECT n.*
FROM user u,
students s, report_cards rc, metrics m, numbers n
WHERE s.user_id = u.id
AND s.id = rc.student_id
AND rc.id = m.report_card_id
AND m.id = n.metric_id
SQL
Number.find_by_sql(sql)
end
end
Muke Tever
October 24, 2009, October 24, 2009 03:45, permalink
I had a lot of these in my app... You're going to want something like this. (The exact syntax re singular and plural in :joins will depend on how your relationships are defined—my solution assumes they're all belongs_to and no habtms—but the hash will basically be a map connecting Number with User.)
Number.find(:all,
:joins => { :metric => { :report_card => { :student => :user } } },
:conditions => { :users => { :id => user.id } })
SELECT "numbers".*
FROM "numbers"
INNER JOIN "metrics" ON "metrics".id = "numbers".metric_id
INNER JOIN "report_cards" ON "report_cards".id = "metrics".report_card_id
INNER JOIN "students" ON "students".id = "report_cards".student_id
INNER JOIN "users" ON "users"."id" = "students"."user_id"
WHERE ("users"."id" = 1)
Aaron Scruggs
October 24, 2009, October 24, 2009 20:49, permalink
I like Muke's approach better, but would suggest doing it slightly different.
The advantage of specifying the ":select" is that is will return models that you can update. Without the ":select", "save" will through an exception.
Number.find(:all,
:joins => { :metric => { :report_card => { :student => :user } } },
:conditions => { :users => { :id => user.id } },
:select => 'numbers.*')
Aaron Scruggs
October 24, 2009, October 24, 2009 20:50, permalink
I like Muke's approach better, but would suggest doing it slightly different.
The advantage of specifying the ":select" is that is will return models that you can update. Without the ":select", "save" will through an exception.
Number.find(:all,
:joins => { :metric => { :report_card => { :student => :user } } },
:conditions => { :users => { :id => user.id } },
:select => 'numbers.*')
Is there a better way to do this?