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!
Symfony2 Doctrine Migrations with Unique Indexes (Slugs)
This is something me and my colleagues encounter from time to time.
The Problem
One of the problems of adding unique indexes to existing data, like adding the sluggable Doctrine behaviour using the StofDoctrineExtensionsBundle, is that the generated migration will end up throwing an error:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'UNIQ_BDAFD8C8989D9B62'
Causes
Since the default values for the new MySQL column are not unique, adding the unique index is not possible - which is what the error above is telling us. So we will need to change the migration to also generate the unique values before adding the index.
Solution
In order to do so, we will have to split the generated migration into 2 different migrations, one for adding the new column, which could be a slug, and the other to add the unique index. After running the first migration, we need to execute the code that generates the unique values needed for the index. We can use the postUp method in a Doctrine migration to execute code after the “up” migration finished. We will also need to instantiate and boot the kernel in order to gain access to the Symfony framework and build our functionality like we would do in a controller:
<?php
namespace Application\Migrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20151021133750 extends AbstractMigration
{
protected static $class = 'AppKernel';
protected static $kernel;
/**
* Creates a Kernel.
*
* Available options:
*
* * environment
* * debug
*
* @param array $options An array of options
*
* @return HttpKernelInterface A HttpKernelInterface instance
*/
protected static function createKernel(array $options = array())
{
if (null === static::$class) {
static::$class = static::getKernelClass();
}
return new static::$class(
isset($options['environment']) ? $options['environment'] : 'test',
isset($options['debug']) ? $options['debug'] : true
);
}
/**
* Creates a Client.
*
* @param array $options An array of options to pass to the createKernel class
* @param array $server An array of server parameters
*
* @return Client A Client instance
*/
protected static function createClient(array $options = array(), array $server = array())
{
if (null !== static::$kernel) {
static::$kernel->shutdown();
}
static::$kernel = static::createKernel($options);
static::$kernel->boot();
$client = static::$kernel->getContainer()->get('test.client');
$client->setServerParameters($server);
return $client;
}
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE book ADD slug VARCHAR(128) DEFAULT ""');
//$this->addSql('CREATE UNIQUE INDEX UNIQ_CBE5A331989D9B62 ON book (slug)');
}
public function postUp(Schema $schema)
{
$this->client = self::createClient();
$this->em = $this->client->getKernel()->getContainer()->get('doctrine')->getEntityManager();
$books = $this->em->getRepository('AppBundle:Book')->findAll();
foreach($books as $book){
// need this so we force the generation of a new slug
$book->setSlug(null);
$this->em->persist($book);
}
$this->em->flush();
}
/**
* @param Schema $schema
*/
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
//$this->addSql('DROP INDEX UNIQ_CBE5A331989D9B62 ON book');
$this->addSql('ALTER TABLE book DROP slug');
}
}
For the second migration file we only add the code necessary to add/remove the unique indexes:
<?php
namespace Application\Migrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20151021141028 extends AbstractMigration
{
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE book CHANGE slug slug VARCHAR(128) NOT NULL');
$this->addSql('CREATE UNIQUE INDEX UNIQ_CBE5A331989D9B62 ON book (slug)');
}
/**
* @param Schema $schema
*/
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('DROP INDEX UNIQ_CBE5A331989D9B62 ON book');
$this->addSql('ALTER TABLE book CHANGE slug slug VARCHAR(128) DEFAULT \'\' COLLATE utf8_unicode_ci');
}
}
You can generate an empty migration file using the doctrine:migratios:generate command.
If you now run the doctrine:migrations:migrate command everything should be fine and the database should be populated with the unique values we needed in the first place.
Conclusion
Luckily I solved this issue before an important deadline. Let me know if you found any other way around it, or a quicker solution to this issue.
How to Store Latitude and Longitude in MySQL
As we know, a latitude value can be between -90 and +90 degrees, whereas a longitude value can be between -180 and +180 degrees. To get accuracy to within a meter, only six decimal places are needed, which is sufficient for most cases. But you can go even further by using eight places which will give you more than centimeter-level accuracy.Read more
Symfony2: Doctrine 2 Entity Listeners
When working on a little more complex application is inevitable that we'll get to the point when we need to trigger an action when something happens somewhere in our application. Quite often is the case that we need to bind our actions to an entity, like for example notify all subscribers to a blog post that the post they are following has changed.
Simple Symfony2.3 Pagination With Both Nativequery And Querybuilder (Doctrine)
Symfony2.3 pagination
I've allocated quite a reasonable amount of time to find a good bundle to achieve pagination AND use NativeQuery in the same time. I didn't. Maybe I should have searched more...Read more
