Agree to Terms Database Field

With a Subscription, click any sentence in the script to jump to that part of the video!

If you compare our old registration form and our new one, we're missing one annoying, piece: the "Agree to terms" checkbox, which, if you're like me, is just one of my favorite things in the world - right behind a fine wine or day at the beach.

Legally speaking, this field is important. So let's code it up correctly.

Adding the "Agreed Terms" Persisted Date Field

A few years ago, we might have added this as a simple unmapped checkbox field with some validation to make sure it was checked. But these days, to be compliant, we need to save the date the terms were agreed to.

Let's start by adding a new property for that! Find your terminal and run:

php bin/console make:entity

Update the User class and add a new field called agreedTermsAt. This will be a datetime field and it cannot be nullable in the database: we need this to always be set. Hit enter to finish.

            
            272 lines
            
            src/Entity/User.php
        
                                                    ... lines 1 - 19
                            
            class User implements UserInterface
        
            {
        
                                                    ... lines 22 - 68
                            
                /**
        
                 * @ORM\Column(type="datetime")
        
                 */
        
                private $agreedTermsAt;
        
                                                    ... lines 73 - 270
                            
            }

Adding the Checkbox Field

Before we worry about the migration, let's think about the form. What we want is very simple: a checkbox. Call it, how about, agreeTerms. Notice: this creates a familiar problem: the form field is called agreeTerms but the property on User is agreedTermsAt. We are going to need more setup to get this working.

            
            45 lines
            
            src/Form/UserRegistrationFormType.php
        
                                                    ... lines 1 - 13
                            
            class UserRegistrationFormType extends AbstractType
        
            {
        
                public function buildForm(FormBuilderInterface $builder, array $options)
        
                {
        
                    $builder
        
                                                    ... lines 19 - 33
                            
                        ->add('agreeTerms', CheckboxType::class)
        
                    ;
        
                }
        
                                                    ... lines 37 - 43
                            
            }

But first, Google for "Symfony form types" and click the "Form Type Reference" page. Let's see if we can find a checkbox field - ah: CheckboxType. Interesting: it says that this field type should be used for a field that has a boolean value. If the box is checked, the form system will set the value to true. If the box is unchecked, the value will be set to false. That makes sense! That's the whole point of a checkbox!

Back on the form, set the type to CheckboxType::class.

            
            45 lines
            
            src/Form/UserRegistrationFormType.php
        
                                                    ... lines 1 - 15
                            
                public function buildForm(FormBuilderInterface $builder, array $options)
        
                {
        
                    $builder
        
                                                    ... lines 19 - 33
                            
                        ->add('agreeTerms', CheckboxType::class)
        
                    ;
        
                }
        
                                                    ... lines 37 - 45

Nice start! Before I forget, find your terminal and make the migration:

php bin/console make:migration

As usual, go to the migrations directory, open that file and... yep! It adds the one field. Run it with:

php bin/console doctrine:migrations:migrate

Oh no! Things are not happy. We have existing users in the database! When we suddenly create a new field that is NOT NULL, MySQL has a hard time figuring out what datetime value to use for the existing user rows!

Migrating Existing User Data

Our migration needs to be smarter. First: when a migration fails, Doctrine does not record it as having been executed. That makes sense. And because there is only one statement in this migration, we know that it completely failed, and we can try it again as soon as we fix it. In other words, the agreed_terms_at column was not added.

If a migration has multiple statements, it's possible that the first few queries were successful, and then one failed. When that happens, I usually delete the migration file entirely, fully drop the database, then re-migrate to get back to a "clean" migration state. But also, some database engines like PostgreSQL are smart enough to rollback the first changes, if a later change fails. In other words, those database engines avoid the problem of partially-executed-migrations.

Anyways, to fix the migration, change the NOT NULL part to DEFAULT NULL temporarily. Then add another statement: $this->addSql('UPDATE user SET agreed_terms_at = NOW()');.

            
            30 lines
            
            src/Migrations/Version20181016183947.php
        
                                                    ... lines 1 - 10
                            
            final class Version20181016183947 extends AbstractMigration
        
            {
        
                public function up(Schema $schema) : void
        
                {
        
                                                    ... lines 15 - 17
                            
                    $this->addSql('ALTER TABLE user ADD agreed_terms_at DATETIME DEFAULT NULL');
        
                    $this->addSql('UPDATE user SET agreed_terms_at = NOW()');
        
                }
        
                                                    ... lines 21 - 28
                            
            }

Great! First, let's run just this migration

php bin/console doctrine:migrations:migrate

This time... it works! To finish the change, make one more migration:

php bin/console make:migration

            
            29 lines
            
            src/Migrations/Version20181016184244.php
        
                                                    ... lines 1 - 10
                            
            final class Version20181016184244 extends AbstractMigration
        
            {
        
                public function up(Schema $schema) : void
        
                {
        
                    // this up() migration is auto-generated, please modify it to your needs
        
                    $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
        
                    $this->addSql('ALTER TABLE user CHANGE agreed_terms_at agreed_terms_at DATETIME NOT NULL');
        
                }
        
                                                    ... lines 20 - 27
                            
            }

