Saving a Relationship

Hi Ryan,
Thank you for the great tutorial.
Assuming that the Genus object and the GenusNote Object are both persisted in different databases. One in MySQL and one in Mongo.
At which level do you you control how the GenusNote document should be persisted (key and value structure). Would you do that in a doctrine listener such as pre-persist? and upon fetching the relation, at what stage would you convert the document to an object?

Hi Ziad!

Ah, that makes things a bit more complex :). I'm sure you're aware, but OpenSky was the company that famously setup their Doctrine to do types of things like this. There are some resources out there (http://doctrine-orm.readthe..., http://www.slideshare.net/s..., but I've never done it myself. I would certainly see if you can hook this up like the cookbook article. BUT, if that gives you any trouble, personally, I would go a very old-fashioned route: do *not* map relationships between the ORM and ODM. Instead, I would map the id's only (e.g. I would have a GenusNote.genusId normal column, and it would contain the mongo ID of the related Genus object). It would be *your* job to manually query for the Genus whenever you needed it (of course, you'd have some service functions to make this easy and not repeat yourself). This is much less convenient, but way more predictable. You *would* need to also control the order that things are persisted. And yes, you *could* also add a listener - I think on postLoad - and use it to automatically use the genusId value to query for the Genus object from Mongo, for example. You just need to weigh if that's worth it, since you may not always need this object.

That's a long way of saying that I don't have personal experience here. So if you can follow the cookbook and have good success, then awesome! Otherwise, treat these like two unrelated data stores (imagine storing some things in Redis and other things in MySQL - we probably wouldn't try to hook up some fancy relation system).

Cheers!

In the Script code there is an error ( it's $genusNote->setSomething not $note )

Hey Zoé!

You're right! The code doesn't match up with the video variable name :). I've just fixed this (https://github.com/knpunive... and it should be up shortly.

Thanks!

Hello again Ryan, what if I didn't want to make a genusnote everytime I made a genus. so lets say the note is added at a later time. How would you link the correct $user then?

Hi Kosta!

No matter what, if you want to link a GenusNote to its Genus, you will do it via `$genusNote->setGenus($genus)`. If you imagine an endpoint that the user can submit to in order to create a new GenusNote, it might look like this:

/**
 * @Route("/genus/{id}/notes")
 */
public function newNoteAction(Genus $genus, Request $request)
{
    // Symfony queries for the Genus object automatically
    $genusNote = new GenusNote();
    // populate this object with data from the request, probably
    // with a Form

    $genusNote->setGenus($genus);
    // get the entity manager, persist($genusNote), flush() and redirect
}

So ultimately, you just need to get a Genus object. You might create this (like we did in this chapter). Or more likely, you will query for the Genus object and then set it on the GenusNote.

Let me know if this helps!

Cheers!

hey brother I figured it out over the weekend. Thanks so much for the reply though awesome work man awesome.

Hey Ryan,
I was wondering why the debugger shows a SQL Statement, with VALUES (?, ?, ?, ...). I guess the parameters replace the question marks at some point but why not simply put the parameters in there directly?

Hey Max!

Another cool question :). So Doctrine uses prepared statements to make the queries (which everyone should do, no matter what libs you're using, as it prevents SQL injection attacks). This means that Doctrine itself never actually makes the full query string - it literally creates this query string with ? and sends that, along with an array of parameters as the "query". What you're seeing here is a representation of that. Of course, it's kind of annoying if you want to copy these queries and manually run them to help debug, so that's why we also have the "View runnable query" link.

Cheers!

Hey weaverryan,

I noticed you have a property with the name '$isPublished', but in your DB the column has the name build like this 'is_published'. How come? I also named my properties like this '$lastName' and this is how they look in the DB 'lastName'. I don't know too much about the naming convention so i followed yours, but my result is different....

Hey Petru Lebada!

Great question :). The answer (for a Symfony 3 project) is this config: https://github.com/symfony/... (you get this config also automatically if you start a Symfony 4 project).

Without *any* config, Doctrine names the columns the same as your properties, which is what you're seeing. Symfony gives you this doctrine.orm.naming_strategy.underscore config to enforce a different naming convention. Add that to your project and you'll see your naming conventions change too :)

