CODEWITHSUNDEEP
rubyruby-on-rails-3postgresqlactiverecord
Ankita.P
Ankita.P

Reputation: 442

Avoiding n+1 query bug in rails has many association

There are many answers with this particular topic but I am not finding any one perfectly suited for me. I have a recipe app where in I need all the users who gave particular ratings to that recipe(example: list of user who gave rating 5 to particular recipe) without having an n+1 query bug. I know it can be solved using includes option but in belongs to reference I don't know how to use it. I am using rails 3.2.15.

below is the modal level description of my app 

class Recipe < ActiveRecord::Base
  belongs_to :user, :foreign_key => 'creator_id'
  has_many :photos, :dependent => :destroy
  has_many :recipe_ingredients
  has_many :ingredients, :through => :recipe_ingredients
  has_many :ratings, :dependent => :destroy
end


class  Rating < ActiveRecord::Base
  belongs_to :user, :foreign_key => 'rater_id'
  belongs_to :recipe
end

class Ingredient < ActiveRecord::Base
  belongs_to :user, :foreign_key => 'creator_id'
  has_many :recipe_ingredients
  has_many :recipes, :through => :recipe_ingredients 
end

class User < ActiveRecord::Base
  has_many :recipes , foreign_key: "creator_id", class_name: "Recipe", :dependent => :destroy
  has_many :ingredients, foreign_key: "creator_id", class_name: "Ingredient", :dependent => :destroy
  has_many :ratings, foreign_key: "rater_id", class_name: "Rating", :dependent => :destroy
end

My query to retrieve users is 

@rec = Recipe.find(params[:id])
ratings = @rec.ratings.where(:ratings => params[:ratings])

users = ratings.map {|rate| rate.user}

this introduces an n+1 query bug is there any proper way in use rails?

Upvotes: 1

Views: 482

Answers (2)

D-side
D-side

Reputation: 9485

An answer by @VedprakashSingh has a major downside of returning instances of Rating populated with data from User. So, suddenly, you're left without all the User's methods. Type safety is thrown out the window.

What you can do instead is a joins/merge combo to fetch instances of one model with conditions on the other one. Like so:

User.joins(:ratings).merge(
  # Here you can place a relation of ratings
  @rec.ratings.where(:ratings => params[:ratings])
  # Yes, it's your line, copy-pasted from your question
) # ...and back here we get users joined with these

So you explicitly start the query with the fact that you want Users. Then you join the tables, getting a huge set of every rating joined with its user (all still in the database!). Contents of merge then filter down ratings in that set to the ones you want.

Beware, as the line I've copied from the question may be vulnerable to faking parameters, it's not filtered. You have ratings table, so if params[:ratings] turns out to be a hash (that can be done, it's literally user's input) it will be treated as a hash of conditions allowing the user to send any hash-based conditions.

yoursite.com/whatever?ratings[id]=12

That query string will result in params[:ratings] being:

{"id" => "12"} # Damn right, a Ruby hash

Strong parameters exist for a reason.

Upvotes: 1

Vedprakash Singh
Vedprakash Singh

Reputation: 532

Modified query:

users = ratings.includes(:user).select('users.name')

I have added select('users.name') considering you have name column in users. You can use any column you want for the view.

Upvotes: 1

Related Questions