Filter on :string not working on Active Admin (Rails 3.2/postgresql)

Question

I am using Active Admin on my Daily Deal app.

I have run across a very specific issue: when I launch a search on the filter sidebar on a field that was defined as filter :as =>:string, I get this error:

enter image description here

ActiveRecord::StatementInvalid at /admin/users
PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...ECT COUNT(*) FROM "users"  WHERE ("users"."email" >> 'math')
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(*) FROM "users"  WHERE ("users"."email" >> 'math')

The strange thing is that if i select, instead of 'contain' equals or 'starts with' or 'ends with', it works perfectly. I only have problem with 'contains'.

It's like postgresql does not undertand the command Active Admin filter on string with 'contain' ask him to do.

I tried with many of my models and it's happening in all my models.

Here is the Active Admin page:

ActiveAdmin.register People do
  filter :name, :as => :string
end

Here is my gemfile

gem 'activeadmin',            github: 'gregbell/active_admin' # added github because of 
gem 'pg'

Here is the complete thing I get in local:

Started GET "/admin/users?utf8=%E2%9C%93&q%5Bemail_contains%5D=math&commit=Filter&order=id_desc" for 127.0.0.1 at 2014-03-01 17:45:42 +0100
Processing by Admin::UsersController#index as HTML
  Parameters: {"utf8"=>"✓", "q"=>{"email_contains"=>"math"}, "commit"=>"Filter", "order"=>"id_desc"}
  AdminUser Load (1.4ms)  SELECT "admin_users".* FROM "admin_users" WHERE "admin_users"."id" = 1 ORDER BY "admin_users"."id" ASC LIMIT 1
   (2.3ms)  SELECT COUNT(*) FROM "users" WHERE ("users"."email" >> 'math')
**PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...ECT COUNT(*) FROM "users"  WHERE ("users"."email" >> 'math')
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.**
: SELECT COUNT(*) FROM "users"  WHERE ("users"."email" >> 'math')
  Rendered /home/mat/.rvm/gems/ruby-1.9.3-p392@rails3tutorial2ndEd/bundler/gems/active_admin-66de2f86ae1d/app/views/active_admin/resource/index.html.arb (32.7ms)
Completed 500 Internal Server Error in 47ms

ActiveRecord::StatementInvalid - PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...ECT COUNT(*) FROM "users"  WHERE ("users"."email" >> 'math')
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(*) FROM "users"  WHERE ("users"."email" >> 'math'):
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract_adapter.rb:440:in `rescue in log'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract_adapter.rb:430:in `log'
  activerecord (4.0.3) lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
  activerecord (4.0.3) lib/active_record/connection_adapters/postgresql_adapter.rb:891:in `select'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `block in select_all'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/query_cache.rb:76:in `cache_sql'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `select_all'
  activerecord (4.0.3) lib/active_record/relation/calculations.rb:262:in `execute_simple_calculation'
  activerecord (4.0.3) lib/active_record/relation/calculations.rb:224:in `perform_calculation'
  activerecord (4.0.3) lib/active_record/relation/calculations.rb:108:in `calculate'
  activerecord-deprecated_finders (1.0.3) lib/active_record/deprecated_finders/relation.rb:84:in `calculate'
  activerecord (4.0.3) lib/active_record/relation/calculations.rb:24:in `count'
   () home/mmat/.rvm/gems/ruby-1.9.3-p392@rails3tutorial2ndEd/bundler/gems/active_admin-66de2f86ae1d/lib/active_admin/helpers/collection.rb:11:in `collection_size'
   () home/mmat/.rvm/gems/ruby-1.9.3-p392@rails3tutorial2ndEd/bundler/gems/active_admin-66de2f86ae1d/lib/active_admin/views/components/scopes.rb:62:in `get_scope_count'
   () home/mmat/.rvm/gems/ruby-1.9.3-p392@rails3tutorial2ndEd/bundler/gems/active_admin-66de2f86ae1d/lib/active_admin/views/components/scopes.rb:40:in `block (3 levels) in build_scope'
  arbre (1.0.1) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.1) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.1) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.1) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.1) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.1) lib/arbre/element/builder_methods.rb:18:in `span'
   () home/mmat/.rvm/gems/ruby-1.9.3-p392@rails3tutorial2ndEd/bundler/gems/active_admin-66de2f86ae1d/lib/active_admin/views/components/scopes.rb:39:in `block (2 levels) in build_scope'
  arbre (1.0.1) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'


Started POST "/__better_errors/70228353699700/variables" for 127.0.0.1 at 2014-03-01 17:45:43 +0100
  User Load (0.7ms)  SELECT "users".* FROM "users" WHERE ("users"."email" >> 'math') ORDER BY "users"."id" desc
PG::UndefinedFunction: ERROR:  operator does not exist: character varying >> unknown
LINE 1: ...CT "users".* FROM "users"  WHERE ("users"."email" >> 'math')...
                                                             ^
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"."email" >> 'math')  ORDER BY "users"."id" desc

Edit

I don't know if it's related to the problem or if it can help but as Active Admin use Ransack, I think it might be useful what's written in my Ransack Initializer

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

Please let me know which version you to use of activeadmin? I think try to this code filter :name, :as :string. I hope this is help full to you. — Abid Hussain
i don't understand: i already have defined filter :name, :as => :string. I think it's the same in ruby with :name, :as :string ? i'll try either way but i'm not sure it will be working — Mathieu
To answser your question: In my gemfile i use the latest AA branch. gem'activeadmin, github:'gregbell/active_admin' — Mathieu

Luke Persola Luke Persola · Accepted Answer · 2014-03-11T04:57:06

It's like postgresql does not undertand the command Active Admin filter on string with 'contain' ask him to do.

Right, and check out these lines from the error:

PG::UndefinedFunction: ERROR: operator does not exist: character varying >> unknown LINE 1: ...ECT COUNT(*) FROM "users" WHERE ("users"."email" >> 'math')

Postgres is being given the operator >> to use between the email column in the users table and the search string 'math'. Which operators can be used depends on the data type(s) of the column(s) being queried, which you can investigate in the database CLI. Enter rails dbconsole on the command line and check columns' data types, for which I believe you can use:

select * from INFORMATION_SCHEMA.COLUMNS.

Emails are often stored as strings. If that's the case here, the use of >> doesn't make sense because it doesn't appear in the Postgres documentation's list of operators for strings. That in turn would suggest that ActiveAdmin or one of it dependencies is misinterpreting your contains.

By the way, it looks like some of the attributes you're filtering using :as =>:string, like Person.name, are probably ruby Strings on the level you deal with them. Active Admin's documentation seems to suggest those attributes' filter input elements would default to text fields; is that the case? If you haven't yet, it's worth a try removing them, though presumably it shouldn't matter.

Filter on :string not working on Active Admin (Rails 3.2/postgresql)

2 Answers