CODEWITHSUNDEEP
ruby-on-railsrubyransackpredicates
Fakhir Shad
Fakhir Shad

Reputation: 1101

Ransack Search Id and Strings Together

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 ?

Upvotes: 2

Views: 3719

Answers (5)

Ula
Ula

Reputation: 2748

If you are trying it with more recent versions then according to the documentation you can also use this for PostgreSQL:

# in the model:

QUERY_SUBJECT = 'id_or_email_cont'

ransacker :id do
  Arel.sql("to_char(id, '9999999')")
end

Upvotes: 0

aqwan
aqwan

Reputation: 500

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..." %>

Upvotes: 0

jibai31
jibai31

Reputation: 1885

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.

Upvotes: 3

fkoessler
fkoessler

Reputation: 7257

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

Upvotes: 2

Ali Hassan Mirza
Ali Hassan Mirza

Reputation: 582

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

Upvotes: -2

Related Questions