Go check it out! Perfect! This gives us the last piece we need: changing the column back to NOT NULL, which will work because each existing user now has a real value for this field. Oh, but, for legal purposes, on a real site - it may not be proper to automatically set the agreed_terms_at for existing users. Yep, you've gotta check with a lawyer on that kind of stuff.

But from a database migration standpoint, this should fix everything! Run the last migration:

php bin/console doctrine:migrations:migrate

Excellent! Next: we have a CheckboxType field on the form... which is good at setting true/false values. And, we have an agreedTermsAt DateTime field on the User class. Somehow, those need to work together!

Leave a comment!

RobinBastiaan | posted 2 years ago | edited

Very good video! I just wanted to add something that developers might come across in their own projects. In my projects at least I usually already have a createdAt property on my user entities. In that case, you do not need the agreedTermsAt property as I can use the former property. (well, maybe you need to consult your lawyer for that again, but it seems excessive to have two columns that are identical)

Anyways, all of the points raised in this video still hold up, and I like how it explains how to migrate a non-nullable property in detail.

Victor | SFCASTS | RobinBastiaan | posted 2 years ago

Hey Robin,

Thank you for your feedback! We're happy your found this video useful for your :)

About reusing the createdAt field - hm, technically yes, but that is something that would be good to check with the layer :) I think, you can just rename that "createdAt" field into "agreedTermsAt" and it will be perfect this way. Because createdAt isn't clear enough from the perspective of agreeing terms - I think the main idea is to have the clear name for this just in case, but I'm not sure 100% here. Also, technically, you may have 2 getters that e.g. getCreatedAt() and getAgreedTermsAt() that both links to the same field called in the DB - probably in code it will have more sense.

P.S. though even having 2 columns in the DB that will hold same data isn't a big deal in this case, so you can just close your eyes on it ;)

Cheers!

Lijana Z. | posted 3 years ago

interesting how you do with not nullable values. In our team we just allow null values for new columns even if they never should be null by business logic, just because we do not know what value to set on existing fields. Is that a bad thing?

MolloKhan | SFCASTS | Lijana Z. | posted 3 years ago | edited

Hey Lijana Z.

In my opinion it can be a bad thing because your application may have a bug where you forgot to set a field's value or something like that, and you won't notice it because your Database will allow it

Adding a new field to an already populated table some times is not easy to know which default value you should put in but based on what I said above I think it worth the effort of thinking in a "good enough" default value

Cheers!

1 Reply

Lijana Z. | posted 3 years ago

This is the example of forms when you need unexpected behaviour like this and feeling like "how the hell now I will do this with symfonny forms" :)

weaverryan | SFCASTS | Lijana Z. | posted 3 years ago

Haha, I couldn't agree more :). That's why I think this "mapped => false" thing is so important: it's a "Swiss Army Knife" that can help with many of these situations.

Cheers!

Jakub G. | posted 4 years ago

general question if I may,
why datetime instead of timestamp? in real app you would have some nice JS calendar to pick a date/time, which then can be converter into timestamp right?

MolloKhan | SFCASTS | Jakub G. | posted 4 years ago | edited

Hey Jakub G.

Doctrine doesn't come with a "timestamp" field type but AFAIK you can use a Datetime field as if it were a Timestamp field

I hope it makes any sense to you :)
Cheers!

Malnet | posted 4 years ago

3:45 you do not have to implement any fix in mysql 8+

MolloKhan | SFCASTS | Malnet | posted 4 years ago | edited

Malnet

What you mean? In MySql8 if a migration with multiple statements fails, it rolls back?

AymDev | posted 4 years ago | edited

Working with MariaDB avoid getting the error but the agreed_terms_at field has a 0000-00-00 00:00:00 value applied.

About the "rollback" idea of "smarter RDBMS", wouldn't using transactions solve the issue (not an expert about that, just wondering) ? Or maybe Doctrine has something similar to transactions (what's the down method for ?) ?

MolloKhan | SFCASTS | AymDev | posted 4 years ago | edited

Hey AymDev

Working with MariaDB avoid getting the error but ...
Ohh, so MariaDB allows you to have almost null dates 0000-00-00 00:00:00 I'm not sure if that's something good

About the "rollback" idea of "smarter RDBMS", wouldn't using transactions solve the issue
I'm not sure, probably transactions only works for updates, inserts, deletes and things like that but not for schema modifications (Again, I'm not sure about this)

what's the down method for ?
It's the method that executes when rolling back a migration bin/console doctrine:migratio:execute --down MigrationVersion1234

Cheers!

1 Reply

IvanPuntiy | posted 4 years ago | edited

Had no errors on the first migration, probably because of MariaDB:

$ bin/console doctrine:migrations:migrate
                                                              
                    Application Migrations                    
                                                              

WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating up to 20181120160559 from 20181118192304

  ++ migrating 20181120160559

     -> ALTER TABLE user ADD agreed_terms_at DATETIME NOT NULL

  ++ migrated (0.11s)

  ------------------------

  ++ finished in 0.11s
  ++ 1 migrations executed
  ++ 1 sql queries
$ mysql --version
mysql  Ver 15.1 Distrib 10.1.36-MariaDB, for Linux (x86_64) using readline 5.1```

Victor | SFCASTS | IvanPuntiy | posted 4 years ago

Hey Ivan,

Thank for proving that it works great with MariaDB! Glad to hear it.

Cheers!

Peter-K | posted 4 years ago

Did you have to generate 2 migration files?
why not just to add 3 statements
$this->addSQL('...DEFAULT NULL..');
$this->addSQL('...UPDATE USERS..');
$this->addSQL('...NOT NULL..');

weaverryan | SFCASTS | Peter-K | posted 4 years ago | edited

Hey Peter K.!

Yep - that's 100% valid. I was mostly being lazy... - after running the first file, the second one is generated for me automatically (instead of me writing it manually) :)

Cheers!

1 Reply

Symfony 4

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.7", // 1.7.0
        "knplabs/knp-paginator-bundle": "^2.7", // v2.8.0
        "knplabs/knp-time-bundle": "^1.8", // 1.8.0
        "nexylan/slack-bundle": "^2.0,<2.2.0", // v2.0.0
        "php-http/guzzle6-adapter": "^1.1", // v1.1.1
        "sensio/framework-extra-bundle": "^5.1", // v5.2.1
        "stof/doctrine-extensions-bundle": "^1.3", // v1.3.0
        "symfony/asset": "^4.0", // v4.1.6
        "symfony/console": "^4.0", // v4.1.6
        "symfony/flex": "^1.0", // v1.17.6
        "symfony/form": "^4.0", // v4.1.6
        "symfony/framework-bundle": "^4.0", // v4.1.6
        "symfony/orm-pack": "^1.0", // v1.0.6
        "symfony/security-bundle": "^4.0", // v4.1.6
        "symfony/serializer-pack": "^1.0", // v1.0.1
        "symfony/twig-bundle": "^4.0", // v4.1.6
        "symfony/validator": "^4.0", // v4.1.6
        "symfony/web-server-bundle": "^4.0", // v4.1.6
        "symfony/yaml": "^4.0", // v4.1.6
        "twig/extensions": "^1.5" // v1.5.2
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.0", // 3.0.2
        "easycorp/easy-log-handler": "^1.0.2", // v1.0.7
        "fzaninotto/faker": "^1.7", // v1.8.0
        "symfony/debug-bundle": "^3.3|^4.0", // v4.1.6
        "symfony/dotenv": "^4.0", // v4.1.6
        "symfony/maker-bundle": "^1.0", // v1.8.0
        "symfony/monolog-bundle": "^3.0", // v3.3.0
        "symfony/phpunit-bridge": "^3.3|^4.0", // v4.1.6
        "symfony/profiler-pack": "^1.0", // v1.0.3
        "symfony/var-dumper": "^3.3|^4.0" // v4.1.6
    }
}

Edit on GitHub

TRACK

Symfony 4 >

COURSE

Symfony 4 Forms: Build, Render & Conquer!

Agree to Terms Database Field

Keep on Learning!

Adding the "Agreed Terms" Persisted Date Field

Adding the Checkbox Field

Migrating Existing User Data

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 19
	class User implements UserInterface
	{
	... lines 22 - 68
	/**
	* @ORM\Column(type="datetime")
	*/
	private $agreedTermsAt;
	... lines 73 - 270
	}

	... lines 1 - 13
	class UserRegistrationFormType extends AbstractType
	{
	public function buildForm(FormBuilderInterface $builder, array $options)
	{
	$builder
	... lines 19 - 33
	->add('agreeTerms', CheckboxType::class)
	;
	}
	... lines 37 - 43
	}

	... lines 1 - 15
	public function buildForm(FormBuilderInterface $builder, array $options)
	{
	$builder
	... lines 19 - 33
	->add('agreeTerms', CheckboxType::class)
	;
	}
	... lines 37 - 45

	... lines 1 - 10
	final class Version20181016183947 extends AbstractMigration
	{
	public function up(Schema $schema) : void
	{
	... lines 15 - 17
	$this->addSql('ALTER TABLE user ADD agreed_terms_at DATETIME DEFAULT NULL');
	$this->addSql('UPDATE user SET agreed_terms_at = NOW()');
	}
	... lines 21 - 28
	}

	... lines 1 - 10
	final class Version20181016184244 extends AbstractMigration
	{
	public function up(Schema $schema) : void
	{
	// this up() migration is auto-generated, please modify it to your needs
	$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

	$this->addSql('ALTER TABLE user CHANGE agreed_terms_at agreed_terms_at DATETIME NOT NULL');
	}
	... lines 20 - 27
	}