How do you use LIKE query for enum in rails?

user_8500089 0
Viewed 646

I have set enum task_status as:

class ConfirmedTask < ApplicationRecord
  enum task_status: {pending: 1, complete: 2}
end

Now i have to search all task with pending status with search params 'pend'. I am using pg_search for search but it doesnot work with enum so i want to query using LIKE query:

ConfirmedTask.where('task_status like ?', '%pend%')

But this gives me error

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR: operator does not exist: integer ~~ unknown) LINE 1: ...asks".* FROM "confirmed_tasks" WHERE (task_status like '%pen... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

any suggestions?

pg-search plpgsql ruby-on-rails ruby
2 Answers

If you want to use enum here, then u need to store your enum values as strings in database. Currently your column task_status has integer type. Therefore u can't search with LIKE operator. To resolve it make this change.

  enum task_status: {pending: 'pending', complete: 'complete'}

And make type of task_status column as string in db. But i'd suggest you to send full name of values from front-end if possible.

user_105291850

Enums are normally stored as integers in the database. The mapping to a label is done on application level. The database is not aware of the labels.

This means you'll have to figure out what labels match your criteria on application level. Then convert them into their integer values (which are used in the database). One way to achieve this would be to use grep in combination with a regex that matches your requirements.

# find the matching labels
labels = ConfirmedTask.task_statuses.keys.grep(/pend/i)
# convert them into their integer values
values = labels.map(&ConfirmedTask.task_statuses)
# create your query
ConfirmedTask.where('task_status IN (?)', values)

Somewhat easier would be to drop the label to value translation and let Rails figure this out. This can be done by passing the array of labels to a normal where call (with a non-SQL string argument).

# find the matching labels
labels = ConfirmedTask.task_statuses.keys.grep(/pend/i)
# pass labels to `where` and let Rails do the label -> integer conversion
ConfirmedTask.where(task_status: labels)

I'm not 100% sure if an array of strings is allowed as the where condition of an enum attribute. The documentation uses symbols. If the above does not work, map the strings into symbols with .map(&:to_sym).

user_39825620
Related