Multi term Ransack search in same field not working

Question

I have implemented Ransack for my site's search function and want to be able to search in one database column for multiple terms entered in the same input field.

Here is the code in my controller:

if params[:q]
    params[:q][:groupings] = []
    split_genres = params[:q][:genres_name_cont].split(' ')
    params[:q][:genres_name_cont].clear
    split_genres.each_with_index do |word, index|
      params[:q][:groupings][index] = {genres_name_cont: word}
    end
end

@q = Band.ransack(params[:q])
@bands = @q.result(distinct: true).includes(:genres).sort_by{|band| band.name}

And here is the query which is returned when I enter multiple genres in the search:

SELECT "bands".* FROM "bands" LEFT OUTER JOIN "bands_genres"
ON "bands_genres"."band_id" = "bands"."id" LEFT OUTER JOIN "genres"
ON "genres"."id" = "bands_genres"."genre_id"
WHERE ("genres"."name" ILIKE '%rock%' AND "genres"."name" ILIKE '%blues%')

The query looks right to me, especially with it being more or less identical to the query generated when only one term is entered, which works fine.

Can anybody shed any light on what I may need to change to get this search working when multiple terms are entered within the field?

Answer 1

Welcome to Stack Overflow 👋.

The query doesn't work for multiple genres because it's only picking rows from the genres table where the name of a genre contains both "rock" and "blues", so it would only match genres like "rock/blues" or "bluesy rock ballads".

It sounds like what you're after is WHERE ("genres"."name" ILIKE '%rock%' OR "genres"."name" ILIKE '%blues%'), where OR means it will match either, so both the "rock" and "blues" genres will be matched.

In terms of getting this to work in Ransack, this comment on the GitHub project looks similar to yours, but includes the following line before you set your groupings:

params[:q][:combinator] = 'or'

I haven't tested this locally, but it appears that adding that line will convert the group combination from AND to OR, which should get you the right search results.

Let me know how it goes and I'll remove the tentative "should" and "appears" if it works 😉.