0

I am working on a PHP Project, utilizing Symfony 2, and Doctrine as ORM. When I persist and flush the object the primary key column is not even in the insert SQL DML Statement, and of course an error is thrown. The ID is a generated value, using PHP's uniqid. I will provide the Entity and a code snippet of the controller.

/**
* Metrics
*
* @ORM\Table(name="metrics", indexes={@ORM\Index(name="metrics_assignedTo_fk",     columns={"assignedTo"}), @ORM\Index(name="metrics_ui_fk", columns={"ui"})})
* @ORM\Entity
*/
class Metrics
{
/**
 * @var string
 *
 * @ORM\Column(name="baseline", type="string", length=15, nullable=true)
 */
private $baseline;

/**
 * @var string
 *
 * @ORM\Column(name="testEvent", type="string", length=40, nullable=true)
 */
private $testevent;

/**
 * @var string
 *
 * @ORM\Column(name="status", type="string", length=20, nullable=true)
 */
private $status;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="date", type="date", nullable=true)
 */
private $date;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="shotStart", type="datetime", nullable=true)
 */
private $shotstart;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="shotEnd", type="datetime", nullable=true)
 */
private $shotend;

/**
 * @var string
 *
 * @ORM\Column(name="c2p", type="string", length=10, nullable=true)
 */
private $c2p;

/**
 * @var string
 *
 * @ORM\Column(name="notes", type="string", length=200, nullable=true)
 */
private $notes;

/**
 * @var string
 *
 * @ORM\Column(name="uniqueid", type="string", length=100, nullable=false)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="NONE")
 */
private $uniqueid;

/**
 * @var \fti\webBundle\Entity\Employees
 *
 * @ORM\ManyToOne(targetEntity="fti\webBundle\Entity\Employees")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="ui", referencedColumnName="empid")
 * })
 */
private $ui;
}

I left out the getters and setters for clarity.

$shotObj->setShotstart(date_create($postData['start']));
                $shotObj->setShotend(date_create($postData['end']));
                $shotObj->setC2p($postData['c2p']);
                $shotObj->setUniqueid((uniqid('', true)));


            $shotForm = $this->createFormBuilder($shotObj)
                ->add('status', 'choice', array(
                    'choices' => array(
                        'changed' => 'Changed',
                        'canceled' => 'Canceled',
                        'added' => 'Added'
                    ),
                    'multiple' => false,
                    'expanded' => false,
                    'required' => false,
                    'placeholder' => '-'
                ))
                ->add('c2p', 'hidden')
                ->add('shotStart', 'datetime', array(
                    'attr' => array(
                        'style' => 'display: none'
                    )
                ))
                ->add('shotEnd', 'datetime', array(
                    'attr' => array(
                        'style' => 'display: none'
                    )
                ))
                ->add('uniqueid', 'hidden')
                ->add('baseline', 'choice', array(
                    'choices' => $this->buildBLChoices(),
                    'multiple' => false,
                    'expanded' => false,
                    'placeholder' => '-'
                ))
                ->add('testevent', 'choice', array(
                    'choices' => $this->buildTEChoices(),
                    'multiple' => false,
                    'expanded' => false,
                    'required' => false,
                    'placeholder' => '-'
                ))
                ->add('test', 'text', array(
                    'mapped' => false,
                    'required' => false
                ))
                ->add('assignedTo', 'entity', array(
                    'class' => 'ftiwebBundle:Employees',
                    'query_builder' => function(EntityRepository $er){
                        return $er->createQueryBuilder('e')
                            ->where('e.active = 1')
                            ->orderBy('e.lastname', 'ASC');
                    }
                ,
                    'multiple' => false,
                    'expanded' => false,
                    'placeholder' => '-',
                ))
                ->add('ui', 'entity', array(
                    'class' => 'ftiwebBundle:Employees',
                    'query_builder' => function(EntityRepository $er){
                        return $er->createQueryBuilder('e')
                            ->where('e.active = 1')
                            ->orderBy('e.lastname', 'ASC');
                    }
                ,
                    'multiple' => false,
                    'expanded' => false,
                    'placeholder' => '-',
                ))
                ->add('notes', 'textarea')
                ->add('submit', 'submit')
                ->add('SubmitAndAlert', 'submit')
                ->getForm();

    $shotForm->handleRequest($request);
    if($shotForm->isValid()) {
        $formData = $request->request->get('form');
        if ($formData['test']) {
            $shotObj->setTestevent($formData['test']);
        }
        $em->persist($shotObj);
        $em->flush();
    }

