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!
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download