Ransack: _cont on Integer column issues unexpected LIKE query

[SomeOfThemAreKillers]

Hi,

Using Ruby 1.9.3p0, Rails 3.2.2, MySQL 5.5.20, and Ransack 0.6.0, I'm unable to use '_cont' on an Integer column, while the database natively supports '%like%' queries there.

For the incoming search parameter "members_payments_amount_cont"=>"3" the SQL issued by Ransack is "WHERE (payments.amount LIKE 0)", which is obviously an issue.

If I use "members_payments_amount_eq"=>"3" the SQL is correct as shown here: "WHERE payments.amount = 3"

I suspect this is related to string vs. integer data types and am wondering if the behavior is intended or a bug?

[radar]

This is most definitely a bug. Could you take a look at submitting a patch to fix this issue?

[ricardocanelas]

Example:
select * from books where convert(char(32), book_id) like '7%'

[shuargan]

I'm having the same issue and I have no idea how to fix it, any help on this please?

[ricardocanelas]

Eu também não sei.. =(

[shuargan]

quick workarround that works for me (i know is not the correct way...but it works)

module Arel
module AliasPredication
def literal_query query
Nodes::SqlLiteral.new("#{self.relation.name}.#{self.name} #{query}")
end
end
end

Ransack.configure do |config|
config.add_predicate 'literal_start', # Name your predicate
# What non-compound ARel predicate will it use? (eq, matches, etc)
:arel_predicate => 'literal_query',
# Format incoming values as you see fit. (Default: Don't do formatting)
:formatter => proc {|v| "like '#{v}%'"},
# Validate a value. An "invalid" value won't be used in a search.
# Below is default.
:validator => proc {|v| v.present?}
end

[jonatack]

My workaround for searching on an integer field with cont is to use a ransacker that converts the integer database field to a string, as follows:

<%= f.search_field :id_cont %>

In the model:

private

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

end

[diospi]

thanks for this advice, I have a mysql database and I changed it a little bit:

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

anybody knows what's the reason for this problem? Is it in arel or in ransack directly? And how would it be possible to avoid this workaround?

Thanks

[snoopdouglas]

I'm using Ransack 1.3.0 and still having this issue. Attempted to use custom predicates but to no avail - Ransack seems to still cast integers to 0

[jonatack]

Did you try the workarounds suggested above?

[jonatack]

Leaving this open as the original issue. For reference, similar issues include #442 and #475.

[jonatack]

(Copied from my reply in #442):

Another workaround, though it has been a couple years since I used it (because I now run Ransack searches on dedicated, indexed search string columns -- an order of magnitude faster), is to make sure the search query _begins with a string column_ (like "author_name_or_author_id_or_author_of_user_type_company_name_cont") and not with an integer one.

If all your query attributes are integer columns, then add a string column at the start that will never match the search and it should work.

[jakeonrails]

I don't have a trivial test case to share, but I did just discover that having an integer column in the scope you call #search on will cause this error to occur.

Company.where(id: nil).search(params).result.to_a # raise the postgres error
Company.search(params).result.to_a # no error raised

Digging into the source of Ransack now to see if there's a way to fix this at the source.

Not sure what you mean by adding a string column at the start that will never match - could you share a super simple example?

In my case I have a complex activerecord scope to filter by id and joins and other stuff, and then I'm searching a string field using ransack - a phone number field. It is the only query passed through Ransack. And like I've shown above - simply having a integer not null constraint somewhere in the scope is enough to cause the issue.

[jakeonrails]

After further investigation, I suspect this is either:

1. a misuse of the way Arel should be used in ActiveRecord relations
2. a bug in Arel or ActiveRecord

In Context#evaluate, there is this line of code:

relation = @object.where(viz.accept(search.base))

In the simple test case I've created, viz.accept(search.base) returns an Arel::Node::Matches where the left and right are correctly set to some_column_name and "%string pattern%".

When we now call @object.where(arel_node_matches).to_sql, it converts the "%string pattern%" to 0, which is incorrect.

So either Arel::Node::Matches was never meant to be passed into ActiveRecord::Relation#where, or there is a bug in the way ActiveRecord/Arel build out the query.

From what I understand, ActiveRecord::Relation#where should be able to handle anything that responds to #to_sql, which Arel::Node::Matches does, but it seems that something is still falling down, and I don't know what yet.

[seanfcarroll]

Closed as related PR is merged.