Rails 3.1: Optimizing many_to_many through join table in single query?

Question

User has many Tracks, through Favorite. Favorite has some extra per-user meta-data about the related track, and the whole thing is returned as a json blob using custom :as_public hashing method.

Even though I'm accessing the related objects using a JOIN, I'm making hundreds of very basic SELECT track FROM tracks WHERE track.id='1' queries. I want to optimize this lookup.

users_controller:

def show
  @user = User.find(params[:id])
  
  respond_to do |format|
    format.html # index.html.erb
    format.json { render json: @user.to_json(:methods => [:favorites_as_public_tracks]) }
  end
end

user.rb

def favorites_as_public_tracks
  favorites.joins(:track).sort_by(&:created_at).map(&:as_public_track)
end

favorite.rb

class Favorite < ActiveRecord::Base
  belongs_to :user
  belongs_to :track
  
  #Grabs some stuff from Favorite, merging it with the public data from Track
  def as_public_track
    track.public_attributes.merge(public_attributes_for_merging_onto_track)
  end
  
  # This stuff gets added onto track.to_json and used by javascript
  def public_attributes_for_merging_onto_track
    return {
      :favorite_id  => id,
      :from_service => from_service,
      :favorited_at => created_at,
      :collection_name => "#{collection_name}, #{from_service}"
    }
  end
    
  def public_attributes
    private_attrs = [:user_id]
    
    attributes.reject {|key, val| private_attrs.include? key.to_sym }
  end
end

track.rb

  def public_attributes
    private_attrs = [] #[:id]
    
    attributes.reject {|key, val| private_attrs.include? key.to_sym }
  end

The SQL that gets run when I access the user's favorites as public tracks is:

User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", "1"]]
  Favorite Load (7.8ms)  SELECT "favorites".* FROM "favorites" INNER JOIN "tracks" ON "tracks"."id" = "favorites"."track_id" WHERE "favorites"."user_id" = 1 ORDER BY "favorites".created_at DESC
  Track Load (1.3ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 1 LIMIT 1
  Track Load (0.5ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 2 LIMIT 1
  Track Load (0.3ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 3 LIMIT 1
  Track Load (0.3ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 4 LIMIT 1
  Track Load (0.5ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 5 LIMIT 1
  Track Load (0.2ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 6 LIMIT 1
  Track Load (0.2ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 7 LIMIT 1
  Track Load (0.2ms)  SELECT "tracks".* FROM "tracks" WHERE "tracks"."id" = 8 LIMIT 1

How do I do this without making hundreds of SELECT track where track.id='...' queries?

Thanks!

Answer 1

If you have a list that stays the same for all instances, consider making it a class method rather than an instance method. See if you can make the public attributes and private attributes lists as class methods. Otherwise, to build these lists for each instance, you will get a hit for each record.

Usually, :include :tracks in the finds can fix it, but I don't think that is the issue here.

Good luck!