Symfony2 MoneyType with divisor: integer conversion leads to wrong database values

Question

We're storing all our money related values as cents in our database (ODM but ORM will likely behave the same). We're using MoneyType to convert user facing values (12,34€) into their cents representation (1234c). The typical float precision problem arises here: due to insufficient precision there are many cases that create rounding errors that are merely visible when debugging. MoneyType will convert incoming strings to floats that may be not precise ("1765" => 1764.9999999998).

Things get bad as soon as you persist these values:

class Price {
    /**
     * @var int
     * @MongoDB\Field(type="int")
     **/
    protected $cents;
}

will transform the incoming values (which are float!) like:

namespace Doctrine\ODM\MongoDB\Types;
class IntType extends Type
{
    public function convertToDatabaseValue($value)
    {
        return $value !== null ? (integer) $value : null;
    }
}

The (integer) cast will strip off the value's mantissa instead of rounding the value, effectively leading to writing wrong values into the database (1764 instead of 1765 when "1765" is internally 1764.9999999998).

Here's an unit test that should display the issue from within any Symfony2 container:

//for better debugging: set ini_set('precision', 17); 

class PrecisionTest extends WebTestCase
{
    private function buildForm() {
        $builder = $this->getContainer()->get('form.factory')->createBuilder(FormType::class, null, []);
        $form = $builder->add('money', MoneyType::class, [
            'divisor' => 100
        ])->getForm();
        return $form;
    }

    // high-level symptom
    public function testMoneyType() {
        $form = $this->buildForm();
        $form->submit(['money' => '12,34']);

        $data = $form->getData();

        $this->assertEquals(1234, $data['money']);
        $this->assertEquals(1234, (int)$data['money']);

        $form = $this->buildForm();
        $form->submit(['money' => '17,65']);

        $data = $form->getData();

        $this->assertEquals(1765, $data['money']);
        $this->assertEquals(1765, (int)$data['money']); //fails: data[money] === 1764 
    }

    //root cause
    public function testParsedIntegerPrecision() {

        $string = "17,65";
        $transformer = new MoneyToLocalizedStringTransformer(2, false,null, 100);
        $value = $transformer->reverseTransform($string);

        $int = (integer) $value;
        $float = (float) $value;

        $this->assertEquals(1765, (float)$float);
        $this->assertEquals(1765, $int); //fails: $int === 1764
    }

}

Note, that this issue is not always visible! As you can see "12,34" is working well, "17,65" or "18,65" will fail.

What is the best way to work around here (in terms of Symfony Forms / Doctrine)? The NumberTransformer or MoneyType aren't supposed to return integer values - people might also want to save floats so we cannot solve the issue there. I thought about overriding the IntType in the persistence layer, effectively rounding every incoming integer value instead of casting. Another approach would be to store the field as float in MongoDB...

The basic PHP problem is discussed here.

Try to use `DecimalType` to store float values as `NUMERIC(precision, scale)`, this one comeback as string without affect the original precision. — yceruto, Dec 22 '16 at 22:54
yeah, I thought so. But I find it so weird to store obviously "float" values (1765.00) as "int". — Stefan, Dec 23 '16 at 08:35
@Yonel: https://www.google.com/search?q=dont%20store%20currency%20as%20float%20in%20database top hits: http://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale (2nd answer), https://www.noelherrick.com/blog/always-use-decimal-for-money , https://www.sitepoint.com/community/t/mysql-why-not-double-or-float-for-storing-money/195731/7 — Stefan, Dec 23 '16 at 18:52
`DecimalType` doesn't store values as float, but string, so there isn't DB float precision issues. Just use a right precision/scale to avoid data truncation. Unless you need to perform PHP math operation with this number, you don't need care about this by using Decimal DBAL type, IMHO. — yceruto, Dec 23 '16 at 20:06
Decimal has been introduced to mongo only very recently (3.4). As soon as doctrine odm catches up with this, I'd be fine I guess. Nevertheless I think that this is an issue that should be addressed by a framework component. http://stackoverflow.com/questions/11541939/mongodb-what-about-decimal-type-of-value — Stefan, Dec 23 '16 at 21:46

score 3 · Answer 1 · answered Dec 22 '16 at 16:02

For now I decided to go with my own MoneyType that calls "round" on integers internally.

<?php

namespace AcmeBundle\Form;

use Symfony\Component\Form\FormBuilderInterface;


class MoneyToLocalizedStringTransformer extends \Symfony\Component\Form\Extension\Core\DataTransformer\MoneyToLocalizedStringTransformer {

    public function reverseTransform($value)
    {
        return round(parent::reverseTransform($value));
    }
}

class MoneyType extends \Symfony\Component\Form\Extension\Core\Type\MoneyType
{
    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->addViewTransformer(new MoneyToLocalizedStringTransformer(
                $options['scale'],
                $options['grouping'],
                null,
                $options['divisor']
            ))
        ;
    }
}

score 0 · Answer 2 · answered Dec 23 '16 at 12:44

0

In my opinion this problem is more related to persistence layer and I would try to solve it by overriding ODM's int type:

AppBundle\Doctrine\Types\MyIntType:

use Doctrine\ODM\MongoDB\Types\IntType;

class MyIntType extends IntType
{
    public function convertToDatabaseValue($value)
    {
        return $value !== null ? round($value) : null;
    }
}

app/config/config.yml:

doctrine:
    dbal:
        types:
            int:  AppBundle\Doctrine\Types\MyIntType

answered Dec 23 '16 at 12:44

Andrii Mishchenko

2,626
21
19

I don't quite agree as this fixes the outcome of problem, not the cause. The entity says it's storing price in cents yet it ends with 12.97 whcih is not reflecting possible real life state as one can't have 0.97c. In my opinion the problem should be fixed in Symfony's transformer as @Stefan suggested in his answer – malarzm Dec 23 '16 at 17:33
@Andrey Mischenko: That was exactly my first approach (only in ODM) but then I thought about malarzm's argument. Your suggestion would indeed solve the problem, though. However, I don't like the idea that *every* incoming integer has to pass "round". – Stefan Dec 23 '16 at 18:45

Symfony2 MoneyType with divisor: integer conversion leads to wrong database values

2 Answers2