Cheers!

That's awesome :D thank you weaverryan , Diego Aguiar

Hey Petru Lebada

You can tell Doctrine which convention to follow in the cofiguration file:

// doctrine.yaml
doctrine:
    # ...
    orm:
        # ...
        entity_managers:
            default:
                naming_strategy: doctrine.orm.naming_strategy.default # or any other convention

Or you can specify the column name in the property annotations:

// SomeEntity.php

/**
 * @ORM\Column(name="my_field", type="string")
 */
private $myField;

Cheers!

weaverryan

a question here on relationships.

lets assume the following scenario.

I have a relationship between a couple of tables

TableA and TableA-type in a 1-1 relationship (too many TableA-types to really repeat them over and over in the db)

and TableA at the same time is on a 1 - n relationship with TableB,
where Many TableB records belong to a single TableA record.

how would I do, to get the information from TableA and get me its TableA-type and its TableB childrens?

the inverse view isnt a bad idea to have, but for the time beign i can live without it.

Yo Irhamel!

First, you described all the relationships perfectly - so nice job - your pretend setup is very clear. But I'm not completely clear on the question - but let me take a shot.

First, with OneToOne relationships, you can choose which side is the "owning" side and which side is the "inverse" side. Make the "owning" side which ever side of the relationship you will more likely want to set.

Suppose you have a `$tableA` object, then you can do things like this:

// this assume you've called your property tableAtType inside TableA (and made a corresponding getter function for it)
// depending on your setup, this may be the inverse side of your relationship. If you want to do something like this,
// then you *must* map the inverse side
echo $tableA->getTableAType()->getName(); // print some make-believe getName() method

// this assumes you have mapped the *inverse* (optional), OneToMany relationship in TableAType to TableB on a property called tableBs, with a corresponding getter function.
$tableBs = $tableA->getTableAType()->getTableBs();
foreach ($tableBs as $tableB) {

}

I hope that helps a bit!

well TableA is the one with relationships to both TableAType and
TableB, TableAType shouldnt have any way to get to TableB without going
through TableA

see it this way, a bit more natural

operation (TableA)
OperationType(TableAType)
Transaction(TableB)

Each Operation has an operation Type (but the owner should be Operation not OperationType)
each Operation can have MANY transaction, but each Transaction can belong to a single operation.

so in this spirit, to get a Transaction from an Operation Type, it would have to go through Operation first, as there is no relation at all other than the OperationId on the Transaction ?

hmmm I'm starting to see a way to "join" these 2 columns.(gota learn more on doctrine joins tho.)

Hey Irhamel!

Ok cool! It sounds to me like you have the following relationships (just looking at the owning sides):

1) Operation is ManyToOne to OperationType (so it will have an operation_type_id).
This means that each Operation will have *one* OperationType. And if you looked in the database, you'll find that one OperationType may be assigned to many Operations.

2) Transaction is ManyToOne to Operation (so it will have an operation_id)
This means that each Transaction will have *one* Operation. And if you looked in the database, you'll find that one Operation may be assigned to (own) many Transactions.

So you asked basically "how do I get a Transaction from an OperationType?". But the better question is, "Howe can I find *all* Transactions for an OperationType". This is because 1 OperationType will potentially have many Operations, and each Operation will potentially have many Transactions.

The answer is to map the inverse - OneToMany side of both relationships. Specifically, you would:

A) Add an operations property to OperationType that is a OneToMany relation to Operation.
B) Add a transactions property to Operation that is a OneToMany relation to Transaction.

Then, you could have code like this:

$operationType = // some way to get a single OperationType
$allOperations = $operationType->getOperations(); // use the OneToMany relationship property

$allTransactions =[];
foreach ($allOperations as $operation) {
    $allTransactions = array_merge(
        $operation->getTransactions(), // use the OneToMany relationship property
        $allTransactions
    );
}

This would give you a giant array. You could also create a query in the database to do this directly. We have some details about those types of queries here: http://knpuniversity.com/sc...

