CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3postgresqlruby-on-rails-4
sreenivas
sreenivas

Reputation: 395

Reduce N+1 Queries

N+1 queries detected what does that mean what should i do to make it work.

I am using Bullet gem for showing N+1 queries

user: kalyan
N+1 Query detected
  Emp => [:functions]
  Add to your finder: :include => [:functions]
N+1 Query method call stack
  /home/Documents/app/views/tics/show.html.haml:20:in `_app_views_tics_show_html_haml___2301551533476719406_237290860'

This is the message from bullet gem.

_app_views_tics_show.html.haml

- if (@tic.assigned_to == current_user.emp) or (current_user.emp_functions.map{|x| x.id}.include?(1) if current_user.emp_functions.present? )
              = best_in_place @tic, :subject
            - else
              = @tic.subject

help me to reduce n+1 query problem

emp.rb

has_many :emp_functions, inverse_of: :emp,dependent: :restrict_with_exception

belongs_to :user, inverse_of: :emp

emp_functions.rb

belongs_to :emp , inverse_of: :emp_functions

belongs_to :function

function.rb

has_many :emp_functions, dependent: :restrict_with_exception

has_many :emp, through: :emp_functions

user.rb

has_one :emp, inverse_of: :user, dependent: :restrict_with_exception

Upvotes: 0

Views: 1419

Answers (3)

jvnill
jvnill

Reputation: 29599

This is a very specific solution to your issue and I advise against using it in a view

change current_user.emp_functions.map{|x| x.id}.include?(1) to

current_user.emp_functions.where(id: 1).exists?

This will result in 1 single query to the db everytime you hit the page containing it.

You can also remove that if statement to remove another db query.

Upvotes: 0

Almaron
Almaron

Reputation: 4147

Imho, the most efficient way to get rid of this N+1 query is to alter the current_user method, adding includes(:emp_functions) to the User.find call. The way you do it depends on the way you handle your authentication. If you're using some kind of gem (Devise or Sorcery for example) you'll need to reopen those classes and alter the method, not forgetting to use super. If youэму written your own authentication, you'd be able to do that more easily.

Second thing I noticed is that you don't actually need to use map on your user.emp_functions in the view, provided that emp_functions is a has_many association on the User model. You can just flatten it to current_user.emp_function_ids.include?(1). This will help you get rid of the N+1 query problem, but only in this particular case. If you do tackle the emp_functions of the current user often in many places, I'd recomend using the first path I described.

Third third thing, not directly relevant to the question is that I really don't think that code belongs in the view. You should move it to a helper or a decorator, keeping the view clean and readable.

Upvotes: 1

Jasdeep Singh
Jasdeep Singh

Reputation: 3326

If emp_functions is a has_many association on your user model (and it seems like it is), You should be doing:

current_user.emp_function_ids.include?(1)

Upvotes: 0

Related Questions