David David - 6 months ago 22
SQL Question

Doctrine: how to manually set an entity's MySQL timestamp

I want to update an entity but also preserve its existing timestamp. In other words, in most cases I want this MySQL timestamp to automatically do what it's designed to do, but in certain cases I want to cheat -- sneak in an update without changing this last-modification column. Doing this manually with SQL is a piece of cake, of course:

UPDATE events SET admin_notes = "foo", lastmod = lastmod WHERE /*...*/


But I can't figure how to make Doctrine do this.

The entity is called
Application\Entity\Event
and the column/annotation looks like:

/**
* @ORM\Column(name="lastmod",type="datetime")
* @var \DateTime
*/
protected $modified;


The underlying table definition, in relevant part:

lastmod timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP


So, I get a
Event
and attempt, e.g.,

$timestamp = $event->getModified();
echo "existing timestamp is: ".$timestamp->format('Y-m-d H:i:s')."\n";
//2016-06-07 10:13:15

$event
->setAdminNotes("this is an update at ".date('Y-m-d H:i:s'))
->setModified($timestamp);
$em->flush();


and this does not work. The timestamp in the in-memory entity object is "right," i.e., unchanged, but in the actual database it gets set to the current datetime as though it didn't hear what I said. Looking at the SQL log of course that's exactly the case. My
$event->setModified($timestamp)
does not make it into the generated SQL statement.

Yes, I realize I could change the table's data type to a MySQL datetime and handle it manually all over the application. Not an option right now.

For argument's sake, I tried getting the
Doctrine\DBAL\Connection
from the entity manager, and running the
executeUpdate($SQL)
myself, and discovered (from the SQL log) that if I do it before
flush()
, it is ignored. So right now, the only thing that works is very ugly: keep the old timestamp in a variable, do a
flush()
, then run another UPDATE to manually set it back to what it was before.

Any suggestions?

Answer

Doctrine see that you did not modify your $timestamp object, that is why it is not included in sql query. try to

$newTimestamp = clone $timestamp; 
$event->setModified($newTimestamp);