Buy

JoinColumn & Relations in Fixtures

Is the relationship required in the database? I mean, could I save a GenusNote without setting a Genus on it? Actually, I could! Unlike a normal column, relationship columns - for whatever reason - are optional by default. But does it make sense to allow a GenusNote without a Genus? No! That's crazy talk! Let's prevent it.

Find the ManyToOne annotation and add a new annotation below it: JoinColumn. Inside, set nullable=false:

96 lines src/AppBundle/Entity/GenusNote.php
... lines 1 - 10
class GenusNote
{
... lines 13 - 39
/**
* @ORM\ManyToOne(targetEntity="Genus")
* @ORM\JoinColumn(nullable=false)
*/
private $genus;
... lines 45 - 94
}

The JoinColumn annotation controls how the foreign key looks in the database. And obviously, it's optional. Another option is onDelete: that literally changes the ON DELETE behavior in your database - the default is RESTRICT, but you can also use CASCADE or SET NULL.

Anyways, we just made a schema change - so time to generate a migration!

./bin/console doctrine:migrations:diff

This time, I'll be lazy and trust that it's correct. Run it!

./bin/console doctrine:migrations:migrate

When Migrations Go Wrong

Ah, it explodes! Null value not allowed? Why? Think about what's happening: we have a bunch of existing GenusNote rows in the database, and each still has a null genus_id. We can't set that column to NOT NULL because of the data that's already in the database.

If the app were already deployed to production, we would need to fix the migration: maybe UPDATE each existing genus_note and set the genus_id to the first genus in the table.

But, alas! We haven't deployed to production yet: so there isn't any existing production database that we'll need to migrate. Instead, just start from scratch: drop the database completely, re-create it, and re-migrate from the beginning:

./bin/console doctrine:database:drop --force
./bin/console doctrine:database:create
./bin/console doctrine:migrations:migrate

Phew! Now it works great.

Tip

If you still get an error while running the migration, it's because of a MySQL change! Find the details here: http://bit.ly/migrations-tweak

Relations in Fixtures

Last step! Our fixtures are broken: we need to associate each GenusNote with a Genus. We know how to set normal properties, like username and userAvatarFilename. But how can we set relations? As usual with Alice: it's so nice. Use genus: @ then the internal name of one of the 10 genuses - like genus_1. That's it!

But, you know what? That's not awesome enough. I really want this to be a random Genus. Ok: change that genus_1 to genus_*:

16 lines src/AppBundle/DataFixtures/ORM/fixtures.yml
... lines 1 - 8
AppBundle\Entity\GenusNote:
genus.note_{1..100}:
... lines 11 - 14
genus: '@genus_*'

Alice will now look at the 10 Genus objects matching this pattern and select a random one each time.

Reload the fixtures:

./bin/console doctrine:fixtures:load

It's alive! Check out the results again with doctrine:query:sql:

./bin/console doctrine:query:sql 'SELECT * FROM genus_note'

Every single one has a random genus. Do you love it? I love it.

