CODEWITHSUNDEEP
ruby-on-railsrubyransack
pwnchaurasia
pwnchaurasia

Reputation: 1499

Ransack exact match with array values

I have to perform a search on a database using ransack. A few columns in the database have data stored in serialized arrays. I want to match the exact data stored in the arrays with data sent by user to perform search (users' data are also arrays). For example, in database, one column has data as (c1, c2 are test cases):

c1.column_data = [1, 2, 3, 4, 5]
c2.column_data = []

User searches for data (t1, t2, t3 are test cases):

t1.user_data = [1]
t2.user_data = [1, 3]
t3.user_data = [1, 2, 3, 4, 5]
t4.user_data = []

I found postgres_ext gem, but I cannot make it work. Can anyone suggest how I can do this or suggest any alternative method for search like this? Any alternative solution is also welcome.

Upvotes: 12

Views: 2474

Answers (2)

pwnchaurasia
pwnchaurasia

Reputation: 1499

I am answering my own question.

It may vary to the requirement.

I had to take the user params and search in the database if any exact data matches then put the user in same group else make a new group with the searched data and at the end save each searched data for respective user also.

As all the fields were multi select dropdowns checkboxes etc all params was coming as an array of strings.

My workaround for the problem: 1. sorted the array params and joined it with comma and made string. ex: 

    a = [1,2,3,4,5,6]
    a.sort.join(',')
    "1,2,3,4,5,6"

I stored this string in the database column which is in text. No when user searches for a group I take its params and convert it again in comma separated string and using where clause query to database is done.

In UI I convert this string to array again and show it to user.

I dont know its a correct way of doing it or not but it is working for me.

still any good solutions are welcome.

Upvotes: 5

Shani
Shani

Reputation: 2541

ok, with rails 5.1 simple Ransack search will works fine. For example assuming one of your columns with serialised array name is 'column_data'

t.text :column_data, array: true, default: []

you can search the array columns by following this syntax:

‘{ val1, val2, … }’

for example in console 

some_search = Model.search(column_data_eq: '{1,3}')
some_search.result

should give you the following results

SELECT  "model".* FROM "mdole" WHERE "model"."column_data" = '{1,3}' LIMIT $1  [["LIMIT", 11]]

Upvotes: 1

Related Questions