Good lucks! This stuff is the hardest part of Doctrine in my opinion: trying to keep your head straight about all the different directions for each relationship. Always think back to how you want it to look in the database. Wherever you have the foreign key column (e.g. operation_type_id) will be the entity that should have the ManyToOne side of the relationship.

Cheers!

Hi Ryan,

After two years on knpuniversity watching screencasts, I wanted to thank you for what you have achieved. You tutorials are to me the best and I always looking forward to seeing new ones and understanding subjets that you make easy to understand.

I watched this screencast a long time ago, but I met a problem this week related to it that I didn't resolve with a nice solution. So I wanted to ask your opinion and how you would have done it.

My question concerns "composite and Foreign Keys as Primary Key", in the context of One-To-One associations as documented here: http://docs.doctrine-projec...

Thus, I have two entities with a one-to-one association. The second entity has a foreign key on the id of the first one. The annotations are the following.

* @ORM\Id
* @ORM\GeneratedValue(strategy="NONE")
* @ORM\OneToOne(targetEntity="firstEntity")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="ID", referencedColumnName="ID")
* })

I did fixtures that create:
-the first entity: $firstEntity = new firstEntity();
-the second entity: $secondEntity = new SecondENtity();
-link both of them : $secondEntity->setId($firstEnity);
-persiting both of them: $em->persist($firstEntity);$em->persist($secondEntity);
-flush: $em->flush();

However I got the following error: "The given entity of type '...' has no identity/no id values set. It cannot be added to the identity map." The only solution, I came with, was to flush the first entity, refresh it, and then persiting the second entity.

Is there any alternative to that? And why does this error occur?

Thanks in advance for you answer. And Happy christams!!

Hey matthieu!

Sorry for my slow reply - holidays! And thank you for the nice words! It's so great to hear success stories, AND that you enjoyed yourself along the way. That's the goal :D.

Ok, let's see about your question! I don't have any personal experience with your situation, unfortunately. And it's a weird enough part of Doctrine that my instinct is that... yea... this might just be a shortcoming you need to deal with :(. But, SO (https://stackoverflow.com/q... seems to suggest there's an interesting hack where you add a primary key field with the same name as foreign field.

So... that's not too much help, but maybe it will give you something! This error might occur... because it's just an uncommon part of Doctrine. I only *occasionally* use OneToOne relations. And when I do, I just treat them like normal relations (e.g. if I have a profile table that is OneToOne to a user table, then the profile table will have an id and a user_id. Technically a little wasteful, but not really a big problem).

Cheers!

Hey,

I get the following error when I try to run the migration:

vagrant@aqua-note:~/Code/aqua-note$ bin/console doctrine:migrations:migrate

Application Migrations

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

++ migrating 20170709224823

-> ALTER TABLE genus_note ADD CONSTRAINT FK_6478FCEC85C4074C FOREIGN KEY (genus_id) REFERENCES genus (id)
Migration 20170709224823 failed during Execution. Error An exception occurred while executing 'ALTER TABLE genus_note ADD CONSTRAINT FK_6478FCEC85C4074C FOREIGN KEY (genus_id) REFERENCES genus (id)':

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`homestead`.`#sql-5cf_59`, CONSTRAINT `FK_6478FCEC85C4074C` FOREIGN KEY (`genus_id`) REFERENCES `genus` (`id`))

[Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException]
An exception occurred while executing 'ALTER TABLE genus_note ADD CONSTRAINT FK_6478FCEC85C4074
C FOREIGN KEY (genus_id) REFERENCES genus (id)':
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a forei
gn key constraint fails (`homestead`.`#sql-5cf_59`, CONSTRAINT `FK_6478FCEC85C4074C` FOREIGN KE
Y (`genus_id`) REFERENCES `genus` (`id`))

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a forei
gn key constraint fails (`homestead`.`#sql-5cf_59`, CONSTRAINT `FK_6478FCEC85C4074C` FOREIGN KE
Y (`genus_id`) REFERENCES `genus` (`id`))

[PDOException]
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a forei
gn key constraint fails (`homestead`.`#sql-5cf_59`, CONSTRAINT `FK_6478FCEC85C4074C` FOREIGN KE
Y (`genus_id`) REFERENCES `genus` (`id`))

