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?

score 1 · Accepted Answer · answered Feb 08 '18 at 17:49

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

score 0 · Answer 2 · edited Feb 22 '18 at 20:32

0

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.

edited Feb 22 '18 at 20:32

halfer

19,824
17
99
186

answered Feb 08 '18 at 15:59

Nikola Gavric

3,507
1
8
16

I tried this, but sadly doctrine2 does not has the attach method, problem being doctrine2 was a requirement duo to faster speed on big databases and thus I cannot use Eloquent :( , also the add method is a doctrine method which will automatically fill the profile_has_skills table, but it's missing the "level" parameter, which is odd since I am adding the level inside the controller, or at least I thought so – killstreet Feb 08 '18 at 16:03
I just stumbled upon https://stackoverflow.com/questions/15616157/doctrine-2-and-many-to-many-link-table-with-an-extra-field which means that your profile_has_skills entity won't be able to have additional fields, which in this case is level, am I wrong? – Nikola Gavric Feb 08 '18 at 16:29
Ah what a bummer! though, I guess I have to add my own functions to doctrine to achieve this. – killstreet Feb 08 '18 at 16:33
True, I think it's a big minus towards Doctrine, but, good luck :D – Nikola Gavric Feb 08 '18 at 16:39
I found a working solution, in case of intrest you can read it above. – killstreet Feb 08 '18 at 17:49

Laravel doctrine2 many to many relation with extra column

2 Answers2