How to force to use LIKE query on nonstring fields?

[biggless]

SELECT `orders`.* FROM `orders` WHERE `orders`.`deleted_at` IS NULL AND ((`orders`.`id` = 2059000 OR `orders`.`number` = 2059000 OR `orders`.`comment` LIKE '%2059000%'))

As you see only comment field is looked up with LIKE statement. But I wanna find orders with numbers: 20590001, 20590002, 20590003 and so on.

[mrkamel]

Hi, that's currently not supported out of the box. Moreover, to support it while staying database agnostic we need to add type cast statements to the generated sql, because e.g. postgres doesn't support it without explicit type casts. It would be possible to add it as a new feature via something like

search_scope ... do
  options :numbers, :cast => :string
end

The obvious downside of this would be: it can no longer be treated as an integer, such that

Order.search "number > 10"

will work on strings instead of integers and imo produces unexpected results. Moreover, there are performance issues, but if you're using wildcard LIKE queries (instead of fulltext indices), this should be negligible. I'll consider this feature to be added in the future, but can't give an ETA, but you're welcome to add a PR.

So, currently you can add an additional string column to your schema or change your search query to:

Order.search "number >= 20590001 and number <= 20590009

[biggless]

Great! Thanks a lot!

[westonganger]

+1 the cast to string option would be great

[westonganger]

@mrkamel I would be interested in helping out in implementing the :cast option. Any hints on where to start?

[westonganger]

Looks like this functionality can be achieved using Custom Operators

search_scope :search do
  attributes :number

  generator :like_string do |column_name, raw_value|
    pattern = quote("%#{raw_value}%")
    "#{column_name} LIKE #{pattern}"
  end
end

Book.search(number: {like_string: "123"})

[sudoremo]

Unfortunately, the example in https://github.com/mrkamel/search_cop/issues/15#issuecomment-1106702797 does not work for us, as we want a single search term that searches many columns, including numeric columns that we want to treat as strings and search using like.

I've tried it by giving a custom column name, but this results in an error:

attributes :my_string, :my_integer: 'convert(my_integer, CHAR)'

Is there another way we can achieve this?

[mrkamel]

hi @sudoremo ... i'm sorry, but https://github.com/mrkamel/search_cop/issues/15#issuecomment-72458655 is still the status quo then. I still consider this feature to be added in the future, but can't give an ETA, but you're welcome to add a PR. Reopening this.

[sudoremo]

@mrkamel Thank you, I'll attempt a PR if I find the time.