ActiveAdmin Filter on postgres Array field

Question

I added the following filter in ActiveAdmin.

filter :roles, as: :select, collection Model::ROLES, multiple: true

but when i choose the filter value to search the roles. it gives me following error

PG::InvalidTextRepresentation: ERROR:  malformed array literal: "teacher"LINE 1: ...ted" = $1 AND roles" IN('teacher
DETAIL:  Array value must start with "{" or dimension information.                                                             ^

Any idea ? How we can search/Filter ARRAY field using AA filters? I'm using Rails 4.2.4, ruby 2.2.2p95

Answer 1

I came up to a solution slightly different (and inspired by) this one over here: https://stackoverflow.com/a/45728004/1170086

Mine involves some changes (and prevent breaking contains operator in other cases). So, you're going to basically create two initializer files:

This one is for Arel, in order to support @> operator (array's contain operator in PG) for a given table column.

# config/initializers/arel.rb

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

The other file aims to create a new Ransack predicate but I also decided to support the :array type (that's not natively supported in Ransack in terms of predicates).

# config/initializers/ransack.rb

module Ransack
  module Nodes
    class Value < Node
      alias_method :original_cast, :cast

      def cast(type)
        return Array(value) if type == :array
        original_cast(type)
      end
    end
  end
end

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

And in other to use it. All you need to do is:

User.ransack(roles_contains_array: %i[admin manager])

Or as a filter in ActiveAdmin (which is my case):

ActiveAdmin.register User do
  # ...
  filter :roles_contains_array, as: :select, collection: User.roles_for_select
  # ...
end

I hope it works for you as it worked for me. ;)

Answer 2

You can set up a custom ransacker method to first collect the ids you want returned using a regular postgres search, and then return the results based on those ids:

class User < ApplicationRecord

  ransacker :roles,
    formatter: proc { |str|
      data = where("? = ANY (roles)", str).map(&:id)
      data.present? ? data : nil
    } do |parent|
      parent.table[:id]
    end

end

If your filter is a select drop-down, then this should work fine. If you have a free-form text box, then make sure to use the "in" predicate:

  filter :roles_in, as: :string

Answer 3

leandroico solutions works well.

But if you add the predicate with this formatter

formatter: proc { |v| "{#{v.join(', ')}}" }, (note the space after the comma)

Then you could use the multiple: true keyword in the filter input and filter by more than one value:

filter :roles_contains_array, as: :select, multiple: true, collection: User.roles_for_select

Answer 4

I used the answer from @leandroico to come up with the below wiki-type approach to doing this.

How to Create Custom SQL Searches for ActiveAdmin (using Arel and Ransack)

In ActiveAdmin, filters are declared in app/admin/model.rb like:

ActiveAdmin.register Model do
  filter 'column_name', label: 'column_name', as: :string
end

That will make a searchbox available on the front-end with options to choose between

contains
equals
starts with
ends with

You can even do something like...

filter 'column_name_contains', label: 'column_name', as: :string

...to only have a contains type search available on the front-end.

You can also (after defining some custom methods elsewhere) specify other, non-built-in search methods, like:

filter 'column_name_custom_contains', label: 'column_name', as: :string

---

The rest of this doc will be about how to define this custom search method, custom_contains

Within config/initializers/arel.rb, define the following:

  module Arel
    # this example of custom_contains will cast the SQL column as ::text and then do a wildcard-wrapped ILIKE

    class Nodes::CustomContains < Arel::Nodes::Binary
      def operator
        '::text ILIKE'.to_sym
      end
    end

    class Visitors::PostgreSQL
      private
      def visit_Arel_Nodes_CustomContains(o, collector)
        infix_value o, collector, '::text ILIKE '
      end
    end

    module Predications
      def custom_contains(column_value)
        column_value = self.relation.engine.column_types[self.name.to_s].type_cast_for_database(column_value)
        column_value = "%#{self.relation.engine.send(:sanitize_sql_like, column_value)}%"  # wrap escaped value with % wildcard
        column_value = Nodes.build_quoted(column_value, self)
        Nodes::CustomContains.new(self, column_value)
      end
    end
  end

  module ActiveRecord::QueryMethods
    def custom_contains(predicates)
      return none if predicates.length == 0
      predicates.map{ |column_name, column_value|
        column_value = table.engine.column_types[column_name.to_s].type_cast_for_database(column_value)
        column_value = "%#{table.engine.send(:sanitize_sql_like, column_value)}%"  # wrap escaped value with % wildcard
        column_value = Arel::Nodes.build_quoted(column_value)
        where Arel::Nodes::CustomContains.new(table[column_name], column_value)
      }.inject(:merge)
    end
  end

  module ActiveRecord::Querying
    delegate :custom_contains, :to => :all
  end

Within config/initializers/ransack.rb, define the following:

  Ransack.configure do |config|
    config.add_predicate(
      'custom_contains',
      arel_predicate: 'custom_contains',
      formatter: proc { |v| v.to_s },
      validator: proc { |v| v.present? },
      type: :string
    )
  end

---

The above has accomplished a couple of things:

1) You can use the custom_contains method that was delegate'd to all ActiveRecord models:

puts Model.custom_contains(column_name: 'search for me').to_sql

2) You can use Ransack to search against the Arel predicates that were defined:

puts Model.ransack(column_name_custom_contains: 'search for me').result.to_sql

However, in order to do the below in ActiveAdmin...

filter 'column_name_custom_contains', label: 'column_name', as: :string

...we must add a scope to Model so that there is a method, column_name_custom_contains, on Model

  scope_name = "#{column_name}_custom_contains".to_sym
  unless Model.methods.include?(scope_name)
    Model.scope(
      scope_name,
      ->(value) {
        Model.custom_contains({column_name.to_sym => value})
      }
    )
  end

---

Voila!