doctrine:migrations:migrate [--write-sql] [--dry-run] [--query-time] [--allow-no-migration] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]

vagrant@aqua-note:~/Code/aqua-note$

Hey Shaun

I believe there is an easy fix, you just need to clear your "genus_note" table before running the migration, this is because that table has records that enters in conflict when running the migration, in this case the genus_id field

Cheers!

When defaulting : "private $isPublished = true;" why is that not a trigger for a diff and migration? I would have expected the database schema to need to be changed so that the database defaults the field to true.

Hey maxii123 ,

Good question! That's because you change implementation not Doctrine metadata ;) If you set properties with default values it affects only your classes, not database. If you want to change schema, do it in annotation:

@ORM\Column(... , options={"default" : 1})

Cheers!

How can I save:
$genus = new Genus();

$genus->setName('Octopus'.rand(1, 100));

$genus->setSubFamily('Octopodinae');

$genus->setSpeciesCount(rand(100, 99999));

$note = new GenusNote();

As an complete object instead of defining every property?

Hey Jelle Schouwstra!

What do you mean by "a complete object instead of defining every property"? Do you want to do something more like this:

$genus = new Genus('Octopus'.rand(1, 100), 'Octopodinae', rand(100, 99999));

... where all of the data is set in the __construct method? Let me know!

Cheers!

Actually, more like this: $genus = $form->getData();
Ready to persist: $em->persist($genus);

So this for example won't be needed anymore:

$genus->setName('Octopus'.rand(1, 100));

Am I making any sense here? :)

Ah, of course! Yes, you can do that with Symfony;'s form - we talk about here http://knpuniversity.com/sc... - which you may or may not have already seen :).

In this case, since we are just creating some dummy data (not from user input) to see how things work on a low level. There is no form, so we need to do it by hand. If you want to create dummy data so that you have some data to play with in your app, a better way is via fixtures - we chat about that as well: https://knpuniversity.com/s...

Let me know if that helps! And cheers!

I will try it out, thanks!

That's exactly what I meant, thanks!

Just a note: you already have "private $isPublished = true;" in your course code, so no error shows up :)

Hey Pavel,

Ah, yes, you're right, it *is* in the start/ directory. I'll tweak it :)
Thanks for letting us know!

Cheers!

Hi @weaverryan ,

thanks very much for your nice tutorials, they are really helping me a lot!
I've got a quick question for you; so, ok, I've created and saved the relationship as in your example, but when examining the relations for the genus_note table via phpmyadmin, I've notice that for the genus_id field I have ON DELETE RESTRICT and ON UPDATE RESTRICT.

How is it possible to set up this relationship in such a manner to have, for instance, ON DELETE SET NULL and ON UPDATE CASCADE?

Thanks!

Hey @Paolo!

Cool question! And yes! This is done with the JoinColumn: https://knpuniversity.com/s..., specifically the onDelete setting. I usually let it stay as RESTRICT, until I know that I definitely want CASCADE (since that's inherently a little more dangerous). As far as I know, there is no onUpdate control, which might be on purpose, as you should really never have a reason to update the id of a row in a table.

Oh, and obviously, after you add the onDelete, don't forget to generate a migration to actually tweak things in the table!

Cheers!

Hi weaverryan, thank you very much for your kind reply!
Take care!

Hi Ryan,
Thanx voor de great and easy to follow tutorials!

I have a little weird thing with the migrations. When i run ./bin/console doctrine:migrations:diff i get the message that a mappedBy attribute is needed. When adding this "@ORM\OneToMany(targetEntity="Genus", mappedBy="genus_note")" and run again; i get the message that there are no changes detected. Unfortunately i can't run the migrate because there is no new file.

Any suggestion what's wrong?

Hi Artjan!

Actually, this is expected! For a detailed explanation, check out our Symfony2 tutorial, which talks about OneToMany relationships: http://knpuniversity.com/sc...

Basically, when you create a ManyToOne relation, this will cause the foreign key column to be added to the database (e.g. genus_id). But, when you add a OneToMany relation, this is *not* a new relationship: it's the *same* relationship seen from the opposite direction. In other words, you can say that "many GenusNote have one Genus" (ManyToOne) or you can say "one Genus has many GenusNotes" (OneToMany). So when you map the OneToMany, there is no change to the database: you're simply adding a convenience layer on Doctrine so that it's easier to use your relationship (e.g. you can now say $genus->getNotes() and it will query for all the related GenusNote objects).

