Rails association query

Question

I am having trouble accessing the correct information in my rails model (which I believe to be correct) The schema for my tables are

create_table :schools do |t|
    t.string :name
    t.timestamps
end  

create_table :variables do |t|
    t.string :name
    t.string :category
    t.timestamps
end

create_table :data do |t|
    t.string :entry
    t.decimal :rank, :scale => 3
    t.integer :school_id, :null => false
    t.integer :variable_id, :null => false
    t.timestamps
end

Model classes:

class Datum < ActiveRecord::Base
    belongs_to :school
    belongs_to :variable
end

class School < ActiveRecord::Base
    has_many :data
    has_many :variables, :through => :data
end

class Variable < ActiveRecord::Base
    has_many :data
    has_many :schools, :through => :data
end

here is my school show.html.erb page currently:

<h2> <%= @school.name %> </h2>

<table>
<% @school.variables.each  do |variable| %>
<tr><tr/>
<td><%= variable.name %></td>
<td><%= Datum.find(:first, :conditions => {:school_id => @school.id, :variable_id => variable.id}).entry %></td> 
<td><%= link_to 'Edit', edit_school_path(@school) %></td>
<td><%= link_to 'Back', schools_path %></td>
<% end %>
</table>  

It does what I want it to but it does wayy to many queries. I'm assuming I have to do eager loading but based on examples I found online I couldn't figure out how to do this (I'm really new to rails). Does anyone have any ideas?

Answer 1

Hi,

Try this and see if you get the results you expect, as well as a reduced number of queries.

Datum.find(:first, 
           :conditions => {:school_id => @school.id, :variable_id => variable.id}, 
           :include => [:school, :variable])

Also, MVC principles dictate that you shouldn't do the find in your view; instead, in the controller's method (app/controllers/school_controller.rb, method show) do

@data = Datum.find(....)

and in the view:

<%= @data.entry %>

That way you don't pollute your view with DB access concerns.

Answer 2

Well you are doing your Datum.find query inside a loop. Rails would be doing That for each one of the elements that are returned by @school.variables

Find statements should really be done in the controller. Set up the variables you need there with one or two thoughtfully constructed finds using eager loading. Then put the HTML stuff in your each loop into a partial (named something like '_variable') and call it in your view like this:

<% render :partial 'variable', :collection => @school.variables %>

Inside the partial you get a local variable named after the name of the partial with the data from current member of the collection in it. Rails will take care of the looping for you.

I hope that helps.