database

How to add TINYINT MySQL type to Doctrine in Symfony 2.8

Hello! A few days ago I needed to define an entity with a TINYINT type column to hold a rating value between 1 and 5. I was doing this in a Symfony 2.8 project and to my surprise, Doctrine was unable to handle this type of data out of the box (it uses it for booleans but it has no TINYINT standalone type).

So I searched the internet for a solution and I found some but nothing complete.

The solution (TINYINT vs Doctrine):

First you need to define the new Doctrine type class like this:

<?php
namespace AppBundle\Types;


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


class TinyintType extends Type
{
    const TINYINT = 'tinyint';


    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return 'TINYINT';
    }


    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return $value;
    }


    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        return $value;
    }


    public function getName()
    {
        return self::TINYINT;
    }
}

Then, to register it, I used the boot function of the AppBundle:

<?php
namespace AppBundle;


use Symfony\Component\HttpKernel\Bundle\Bundle;
use Doctrine\DBAL\Types\Type;


class AppBundle extends Bundle
{
    public function boot()
    {
        parent::boot();
        
        $em = $this->container->get('doctrine.orm.default_entity_manager');
 
        // types registration
        if(!Type::hasType('tinyint')) {
            try {
                Type::addType('tinyint', 'AppBundle\Types\TinyintType');
                $em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping('TINYINT', 'tinyint');
            } catch (\Exception $e) {
                
            }
        }
    }
}

I had to use the try / catch block to avoid an error when running symfony console commands having no database defined:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000] [1049] Unknown database 'rating'' in vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43

Now you can use the new type in your entities:

    /**
     * @ORM\Column(name="rating", type="tinyint")
     * @Assert\NotBlank()
     * @Assert\GreaterThanOrEqual(
     *     value = 1
     * )
     * @Assert\LessThanOrEqual(
     *     value = 5
     * )
     */
    protected $rating;

Learn how to setup Docker machine for Symfony development.

Hope this helps someone :)
Have a great day!

by Dragos Holban