How can I make this Ruby on Rails page more efficient?

Question

I'm building a site where users can track their collection of figures for Dungeons & Dragons (www.ddmdb.com). The models/relationships involved in this funcitonality are the following:

User:

• id
• login (username)
• a bunch of other fields

Miniature:

• id
• name
• number (# in the set, not count)
• release_id (foreign key)
• a bunch of other fields and foreign keys

Ownership:

• id (is this really even needed?)
• user_id
• miniature_id
• have_count
• favorite (boolean)

The pertinent relationships I have set up are as follows:

User:

• has_many :ownerships
• has_many :miniatures, :through => :ownerships, :uniq => true, :conditions => "ownerships.have_count > 0"
• has_many :favorites, :through => :ownerships, :source => :miniature, :uniq => true, :conditions => "ownerships.favorite = true"

Miniatures:

• has_many :ownerships
• has_many :owners, :through => :ownerships, :source => :user, :uniq => true, :conditions => "ownerships.have_count > 0"

Ownership:

• belongs_to :user
• belongs_to :miniature

I have a page where user's can both view and update their collection, as well as view other user's collections. It contains a list of all the miniatures on the site and a text box next to each where the user can enter how many of each miniature they have. This functionality also exists in sub-lists of miniatures (filtered by type, release, size, rarity, etc.)

When a user creates an account they have no entries in the ownership. When they use the collection page or sub-list of miniatures to update their collection, I create entries in the ownership table for only the miniatures on the submitting page. So if it's the full Collection list I update all minis (even if the count is 0) or if it's a sub-list, I only update those miniatures. So at any time a particular user I may have: - no entries in ownership - entries for some of the miniatures - entries for all the miniatures.

The problem I'm having is that I don't know how to query the database with a LEFT JOIN using a "Rails method" so that if a user doesn't have an entry for a miniature in Ownerships it defaults to a have_count of 0. Currently I query for each user_id/miniature_id combination individually as I loop through all miniatures and it's obviously really inefficient.

View:

<% for miniature in @miniatures %>
  <td><%= link_to miniature.name, miniature %></td>
  <td><%= text_field_tag "counts[#{miniature.id}]", get_user_miniature_count(current_user, miniature), :size => 2 %></td>
<% end %>

Helper:

def get_user_miniature_count(user, miniature)
  ownerships = user.ownerships
  ownership = user.ownerships.find_by_miniature_id(miniature.id)
  if ownership.nil?
    return 0
  else
    return ownership.have_count
  end
end

An alternate solution would be creating entries for all miniatures when a user signs up, but then I would also have to add a 0 have_count for all users when a new miniature is added to the database after they sign up. That seems like it could get a bit complex, but perhaps it's the right way to go?

Is there a way to do the join and supply a default value for miniatures where there's no entries in the Ownership table for that particular user?

Answer 1

Maybe I'm missing something, but the way you've specified the relationships seems sufficient for rails to figure out the counts on its own? Have you tried that?

edit: Re the discussion in the comments...how about this:

<% ownerships=current_user.ownerships %> 
<% for miniature in @miniatures %>
  <td><%= link_to miniature.name, miniature %></td>
  <td><%= text_field_tag "counts[#{miniature.id}]", get_miniature_count(ownerships, miniature), :size => 2 %></td>
<% end %>

Where get_miniature_count() just iterates through the supplied ownerships and returns 0 or the count if the miniature appears in the list? I think this will avoid going back to the DB again in each iteration of the 'for'.

edit 2: I'd also suggest firing up script/console and trying to do what you want in ruby directly, i.e. test for the miniatures membership in the ownerships list thinking in terms of ruby not SQL. Often, rails and activerecord is smart enough to do the necessary SQL black magic for you behind the scenes, given it knows the relationships. If you find a user and then do user.methods you'll see what is available.

Answer 2

The first thing I would say is that the User model should own the code that works out how many of a given miniature the user owns, since it seems like "business logic" rather than view formatting.

My suggestion would be to add a method to your User model:

def owns(miniature_id)
  o = ownerships.detect { |o| o.miniature_id == miniature_id }
  (o && o.have_count) || 0
end

Dry-coded, ymmv.

Edit: Note that ownerships is cached by Rails once loaded and detect is not overridden by ActiveRecord like find is, and so acts as you would expect it to on an Array (ie no database operations).

Answer 3

Using fd's suggestion and information found at http://www.ruby-forum.com/topic/52385, I created the following method:

def miniature_count(miniature_id)
  if @counts.nil?
    @counts = Hash.new
    ownerships.collect{|o| @counts[o.miniature_id] = o.have_count }
  end
  count = @counts[miniature_id] || 0
end

This ends up being faster than the detect approach.

I picked *miniature_count* over owns for the name because owns sounds like a method that should return a boolean instead of an integer.

Query Every Entry

Completed in 2.61783 (0 reqs/sec) | Rendering: 1.14116 (43%) | DB: 1.34131 (51%) | 200 OK [http://ddmdb/collection/1]

Detect Methods

Completed in 2.20406 (0 reqs/sec) | Rendering: 1.87113 (84%) | DB: 0.21206 (9%) | 200 OK [http://ddmdb/collection/1]

Hash Method

Completed in 0.41957 (2 reqs/sec) | Rendering: 0.19290 (45%) | DB: 0.10735 (25%) | 200 OK [http://ddmdb/collection/1]

I will definitely need to add caching, but this is definitely an improvement. I also suspect I am prematurely optimizing this code, but it's a small site and a 2.5 second load time was not making me happy.