Doctrine Custom ID with auto increment

Question

I want to define some id with prefix.

For example, for one order entity its : "OR17000001" In this example, the prefix is "OR17"

So i have declare my id entities like this :

/**
 * @var string
 *
 * @ORM\Column(name="id", type="string", length=8)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="CUSTOM")
 * @ORM\CustomIdGenerator(class="My\Bundle\Generator\OrderCodeGenerator")
 */
private $id;

And my Generator is :

<?php

namespace My\Bundle\Generator;

use Doctrine\ORM\Id\AbstractIdGenerator;
use Doctrine\ORM\EntityManager;    
use My\Bundle\Entity\Order;

class OrderCodeGenerator extends AbstractIdGenerator
{
    /**
      * Format :
     * $prefix - string
     * $year - take 2 first letters (17)
     * $increment - Take the last code + 1
     *
     * @param EntityManager $em
     * @param \Doctrine\ORM\Mapping\Entity $entity
     * @return bool|string
     */
    public function generate(EntityManager $em, $entity)
    {
        if ($entity instanceof Order) {
            $now = new \DateTime();
            $year = $now->format('y');
            $prefix = 'OR';

            $maxCode = $em->getRepository('MyRepo:Order')->findMaxCode($year, $prefix);

            if ($maxCode) {
                $increment = substr($maxCode[1], -4);
                $increment = (int)$increment + 1;
            } else
                $increment = 0;

            $code = $prefix . $year . sprintf('%04d', $increment);
            return $code;
        }

        return false;
    }
}

Without forget the method findMaxCode :

public function findMaxCode($year, $prefix)
{
    $qb = $this->createQueryBuilder('entity');

    $qb->where($qb->expr()->like('entity.id', ':code'))
        ->setParameter('code', '%' . $prefix . $year . '%');

    $qb->select($qb->expr()->max('entity.id'));

    return $qb->getQuery()->getOneOrNullResult();
}

That's work fine =)

My problem is when i try to add some entities in same time. My case is :

Order entity with some items (its a form collection)
Item entity

So i need to custom id of Items Order with this strategy. And the problem is for found the max code. I have this error :

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicata du champ 'IT170000001' pour la clef 'PRIMARY'

The generator can't found the max code for generate the second item, because there is no flush.

How can i save the increment value between 2 id generation before the flush ??

Solution :

I keep numeric id for my Item. Its useful for my Order entity, its more readable than an simple int. But i don't care for Item.

Thx to Honza Rydrych

score 2 · Accepted Answer · answered Feb 04 '17 at 19:56

2

Querying DB for last inserted ID and then inserting "+one" isn't reliable solution. My solution for this case would be let doctrine generate ID's by the standard way and add the prefix "OR/Year/" when you need to present the data.

(Optionaly you can write custom Twig extension for presenting the ID http://symfony.com/doc/current/templating/twig_extension.html)

answered Feb 04 '17 at 19:56

Jan Rydrych

2,188
2
13
18

Thx for your answer, what is it the limit of a numeric id ?? i'm scared about that, i can have ~ 10 000 items / year – Quovandius Feb 04 '17 at 20:59
I don't know which RDBM server do you use, but as I know in Doctrine/MySQL uses int(11) as standard ID field type (max value is 2147483647 signed), so you have more than 200-thousands years :-) – Jan Rydrych Feb 04 '17 at 21:09
Ok thx for your tips. I haven't write it, i use Mysql too. I continue to search in wich case i can use custom strategy id, i have no idea ^^ – Quovandius Feb 04 '17 at 21:22
The other possible way would be to generate some kind of unique id (e.g. PHP uniqid()), but the the ID will be waaay less human readable. Another suggestion of solution is here http://stackoverflow.com/questions/15039665/generate-unique-id-doctrine-symfony2, but it's not 100% collision-proof to. – Jan Rydrych Feb 04 '17 at 21:24
No i didn't like this method ^^ i search some elegant method for sort this. Auto increment id is very useful for most of case. I think for my case too. Thx for your help, i update my first post ;) – Quovandius Feb 04 '17 at 21:42

Doctrine Custom ID with auto increment

1 Answers1