lucid: Creating records with BelongsToMany relation and custom primary keys not working

I’m having an issue creating records with a model relationship as described below. It seems as if its trying to insert the primary key value of the foreign table rather than the related primary key value defined in the relation.

Tables:

parties:    id(pk), type(varchar) # type is either U=user, T=team
user:       id(pk), party_id(int)
team:       id(pk), name(varchar), party_id(int)
team_user:  team_party_id(int), user_party_id(int)

Relations:

class User extends Model {
  teams () {
    return this.belongsToMany('App/Models/Team', 'user_party_id', 'team_party_id', 'party_id', 'party_id')
  }
}

class Team extends Model {
  users () {
    return this.belongsToMany('App/Models/User', 'team_party_id', 'user_party_id', 'party_id', 'party_id')
  }
}

create a new team for a user and add them to it:

await user.teams().create({name: 'Team 1'})

SQL executed:

insert into `parties` (`type`) values (?);
insert into `teams` (`created_at`, `name`, `party_id`, `updated_at`) values (?, ?, ?, ?);
select `team_party_id`, `user_party_id` from `team_user` where `user_party_id` = ?;
insert into `team_user` (`team_party_id`, `user_party_id`) values (?, ?);

Instead of inserting the value of teams.party_id in the team_user table, it uses teams.id even though the relation has been set up with relatedPrimaryKey as party_id (see Relations section above).

I think I have narrowed down the problem to here:

// @adonisjs/lucid/src/Lucid/Relations/BelongsToMany.js from line 772 onwards: 

async save (relatedInstance, pivotCallback) {
    
    // ...

    const pivotRows = await this.attach(relatedInstance.primaryKeyValue, pivotCallback)
    // should be: `await this.attach(relatedInstance[this.relatedPrimaryKey], pivotCallback)`

    // ...
  }

Instead of passing relatedInstance.primaryKeyValue into the this.attach() relatedInstance[this.relatedPrimaryKey] as per the relatedPrimaryKey value defined in the relationship.

For the time being I had to use this solution to achieve the result I wanted:

await user.teams().attach((await use('App/Models/Team').create({name: 'Team 1'})).party_id)

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 15 (7 by maintainers)

Most upvoted comments

Would you try it from github develop branch and lemme if all works fine. Feel free to try it under different use cases

thetutlage on Dec 14, 2017

First of all, thank you for taking the time to compose an elaborate response for me.

Sadly this does not address the issue entirely.

I will try to detail eveything below. Apologies for some repetition.

Users and Teams

Teams can have multiple users.
Users can belong to multiple teams.
Users can belong to no teams.

Products

A user can create a product and pick (from a dropdown) who the product is owned by. The owner options are 1) the user who created the product or; 2) a team the user is a member of but not both of these options.
If a user decides the product owner is one of the teams he belongs to then all users of that team can view/edit the product. Alternatively if he decides to pick the owner as himself only then only he can view/edit the product.

Party

In order to easily deal with table joins and simplify all the code, all products belong to a party rather than to a team or a user. The concept of a user or team is unknown to the product. This means querying the database for all products belonging to a user and all their teams is as simply as select * from products where party_id in (<user id>, ...<team ids>)
It removes the need to do checking everywhere to see if we are dealing with teams or a user because teams and users are under the same umbrella (the party umbrella).
more reading here regarding party model

Example

Pivot table

CREATE TABLE `team_user` (
  `team_party_id` int(10) unsigned NOT NULL,
  `user_party_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`team_party_id`,`user_party_id`),
  KEY `team_user_user_party_id_foreign` (`user_party_id`),
  CONSTRAINT `team_user_team_party_id_foreign` FOREIGN KEY (`team_party_id`) REFERENCES `parties` (`id`),
  CONSTRAINT `team_user_user_party_id_foreign` FOREIGN KEY (`user_party_id`) REFERENCES `parties` (`id`) 
)

Relations

// User.teams()
this.belongsToMany('App/Models/Team', 'user_party_id', 'team_party_id', 'party_id', 'party_id')

// Team.users()
this.belongsToMany('App/Models/User', 'team_party_id', 'user_party_id', 'party_id', 'party_id')

Tests

// get users -- this works
const team = Team.first()
await team.users().fetch() // gives me all users in team using users.party_id on pivot table

// get teams -- this works
const user = User.first()
await user.teams().fetch() // gives me all teams that user is a member of using teams.party_id on pivot table

// create team and add user as a member -- does not work
const user = User.first()
await user.teams().create({name: 'Team 1'}) // creates a team then uses the teams.id on the pivot table instead of the teams.party_id which causes an error

Currently my work around:

const team = await Team.create({name: 'Team 1'})
await user.teams().attach(team.party_id)

So as you can see my issue is creating teams and attaching an existing user (or attaching users to an existing team).

From what I understand the relatedPrimaryKey parameter you pass into the belongsToMany() should act as the joining factor regardless of if you are doing a fetch or a create/update but maybe you can shed some light on that for me.

Thanks

simontong on Dec 11, 2017