Home > Blockchain >  Sequelize join two tables on id
Sequelize join two tables on id

Time:01-06

I have a table of book users and a table of movie users. I'm trying to return a list of the top 100 movie viewers, along with their book profile information. I want to join on ids, but I can't seem to find the right syntax.

This is what I've tried:

const mostActiveMovieWatchers = await MovieWatchers.findAll({
      order: [
        ['moviesWatched', 'DESC'],
      ],
      limit: '100',
      include: [{
        model: BookReaders,
        where: { 
          userId: '$MovieWatchers.id$'
        },
        required: true
      }]
    });

I've also seen examples where the where clause looks something like this where: ['userId = id']

CodePudding user response:

Before join tables you need create association:

BookReaders.hasMany(MovieWatchers, { foreignKey: 'bookId' });

MovieWatchers.belongsTo(BookReaders, { foreignKey: 'bookId' });

Then, you can use the include option in a find or findAll method call on the MovieWatchers model to specify that you want to include the associated BookReaders data:

MovieWatchers.findAll({
  include: [
    {
      model: BookReaders,
    },
  ],
}).then((movies) => {
  // array of movies including books
});
  • Related