Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

JoinColumn & Relations in Fixtures

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

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

Login Subscribe

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:

... 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_*:

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

53
Login or Register to join the conversation

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

2 Reply

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!

Reply

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

1 Reply

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

Reply
Default user avatar
Default user avatar Andrew Grudin | posted 5 years ago

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

1 Reply

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

Cheers!

1 Reply

I'm not getting any errors that I can see, but my fixtures are not loading at the end of this video. My database is empty. Something to do with this: ;https://gist.github.com/wea... ? I'm using mysql version 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Reply

Hey james-langridge!

Hmm, that's interesting. If you run bin/console doctrine:fixtures:load and don't see any errors, it definitely means that there were no errors while putting the data into the database. So, it means the problem must be somewhere else. There are two things I can think to check initially:

1) When you run that command, you should see it say something like loading AppBundle\DataFixtures\ORM\LoadFixtures in the terminal. If you see this, then we know that DoctrineFixturesBundle is seeing your fixture class correctly. If not, then we know that's the problem (and can debug why).

2) Assuming you see the correct message about loading the fixture class in step (1), I would then check closer into your LoadFixtures class to make sure it's loading things correctly (a few things could be going wrong there).

Btw, what version of DoctrineFixturesBundle are you using? (try composer show doctrine/doctrine-fixtures-bundle) We use an older version on this tutorial (which you will get if you download the course code) as this tutorial is getting a bit older now, and that could also be a problem (especially if the fixture class is not being loaded at all - my point 1 above).

Let me know what you find out! Cheers!

Reply

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.

Reply

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.

Reply

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 :/

Reply

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.

Reply
Default user avatar

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.

Reply

Hey Nina,

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

Cheers!

Reply
Default user avatar
Default user avatar Terry Caliendo | posted 5 years ago

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

Reply

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!

Reply
Default user avatar
Default user avatar Terry Caliendo | weaverryan | posted 5 years ago

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?

Reply

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!

Reply
Default user avatar
Default user avatar Krzysztof | posted 5 years ago

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?

Reply

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!

Reply
Richard Avatar
Richard Avatar Richard | posted 5 years ago

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

Reply
Victor Avatar Victor | SFCASTS | posted 5 years ago | edited

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!

Reply

Hey Matt,

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

Cheers!

Reply
Default user avatar
Default user avatar Valdass87 | posted 5 years ago

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.

Reply

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!

1 Reply
Default user avatar

Hi,

Now second command works with double quotes around the query.

Reply
Default user avatar
Default user avatar Peter Stephens | posted 5 years ago

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

Reply

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!

Reply
Default user avatar
Default user avatar Terry Caliendo | posted 5 years ago

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).

Reply

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!

Reply
Default user avatar
Default user avatar Terry Caliendo | MolloKhan | posted 5 years ago | edited

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.

Reply

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!

Reply
Default user avatar
Default user avatar Yang Liu | posted 5 years ago

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

Reply

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!

Reply
Default user avatar
Default user avatar Kebabra Abdessamad | posted 5 years ago

Falling in love with alice :)

Reply

Hey Kebabra,

Agreed! Very simple :)

Cheers!

1 Reply
Default user avatar
Default user avatar Kebabra Abdessamad | Victor | posted 5 years ago

call me abdessamad is my first name hhhh in arabic

Reply

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

Cheers!

Reply
Default user avatar

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?

Reply
Victor Avatar Victor | SFCASTS | Lucas | posted 5 years ago | edited

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. "genusnote{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!

1 Reply
Default user avatar
Default user avatar Lucas | Lucas | posted 5 years ago | edited

Edit: Should be`
&lt;numberBetween(1, 10)&gt;`
instead of`
&lt;numberbetween(1, 10)=""&gt;`

Reply
Default user avatar

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

Reply

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;" :)

Reply

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!

Reply
Default user avatar

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'

Reply

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!

Reply
Default user avatar

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

Reply
Default user avatar

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.

Reply

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!

Reply
Default user avatar

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.

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=5.5.9",
        "symfony/symfony": "3.1.*", // v3.1.4
        "doctrine/orm": "^2.5", // v2.7.2
        "doctrine/doctrine-bundle": "^1.6", // 1.6.4
        "doctrine/doctrine-cache-bundle": "^1.2", // 1.3.0
        "symfony/swiftmailer-bundle": "^2.3", // v2.3.11
        "symfony/monolog-bundle": "^2.8", // 2.11.1
        "symfony/polyfill-apcu": "^1.0", // v1.2.0
        "sensio/distribution-bundle": "^5.0", // v5.0.22
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.16
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.2
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.4", // 1.4.2
        "doctrine/doctrine-migrations-bundle": "^1.1" // 1.1.1
    },
    "require-dev": {
        "sensio/generator-bundle": "^3.0", // v3.0.7
        "symfony/phpunit-bridge": "^3.0", // v3.1.3
        "nelmio/alice": "^2.1", // 2.1.4
        "doctrine/doctrine-fixtures-bundle": "^2.3" // 2.3.0
    }
}
userVoice