FNGR FNGR - 1 year ago 101
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:


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


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

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 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 ?
        .where("tags.name LIKE %:q% OR posts.content LIKE %:q%", q: query)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download