Eloquent casts decimal as string

Question

I have a decimal field to represent money in my Eloquent Model and I'm noticing erroneous results when trying to add some number to this field.

Upon further inspection, I found that the decimal field is being cast as a string, like "1245114.00" in the tinker console.

I checked the table structure and I can verify that the field is indeed decimal(11,3).

This question was asked before but has no answers.

Why is this happening?

Solution 1

You need to define in your model which fields need to be cast to a primitive attribute.

protected $casts = [
    'my_decimal' => 'float',
];

The $casts property on your model provides a convenient method of converting attributes to common data types. The $casts property should be an array where the key is the name of the attribute being cast and the value is the type you wish to cast the column to. The supported cast types are: integer, real, float, double, string, boolean, object, array, collection, date, datetime, and timestamp

There is a really good explanation here:

https://mattstauffer.com/blog/laravel-5.0-eloquent-attribute-casting/

Also there is an explanation in the docs:

https://laravel.com/docs/5.5/eloquent-mutators#attribute-casting

Solution 2

According to this thread on Laravel's github repository: https://github.com/laravel/framework/issues/11780

It seems to be a PDO driver issue. Automatically casting decimals to strings. People in that thread said that it was a problem with the mysql 5.2 driver and some rolled back to 5.1. If you're on your own server it you'll be able to downgrade. Otherwise you'll have to cast it to float on model level.

Solution 3

I just spent some time analyzing this issue because my Laravel model shows database decimal columns as strings.

The important thing is that adding typecasting to float in the model does fix it, but if you were like me, you noticed dd(\App\SomeModel::someScope()->get()); still shows those decimal columns as strings after adding typecasting in the model.

When these values are sent to the client, they are integers, so the typecasting did fix the original problem, thus allowing my JavaScript to receive Number not String.

I am just making this answer so a person doesn't waste time due to focusing on dd() output. I investigated solutions about the PDO driver, and while some perhaps have merit, it isn't a big deal because the real database outputs are cast to the correct type.

Solution 4

That's the way it should be since PHP has no decimal type. If you cast decimal to float you may loose precision.

The best option is leaving it as it is, as strings. Then use bcmath to operate, which is meant to be used with strings.

View more solutions

Comments

[Luis felipe De jesus Munoz]

mattstauffer.com/blog/laravel-5.0-eloquent-attribute-casting

[PULL STACK DEV]

column type in the database?

[Sapnesh Naik]

@AdnanMumtaz DECIMAL

[Sapnesh Naik]

forgive me if this is a stupid question. But isn't decimal a better way to handle money than float or double? If I Cast the decimal as float and do operations on it, won't that be defeating the purpose of using a DECIMAL field to store the amount?

[Luis felipe De jesus Munoz]

You can use the one that fit your need the most. I just used float as an example but when handling money is better to use float because it can handle more decimals than decimal and is more accurate

[Luis felipe De jesus Munoz]

@SapneshNaik Also, i forgot to say, decimal is a floating decimal point.

[common sense]

Another idea is to store it in as integer in cent and convert the value when you need to present it.

[Luis felipe De jesus Munoz]

@commonsense that would be a good idea if working only with small numbers. Remember int is usually a 32 bit representation of a number so the max number it can store is 2,147,483,647. So when storing some millions (for example) in cents, it will crash.

[Matt Wohler]

@SapneshNaik As someone who has built plenty of web apps/crm's with accounting functionality, it's always best to store MONEY in the form of an INT.

[alexw]

@LuisfelipeDejesusMunoz with SQL's BIGINT type, and systems in general moving towards 64-bit architectures, I don't think overflow is such a big issue anymore. 8-byte integers can represent values up to 9 quintillion, so unless you are working with Zimbabwe currency circa 2008 you should never overflow with the minor unit approach.