Laravel doctrine2 many to many relation with extra column

Question

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:

• Profiles
  • id
  • extra data

• Skills

  • id
  • name

• profile_has_skills

  • profile_id
  • skill_id
  • level

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?

Answer 1

The thing with many-to-many relationships is that any additional columns other than two primary keys from both tables are considered pivot columns, when attaching entities to such relationships you want to use the method attach which accepts array of ids as first parameter and an array with pivot columns, take the following into consideration.

public function addSkill(Skill $skill)
{
    if ($this->skills->contains($skill)) {
        return;
    }
    //Dunno what this method does
    return $this->skills->add($skill);
    //But this is the correct way of adding a skill
    $this->skills->attach($skill->id, ['level' => $skill->level]);
}

Hope this can clarify few things even though Eloquent was used as an example; here is the manual link for the above code.

Answer 2

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();
}