In Rails, Ransack search gives the error `PG::UndefinedFunction: ERROR: operator does not exist` when trying to filter by ID

Question

Im trying to filter the records by the ID. Here's my search form :

<%= search_form_for @search, url: dash_orders_path, id: 'searchForm' do |f| %>
    <div class="col-md-4 margin-top-15">
        <%= f.search_field :id_cont, class: 'form-control', placeholder: 'Order id' %>
    </div>
    <span class="button-sec pull-left width-wide center-text searchBtn">
        <%= f.submit "Search", class: "button-big blue" %>
        <%= f.submit "Clear", class: "button-big red", onclick: "$('#searchForm')[0].reset();" %>
    </span>
<% end %>

When trying to do the search, it gives an error like this :

PG::UndefinedFunction: ERROR:  operator does not exist: integer ~~* integer
LINE 1: ...CT  "orders".* FROM "orders" WHERE ("orders"."id" ILIKE 0)  ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT  "orders".* FROM "orders" WHERE ("orders"."id" ILIKE 0)  ORDER BY "orders"."updated_at" DESC LIMIT 2 OFFSET 0

What might be the issue?

Answer 1

I had a similar problem and found your post trying to find a solution. This is how I solved it:

The problem is PG trying to match an integer (it was a date in my case) with a string.

I changed the predicate to _eq and everything worked perfect.

With the :field_cont (contains) predicate, the SQL query made by Ransack in PG is a ILIKE and using :field_eq is IS (=).

I guess it's too late to help you but maybe someone else can benefit from this answer.

Good luck,