Adding additional columns to pivot table

Hello all,

I have read the documentation regarding attach() and using the callback to have additional columns populated, but to be honest I’m still not coming right.

I have people allocated to teams, both have a belongsToMany relationship.

I am passing the team information using form input arrays.

<input type="hidden" name="teamData[0][team_id]">
<input type="hidden" name="teamData[0][position]">
<input type="hidden" name="teamData[0][startDate]">
<input type="hidden" name="teamData[0][endDate">

<input type="hidden" name="teamData[1][team_id]">
<input type="hidden" name="teamData[1][position]">
<input type="hidden" name="teamData[1][startDate]">
<input type="hidden" name="teamData[1][endDate">

etc...

That sends an array to the controller when I submit the form that looks like this:

[
  {
    team_id: '3',
    position: 'farsegf',
    teamStartDate: '2020-05-12',
    teamEndDate: '2020-05-12'
  },
  {
    team_id: '7',
    position: 'fzsdx',
    teamStartDate: '2020-05-19',
    teamEndDate: '2020-05-22'
  },
  {
    team_id: '7',
    position: 'zfgx bfxsd',
    teamStartDate: '2020-05-12',
    teamEndDate: '2020-05-21'
  }
]

I am assuming that I would need something like the following, but I’m trying to understand how I could get around needing to do this in a loop of some sort.

await person.teams().attach(teamData, (row) => {
    row.position = teamData.position
    row.startDate = teamData.startDate
    row.endDate = teamData.endDate
})

Any direction would be appreciated!

Thanks in advance.

Hey @kJones.

I’m sure you’ve added the startDate and endDate fields already. Next, you have to use loop. You can’t escape it based on the structure of your payload.

const Team = use('App/Models/Team');

const data = [
  {
    team_id: '3',
    position: 'farsegf',
    teamStartDate: '2020-05-12',
    teamEndDate: '2020-05-12',
  },
  {
    team_id: '7',
    position: 'fzsdx',
    teamStartDate: '2020-05-19',
    teamEndDate: '2020-05-22',
  },
  {
    team_id: '7',
    position: 'zfgx bfxsd',
    teamStartDate: '2020-05-12',
    teamEndDate: '2020-05-21',
  },
];

for (const key in data) {
  const item = data[key];
  const team = await Team.find(item.team_id);
  await team.positions().create({
    position: item.position,
    teamStartDate: item.teamStartDate,
    teamEndDate: item.teamEndDate,
  });
}

Sorry, going back and reading my question I realise that I worded it poorly.

That information above gets sent when adding a person to the database. The team information is in addition to that. So the first step is creating that record. I have this:

        const person = new Person()

        let isManager = "N"
        if(request.all().isManager == "Y"){
            isManager = "Y";
        }

        person.employeeID  = request.all().employeeID;
        person.vertical_id  = request.all().vertical_id;
        person.firstName  = request.all().firstName;
        person.surname  = request.all().surname;
        person.employmenttype_id  = request.all().employmenttype_id;
        person.manager  = request.all().manager;
        person.officialTitle  = request.all().officialTitle;
        person.map_title_id  = request.all().map_title_id;
        person.location_id  = request.all().location_id;
        person.user_id  = request.all().user_id;
        person.isManager  = isManager;
        person.startDate  = request.all().startDate;
        person.endDate  = request.all().endDate;

        const teamData = request.all().teamData;

Once that’s complete I would need to add the team information to the person_team pivot table. Which would include the person_id which has just been created, and the team_id, position, start, and end date from the data mentioned in the first post.

Previously when I have added information to a pivot table I’ve used attach(), so I assumed it would be the same this time. Is that incorrect?

Many thanks again for your continued help.

You can use the attach() method if your JSON is structured in a way that each object in the JSON array has unique data for direct insert, or the create() method (which I proposed based on the structure of your JSON.

Okay, I have tried this a few ways and I’m still not coming right.

...
        await person.save();

        const teamData = request.all().teamData;

        for (const key in teamData) {
            const item = teamData[key];
            await person.teams().create({
                team_id: item.team_id,
                position: item.position,
                teamStartDate: item.teamStartDate,
                teamEndDate: item.teamEndDate,
            });
        }

This attempts to add the information into the teams table, which isn’t correct, as it should go into the person_team pivot table

I also tried changing it to attach() which attempts to insert into the correct table, but as you mentioned because of the data format, it was trying to add in the JSON as the content of one column.

Perhaps if I restructure the data on the server side to what attach() would accept? Is there somewhere I can see what that structure should be?

Thanks again

1 Like

Okay. I think I got there :smiley:

Here’s what I used for anyone else that’s looking:

for (const key in teamData) {
  const item = teamData[key];
  await person.teams().attach(item.team_id, (row) => {
    row.position = item.position
    row.startDate = item.teamStartDate
    if(item.teamEndDate !== ""){
      row.endDate = item.teamEndDate
    }
  });
}

The if statement is in there because the endDate is not required.

Thanks again @ndianabasi, I wouldn’t have got there without your help!

1 Like