FNGR FNGR - 7 months ago 39
SQL Question

Rails N+1 because of .joins?

I got a simple search for posts in my rails app. It searches through taggings of the post and the content (body) of the post. Everything works finde, but bullet moans about a N+1 query.

N+1 Query detected Post => [:tags] Add to your finder: :includes => [:tags]


How can I avoid that in my situation? Code looks like that:

model

def self.search(search)
Post.joins(:tags).where("name LIKE ? or body LIKE ?", "%#{search}%", "%#{search}%").uniq
end


controller

def index
if params[:search]
@posts = Post.includes(:author).search(params[:search])
else
@posts = Post.includes(:author, :tags).all
end
end


If i use .includes instead of .joins, I get the following:

SQLite3::SQLException: no such column: name:


so, as a beginner, how to deal with that? Are there better solutions? Thank you in advance!

max max
Answer

You need to tell ActiveRecord which table you are using in the WHERE clause.

In most cases you would use a hash like this to target the associated table:

Post.includes(:tags).where(tags: { name: 'foo' })

When using LIKE however you need to create a string condition and in that case you would simply specify the table:

class Post
  # don't use an argument with the same name as the method.
  # its confusing and can lead to strange edge cases.
  def self.search(query)
    # Note that we use a named placeholder instead of ?
    self.includes(:tags)
        .where("tags.name LIKE %:q% OR posts.content LIKE %:q%", q: query)
        .uniq
  end
end
Comments