The error that returns is: An exception occurred while executing 'INSERT INTO metrics (baseline, testEvent, status, shotStart, shotEnd, c2p, notes, date, ui, assignedTo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["foo", "foo", null, "2015-03-03 00:30:00", "2015-03-03 02:30:00", "foo", "test", null, 18, 21]:

As you see, the primary key (uniqueId) is not in the columns to be inserted, which leads me to believe its mapping Any help would be greatly appreciated

Geoff Lentsch
  • 1,054
  • 11
  • 17
  • run app/console doctrine:schema:create --dump-sql and doctrine:schema:validate to verify that doctrine is seeing your mappings. It's possible that you may need to rebuild the schema. You have inconsistent spellings. Double check that setUniqueid() is correct. – Cerad Mar 03 '15 at 20:19
  • I have run the commands. To check that doctrine is viewing my mappings, I did change generatedvalue strategy, to something that does not exist, and an error was propegated. I have confirmed setUniquid() and getUniqueid() works. What incorrect spelling do I have? The entity classes were auto-generated by doctrine, as I was using an existing MySQL DB – Geoff Lentsch Mar 04 '15 at 01:48
  • I said inconsistent, not necessarily incorrect. Verify that $shotObj still has it's id after the form is posted. Maybe something is going on in the form. By the way, no need to pass the id as a hidden value and in fact it's a bit dangerous since users could change it. Also just try posting the entity without the form at all. If the id is set then please make a gist of your complete entity class (with getters and setters). I'll test it. – Cerad Mar 04 '15 at 11:51
  • https://gist.github.com/b171e17685cfa704ff06.git I did what you said, but to no avail. I also created another object, set the uniqueid, and persisted and flushed to the DB, I received the same results. – Geoff Lentsch Mar 04 '15 at 12:04

2 Answers2

0

You should play with the @ORM\GeneratedValue(strategy="NONE"), you can choose from: AUTO, SEQUENCE, TABLE, IDENTITY, UUID, CUSTOM and NONE.

I use AUTO - Tells Doctrine to pick the strategy that is preferred by the used database platform. The preferred strategies are IDENTITY for MySQL, SQLite and MsSQL and SEQUENCE for Oracle and PostgreSQL. This strategy provides full portability.

If you are using PHP uniqueid generation use the next anotation:

/**
 * @ORM\Column(type="guid")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="UUID")
 */
Alexandru Olaru
  • 6,842
  • 6
  • 27
  • 53
  • The primary key, uniqueid, is a value that is generated using php's uniqid. So it should not be a generated value, as I am supplying the id. Documentation states to use none or leave it off, if generating your own id. – Geoff Lentsch Mar 04 '15 at 09:16
  • I used, the annotation, still nothing. I posted the gist of the whole entity class, in the comment below my post. I have created a separate object, set the uniqueid, and i did echo getUniqueId() to ensure it is being stored in the uniqueid property, and persisted and flushed to DB, but still the same results. The uniqueid column is not on the insert DML statement. – Geoff Lentsch Mar 04 '15 at 12:29
  • Can you then remove the `@ORM\Id` option from anotations and test it, I think when you specify the 'Id' column you say to doctrine do not touch it let the db handle it. – Alexandru Olaru Mar 04 '15 at 13:54
  • Tested that as well. Still nothing. – Geoff Lentsch Mar 04 '15 at 13:55
0

Thanks for all the help guys. The answer ended up being, in the orm.xml files for doctrine, the generatedvalue would never change, I manually removed the generatedvalue, in the xml document. After this, the insert DML statement worked correctly, and I see the row in my DB. The orm.xml file is in the Bundle/Resources/config/doctrine directory.

Geoff Lentsch
  • 1,054
  • 11
  • 17