CODEWITHSUNDEEP
doctrine-ormzend-framework2cascadeone-to-manyjoincolumn
Wilt
Wilt

Reputation: 44422

Zend2 Doctrine2 One-To-Many uni-directional with join table, delete cascade issue

I'm having some problems with the following... I have a table with phone numbers. I want to use the same table for both users and companies. A user can have several phone numbers and a company too. So i want a One to many unidirectional relationship with two different join tables. One linking phone numbers to users, the other linking phone numbers to companies. This is solution following the doctrine2 manual chapter 5.9 found here: (click)

My users entity holds this code:

/** @ORM\ManyToMany(targetEntity="Application\Entity\PhoneNumber")
  * @ORM\JoinTable(name="user_phone_number_linker",
  *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
  *      inverseJoinColumns={@ORM\JoinColumn(name="phone_number_id", referencedColumnName="id")}
  *      )
  */
  protected $phone_numbers;

I use a unidirectional one to many because the thing is I can't make a bidirectional one because if I refer back to the user I cannot use the same phone number entity class for the company. Now it all works fine, but when I delete a phone number I get the following error: An exception occurred while executing 'DELETE FROM phone_number WHERE id = ?' with params {"1":1}:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (database/user_phone_number_linker, CONSTRAINT user_phone_number_linker_ibfk_11 FOREIGN KEY (phone_number_id) REFERENCES phone_number (id))

If I set the ON DELETE CASCADE value manually in the database it works fine, but this is not the idea of using doctrine2 and I think I should be able to solve it within the code without going to my phpMyAdmin panel. Somehow the cascading from the phone number towards the join table should be initiated on deletion, but without making a reference back to the join table from the phone_number entity.

Hope someone smart can help me solve this.

EDIT

In the meantime I learned a lot more about Doctrine2 and reviewing my old question made me realize that this is not a correct way to store several phoneNumbers in one table in the first place. To be able to store user phone numbers and company phone numbers in the same table I should use table inheritance with a discriminator column. The column should hold some user/company discriminator.

Because of this column the doctrine ORM will "know" if that phoneNumber is actually a user or a company phone-number. I need to make two different entity definitions following the single table inheritance mapping principles from the doctrine 2 specs.

One class UserPhoneNumber will have a many-to-one relationship with User the other called CompanyPhoneNumber a one-to-many relationship with Company. I don't necessarily need a join column, the user_id or company_id columns can be in the phone-number table. In the User class the Company association is omitted and in the Company class the User association is omitted (database should allow null values for those columns).

If I do use a join table it is according to the one-to-many unidirectional with join table description in the Doctrine2 specs

READ MORE

Otherwise you can also read more on associations and cascade issues here on this elaborate Doctrine2 in depth website.

Upvotes: 1

Views: 763

Answers (2)

Wilt
Wilt

Reputation: 44422

My bad, The phone number user relationship is regarded a Many-To-Many relationship, so if want to remover the phone number I should not only remove the phone number itself, but I have to explicitly remove the phone number from the user as well. So in the Controller like this:

// Remove the phone number user connection from the database
$user->removePhoneNumber($phone_number);

// Remove the phone number from the database
$em->remove($phone_number);

I just thought the unique restriction which makes the relationship to a unidirectional One-To-Many would be enough to make doctrine take care of it. That was not correct.

Upvotes: 0

lluisaznar
lluisaznar

Reputation: 2393

As you said, your relation is unidirectional. You've defined a relation from Users to PhoneNumbers. The cascade delete will work when you delete a User, it will remove all rows in user_phone_number_linker because that's the relation you've defined.

If you want to do it the other way, you've got to create a relation from PhoneNumbers to Users. Doctrine needs it to work for you. But you have the problem that the entity is shared by two other entities, Users and Companies.

Keep in mind that entities are objects, not tables. So you could try to create two entities to the same table, one named PhoneNumberUsers and the other PhoneNumberCompanies. This way you'll be able to create the needed relation to do the cascade delete. I haven't tested by myself, but I think it could work.

By the way, you can remove the oncascade parameter on the Users' entity join table. I've the same scenario as you with users and roles, and I haven't used it. I think it's only needed when you want to cascade from entity to entity. I'm not sure about that, but that's what I've been experiencing until now.

Upvotes: 1

Related Questions