Daniel S. W. Daniel S. W. - 25 days ago 6
MySQL Question

How to automatically convert the mysql's SET to its integer when SELECTing using Doctrine2?

The mysql's SET data type is commonly used for representing options whereby they can be combined using bitwise operations.

eg.

SELECT set_column, set_column + 0 FROM table



  • The first would return a comma-separated list of options (eg. 'option1, option2')

  • The second would return 3 (1+2)



I'm using Doctrine2 and I'm only working with objects (not using partial objects or arrays). So my question is how can I retrieve the second format when querying the entity's repository?

If I map the column to be string it returns the first format. If mapped to integer php will convert to 0 (intval('options, ...')).

A dirty hack would be adding this to the mapped attribute:

@ORM\Column(name="set_column+0", type="integer")


... however it will cause problems when INSERTING or UPDATING.

Any ideas?

Answer

You need to create a custom type for Doctrine, as it doesn't provide built-in support for SET type. Luckly, it's quite simple:

  1. Define the type class:

    use Doctrine\DBAL\Platforms\AbstractPlatform;
    use Doctrine\DBAL\Types\Type;
    
    class MyCustomSetType extends Type
    {
        const TYPE = 'my_custom_set';
    
        private $possibleValues = array('option1', 'opton2', ..., 'optionN');
    
        public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
        {
            return sprintf("SET('%s')", implode(',', $this->possibleValues));
        }
    
        public function convertToPHPValue($values, AbstractPlatform $platform)
        {
            return explode(',', $values);
        }
    
        public function convertToDatabaseValue($values, AbstractPlatform $platform)
        {
            if (!is_array($values)) {
                throw new \InvalidArgumentException('...');
            }
    
            foreach ($values as $value) {
                if (!in_array($value, $this->possibleValues, true)) {
                    throw new \InvalidArgumentException('...');
                }
            }
    
    
            return implode(',', $values);
        }
    
        public function getName()
        {
            return self::TYPE;
        }
    }
    
  2. Register your newly created type:

    \Doctrine\DBAL\Types\Type::addType('my_custom_set', '\My\Type\MyCustomSet');
    
  3. Define a appropriate mapping for your entity:

    /**
     * @ORM\Column(type="my_custom_set")
     */
    protected $options = array();
    

If you want to find more about custom types read the documentation: Custom Mapping Types