How to Search Array Through Ransack Gem

How to search array through ransack gem?

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

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.

So it is a choice for you to add a contain query in Arel Node by yourself instead of using postgres_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 attribute roles. What I want to do is to use ransack to search roles. It is the same condition like yours.

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

User.where("roles @> ?", '{admin}').to_sql)

it produce the sql query like this:

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

So what I want to do is to add a similar contains query in Arel Nodes

You can do it this way:

# 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

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

Done!

Now, you can search array:

User.ransack(roles_contains: 'admin')

The SQL query will be like this:

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

Yeah!

Search an array of values with Ransack

I ended up using a custom Ransacker for this case:

ransacker :rep_code_list do
  Arel.sql("array_to_string(rep_code_list, ',')")
end

This will turn the array into a string so that Ransack can search with the cont predicate. Not sure if this is the best way to do it but it worked for my case.

How does ransack search works on an array list?

If you want to search the entire list of users, you could do something like this:

@q = User.ransack(params[:q])
@users = @q.result.page(params[:page])

You want to call ransack on your model prior to adding pagination. Once you have the ransack result, you can add pagination. This section of the documentation shows you how to handle pagination with ransack. In its most basic form, this is how you can use ransack.

---

Also, it looks like you have some odd code in your example. Apologies if I'm misunderstanding what you're trying to do.

When you call emails.map{|a| User.where(email: a).first}, you're making a where query 3 times in this case and returning an array of 3 models. Ransack won't operate on that array.

I would change it to something like this in your case:

emails = ["abc@abc.com", "a@a.com", "b@b.com"]
@q = User.where(email: emails).ransack(params[:q])
@checked_in_users = @q.result.page(params[:page])

Just know that you'd be searching from an array of users with emails of "abc@abc.com", "a@a.com", and "b@b.com". If you search with your ransack form for "bob@example.com", you wouldn't get any search results. This may not be what you want.

Rails Ransack gem: search for multiple values with one condition

If you look over Ransack's Search Matchers you will see one with *_in - match any values in array, which is what you need if you want to search in arrays.

Because your event_ids can come in either as a string or an array and *_in requires and array, we have to make sure we always feed it an array.

[event_ids].flatten # returns an array all the time

The query below should works properly now.

ransack("job_name_cont" => job_name, "event_id_in" => [event_ids].flatten).result

Use ransack to find objects whose array attribute contain a specific string

I just answered this on another question, I will add the answer here too for future reference.

As discussed on this issue you need to add the gem postgres_ext to your project:

# Gemfile
gem 'postgres_ext'

And add this to an initializer:

# config/initializers/ransack.rb
Ransack.configure do |config|
  %w[
    contained_within
    contained_within_or_equals
    contains
    contains_or_equals
    overlap
  ].each do |p|
    config.add_predicate p, arel_predicate: p, wants_array: true
  end
end

After that you'll be able to use contains in a serialized array.

Ransack exact match with array values

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.

---

Related Topics