问题描述
我正在使用 ransack gem
进行Rails搜索。我需要在 User
表中搜索 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: ['[email protected]'])
它生成如下所示的sql:
it produces the sql like this:
"SELECT \"users\".* FROM \"users\" WHERE \"users\".\"deleted_at\" IS NULL AND (\"users\".\"email\" >> '---\n- [email protected]\n')"
并给出如下错误:
User Load (1.8ms) SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- [email protected]
')
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" >> '---
- [email protected]
')
此查询预期为:
SELECT "users".* FROM "users" WHERE ("users"."roles" @> '{"3","4"}')
我在做什么错?
推荐答案
我遇到了与您相同的问题,我正在使用Rails 5,需要在 User
表中搜索角色的数组
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搜索数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!