Ransack Search Id and Strings Together

Question

I am trying to search using Ransack but having issue when i am trying to search id and string together like

:id_or_title_or_user_username_cont

It produces the error

ActionView::Template::Error (PG::UndefinedFunction: ERROR: operator does not exist: integer ~~* integer

I have also tried this

:id_eq_or_title_or_user_username_cont

it produces following error

undefined method `id_eq_or_title_or_user_username_cont' for Ransack::Search

What is the appropriate way of searching id and strings together using ransack other than custom predicates or with custom predicates ?

The possible solutions have been added to ransack wiki https://github.com/activerecord-hackery/ransack/wiki/using-ransackers#4-convert-an-integer-database-field-to-a-string-in-order-to-be-able-to-use-a-cont-predicate-instead-of-the-usual-eq-which-works-out-of-the-box-with-integers-to-find-all-records-where-an-integer-field-id-in-this-example-contains-an-input-string — Yohann, Sep 17 '21 at 20:10

score 2 · Answer 1 · answered Sep 20 '19 at 06:47

The following post Finding records by ID with Ransack provides an answer:


  # Cast the ID column to a string in PostgreSQL
  # when searching with Ransack.
  # Allows searches with _cont and _eq predicates.
  # From https://github.com/ernie/ransack/issues/224
  ransacker :id do
    Arel.sql("to_char(\"#{table_name}\".\"id\", '99999999')")
  end

The provided answer originally comes from this github issue: https://github.com/activerecord-hackery/ransack/issues/224

score 2 · Answer 2 · answered May 28 '21 at 14:29

I needed a bit more details than the other answers gave, so here it is.

A _cont search on an integer column doesn't work because LIKE queries wouldn't work on such columns. You need to cast the column from integer to string for your query to work. In your model, you can do that with a ransacker call:

# app/models/your_model.rb
class YourModel

  # For Postgres
  ransacker :id do
    Arel.sql("to_char(\"#{table_name}\".\"id\", '99999999')")
  end

  # For MySQL
  ransacker :id do
    Arel.sql("CONVERT(`#{table_name}`.`id`, CHAR(8))")
  end

You can then use a search predicate like id_cont.

score 0 · Answer 3 · answered Feb 08 '22 at 18:11

Small update as the answers above correct, but I found they had the side effect of causing normal results (e.g full list, no search) to be Ordered by the string value of ID, so - 1,10,11,12...etc...2,20,21...3,30 etc instead of 1,2,3,4...

So combining both ideas above, can search by ID but still sort by Int ID

In the Model create a stringified version of ID for ransack

ransacker :id_as_str do
  Arel.sql("to_char(\"#{self.table_name}\".\"id\", '99999999')")
end

And in the form

<%= f.search_field :id_as_str_or_some_other_stuff_cont, placeholder: "Search..." %>

score -2 · Accepted Answer · answered Jan 21 '16 at 11:27

-2

ransacker :id_to_s do 
  Arel.sql("regexp_replace(to_char(\"#{table_name}\".\"id\", '9999999'), ' ', '', 'g')")
end

answered Jan 21 '16 at 11:27

Ali Hassan Mirza

552
11
23

Thanks for your help. I am bit confused here how i am suppose to use it in search field ? Like the predicate in my question can be used as <%= f.search_field :id_eq_or_title_or_user_username_cont %> can you please elaborate it futher. It will be very appreciated. Thanks in advance – Fakhir Shad Jan 21 '16 at 11:38
So @FakhirShad if you are confused then you should not accept this as an answer. – Muhammad Zohaib Ehsan Aug 04 '16 at 09:37
Check out the date of my comment and the date when answer was accepted @zozo – Fakhir Shad Aug 04 '16 at 09:48

Ransack Search Id and Strings Together

4 Answers4