如何通过ransack gem搜索数组？ [英] How to search array through ransack gem?

问题描述

我正在使用 ransack gem 进行Rails搜索。我需要在 User 表中搜索<​​code> email_ids 的数组。

I'm using ransack gem for searching in rails application. I need to search an array of email_ids in User table.

在洗劫案中引用此问题，我按照步骤操作并添加了这到初始化文件夹 ransack.rb

Referring to this issue at ransacking, I followed the steps and added this to the initializers folder ransack.rb

Ransack.configure do |config|
   {
    contained_within_array: :contained_within,
    contained_within_or_equals_array: :contained_within_or_equals,
    contains_array: :contains,
    contains_or_equals_array: :contains_or_equals,
    overlap_array: :overlap
   }.each do |rp, ap|
   config.add_predicate rp, arel_predicate: ap, wants_array: true
  end
end

在rails控制台中，如果我这样做：

In the rails console, if i do like this:

a = User.search(email_contains_array: ['[e-mail]'])

它生成如下所示的sql：

it produces the sql like this:

"SELECT \"users\".* FROM \"users\" WHERE \"users\".\"deleted_at\" IS NULL AND (\"users\".\"email\" >> '---\n- [e-mail]\n')"

并给出如下错误：

  User Load (1.8ms)  SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- [e-mail]
')
 ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: character varying >> unknown
 LINE 1: ...RE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
                                                            ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 : SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
 - [e-mail]
')

此查询预期为：

SELECT "users".* FROM "users"  WHERE ("users"."roles" @> '{"3","4"}')

我在做什么错？

推荐答案

我遇到了与您相同的问题，我正在使用Rails 5，需要在 User 表中搜索<​​code>角色的数组

I met the same problem as you do. I'm using Rails 5, and I need to search an array of roles in User table

似乎您已经在自己的gemfile中添加了 postgres_ext gem ，但是如果在Rails 5应用程序中使用它，则会出现一些问题。

It seems that you have already add postgres_ext gem in your gemfile, but it has some problems if you are using it in Rails 5 application.

就这样是您自己在Arel Node中添加包含查询的一种选择，而不是使用 postgres_ext gem

So it is a choice for you to add a contain query in Arel Node by yourself instead of using postgres_ext gem

如果您使用的是其他版本的Rails，我认为它也很好用。

And if you are using other version of Rails, I think it works well too.

我有一个 User 模型，以及数组属性 roles 。我想做的是使用 ransack 搜索角色。

I have an User model, and an array attribute roles. What I want to do is to use ransack to search roles. It is the same condition like yours.

流袋无法搜索数组。
但是 PostgresSQL 可以像这样搜索数组：

ransack can't search array. But PostgresSQL can search array like this:

User.where（ roles @>？，'{admin}'）。to_sql）

它会生成如下所示的sql查询：

it produce the sql query like this:

SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND (roles @> '{admin}')

所以我要做的是在<$ c $中添加类似的包含查询c> Arel节点

您可以做到这种方式：

# app/config/initializers/arel.rb

require 'arel/nodes/binary'
require 'arel/predications'
require 'arel/visitors/postgresql'

module Arel
  class Nodes::ContainsArray < Arel::Nodes::Binary
    def operator
      :"@>"
    end
  end

  class Visitors::PostgreSQL
    private

    def visit_Arel_Nodes_ContainsArray(o, collector)
      infix_value o, collector, ' @> '
    end
  end

  module Predications
    def contains(other)
      Nodes::ContainsArray.new self, Nodes.build_quoted(other, self)
    end
  end
end

因为您可以自定义ransack谓词，
，因此添加包含 Ransack谓词，例如 ：

Because you can custom ransack predicate, so add contains Ransack predicate like this:

# app/config/initializers/ransack.rb

Ransack.configure do |config|
  config.add_predicate 'contains',
    arel_predicate: 'contains',
    formatter: proc { |v| "{#{v}}" },
    validator: proc { |v| v.present? },
    type: :string
end

完成！

现在，您可以搜索数组：

Now, you can search array:

User.ransack(roles_contains: 'admin')

SQL查询如下：

SELECT \"users\".* FROM \"users\" WHERE \"users\".\"deleted_at\" IS NULL AND (\"users\".\"roles\" @> '{[\"admin\"]}')

是的！

这篇关于如何通过ransack gem搜索数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！