Rails Ransack search PG hstore

Question

I'm using Ransack in a Rails4 app. I've recently added a PG hstore field to a table. In this app, the user can define the hstore field contents (key and value).

I found a way to use Ransack to search the hstore field if I know the key name. This is a sample - the key is color:

ransacker :color do |parent|
  Arel::Nodes::InfixOperation.new('->', parent.table[:data], 'color')
end

Then in the view, I use:

    <td><%= f.label :color_cont, "Color:" %></td>
    <td><%= f.text_field :color_cont %></td>

But, I won't know what the user is going to use for the key.

Has anyone figured out a way to Ransack search an hstore field without knowing the key?

Thanks for the help!

Answer 1

You might be able to do this with Arel::Nodes directly, but you can also use the ransackable_scopes method that was added in later versions of Ransack.

First create a method to return your hstore_col_name.

Then create a few scopes that use the postgres operators on the hstore column. The first scope can be used to find rows with a key_value_pair. The second scope can be used to find rows with a given key and any value, which might also be helpful.

scope :with_key_value_pair, ->(key,value){where("#{table_name}.#{hstore_col_name} @> '#{key}=>#{value}'")}
scope :with_key, ->(key){where("#{table_name}.#{hstore_col_name} ? :key", :key=>key)}

Then create a class method that will dynamically return an array of all the current unique keys being used in that hstore column:

def self.hstore_keys_to_a
  self.connection.execute("SELECT DISTINCT UNNEST(AKEYS(#{hstore_col_name})) from #{table_name}").map(&:values).flatten.uniq
end

Since I didn't find any examples of using ransack with two arguments on one scope, I created another class method to dynamically add scopes for ransack to call, matching each key currently being used in the hstore column. Maybe there is a more direct way to pass two arguments than by putting one argument into the scope name as I've done here:

def self.add_hstore_key_scopes do
  hstore_keys_to_a.each do |key|
    define_singleton_method "with_#{key}" do |val|
      with_key_value_pair(key,val)
    end
  end
end

def self.ransackable_scopes(auth_obj=nil)
  hstore_keys_to_a.map{|key| "with_#{key}"} << "with_key"
end

I appended the static 'with_key' scope to ransackable_scopes to allow the option of searching for rows with a given key and any value.

Then in the rails controller method for the search form, call Model.add_hstore_key_scopes before rendering. This will ensure any recently added keys have with_#{key} scopes added for them as well.

Now in the view you can render a partial for each current key being used:

<% Model.hstore_keys_to_a.each do |key| %>
  <%= f.label "with_#{key}" %>
  <%= f.text_field "with_#{key}" %>
<% end %>

and if you like, another field for searching for rows with a given key of any value:

<%= f.label "with_key" %>
<%= f.text_field "with_key" %>