Issue with DECIMAL columns and MySQL about scoped_search HOT 7 CLOSED

----- Original Message -----

From: "SteveFenn" [email protected]
To: [email protected]
Sent: Monday, September 5, 2011 6:27:07 PM
Subject: [scoped_search] Issue with DECIMAL columns and MySQL (#12)
Hey

I have just started using the gem and it's great!

Whilst using it I have encountered a problem with scoped_search on
decimals. I've isolated my problem down to the simple test case below:

class Order < ActiveRecord::Base

scoped_search :on => :total

end
"total" is a DECIMAL column within the database, then running this
line within console:

Order.search_for("123.12")

Gives these results in the development log:

Order Load (65.0ms) SELECT * FROM orders WHERE (orders.total =
123)

It seems that at some point my search number is being converted into
an integer. I believe this should be querying for "orders.total =
123.12". As the moment, the query is, of course, effectively looking
for values of 123.00, and hence is not finding the order I'm looking
for.

I am using rails version 2.3.10, scoped_search version 2.3.1 and I am
using MySQL.

I apologise if this post doesn't make sense.

Thanks for the plugin,
Steve

Reply to this email directly or view it on GitHub:
#12

Thanks for reporting the problem.
I'll try to reproduce it and will get back to you as soon as I have results.
Amos.

from scoped_search.

----- Original Message -----

From: "SteveFenn" [email protected]
To: [email protected]
Sent: Monday, September 5, 2011 6:27:07 PM
Subject: [scoped_search] Issue with DECIMAL columns and MySQL (#12)
Hey

I have just started using the gem and it's great!

Whilst using it I have encountered a problem with scoped_search on
decimals. I've isolated my problem down to the simple test case below:

class Order < ActiveRecord::Base

scoped_search :on => :total

end
"total" is a DECIMAL column within the database, then running this
line within console:

Order.search_for("123.12")

Gives these results in the development log:

Order Load (65.0ms) SELECT * FROM orders WHERE (orders.total =
123)

It seems that at some point my search number is being converted into
an integer. I believe this should be querying for "orders.total =
123.12". As the moment, the query is, of course, effectively looking
for values of 123.00, and hence is not finding the order I'm looking
for.

I am using rails version 2.3.10, scoped_search version 2.3.1 and I am
using MySQL.

I apologise if this post doesn't make sense.

Thanks for the plugin,
Steve

Reply to this email directly or view it on GitHub:
#12

Hi Steve,
I have found the following suggestion in a blog:

...
Check the definition of the table in Mysql, chances are you have a decimal column with no decimal "scale" set.
By default, if you don't tell Mysql ho many decimal places to use it will have none, and ActiveRecord will then simply interpret that as an Integer.
Update you column with a migration like this:
change_column('table_name', 'colun_name', :decimal, { :scale => 2, :precision => 10 } )

See original post here:
http://n4k3d.com/blog/post/18/activerecord_magically_changing_decimal_columns_to_integers/

Please update me if this solves your issue.
Thanks,
Amos.

from scoped_search.

Hi Amos,

Thanks for getting back so quickly

I've taken a look, and the column is a decimal - it's got :precision => 8, :scale => 3 I should have put this in the original post.

The regular Rails finders seem to work fine:

$ Order.find_by_total(123.12)

This gives this result in the development log:

=> SELECT * FROM `orders` WHERE (`total` = 123.12) LIMIT 1

Checked order.total as well:

$ Order.first.total.class
=> BigDecimal

Thank you again for looking into this
Steve

from scoped_search.

----- Original Message -----

From: "SteveFenn" [email protected]
To: [email protected]
Sent: Tuesday, September 6, 2011 11:25:56 AM
Subject: Re: [scoped_search] Issue with DECIMAL columns and MySQL (#12)
Hi Amos,

Thanks for getting back so quickly

I've taken a look, and the column is a decimal - it's got :precision
=> 8, :scale => 3 I should have put this in the original post.

The regular Rails finders seem to work fine:

$ Order.find_by_total(123.12)

This gives this result in the development log:

=> SELECT * FROM orders WHERE (total = 123.12) LIMIT 1

Checked order.total as well:

$ Order.first.total.class
=> BigDecimal

Thank you again for looking into this
Steve

Reply to this email directly or view it on GitHub:
#12 (comment)

Hi Steve,
I have managed to reproduce the issue.
It is now fixed and I have added a relevant test.
Thanks again for reporting the issue.
The fix will be available shortly in the gem release.
Thanks,
Amos.

from scoped_search.

Hi Amos,

Thanks for looking into this and fixing it, Its a great plugin

When the gem release comes out I will test and confirm it fixes problem

Thanks,
Steve

from scoped_search.

fixed in release 2.3.3

from scoped_search.

Hey

This has fixed the original problem we were having,

Thanks again

from scoped_search.