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