Sequelize-typescript: populating the connecting object in many-to-many relationship

[satellink]

Hi!

I am not sure whether this is just missing feature, or I just don't understand enough of the library/sequelize, but i'll put this here anyway..

I have implemented a many-to-many relationship as described with ForeignKey's. However I am not sure how to control the query output of the connecting object. This is the object connecting Team and User, and having number as additional info:

@DefaultScope({
  include: [() => Team, () => User],
  attributes: ['number']
})
@Table
export default class TeamPlayer extends Model<TeamPlayer> {

  @ForeignKey(() => User)
  @Column
  userId: number;

  @ForeignKey(() => Team)
  @Column
  teamId: number;

  @Unique
  @Column
  number: number;
}

When I query for eg. teams, I do get the following object out:

{
  "id": 1,
  "name": "Doe's Team",
  "players": [
    {
      "id": 1,
      "firstName": "John",
      "lastName": "Doe",
      "TeamPlayer": {
        "userId": 1,
        "teamId": 1,
        "number": 32
    }
 }]
}

Things I want to improve here:
1) I want to rename the TeamPlayer to something like "membership"; but not by changing the name of the class
2) the content of TeamPlayer should not have the id`s, but I want it to contain the data of the team, for example:

{
  "firstName": "John",
  "lastName": "Doe"
  "membership": {
     "number": 32,
   }
 }

Any ideas whether this is possible?

(edit: I've also posted a question about this in stackoverlow: https://stackoverflow.com/questions/45130037/sequelize-typescript-many-to-many-relationship-model-data-with)

[RobinBuschmann]

Hi @satellink sequelize does not seem to provide any functionality that is intent to really solve your first issue. But you could explicitly set modelName in the Table annotation to membership:

@Table({modelName: 'membership'})
export default class TeamPlayer extends Model<TeamPlayer> { ... }

This will result in what you want. So that you will get membership instead of TeamPlayer. As I already said, modelName was not intent to be used for that. But as far as I know, this should currently not lead to any issues (The name of the table will still be TeamPlayer unless you change it with tableName property).
But I will investigate this a little bit deeper - Probably there is something sequelize-typescript can do to finally solve this.

Regarding 2.:
When retrieving relational data by defining the include options, you can tell sequelize which attributes of the through table you want to present on your model as well:

Team.findAll({
  include: [{
    model: User,
    through: {
       attributes: ['number']
    }
  }]
})

I hope this helps.

[satellink]

Hi, thanks for the answer!

Regarding 1) - I actually remember to try to implement that with pure sequelize too, and it was not trivial. It would be cool if this could be somehow solved!

I ended up implementing the TeamPlayer as a class with its own identifier at this point, and test the uniqueness of the relationship in the code level. But I would love to see a solution with the foreign keys, as that would be much simpler.

thanks for the tip about the through attributes; I completely overlooked that feature.

[RobinBuschmann]

You're welcome :)

Regarding:

I ended up implementing the TeamPlayer as a class with its own identifier at this point, and test the uniqueness of the relationship in the code level. But I would love to see a solution with the foreign keys, as that would be much simpler.

I don't really understand what you approach is. Can you provide an example?

[satellink]

This is what I do now:

team.ts

@Table
export default class Team extends Model<Team> {
  @AllowNull(false)
  @Column
  name: string;

  @HasMany(() => TeamPlayer, 'teamId')
  players: TeamPlayer[];
}

user.ts

@Table
export default class User extends Model<User> {

  @AllowNull(false)
  @Column
  firstName: string;

  @AllowNull(false)
  @Column
  lastName: string;

  @HasMany(() => TeamPlayer, 'userId')
  teams: TeamPlayer[];
}

team.player.ts

@Table
export default class TeamPlayer extends Model<TeamPlayer> {

  @BelongsTo(() => Team, 'teamId')
  team: Team;

  @BelongsTo(() => User, 'userId')
  user: User;

  @Column
  number: number;
}

And then in code I do simple check that the TeamPlayer with the same userId & teamId does not exist yet.

[RobinBuschmann]

Why do you need to check this manually? You could define teamId and userId as primary keys of TeamPlayer.

[satellink]

Your answer made me play around a bit; I though that I cannot use @ForeignKey directly on the team and user parameters (which I've already tried), but realized that I can actually make it work like this:

  @BelongsTo(() => Team, 'teamId')
  team: Team;

  @PrimaryKey
  @Column
  teamId: number;

  @BelongsTo(() => User, 'userId')
  user: User;

  @PrimaryKey
  @Column
  userId: number;

[RobinBuschmann]

..and if you add @ForeignKey to the userId and teamId properties, you can omit the foreign keys passed to @BelongsTo and @HasMany, like:

  @BelongsTo(() => Team)
  team: Team;

  @ForeignKey(() => Team)
  @PrimaryKey
  @Column
  teamId: number;

  @BelongsTo(() => User)
  user: User;

  @ForeignKey(() => User)
  @PrimaryKey
  @Column
  userId: number;

  /* ... */

  @HasMany(() => TeamPlayer)
  teams: TeamPlayer[];

  /* ... */

  @HasMany(() => TeamPlayer)
  players: TeamPlayer[];

[Patrax]

Hi I'm having a similar issue. I have tried both defining my join model explicitly as in this thread and using decorators, as the example below. When trying to create a ContentItem and assign it a Source I get the same webpack compile error in both scenarios in the same line that involves the method $add.

Webpack Compile Error

error TS2345: Argument of type 'Source | null' is not assignable to parameter of 
type 'Model<Source | null> | Model<Source | null>[]'.
  Type 'null' is not assignable to type 'Model<Source | null> | Model<Source | null>[]'.

Add a source to a new content item

const contentItem = new ContentItem({
  title: requestBody.title,
  url: requestBody.url
});

return Promise.resolve(
  Source.findOne<Source>()
  .then(source => {
    contentItem.$add("Source", source);
    return contentItem;
  })
  .then(contentItem => contentItem.save())
  .catch(e => {
    console.log("ERR", e)
    return e
  })
)

Source Model

import {Column, Model, Table, Unique, BelongsToMany} from "sequelize-typescript";

@Table({
  tableName: "Sources",
})
export class Source extends Model<Source> implements ISource {
  @BelongsToMany(
    () => ContentItem,
    "SourceContentItem",
    "contentItemId",
    "sourceId"
  )
  contentItems: ContentItem[];  

  @Column
  get name(): string {
    return this.getDataValue("name");
  }

  set name(value: string) {
    this.setDataValue("name", value);
  }

  @Unique
  @Column
  get uri(): string {
    return this.getDataValue("uri");
  }

  set uri(value: string) {
    this.setDataValue("uri", value);
  }
}

ContentItem Model

import {Column, Model, Table, BelongsToMany, Unique} from "sequelize-typescript";

@Table({
  tableName: "ContentItems",
  timestamps: true,
})
export class ContentItem extends Model<ContentItem> implements IContentItem {
  @BelongsToMany(
    () => Source,
    "SourceContentItem",
    "sourceId",
    "contentItemId"
  )
  sources: Source[];

  @Column
  get title(): string {
    return this.getDataValue("title");
  }

  set title(value: string) {
    this.setDataValue("title", value);
  }

  @Unique
  @Column
  get url(): string {
    return this.getDataValue("url");
  }

  set url(value: string) {
    this.setDataValue("url", value);
  }
}

[RobinBuschmann]

Hey, thanks for contributing. Does this error points to any file? Or can you provide any stack trace? Or even better an example project? thank you.

btw: You don't need to use get/set accessors, if you don't add any additional functionality in your getters and setters (sequelize will call this.getDataValue(...) internally anyway). Simply write:

@Column
title: string;

@Unique
@Column
url: string;

// .. and so on

[Patrax]

Hey thanks for the prompt response. The only error that webpack is showing is that one and it points to line contentItem.$add("Source", source); .

Argument of type 'Source | null' is not assignable to parameter of type 'Mode<Source | null>'.

I will make a small repo reproducing the error and add you as a contributor in a few minutes. :)

[RobinBuschmann]

Ah ok, this is because source can be null and $add expects a non-null value as second parameter. If findOne does not find any model, it resolves with null. So you need to check if source is truthy:

  Source
  .findOne<Source>()
  .then(source => {
    if(source) {
      contentItem.$add("Source", source);
    }
  })

[Patrax]

Oh wow, I didn't even know compilers could check for that. Thank you so much this resolved it!