2

I am trying to search using an array of strings in my where clause.

The array is generated by splitting the string contained within param[:q][:genres_name_cont] like this:

params[:q][:genrearray] = params[:q][:genres_name_cont].split

For example, if 'Rock Blues' is entered in the genre field, params[:q][:genrearray] = ["Rock", "Blues"].

Giving me the array which I then use in my where clause:

@bands = Band.joins(:genres).where("genres.name IN (?)", params[:q][:genrearray])

However, the SQL generated doesn't seem to be searching through an array like I would expect:

SELECT DISTINCT "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 Blues%'

Rather than ' ILIKE '%Rock Blues%'' I would be expecting to see something like ' IN ('Rock','Blues')'

Thanks in advance to anyone who can help.

Improve this question
15
  • 4
    why don't you want to search via an IN clause? And Band.joins(:genres).where(genres: { name: params[:q][:genrearray] }) does the same as your query, but is nicer Commented Jan 9, 2019 at 13:20
  • Possible duplicate of ActiveRecord where field = ? array of possible values Commented Jan 9, 2019 at 13:30
  • @davegson shouldn't that also work to search through the whole array? I have tried this also, but it works exactly the same as above, i.e. not giving me what I want. I understand it's tidier though! Commented Jan 9, 2019 at 13:44
  • 1
    I would consider if using a bit of data normalization is a better solution. Create a table for genres and a select tag where the user can select genres. This will give you an array of ids instead and avoid a costly string comparison. Commented Jan 9, 2019 at 13:55
  • ok, you do want to use IN - I did not get that initially. The query I wrote does generate a IN query, given you pass it an array as a param. I assume the problem is there. What does params[:q][:genrearray] return? Commented Jan 9, 2019 at 14:10
Related questions
18
rails where() sql query on array
1
Creating a where query in rails from an array
1
Passing array to Rails where method
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.