Filtering enum for blank or present causes InvalidTextRepresentation error

[dgobaud]

My User model has a churn_reason enum. The field type in the database is integer. In Rails Admin filtering it as present or blank causes an error

Present
PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "" LINE 1: ...hurn_reason IS NOT NULL AND users.churn_reason != '')) ORDE... ^ : SELECT "users".* FROM "users" WHERE "users"."deleted" = $1 AND ((users.churn_reason IS NOT NULL AND users.churn_reason != '')) ORDER BY users.id desc LIMIT 20 OFFSET 0

Blank
PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "" LINE 1: ...sers.churn_reason IS NULL OR users.churn_reason = '')) ORDE... ^ : SELECT "users".* FROM "users" WHERE "users"."deleted" = $1 AND ((users.churn_reason IS NULL OR users.churn_reason = '')) ORDER BY users.id desc LIMIT 20 OFFSET 0

[kevinjcoleman]

@dgobaud I'm getting the same thing as well while searching on an association, did you ever figure it out?

[dgobaud]

No 😭

Sent from iPhone

On May 28, 2016, at 6:02 PM, Kevin Coleman [email protected] wrote:

@dgobaud I'm getting the same thing as well while I'm actually searching on an association, did you ever figure it out?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[kevinjcoleman]

@dgobaud, cool, I just worked around it by adding a scope when I was looking for null associations and that seems to work.

[jandillmann]

@kevinjcoleman can you maybe post a code sample on how you achieved this? I'm running into the same error…

[kevinjcoleman]

@jandillmann so I have the following scopes related a null relationship in a model.

scope :without_sid_category, -> { where(sid_category_id: nil) }

Then in /config/initializers/rails_admin.rb in the config for that model, I have a list block that includes the scopes I want.

  config.model 'Business' do
    ...
    list do 
      scopes [nil, :without_sid_category, :geocoded, :ungeocoded]
    end
  end

Does that makes sense? You won't be able to use filtering in the same way and will have to click the scope first, but it works and will look like the picture below.

[jandillmann]

Thanks a lot @kevinjcoleman – but I don't think this solution will work for me. I need to filter by the associated object, so the list of scopes would need to be dynamic…

[kevinjcoleman]

@jandillmann you should be able to filter for existing objects with something like this in your rails initializer. It just won't work for null relationships, so you'd use the scope for the null relationships.

     field :sid_category_id, :enum do
        label {"Business Category"}
        visible false
        enum do
          SidCategory.pluck(:label, :id)
        end
        searchable :sid_category_id
      end

[jandillmann]

Yes, I did create these enum fields for filtering by the association and they work. But rails_admin automatically adds filter options for "is present" and "is blank", which I don't need and in this case crash the application. These are hardcoded to something like id IS NOT NULL AND id != '' and fail on PostgreSQL because comparing an empty string to an integer column is not allowed.

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: ""
LINE 1: ...nt.post_id IS NOT NULL AND comment.post_id != ''))  ORDE...

[kevinjcoleman]

@jandillmann yup I noticed that too, so blank and present you'd need to use the scope that I mentioned above.

[serge1peshcoff]

I am facing this issue, but not only for the present or blank, but for the rest of them.

I have an Event model, and status enum inside it, like that:

class Event < ActiveRecord::Base
  enum status: [:unconfirmed, :confirmed, :locked, :for_sale, :paid]
end

(the status field is integer).

And then I am filtering this model and trying to select, for example all Events that are unconfirmed, and getting this error:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "unconfirmed"
LINE 1: ...events".* FROM "events" WHERE ((events.status IN ('unconfirm...
                                                             ^
: SELECT  "events".* FROM "events" WHERE ((events.status IN ('unconfirmed')))  ORDER BY events.id desc LIMIT 20 OFFSET 0

I guess it is the same, rails_admin is representing this enum value as string and tries to search by it.

[szymon33]

+1

[mryurii]

So for those who googled on this issue too.

Used ActiveRecord::Enum for example above:

class Event < ActiveRecord::Base
  enum status: [:unconfirmed, :confirmed, :locked, :for_sale, :paid]
end

One of workarounds is adding status_enum method for Event:

class Event < ActiveRecord::Base
  enum status: [:unconfirmed, :confirmed, :locked, :for_sale, :paid]

  def status_enum
    self.class.statuses
  end
end

It will allow filtering on enum values, but filtering still will fail on 'Is present', 'Is blank' options

Checked at:

rails_admin (1.2.0)
rails (5.1.4)

Error provoked by:
https://github.com/sferik/rails_admin/blob/master/lib/rails_admin/adapters/active_record.rb#L172-L173

[jszwedko]

Also having this issue when attempting to filter for "is present" belongs_to relationships.

EDIT: Actually appears to be fixed by 0294c6a

[hamdi777]

this is the solution i came up with for the problem with not being able to process a string in filtering with rails_admin that's an example for an enum attr :color
so when declaring an enum attr declare it this way
enumerize :color, in: {
"#{0}": 0,
"#{1}": 1,
"#{2}": 2,
"#{3}": 3,
"#{4}": 4,
"#{5}": 5,
"#{6}": 6,
"#{7}": 7
}, i18n_scope: "views.enumerize.car.color", default: 0

in the yml i18n file make it this way
color:
"0": Black
"1": Grey
"2": Red
"3": Pink
"4": White
"5": Blue
"6": Orange
"7": Yellow

it works great
the problem persists with blank i'm trying to find a solution for it

[rgarifullin]

If your enums are integers you can override unary_operators method in your config/initializers/rails_admin.rb

require 'rails_admin/adapters/active_record'

module RailsAdmin
  module Adapters
    module ActiveRecord
      class StatementBuilder < RailsAdmin::AbstractModel::StatementBuilder
        protected

        def unary_operators
          case @type
          when :boolean
            boolean_unary_operators
          when :integer, :decimal, :float, :enum
            numeric_unary_operators
          else
            generic_unary_operators
          end
        end
      end
    end
  end
end

[GriwMF]

Nevermind

@rgarifullin nice! In my case, filtering by "is present" / "is blank" wasn't working for enums(without any errors). I had to use your solution as well as adding:

require 'rails_admin/config/fields/types/active_record_enum'
module RailsAdmin
 module Config
    module Fields
      module Types
        ActiveRecordEnum.class_eval do
          def parse_value(value)
            return unless value.present?
            return value if %w[_present _blank].include?(value) # this is the added line

            abstract_model.model.attribute_types[name.to_s].serialize(value)
          end
        end
      end
    end
  end
end

In my case, filtering by "is present" / "is blank" doesn't work for enums (without any errors, just doesn't apply filter to a table). The simplest solution I found is adding to initialiser

require 'rails_admin/config/fields/types/active_record_enum'

module RailsAdmin
  module Config
    module Fields
      module Types
        ActiveRecordEnum.class_eval do
          def parse_value(value)
            return unless value.present?
            return '_null' if value == '_blank'
            return '_not_null' if value == '_present'

            abstract_model.model.attribute_types[name.to_s].serialize(value)
          end
        end
      end
    end
  end
end