Limiting the SQL queries generated by Rails when accessing the data from multiple tables on one page

I have many items and many users in a database. A user can set a status to the various items. The three tables are (they have been simplified in this explanation) users, statuses, items. The models are shown below.

class Item < ActiveRecord::Base
    has_many :statuses  
end

class Status < ActiveReco开发者_StackOverflow中文版rd::Base
    belongs_to :user
    belongs_to :item    
end

class User < ActiveRecord::Base
    has_many :statuses  
end

The controller

class ItemController < ApplicationController
    def index
        @items = Item.all
    end
end

The view (this has been simplified, in my code I actually generate a form for the status so the user can create/modify their status but in the example below I'm just making as if the status is being printed out as text):

<th>Item ID</th>
<th>Item Status</th>
...
<% @items.each do |item| %>
  <td><%= item.id %></td>
  <% status = item.statuses.where(:user_id => current_user.id) %>
  <td><%= status.first.status %></td>
<% end %>

Here are the queries generated by Rails:

Item Load (0.3ms)  SELECT `items`.* FROM `items` ORDER BY id asc
SQL (0.2ms)  SELECT COUNT(*) FROM `statuses` WHERE (`statuses`.item_id = 1) AND (`statuses`.`user_id` = 103)
SQL (0.2ms)  SELECT COUNT(*) FROM `statuses` WHERE (`statuses`.item_id = 2) AND (`statuses`.`user_id` = 103)
Status Load (0.1ms)  SELECT `statuses`.* FROM `statuses` WHERE (`statuses`.item_id = 2) AND (`statuses`.`user_id` = 103) LIMIT 1
SQL (0.2ms)  SELECT COUNT(*) FROM `statuses` WHERE (`statuses`.item_id = 3) AND (`statuses`.`user_id` = 103)

All the items are selected in one SQL query which I think is good. Then it seems the SELECT COUNT (*) query is executed, if a status is found then that status is fetched using another SQL query, this happens for each item.

Guessing there is a much better way of going about this, when I have a few hundred items the number of SQL queries being carried out is huge! If anyone has any tips for how they would go about this I'd be interested to hear.

Thanks.

The solution is to include the statuses when you build the items.

@items = Item.includes(:statuses).all

And then use a select method to return the relevant status.

item.statuses.select {|s| s.user_id => current_user.id}

The rails magic sometimes results in these crazy queries, since item.statuses can be interpreted as either an array of status objects, or an ActiveRecord Relation.

When you call the where method on item.statuses, it defaults to ActiveRecord Relation behaviour, and builds a new SQL query. If you call select, item.statuses behaves as an array.

This is also the case for methods like sum. Very annoying!