Doctrine2 + Symfony2 Sequence ID Table

Question

I'm trying to retrieve the id value from vtiger_crmentity_seq in order to update vtiger_crmentity.crmid becase it isn't auto_increment

So this is my entity vtiger_crmentity

/**
 * @var integer
 *
 * @ORM\Column(name="crmid", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="SEQUENCE")
 * @ORM\SequenceGenerator(sequenceName="vtiger_crmentity_seq", allocationSize=1, initialValue=1)
 */
private $crmid;

/**
 * Get crmid
 *
 * @return integer 
 */
public function getCrmid()
{
    return $this->crmid;
}

But when I try to submit my form, Symfony2 returns me this error.

Operation 'Doctrine\DBAL\Platforms\AbstractPlatform::getSequenceNextValSQL' is not supported by platform. 500 Internal Server Error - DBALException

I cannot change vtiger_crmentity.crmid to auto_increment so this idea is discarted, also I need to update vtiger_crmentity_seq.id to the latest value used obviously...

score 5 · Accepted Answer · answered Aug 07 '13 at 14:39

you can try with a custom generator, with custom generator you can solve any primary key generation problem.

this code calls some native query in sql, but you can do really everything in your custom generator.

<?php

namespace MY\Doctrine;

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Id\AbstractIdGenerator;
use Doctrine\ORM\Query;

class CustomGenerator extends AbstractIdGenerator
{
    static $cache = array();

    public function generate(EntityManager $em, $entity)
    {
        $id = $em->createNativeQuery(
                'SELECT crmid FROM vtiger_crmentity_seq WHERE used = 0 LIMIT 1',
                new Query\ResultSetMapping()
            )->getResult(Query::HYDRATE_SINGLE_SCALAR);
        $em->getConnection()->executeUpdate(
            'UPDATE vtiger_crmentity_seq SET used = 1 WHERE crmid = ?', 
             array($id)
        )

        return $id;
    }
}

and in your entity

...
class AbstractProduct
{
    /**
     * @var integer
     * @ORM\Id
     * @ORM\Column(name="ID", type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="CUSTOM")
     * @ORM\CustomIdGenerator(class="\My\CustomGenerator")
     */
    protected $id;
...

Is this ID setted on calling the constructor or when we persist/update the entity? — Vincent Guyard, Sep 27 '17 at 16:22

score 4 · Answer 2 · edited May 23 '17 at 12:00

4

Doctrine does not support using the SEQUENCE strategy for GeneratedValue when using MySQL. If you cannot set the column to auto_increment, you'll have to generate the IDs in your own code. See here.

edited May 23 '17 at 12:00

Community

1
1

answered Aug 07 '13 at 11:39

Ulrich Schmidt-Goertz

1,096
6
10

Thanks for your answer, also I'm looking the page you said but I don't see how can I generate the IDs in my own code... I think it'll be useful when TableGenerator is supported but now, I'm a bit lost :/ – Robert W. Hunter Aug 07 '13 at 11:45
As a workaround, you could set the strategy to `NONE` and generate your own IDs, similar to here: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/sharding.html#table-generator – Ulrich Schmidt-Goertz Aug 07 '13 at 12:10
But actually there's no method for do `nextValue` to anything, I mean, the only way I can handle all of this is what I'm doing right now: `$id = QueryTableSeq->getId` then when inserting do `QueryTableSet->DoSomeQueryForIdField($id+1)` and `MyData->setId($id)` – Robert W. Hunter Aug 07 '13 at 12:51
I've made a simple annotation that worked: `/** * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") * @ORM\SequenceGenerator(sequenceName="id", initialValue=100) * @ORM\Column(type="integer") */ protected $id;` – Andre Cardoso Feb 14 '18 at 00:09

Doctrine2 + Symfony2 Sequence ID Table

2 Answers2