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.

Josh
  • 53
  • 9
  • 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 – davegson Jan 09 '19 at 13:20
  • Possible duplicate of [ActiveRecord where field = ? array of possible values](https://stackoverflow.com/questions/28954500/activerecord-where-field-array-of-possible-values) – DonPaulie Jan 09 '19 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! – Josh Jan 09 '19 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. – max Jan 09 '19 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? – davegson Jan 09 '19 at 14:10
  • 'params[:q][:genrearray]' returns an array in my console, for example ["Rock", "Blues"] – Josh Jan 09 '19 at 14:30
  • `Band.joins(:genres).where(genres: { name: params[:q][:genrearray] })` should work. What does `Band.joins(:genres).where(genres: { name: ["Rock", "Blues"] }).to_sql` output? – davegson Jan 09 '19 at 15:29
  • actually put `Band.joins(:genres).where("genres.name IN (?)", params[:q][:genrearray]).to_sql` in the console please and past the output here as it doesn't look like a normal thing to me. In a new project, it doesn't do that for me. – khaled_gomaa Jan 09 '19 at 15:54
  • I'm a bit confused. You're doing `"genres.name IN (?)"` and it is giving you an ILIKE query or you *want* to do an ILIKE query against `'%rock%'` and `'%blues%'`? – mu is too short Jan 09 '19 at 19:50
  • Inputting `Band.joins(:genres).where("genres.name IN (?)", params[:q][:genrearray]).to_sql` gives me `"SELECT \"bands\".* FROM \"bands\" INNER JOIN \"bands_genres\" ON \"bands_genres\".\"band_id\" = \"bands\".\"id\" INNER JOIN \"genres\" ON \"genres\".\"id\" = \"bands_genres\".\"genre_id\" WHERE (genres.name IN ('Rock','Blues'))"` which is the query I want from the above code – Josh Jan 10 '19 at 10:13
  • @muistooshort I'm doing `"genres.name IN (?)"` and it is giving me an ILIKE query, when I want an IN query – Josh Jan 10 '19 at 10:14
  • @davegson `Band.joins(:genres).where(genres: { name: ["Rock", "Blues"] }).to_sql` outputs `"SELECT \"bands\".* FROM \"bands\" INNER JOIN \"bands_genres\" ON \"bands_genres\".\"band_id\" = \"bands\".\"id\" INNER JOIN \"genres\" ON \"genres\".\"id\" = \"bands_genres\".\"genre_id\" WHERE \"bands\".\"id\" IS NULL"` – Josh Jan 10 '19 at 10:22
  • Sorry but I think you're looking at the wrong code and getting yourself confused. AR won't rewrite SQL like that. The `to_sql` output for `Band.joins(:genres).where("genres.name IN (?)", params[:q][:genrearray])` makes sense, the `to_sql` output for `Band.joins(:genres).where(genres: { name: ["Rock", "Blues"] })` does not. Open a new Rails console, do `puts Band.joins(:genres).where("genres.name IN (?)", ['Rock', 'Blues'])` and `puts Band.joins(:genres).where(genres: { name: ["Rock", "Blues"] }).to_sql` and paste it all into your question please. – mu is too short Jan 10 '19 at 18:30
  • Ok... I had inputted it while using the Pry console, my mistake. The first clause returns `SELECT "bands".* FROM "bands" INNER JOIN "bands_genres" ON "bands_genres"."band_id" = "bands"."id" INNER JOIN "genres" ON "genres"."id" = "bands_genres"."genre_id" WHERE (genres.name IN ('Rock','Blues'))` then nil. The second clause returns `SELECT "bands".* FROM "bands" INNER JOIN "bands_genres" ON "bands_genres"."band_id" = "bands"."id" INNER JOIN "genres" ON "genres"."id" = "bands_genres"."genre_id" WHERE "bands"."id" IS NULL` then nil – Josh Jan 11 '19 at 10:45
  • I have rewritten the code around my where clause with the help of a friend and I now have it working. I'm not sure what the problem was, but it must've been in the surrounding code as the clause is still the same but is working. Thanks for the help. – Josh Jan 11 '19 at 14:38

0 Answers0