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:

101 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 - 99
}

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!

  • 2017-08-30 Victor Bocharsky

    Hey Lucas,

    Ha, that's a bit tricky :) I bet you have the next definitions:


    AppBundle\Entity\Genus:
    genus_{1..10}:

    AppBundle\Entity\GenusNote:
    genus_note_{1..100}:

    i.e. "genus_note_{1..100}" instead of "genus.note_{1..100}". That's why the pattern "genus_*" match both Genus and GenusNote entities and trying to pass GenusNote instead in some cases. So what you need to do is to tweak your patters to match whether only Genus entities or GenusNote entities.

    Cheers!

  • 2017-08-30 Victor Bocharsky

    Ah, yeah, it's a bit tricky to write < and > symbols in Disqus comments, I replace them with HTML escape characters like "&lt;" and "&gt;" :)

  • 2017-08-29 Victor Bocharsky

    Hey Lucas,

    What's the difference between how it should be and how it is? Because in both cases you wrote "<numberbetween(1, 10)="">" :)

    Cheers!

  • 2017-08-29 Lucas

    The b in between should be capital and there shouldn't be ="" but for some reason the website likes to mess with my comment.

  • 2017-08-29 Lucas

    Edit: Should be

    <numberBetween(1, 10)>

    instead of

    <numberbetween(1, 10)=""> 
  • 2017-08-29 Lucas

    When I tried using '@genus_*' for fixtures.yml I get the error:

    "Catchable Fatal Error: Argument 1 passed to AppBundle\Entity\GenusNote::setGenus() must be an instance of AppBundle\Entity\Genus, instance of AppBundle\Entity\GenusNote given".

    I guess the '@genus.*' might be causing Alice to create a GenusNote in some instances. I changed * to <numberbetween(1, 10)=""> and it worked fine, but it isn't as elegant. Was there some change in the behavior of * or am I doing something wrong?

  • 2017-07-03 Victor Bocharsky

    Hey Nina,

    Probably you have some invalid YAML structure of your fixtures. Could you show us your fixtures YAML files?

    Cheers!

  • 2017-07-02 Nina

    Hello,
    I have error in console when do
    php bin/console doctrine:fixtures:load

    [Symfony\Component\Debug\Exception\FatalThrowableError] Type error: Argument 3 passed to Nelmio\Alice\Fixtures\Builder\Builder::build() must be o f the type array, string given, called in C:\OpenServer\domains\symfony0\vendor\nelmio\al ice\src\Nelmio\Alice\Fixtures\Loader.php on line 275

    please, help me fix this.

  • 2017-06-29 Diego Aguiar

    Nice research!

    I'm not a database expert but looks like it won't make any guesses for you, you might want an onDelete:cascade for only one side of your relation but not for the other side

    Thanks for sharing this, have a nice day!

  • 2017-06-28 Terry Caliendo

    Thanks for the reply and for the info about the OrphanRemoval option and I will explore it next. For now, for certain learning purposes, I'm trying to get it to work on the database side using the onDelete="CASCADE"

    Here's what I'm discovering... its all about how I bind the two together. I'd think the onDelete="Cascade" would work both ways (especially if I put it on both sides), but apparently it doesn't.

    Here are (basically) my two Entities:
    ============================

    /**
    * @ORM\Entity
    * @ORM\Table(name="user")
    */
    class User
    {
    ...

    /**
    * @ORM\OneToOne(targetEntity="AppBundle\Entity\User_Preferences", mappedBy="User")
    * @ORM\JoinColumn(onDelete="CASCADE");
    */
    Private $UserPreferences;
    }



    /**
    * @ORM\Entity
    * @ORM\Table(name="user_preferences")
    */
    class User_Preferences
    {
    ...

    /**
    * @ORM\OneToOne(targetEntity="AppBundle\Entity\User", inversedBy="UserPreferences")
    * @ORM\JoinColumn(onDelete="CASCADE");
    */
    Private $User;
    }


    NOW...

    If I bind them like the following, the delete only cascades if I delete the User_Preferences entry in the database, it does not cascade if I first delete the User entry.

                    /** @var User $user */
    $user = $form->getData();
    ...

    $userPreferences = new User_Preferences();
    $user->setUserPreferences($userPreferences); // setting on the owning side

    $em = $this->getDoctrine()->getManager();
    $em->persist($userPreferences);
    $em->persist($user);
    $em->flush();

    If I bind them like the following, the delete does cascade if I delete the User entry in the database, it does not cascade if I first delete the User_Preferences entry.

                    /** @var User $user */
    $user = $form->getData();
    ...

    $userPreferences = new User_Preferences();
    $userPreferences->setUser($user); // setting on the inverse side

    $em = $this->getDoctrine()->getManager();
    $em->persist($userPreferences);
    $em->persist($user);
    $em->flush();

    So my problem when I first wrote the question was that I was using the first set of code (binding by the owning side as opposed to the inverse side) and deleting the User (which does not cascade in that direction).

    So apparently there's something happening at the database level that I'm not understanding as I was initially expecting it would work both ways regardless of they are bound (as I was expecting onDelete was completely database level).

    I merged the code, setting both sides, then the onDelete to cascades both ways.

    So I get it, but still don't really get it. Ha. Need to learn more about foreign keys, I guess.

  • 2017-06-27 Diego Aguiar

    Hey Terry Caliendo!

    It should work for OnteToOne too, maybe your database is not configured properly, try updating your schema.

    Also you could use the OrphanRemoval option, it's great for OneToOne, you can get more information about it here: http://docs.doctrine-projec...
    Or even better, watch Ryan explaining it: https://knpuniversity.com/s...

    Cheers!

  • 2017-06-27 Terry Caliendo

    Not sure where to ask this but there is some discussion of "ON DELETE" in this tutorial...

    I have two entities that I've mapped with a OneToOne relationship. When I delete the entry on the owning side I want the inverse side to be deleted as well. I'd like the delete to be done by the database, so I'm trying to use the "@ORM\JoinColumn(onDelete="CASCADE");" on the inverse side.

    The OnDelete/CASCADE line seems to work just fine on my OneToMany relationships, but it doesn't seem to be working on my OneToOne relationship.

    Does this option not work with OneToOne relationships?

    I know in other tutorials, Ryan discusses only thinking about using two types of relationships... OneToMany and ManyToMany. So maybe I should think about what I'm doing differently (I can explain my situation further if necessary).

  • 2017-04-14 Victor Bocharsky

    Hey Matt,

    Thanks a lot for sharing it with us! I think it could be really useful for other users.

    Cheers!

  • 2017-04-13 mattxtlm

    Hi Victor Bocharsky ,

    that was exactly what I was looking for.

    Thank you very much.

    And by the way, if someone else tries to figure out how to configure it, the symfony doc is a bit unclear about filters: Just be sure to not only enable it (in config.yml):


    doctrine:
    orm:
    filters:
    softdeleteable:
    class: Gedmo\SoftDeleteable\Filter\SoftDeleteableFilter
    enabled: true

    But also register it (also in config.yml):


    stof_doctrine_extensions:
    default_locale: en_US
    orm:
    default:
    softdeleteable: enabled


    And if you want to disable it for a given EntityManager, just do in your Controller:


    /** @var EntityManager $em */
    $em = $this->getDoctrine()->getManager();
    $filters = $em->getFilters();
    $filters->disable('softdeleteable');

    PhpStorm expects an ObjectManager Object, but in fact an EntityManager Object is returned. I don't know why, but with the annotation you get correct type hinting.

    Cheers.
    Matt

  • 2017-04-13 Victor Bocharsky

    Hey mattxtlm ,

    On practice, there's StofDoctrineExtensionsBundle which has Softdeleteable behavior which allows to implicitly remove records. There're much more behaviors in that bundle which helps to solve different tasks. Check them out.

    Cheers!

  • 2017-04-13 mattxtlm

    Hi,

    is there a way to prevent accidental deletion of a GenusNote or in the real World of a, e.g. Order, for audit reasons? The table would require an additional column (e.g. Status), which would be set according to the operation (like "created","active","deleted","archived"). But it should not be possible to actually delete a row in that table...

    Is there a way by means of doctrine ?

    Thanks!

  • 2017-03-28 Diego Aguiar

    Hey TamNC!
    Can you show me how your fixtures.yml file looks like ?
    I couldn't reproduce that error from finish folder, did you update your database schema ?

    Have a nice day!

  • 2017-03-28 TamNC

    Hi,

    I got an error after run command bin/console doctrine:fixtures:load

    [Symfony\Component\Debug\Exception\FatalThrowableError]
    Type error: Argument 1 passed to AppBundle\Entity\GenusNote::setGenus() must be an instance of AppBundle\Entity\Genus, instance of AppBundle\Entity\GenusNote given,

    I think it came from the setGenus(Genus $genus) function. It meant, we need to pass the Genus Object to this function to be able creating GenusNote, But in the fixtures.yml the code is : genus: '@genus_*', i doubt that this syntax to did not get the Note Object for us.

    Anyway, how can i fix it ?

  • 2017-03-24 Victor Bocharsky

    Oh, I'm not familiar with arabic names, sorry!. OK Abdessamad, thanks for correcting me ;)

    Cheers!

  • 2017-03-24 Kebabra Abdessamad

    call me abdessamad is my first name hhhh in arabic

  • 2017-02-21 maxii123

    deleting all migrations, then dropping, creating and migrating worked.

  • 2017-02-13 Victor Bocharsky

    Hey Kebabra,

    Agreed! Very simple :)

    Cheers!

  • 2017-02-12 Kebabra Abdessamad

    Falling in love with alice :)

  • 2016-12-12 weaverryan

    Ah, thanks Teodor! It makes sense then - there is a bug in *how* the migration generator for 5.6.7, so you generated the same "bug" that I did :). Nothing to worry about

  • 2016-12-12 Teolan

    Hy Ryan!
    my version is not from download, I made step by step with you from begining :)

  • 2016-12-10 weaverryan

    Hey Teodor!

    Glad you got it working :). I'm curious, did you download the "start" code for the project and have problems with the migration? We pushed a fix for the migration for MySQL 5.6.7 - so I *thought* we had that issue tackled. I'm interested in what you saw!

    Cheers!

  • 2016-12-10 Teolan

    that's work for me:

    Delete all files inside DoctrineMigrations folder

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

    Restart MySQL server

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

  • 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/op...

  • 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/qu...

    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/w...

    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/s....

    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/s...

    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* !