Search columns of type "integer" on PostgreSQL

[dr-gonzo]

How can I search the ID column of a table in a postgresql database with this gem? There is nothing like "cast_to_text" present so the request on an integer column always fails because of the lower() function.

PG::UndefinedFunction: ERROR: function lower(integer) does not exist LINE 1: ...s"."id", "accounts"."name" FROM "accounts" WHERE (LOWER(acco... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. : SELECT accounts.id, "accounts"."id", "accounts"."name" FROM "accounts" WHERE (LOWER(accounts.id) ILIKE '10%') ORDER BY LOWER(accounts.id) ASC LIMIT 20

[dr-gonzo]

Ok I wrote an implementation of CAST(.. AS text). Find it here: https://github.com/dr-gonzo/rails-jquery-autocomplete/blob/master/lib/rails-jquery-autocomplete/orm/active_record.rb

If I can find the time I'll write some tests and file a pull request. Great gem btw, thanks!

[bigtunacan]

@dr-gonzo, did you test against master, or only the most recently released official gem?

It sounds like the issue you are mentioning here may have been fixed on master with this other pull request, #15 ?

[dr-gonzo]

Hi, I used master and just tested again to make sure. Besides I don't see how it should be possible to solve the problem of using the LOWER() function in an SQL statement on an integer type column through jQuery.

Both statements in the active_record.rb:
order || "LOWER(#{table_prefix}#{method}) ASC"
["LOWER(#{table_name}.#{method}) #{like_clause} ?", "#{(is_full_search ? '%' : '')}#{term.downcase}%"]

must necessarily fail on every integer type database column, mustn't it?

[bigtunacan]

@dr-gonzo It's been awhile since you added this pull request. I just wanted to send you a quick update. I haven't pulled this in yet as it doesn't work correctly as it is.

The def active_record_get_autocomplete_order(method, options, model=nil, cast_text) method model parameter is optional. Adding a required parameter after an optional parameter would break backwards compatibility.

The casting is being blanket applied, but does not work with all databases (for example it is incompatible with SQL Server); so some checks should be applied first.

I do plan to update it prior to the next release.

[dr-gonzo]

Hey, thanks for the update. I agree to your arguments and look forward to the upcomming release.

[daande]

Is there any update on this as I am currently experiencing this problem?