Leave a comment!

  • 2016-10-31 weaverryan

    No, it doesn't autocomplete for me, at least not right now (I feel like this *might* have worked in the past, but I'm not sure). So, you're not alone! It's all up to the Symfony plugin to provide this intelligence, which is AMAZING, but isn't 100% perfect (but it gets a pass for kicking butt otherwise).

    Cheers!

  • 2016-10-31 Terry Caliendo

    Thanks... when you say PhpStorm *should* do a better job... are you saying this typically does work and maybe my code is somehow goofed up?

    Or are you saying that this typically doesn't work and the PhpStorm programmers *should* do a better job of programming in future versions?

  • 2016-10-30 weaverryan

    Yo Terry!

    PhpStorm should really do a better job in this one case - it should know (via the Symfony plugin) that since findAll() is being called on your Flow_Chart repository, that it is returning an array of Flow_Chart objects (and thus, if you loop over them, each has the methods of a Flow_Chart object).

    The fix is to help PhpStorm with some inline phpdoc:

    /** @var Flow_Chart[] $FlowCharts */
    $FlowCharts = $em->getRepository('...')->findAll();

    (make sure you auto-complete the Flow_Chart in the phpdoc, so you get that use statement in this class). This is manual, but the nice thing is that when you type /** and then press space, it'll fill in all of the details except for the class. And of course, when you're writing custom methods (e.g. custom repository methods), you'll want to include this type of thing as your method's @return to get autocompletion when you call those.

    Cheers!

  • 2016-10-29 Terry Caliendo

    This code works just fine, but PHP Storm doesn't seem to be able to figure out that getFlowObjects is a valid method. Any thoughts on a fix? See image here: https://drive.google.com/open?...

  • 2016-09-25 weaverryan

    Yo Peter!

    As long as you haven't already deployed your app to production, this is totally valid! This would create one big migration file with *all* of the migrations needed for your app up until now (but delete all the other files, if you have them).

    Also, if you download the code and open our migration file, we've updated it to drop the F-key and re-add it... for people that are having the MySQL 5.6.7+ problem.

    Cheers!

  • 2016-09-24 Peter Stephens

    I think I cheated, but hey - Whatever keeps us going,

    ./bin/console doctrine:database:drop --force
    ./bin/console doctrine:database:create

    THEN do,

    ./bin/console doctrine:migrations:diff
    ./bin/console doctrine:migrations:migrate

  • 2016-09-16 weaverryan

    Hey!

    Thanks for the link - which finally explains this weird behavior! We've already fixed the finish code download (dropping the FKey in the migration) so that it works for everyone. I'm adding a note to the script and video now :).

    Cheers!

  • 2016-09-14 Krzysztof

    I had the same problem like the other readers (the last migration is not working).

    I found this:

    http://stackoverflow.com/quest...

    So you must change your migration to drop the foreign key and the create it again.

    Maybe it is worth to mention it in this video script?

  • 2016-08-29 weaverryan

    Hi Yang!

    Hmm, I think you deleted your original comment, but I saw it and I think I can help :). You just need to put the JoinColumn onto the same 1 line, like this:

    /**
    * @ORM\JoinColumn(nullable=false, onDelete="CASCADE")
    */

    And then that should do it :).

    Cheers!

  • 2016-08-29 Yang Liu

    I fixed problem 1, is was actually a syntax error... but problem 2 still remains

  • 2016-08-08 Mark

    Hi Andrea,
    I got the same problem as you and i actually found the answer.

    1. Delete the old files from the DoctrineMigrations folder
    2. Drop the database in the cmd/terminal
    3. Recreate it in the cmd/terminal
    4. Delete genus variable with it's getter and setter from GenusNote entity.
    5. Migrate the the two table in cmd
    6. Now you've got your tables without relationship
    7. Next insert genus var again to the GenusNote entity, generate getter and setter and
    insert the comment part above it but both ORM\ManyToOne and ORM\Joincolumn
    8. Migrate again and it should work fine.

    The problem was that if you set the column to be a foreign key you cannot modify it to be not null even if you dont have any data in it. So after you added the genus_id column you have to tell the database to set it not null and then it can be foreign key.

    I hope it helps.

  • 2016-06-15 JLChafardet

    how about attempting a newer version of symfony? who knows.
    I always go about doing
    composer install, then composer update
    in the end had to get rid of the migration point that was giving the error (the file) and got it to work.

  • 2016-06-15 weaverryan

    Yea, this is a bit of a mystery. There is clearly some issue here (as other users are getting the same migration error), but I haven't been able to repeat it yet locally :/

  • 2016-06-14 JLChafardet

    ok, I went back again not to the previous git commit, but to the first commit of this screencast series (1.1) and redid the whole thing, it worked now.

  • 2016-06-14 JLChafardet

    hmmm, im getting nowhere here. im getting a whole different world of errors than what you have. and read all the comments, the drop --force;create;migrate just doesnt work, keeps erroring the same error.

    and here goes the wall of text.

    ....
    lies.

    check the whole output here

    http://pastie.org/private/wmf2...

    I have even gone back to the previous commit in git (local git repo), and restarted the process, still get the same issue.

  • 2016-05-22 weaverryan

    Hmm, this one has me stumped! My impression is that you *should* be able to modify this constraint, as long as there are no null values already in the table for this (and in this case, the tables are empty!). Technically, you could fix this by modifying your migration to drop the foreign key and the re-create it afterwards, but I'm not sure what the cause of the problem is :/

  • 2016-05-19 Andrea

    Yes i do.
    I tried to use even yours files but nothing has changed.

    Mysql version, i'm using is: 5.6.15

    Thanks for your reply.

  • 2016-05-19 weaverryan

    Thanks for the details!

    So, I definitely don't get the same error :/. Do you get the error if you download the code for this tutorial and try the same procedures using the "finished" code? I'm also wondering if this is caused by some MySQL version difference - do you know what version you're using?

    Thanks for the help!

  • 2016-05-18 Andrea

    Hi ryan,
    thanks for yout replay.
    Yes i do, i used:
    ./bin/console doctrine:database:drop --force
    ./bin/console doctrine:database:create
    and everything gone ok.

    Then
    ./bin/console doctrine:migrations:migrate
    and i've got the error.

    If i look in my db, after the migrate command, i see 3 tables:
    - genus (empty)
    - genus_note (empty)
    - migration_versions (5 rows with migration code inside column "version").

    Hope this help

  • 2016-05-18 weaverryan

    Hi Andrea!

    Hmm, this definitely makes me think that here might be an issue here - several people have problems. When you say "I dropped and created" - did you drop the entire database or just the tables? I mean, did you run:

    bin/console doctrine:database:drop --force
    bin/console doctrine:database:create

    Also, when you get the error, if you look in your database manually, do you see a GenusNote table? And if so, are there any records in this table?

    Thanks! I'm very happy to help debug this - I just need some help from someone who can see the issue :).

    Cheers!

  • 2016-05-14 Andrea

    Hi, i've got the same problem of others users.
    I read about the link passed in weaverryan's response but.... i dropped and created and then migrated and i still have the same error:
    SQLSTATE[HY000]: General error: 1832 Cannot change column 'genus_id': used in a foreign key constraint 'FK_6478FCEC85C4074C'

  • 2016-03-29 Valdass87

    Hi,

    Now second command works with double quotes around the query.

  • 2016-03-29 weaverryan

    Hi there!

    You *should* get the first failure :). I got it too - I talk about it here: https://knpuniversity.com/scre....

    The second command, however, should *not* fail. When I copy that, it works. Is it possible you forgot the single quotes around the query when you originally tried it? The error is because the doctrine:query:sql command thinks that you're passing *more* than 1 argument to it. One way that could happen is if you didn't have the quotes around your query (then it would look like 4 arguments).

    I hope that helps!

  • 2016-03-29 weaverryan

    Yes, you *should* get an error - I got one too :). I talk about it here: https://knpuniversity.com/scre...

    Cheers!

  • 2016-03-27 Valdass87

    I got two exceptions:

    1. After bin/console doctrine:migrations:migrate

    -> ALTER TABLE genus_note CHANGE genus_id genus_id INT NOT NULL
    Migration 20160327014231 failed during Execution. Error An exception occurred while executing 'ALTER TABLE genus_note CHANGE genus_id genus_id INT NOT NULL':
    SQLSTATE[HY000]: General error: 1832 Cannot change column 'genus_id': used in a foreign key constraint 'FK_6478FCEC85C4074C'

    2. After bin/console doctrine:query:sql 'SELECT * FROM genus_note'

    [Symfony\Component\Console\Exception\RuntimeException]
    Too many arguments.

  • 2016-03-24 Andrew Grudin

    After
    ( bin/console doctrine:migrations:migrate )
    I got exception about *foreign key* !