how to use LIKE in included relations

Question

I want to use search condition with included relations, just like below

Post.includes(:tags).where( tags: { title: '%token%' }).all

The posts and tags table has been associated with a 3rd table named post_tag_relations.
The schema is like below:

posts
 id: pk
 title: string
 content: text

tags
 id: pk
 title: string

post_tag_relations
 id: pk
 tag_id: integer
 post_id: integer

The syntax only works with equal condition, I really dont know how to use LIKE search condition.

When using Post.joins(:tags) and Tag.area_table[:title].matches('%token%') it will works fine, but some post that has no tags will not be fetch out.

Could anyone help me? Thanks a lot.

---

UPDATE:

The Rails version is 4.1.

I want to search the post like posts.title LIKE '%token%' OR tags.title LIKE '%token%', so if use Post.joins(:tags) will not be functional if some posts have no tags. So I need use Post.includes(:tags) instead.

---

UPDATED AGAIN:

looks cannot use one-query to fetch, so I had already try another database schema...

Answer 1

Something like this:

Post.includes(:tags).where( "tags.title LIKE ?", "%#{token}%" )

could work. (The syntax might be a little wrong, sorry, but you get the idea)

Answer 2

Why not do this:

Post.includes(:tags).where(Tag.arel_table[:title].matches('%token%').or(Tag.arel_table[:post_id].eq(nil)))

Answer 3

Since ruby-on-rails-2 the joins operation is used in all cases before the includes operation during performance, but since includes uses LEFT OUTER JOIN operator, you should use exactly it. May be you need also to use not LEFT, but FULL join. So try this with arel gem:

class Post
   scope :with_token(token), -> do |token|
      re = Regexp.union(token).to_s
      cond = Arel.sql("title REGEXP ? OR content REGEXP ?", re, re)
      includes(:tags).where(Tag.arel_table[:title].eq(token).or(cond))
   end
end

Of course original condition could be replaced to use LIKE operator:

class Post
   scope :with_token(token), -> do |token|
      includes(:tags).where(arel_table[:title].matches("%#{token}%")
         .or(arel_table[:content].matches("%#{token}%")
         .or(Tag.arel_table[:title].eq(token))))
   end
end

NOTE: If there are some errors, provide please result SQL.