I'm using ransack gem
for searching in rails application. I need to search an array of email_ids
in User
table.
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
In the rails console, if i do like this:
a = User.search(email_contains_array: ['priti@gmail.com'])
it produces the sql like this:
"SELECT \"users\".* FROM \"users\" WHERE \"users\".\"deleted_at\" IS NULL AND (\"users\".\"email\" >> '---\n- priti@gmail.com\n')"
and gives error like this:
User Load (1.8ms) SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND ("users"."email" >> '---
- priti@gmail.com
')
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" >> '---
- priti@gmail.com
')
Expected is this query:
SELECT "users".* FROM "users" WHERE ("users"."roles" @> '{"3","4"}')
What is wrong am I doing?
I met the same problem as you do. I'm using Rails 5, and I need to search an array of
roles
inUser
tableIt 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.So it is a choice for you to add a
contain
query in Arel Node by yourself instead of usingpostgres_ext gem
And if you are using other version of Rails, I think it works well too.
I have an
User
model, and an array attributeroles
. What I want to do is to useransack
to searchroles
. It is the same condition like yours.ransack
can't search array. ButPostgresSQL
can search array like this:User.where("roles @> ?", '{admin}').to_sql)
it produce the sql query like this:
So what I want to do is to add a similar contains query in
Arel Nodes
You can do it this way:
Because you can custom ransack predicate, so add
contains
Ransack predicate like this:Done!
Now, you can search array:
The SQL query will be like this:
Yeah!