Skip to content

Symfony & Doctrine Mapping problems with MySQL Bigint columns, and how to fix this

In the process of upgrading a Symfony project from version 2 to version 5, I came across an issue with a MySQL table that has a number of BIGINT columns. There had been no issue previously, but after updating to the newer symfony (and doctrine components) mySQL bigint columns were empty/null in my doctrine entities.

After some investigation I came upon this section of the Doctrine manual:

For compatibility reasons this type is not converted to an integer as PHP can only represent big integer values as real integers on systems with a 64-bit architecture and would fall back to approximated float values otherwise which could lead to false assumptions in applications.


To protect symfony apps running on 32 bit systems, Doctrine maps Bigint columns to the string type, and this broke the entity definition I was using, even though my application will only be deployed on 64 bit systems. I think that for most people this is the norm.

There are a few different ways to get around this issue, including type casting from string to integer/integer to string in your getters and setters, but if you have a lot of bigint columns across tables, that probably isn't a great solution. In this article, I present the solution I implemented, which utilizes a Custom Doctrine type to override the built in Doctrine behavior.
There are 3 things that I needed to do to implement this:

  1. Create my own Custom DBAL type

  2. Configure the Doctrine service to use this type for bigint columns

  3. Update the Entity definition for the bigint columns with a type annotation so that Doctrine would treat them as bigints



Creating a Custom DBAL Type


Previously when you developed your symfony code you put it in a bundle, but the newer versions of Symfony did away with this standard in favor of using the "App" namespace. App maps to the top of your src directory, so beneath src, you will need to make /Doctrine and under that /Types folders in your project: src/Doctrine/Types

Inside this directory, you will add your custom Type class. You can name it whatever you like. I named the class BigIntAsIntType.

The important change in this class is the implementation of the convertToPHPValue method, which casts the value received from PDO, from a string to a php integer (so long as the value isn't null).


<?php
// src/Doctrine/Types/BigIntAsIntType.php

namespace App\Doctrine\Types;

use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;

/**
 * Type that maps a database BIGINT to a PHP int.
 */

class BigIntAsIntType extends Type
{
    /**
     * {@inheritdoc}
     */

    public function getName()
    {
        return Types::BIGINT;
    }

    /**
     * {@inheritdoc}
     */

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return $platform->getBigIntTypeDeclarationSQL($fieldDeclaration);
    }

    /**
     * {@inheritdoc}
     */

    public function getBindingType()
    {
        return ParameterType::STRING;
    }

    /**
     * {@inheritdoc}
     */

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return null === $value ? null : (int) $value;
    }
}
 




Configure the Doctrine service to use this type (for bigint columns)


This is another place where Symfony changed the directory structure and configuration standards, introducing a packages directory. You now configure doctrine connections in the config/packages/doctrine.yaml file.

Most projects will have at least one configured connection. To this configuration, add a new yaml key for the type mapping class to under the dbal key. Doctrine DBAL is the underlying abstraction layer that the ORM uses, but it also supports low level querying calls in situations where you either can't or don't want to use ORM entities.


doctrine
:
    dbal
:
        default_connection
: default
        connections
:
            default
:
               # configure these for your database server
                url
: '%env(resolve:DATABASE_URL)%'
                driver
: 'pdo_mysql'
                server_version
: '5.7'
                charset
: utf8mb4
        types
:
            bigint
: App\Doctrine\Types\BigIntAsIntType
 


You should notice the types: key, and beneath that, the configuration to override bigint type to the custom Doctrine Type "BigIntAsIntType"

Updating the Entity definition for the bigint columns with a type annotation so that Doctrine would treat them as bigints


Alas, just overriding the mapping was not enough to get Doctrine to hydrate the entity object bigint values again. I also had to update Entities with bigint attributes, with a type annotation that telling doctrine these are bigint columns.

I want to reiterate that this was not needed with the versions of Doctrine that were part of Symfony 2.x

The 5.x controller code to query the table and hydrate an entity looks something like this:


class SampleController extends BaseController {
    /**
     * Lists all Sample entities.
     *
     * @Route("/", methods={"GET","HEAD"}, name="sample")
     * @Template()
     */

    public function indexAction(Request $request)
    {

        $request = Request::createFromGlobals();

        $em = $this->getDoctrine()->getManager();

        $entities = $em->getRepository(Sample::class)->findBy([], ['id' => 'DESC']);

        return array(
            'entities' => $entities,
        );
    }
 


This should all be simple and obvious to anyone familiar with route annotations in controllers. The Template() annotation is a symfony addition that provides some magic to build an HTTP response from twig templates, using naming convention standards.

This is why you don't see the controller returning anything. It's beside the point of this article, but I wanted to make sure everything presented was explained. This works, because there is a pre-existing twig template (index.html.twig) in a directory with the same name as the controller class (templates/sample/).

In this application I have groups of entities that exist in different databases, so I have subdirectories for those inside the Entity directory. Here's part of the entity with the important change that's required:


<?php
namespace App\Entity\Web;

use App\Repository\Web\SiteRepository;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * @ORM\Table(name="sample")
 * @ORM\Entity
 */

class Sample
{
    /**
     * @var integer
     *
     * @ORM\Column(name="sampleid", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */

    private $id;

     /**
     * @var integer
     *
     * @ORM\Column(type="bigint")
     */

    private $exampleBigint;

    /**
     * Get exampleBigint
     * @return integer
     */

    public function getExampleBigint()
    {
        return $this->exampleBigint;
    }

    /**
     * Set exampleBigint
     * @param integer $exampleBigint
     *
     */

    public function setExampleBigint($exampleBigint)
    {
        $this->exampleBigint = $exampleBigint;
        return $this;
    }
}
 


The important change I needed to make was the addition of the ORM column annotation where previously it was not needed, given that the column name matched the name in the entity definition.

That annotation is: @ORM\Column(type="bigint")


I have additional controller, form and template code that allows the entry of rows, into these tables. Other than the required changes to symfony form classes, I did not need to make any other updates to the code, and the addition of new rows with bigint columns were successful.


Defined tags for this entry: , , , , , ,

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

Pavatar, Gravatar, Favatar, MyBlogLog, Pavatar author images supported.
BBCode format allowed
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
Form options