Ransack exact match with array values

Question

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 = []

For case c1 with t1, t2, t4, it should return no match found.
With t3, it should result with match found.
For case c2 with t1, t2, t3, it should return no match found.
With t4, it return `match found.

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.

So you are looking for *exact matches* only, no subset matches (e.g. `t2` on `c1`), right? — Alexander Presber, Mar 19 '18 at 14:20
yes no subset match I need a exact match.. I actually figured out a to do it. — thedudecodes, Mar 20 '18 at 06:41

score 5 · Accepted Answer · answered Mar 20 '18 at 06:56

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.

score 1 · Answer 2 · answered Mar 16 '18 at 11:15

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]]

Ransack exact match with array values

2 Answers2