Is there a way to get all Entity records through a custom pivot table

Question

I am trying to create a tweet bookmarking feature in my project. Where a user can save tweets to view them later. I am able to hit an endpoint and save a bookmark table record given a userId and tweetId. I'm having trouble figuring out how to return all bookmarked tweets using typeorm. One user can have many bookmarks.

I have the three following entities in a mysql database

tweet.entity.ts

@Entity()
export class Tweet {
    @PrimaryGeneratedColumn()
    public id?: number;

    @Column('text')
    public text: string;

    @ManyToOne(() => User, (user: User) => user.tweets)
    public user: User;

    @OneToMany(() => Comment, (comment: Comment) => comment.tweet)
    public comments: Comment[];
}

user.entity.ts

@Entity()
class User {
  @PrimaryGeneratedColumn()
  public id?: number;
 
  @Column({ unique: true })
  public email: string;

  @OneToMany(() => Tweet, (tweet: Tweet) => tweet.user)
  public tweets: Tweet[];
}

bookmark.entity.ts

@Entity()
export class Bookmark {
    @PrimaryGeneratedColumn()
    public id?: number;
    
    @Column()
    public userId: number;
    
    @Column()
    public tweetId: number;    
}

Answer 1

One solution using query builder:

const items = await dataSource
    .createQueryBuilder(tweet, "tweet")
    .innerJoin("bookmark", "bookmark", "bookmark.tweetId = tweet.id")
    .where("bookmark.userId = :userId", { userId: userId })
    .getMany();

You can also declare a many-to-many relation between Tweet and User with Bookmark as pivot table:

user.entity.ts

@ManyToMany(type => Tweet)
@JoinTable({
    name: "bookmarks", // pivot table name
    // Custom column name
    // joinColumn: {
    //    name: "userId",
    //    referencedColumnName: "id"
    // },
    // inverseJoinColumn: {
    //    name: "tweetId",
    //    referencedColumnName: "id"
    // }
})
bookmarks: Tweet[];

Usage:

userRepository.find({
  relations: ["bookmarks"],
})

More info: https://github.com/typeorm/typeorm/blob/master/docs/relations.md#jointable-options