Ransack与数组值完全匹配

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):

我必须使用ransack在数据库上执行搜索。数据库中的一些列将数据存储在序列化数组中。我想将存储在数组中的确切数据与用户发送的数据进行匹配以执行搜索（用户的数据也是数组）。例如，在数据库中，一列的数据为（c1，c2为测试用例）：

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

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

用户搜索数据（t1，t2，t3是测试用例）：

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.
对于具有t1，t2，t4的情况c1，它应返回未找到的匹配。
With t3, it should result with match found.
使用t3时，应该找到匹配结果。
For case c2 with t1, t2, t3, it should return no match found.
对于具有t1，t2，t3的情况c2，它应该返回没有找到匹配。
With t4, it return `match found.
使用t4，它返回`匹配找到。

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.

我找到了postgres_ext gem，但我无法使它工作。任何人都可以建议我如何做到这一点或建议任何替代搜索方法这样？任何替代解决方案也欢迎。

2 个解决方案

#1

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:

我对该问题的解决方法：1。对数组params进行排序，并使用逗号和字符串连接它。例如：

    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.

我将此字符串存储在文本中的数据库列中。否，当用户搜索组时，我会使用其参数并以逗号分隔的字符串再次转换它并使用where子句查询到数据库。

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

在UI中，我再次将此字符串转换为数组并将其显示给用户。

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

我不知道它是否正确的做法，但它对我有用。

still any good solutions are welcome.

仍然欢迎任何好的解决方案。

#2

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'

好吧，使用rails 5.1简单的Ransack搜索将正常工作。例如，假设您的一个具有序列化数组名称的列是'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]]

#1