So I'm beginning to struggle with Doctrine2 when it comes to a many-to-many relation for a project where the relation has 1 extra column.
I have the following tables:
Skills
profile_has_skills
Now I added the level column later on, and noticed some problems happening, of course I am missing level now whenever I try to create the relation. My question is, with the code below, how would I go over to add this in my doctrine?
My controller:
public function store(Request $request)
{
$time = new DateTime();
$this->validate($request, [
'name' => 'required',
'lastname' => 'required',
'gender' => 'required',
'profile_skills' => 'required'
]);
$this->em->getConnection()->beginTransaction();
try {
$profile = new Profile(
$request->input('company_id'),
$request->input('name'),
$request->input('lastname'),
$request->input('gender'),
new DateTime(),
$time,
$time
);
$company = $this->em->getRepository(Company::class)->find($request->input('company_id'));
$profile->addCompany($company);
foreach($request->input('profile_skills') as $skill => $level) {
$skill = $this->em->getRepository(Skill::class)->find($skill);
$skill->level = $level;
$profile->addSkill($skill);
}
$this->em->persist($profile);
$this->em->flush();
$this->em->getConnection()->commit();
} catch (OptimisticLockException $e) {
$this->em->getConnection()->rollBack();
throw $e;
}
return redirect(route('profiles.index'));
}
My ProfileHasSkill entity looks as follow:
/**
* @ORM\Entity
* @ORM\Table(name="profile_has_skill")
*
*/
class ProfileHasSkill
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
protected $id;
/**
* @Column(type="integer", name="profile_id")
*/
protected $profile_id;
/**
* @Column(type="integer", name="skill_id")
*/
protected $skill_id;
/**
* @Column(type="integer", name="level")
*/
protected $level;
/**
* @param $profile_id
* @param $skill_id
* @param $level
*/
public function __construct($profile_id, $skill_id, $level = 0)
{
$this->profile_id = $profile_id;
$this->skill_id = $skill_id;
$this->level = $level;
}
And my addSkill method inside the profile entity is as follow:
public function addSkill(Skill $skill)
{
if ($this->skills->contains($skill)) {
return;
}
return $this->skills->add($skill);
}
But anytime I try to run this it gives me the following error
An exception occurred while executing
'INSERT INTO profile_has_skill (profile_id, skill_id) VALUES (?, ?)'
with params [3, 2]: SQLSTATE[HY000]: General error: 1364 Field 'level'
doesn't have a default value
Now I know one way to get rid of this error is setting a default value in the database, but I much rather just find out why it's not picking up my skill level that I'm also passing?
As per my solution which has worked, by reading another question passed by @Nicola Havric - Read as follow That doctrine does not support extra columns in a many-to-many relation. Thus you should use the relation as it's own entity. My own solution was to change the way I wanted it to run with flushing.
In my controller I changed my code as follow:
try {
$profile = new Profile(
$request->input('company_id'),
$request->input('name'),
$request->input('lastname'),
$request->input('gender'),
new DateTime(),
$time,
$time
);
$company = $this->em->getRepository(Company::class)->find($request->input('company_id'));
$profile->addCompany($company);
//Flush the user, so I can grab it's profile ID
$this->em->persist($profile);
$this->em->flush();
foreach($request->input('profile_skills') as $skill => $level) {
$skill = $this->em->getRepository(Skill::class)->find($skill);
$skill->level = $level;
$profile->addSkill($skill);
}
$this->em->getConnection()->commit();
Inside my Profile Entity function:
public function addSkill(Skill $skill)
{
//I left this check since it only checks if the relation is set already. If so, it will skip it.
if ($this->skills->contains($skill)) {
return;
}
//Since this function gets called inside a loop, I can call the entity to add a new "relation" to the table.
(new ProfileHasSkill($this->getId(), $skill, $skill->level))->addSkill($this->getId(), $skill, $skill->level);
return true;
}
Inside my ProfileHasSkill entity:
public function addSkill($profileId, $skill)
{
//Creating a new ProfileHasSkill inside the table.
$profileSkill = new ProfileHasSkill(
$profileId,
$skill->getId(),
$skill->level
);
/*Since I do a roll-back inside my controller in case something goes wrong.
I decided to add the flush here.
As far no additional checks where needed in my case
since I require a Profile instance and a Skill instance inside the Profile entity.*/
EntityManager::persist($profileSkill);
EntityManager::flush();
}