Doctrine : Do not insert all fields, only those which aren't NULL

Question

I have an Entity.

My\Bundle\Entity\Service:
    type: entity
    table: SERVICE
    fields:
        idService:
            id: true
            type: integer
            unsigned: false
            nullable: false
            column: ID_SERVICE
            generator:
                strategy: IDENTITY
        codeService:
            type: string
            length: 5
            fixed: false
            nullable: false
            column: CODE_SERVICE
        dateCreation:
            type: date
            nullable: false
            column: DATE_CREATION
        dateModification:
            type: date
            nullable: false
            column: DATE_MODIFICATION

In my database, I have a BEFORE INSERT Trigger that set dateCreation and dateModification.

I'd like to let him do his job, but when I persist a new entity, I got this SQL error :

An exception occurred while executing 'INSERT INTO SERVICE (CODE_SERVICE, DATE_CREATION, DATE_MODIFICATION) VALUES (?, ?, ?)' with params ["test", null, null]:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'DATE_CREATION' cannot be null

Is there a way to do this ? I know triggers are bad, but I don't have any choice...

Here is the trigger, it works :

    CREATE TRIGGER `SERVICE_BI_TG` BEFORE INSERT ON `SERVICE` FOR EACH ROW BEGIN
    BEGIN
        SET NEW.DATE_CREATION=NOW();
        SET NEW.DATE_MODIFICATION=NOW();
    END

The problem is that I have some other fields set on INSERT or UPDATE, like some foreign key and some UPDATE on other tables, I simplified it for my post.

Where have you set this trigger? Are you doing this via the entity doStuffOnPrePersist() function? — Liam Sorsby, Dec 12 '14 at 08:40
So, if I find a way to let him do his job, my entity will not be up-to-date I guess :-/ — Ulti, Dec 12 '14 at 08:41
If you set a the function in your entity to set a date everytime then yes it will auto update. Also, just set the creation to default to the day as this will never need to be updated, will it? — Liam Sorsby, Dec 12 '14 at 08:42
Have a look here: http://stackoverflow.com/questions/13850693/doctrine-add-default-time-stamp-to-entity-like-now — Liam Sorsby, Dec 12 '14 at 08:43
Yes, you are right. The problem is that I have some other fields set on INSERT or UPDATE, like some foreign key and some UPDATE on other tables, I simplified it for my post. — Ulti, Dec 12 '14 at 08:46

Cristian Bujoreanu · Answer 1 · 2014-12-12T13:13:02.673

2

You don't need a trigger defined in database for this.

For dateCreation you can initialize it in your entity constructor:

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

And dateModification you need to a method triggered on preUpdate event:

use Doctrine\ORM\Mapping as ORM;

/**
 *  @ORM\HasLifecycleCallbacks
 */
class myEntity{

    /**
     * @var \DateTime
     * @ORM\Column(name="date_modification", type="datetime")
     */
    private $this->dateModification;

    /**
     * @ORM\PreUpdate
     */
     public function incremenDateModification() {
         $this->dateModification = new \DateTime();
     }
}

edited Dec 12 '14 at 13:13

answered Dec 12 '14 at 08:43

Cristian Bujoreanu

1,147
10
21

Will adding this to the constructor not update the creation time, every time? – Liam Sorsby Dec 12 '14 at 08:44
Now, will be initialized only once, when object is created. That's it. – Cristian Bujoreanu Dec 12 '14 at 08:45
Thanks for your help, but I'm not looking for a workaround. I know the preUpdate / prePersist and other doctrine events. But I don't have the choice for triggers, I don't decide :D (I hate triggers too...) – Ulti Dec 12 '14 at 08:49
I suggest you to add the `use ...;` declarations for the ORM annotations, it will be useful to copy and paste your code (only if the user hasn't already used ORM annotations before). – A.L Dec 12 '14 at 13:03
yes A.L, fair enough, thanks. I missed it, but i added it now – Cristian Bujoreanu Dec 12 '14 at 13:13

Doctrine : Do not insert all fields, only those which aren't NULL

1 Answers1