Let me know if this makes sense! Understanding the "owning" versus "inverse" side of a relation is quite difficult. We're planning on making a Doctrine relations tutorial that will talk about this and ManyToMany relationships.

Cheers!

Hi Ryan.

Thanx for the quick response and the good explanation about the relationships. Everything works fine now!

Hi weaverryan,

Thanks for the great tutorial! I've followed along but tried a slightly different route, by creating the entities via doctrine using ```bin/console doctrine:generate:entity``` and choosing yml as the mapping format. This basically worked the same up until now when we're trying to save the relationships.

The problem that I'm having is that in this way, I need to map all the GenusNote entity's properties via GenusNote.orm.yml. Specifically, I need to define a type for the genus property on the GenusNote entity, but no matter what type I choose, I keep getting either that genus can't be converted to the type I choose or that the data for column genus in the database is too long.

Could you give me any advice on this? Should I just start over with the entities and follow your directions?

Thanks in advance!

N8

Hey Nate!

This is probably just a small mapping thing. The PHP code in GenusNote should look identical to mine and so will all the rest of the code (controller, form, etc). I think you already know that - but just for others :).

For the YAML-equivalent of the annotations, check out the first code-block here: http://symfony.com/doc/curr.... there's a YAML tab: you can use that to compare YAML with annotations. As you can see, the genus property should *not* be under the "fields" key, like normal properties/columns.

One quick word of warning with YAML: for various reasons, the Doctrine core team will remove YAML support from Doctrine version 3. So, unfortunately, YAML is not a great long-term plan (however, Doctrine already has tools to convert to different formats, like XML or annotations).

Cheers!

I used your tutorial to learn about saving relationships. I have a program that, in a loop, builds a bunch of sub objects and inserts them into a parent one-to-many. Then it needs to work on that Parent & SubObjects to verify some things. But when I go to read that parent after the flush, it doesn't know about the sub objects. I can call the entity manager "refresh" to get it to re-evaluate, but why do I have to do so? Shouldn't all the information be there?

$parentObject = new Parent_Object(); // one-to-many relation with SubObject

foreach(  $myLoopVar ) 
{
  // create new sub object
  $subObject = new Sub_Object();
 // save to parent
  $subObject->setParentObject($parentObject );
 // persist
  $em->persist($subObject)
}

  $em->persist($parentObject );
  $em->flush();

// now try to use $parentObject
foreach($parentObject->getSubObjects() as $subObject)
{
  dump($subObject);  // this loop is NOT entered!  
} 

// but if I refresh
$em->refresh($parentObject);
foreach($parentObject->getSubObjects() as $subObject)
{
  dump($subObject);  // now its entered and the subObjects are dumped!
} 

Hey Terry,

It's up to you to store all this info on the inverse side. It's not something required, but you may want to update inverse side as well of you're going to do some operations with these new data further. So after you setting the parent object, you also have to update ArrayCollection on inverse side, i.e. add sub object to the array collection, otherwise inverse side will be updated by Doctrine only on the next page load or after refresh() call:

foreach(  $myLoopVar ) 
{
  // create new sub object
  $subObject = new Sub_Object();
  // save to parent
  $subObject->setParentObject($parentObject );
  // update the inverse side:
  $parentObject->getSubObjects()->add($subObject);
  // persist
  $em->persist($subObject)
}

Or you can add ParentObject::addSubObject() method which will do some work for for you:

class ParentObject
{
  public method addSubObject(SubObject $subObject)
  {
    $subObject->setParentObject($this);
    $this->subObjects->add($subObject);
  }
}

// and then just call it:

foreach(  $myLoopVar ) 
{
  // create new sub object
  $subObject = new Sub_Object();
  $parentObject->addSubObject($subObject);
  $em->persist($subObject)
}

Actually, take a look at Synchronizing Owning & Inverse Sides for more info.

Cheers!

Got it. Thanks much!