Let's say there is a model called User. That has the attribute :hobbies.
user = User.first
user.hobbies
=> ["Bowling", "Cooking", "Knitting"]
If I wanted to use a .where searching for the :hobbies attribute, how would I do that? I'm using postgresql, and
:hobbies, :text, array:true
User.where(hobbies: "Anything I put here, string array, hash") throws the error
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR: malformed array literal:
Remember that you can use any SQL you want by passing a string to where, e.g.:
User.where("hobbies @> ARRAY['Bowling', 'Cooking', 'Knitting']")
or
User.where("hobbies && ARRAY['Bowling', 'Cooking', 'Knitting']")
Postgres has good documentation of the array operators available.
WHERE hobbies IN('Bowling', 'Cooking', 'Knitting')? 'Cos that would be the optimal query.
COUNT does not tell you the length of the array, but it tells you how many rows are getting rollup up in an aggregate query. That's why it's always giving you 1: no matter how long the array, there is still just one row. You probably want array_length(hobbies, 1). But I'm not actually sure what you're trying to do, so I could be on the wrong track.IN here is because hobbies is not a string, but an array of strings. So you should use the array operators instead. @> means hobbies has all of Bowling, Cooking, and Knitting. && means it has at least one of them.
You may try contains or overlap from postgres_ext gem. Just install the gem and then query:
query = ["Bowling", "Cooking", "Knitting"]
#With Overlap operator:
User.where.overlap(hobbies: query)
#or with Contains Operator
User.where.contains(hobbies: query)
postgres_ext gem for this to work.