doctrine 2 persist entity and exclude null fields from insert query

Question

I am trying to add to all my doctrine generated entities two fields to handle dates (date_created and date_modified).

Please see the current yml for reference

`Project\PasswordRecovery:
type: entity
table: PasswordRecovery
lifecycleCallbacks:
  prePersist: [ prePersist ]
indexes:
    fk_PasswordRecovery_User_idx:
        columns:
            - user_id
id:
    id:
        type: integer
        generator:
            strategy: AUTO
fields:
    date_created:
        type: datetime
        nullable: false
        columnDefinition: 'TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP'
        options:
            default: CURRENT_TIMESTAMP
    date_modified:
        type: datetime
        nullable: false
        columnDefinition: 'TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
        options:
            default: CURRENT_TIMESTAMP
    token:
        type: text
        nullable: true
    user_id:
        type: integer
        nullable: true
manyToOne:
    users:
        targetEntity: User
        inversedBy: passwordRecoveries
        joinColumn:
            name: user_id
            referencedColumnName: id

My problem:

As you can see in the sql table CREATE info below MYSQL can properly take care of handling current timestamp when inserting or updating queries.

CREATE TABLE `PasswordRecovery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `token` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `fk_PasswordRecovery_User_idx` (`user_id`),
  CONSTRAINT `FK_41CD3A90A76ED395` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `PasswordRecovery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `token` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `fk_PasswordRecovery_User_idx` (`user_id`),
  CONSTRAINT `FK_41CD3A90A76ED395` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

However when persisting the entity above even if I forcibly set to null $date_created and $date_modified when persisting, the generated query will in any case contain these two fields. This results in overriding the sql DEFAULT and ON UPDATE values.

Firstly I forced these values to NULL to override CURRENT_TIMESTAMP default value which is imposed by datetime type in doctrine as default.

$entity->setDateCreated(null);
$entity->setDateModified(null);

Then after setting the remaining values I persist:

$em->persist($entity);
$em->flush();

By logging the query with doctrine's DebugStack I obtain the following:

Array
(
    [1] => Array
        (
            [sql] => "START TRANSACTION"
            [params] => 
            [types] => 
            [executionMS] => 0.000178098678589
        )

    [2] => Array
        (
            [sql] => INSERT INTO PasswordRecovery (date_created, date_modified, token, user_id) VALUES (?, ?, ?, ?)
            [params] => Array
                (
                    [1] => 
                    [2] => 
                    [3] => stackoverflow test insert
                    [4] => 14
                )

            [types] => Array
                (
                    [1] => datetime
                    [2] => datetime
                    [3] => text
                    [4] => integer
                )

            [executionMS] => 0.00274181365967
        )

    [3] => Array
        (
            [sql] => "COMMIT"
            [params] => 
            [types] => 
            [executionMS] => 0.000401973724365
        )

)

The sql result as INSERT statement from the db:

INSERT INTO `PasswordRecovery` (`id`, `user_id`, `date_created`, `date_modified`, `token`)
VALUES
    (21, 14, NULL, NULL, 'stackoverflow test insert');

The expected result:

INSERT INTO `PasswordRecovery` (`id`, `user_id`, `date_created`, `date_modified`, `token`)
VALUES
    (23, 14, '2017-02-23 08:28:23', '2017-02-23 08:28:23', 'stackoverflow test insert');

So far I haven't found a way to skip inserting a field based on some condition or the data. Setting 'nullabe' to false or true didn't seem to make any difference.

Using query builder is not an option as the EntityManager persitence scheme is deeply rooted in the system I'm working on.

Also using datetime in mysql is not an option as well for this specific project.

I have seen similar threads regarding dates management but in this case the problem regards only the fact that I'm unable to completely let MYSQL take care about generating and updating these two date fields as doctrine persist/flush will always overwrite these with the generated INSERT statement.

I am looking into lifecycle callbacks to see if these can help in not inserting some specific field based on a number of parameters.

The next step will be fiddling with the orm source.

I hope someone can help with the above !

Is there a reason you don't use prepersist doctrine callback to manage it? In my opinion putting some logic inside the database is not a good thing to do. — goto
I think you're making a good point here, my issue is that I'm working with generated entities, and I am migrating to a new api, I'm trying to solve this with the minimum effort in regards of doctrine. In that sense having mysql handling dates would be beneficial. — Davide
if you dont want the variables to have any data and let it be handled by mysql, why dont you just make it so that they are not required? — Giovanni Le Grand

Jeet Jeet · Accepted Answer · 2017-02-23T09:14:40

It's not a good idea to configure doctrine in order to skip inserting null values. There might be a situation where you need null values in database.

You should probably set default values in Entity i.e inside the constructor. When you provide default values. Doctrine will take those values automatically when not provided from setters.

For the current example: you should have something like :

public function __construct()
{
    $this->created_at = new \DateTime();
}

I have never faced any issues in this approach.

Hope this helps!

doctrine 2 persist entity and exclude null fields from insert query

1 Answers