How to correctly configure and count a belongsToMany association in Sequelize?

Question

I'm having problems this belongsToMany thing in Sequelize.

I'm creating a video content site. One video can be in many lists and obviously, a list can have many videos(n:n). A video can only belong to one channel (1:n). A list, video and channel are associated with an account (all 1:n).

I want to get the all the lists with the number of videos in each one.

So I created the models this way:

Video:

export class Video extends Model {
  public static associations: {
    channel: BelongsTo;
    account: BelongsTo;
    list: BelongToMany;
  };

  public id: string;
  public title: string;
  public desc: string;
  public createdAt: Date;
  public updatedAt: Date;

  public channelId: string;
  public channel: Channel;
  public getChannel: BelongsToGetAssociationMixin<Channel>;
  public setChannel: BelongsToSetAssociationMixin<Channel, string>;

  public accountId: string;
  public account: Conta;
  public getAccount: BelongsToGetAssociationMixin<Account>;
  public setAccount: BelongsToSetAssociationMixin<Account, string>;
}

Video.init(
  {
    title: STRING(100),
    descricao: STRING(500),
  },
  {
    sequelize,
    modelName: 'videos',
    hooks: {
      beforeCreate: (model, options) => {
        model.id = uuidv4();
      }
    }
  }
);

import { Channel } from './channels';
export const channel = Video.belongsTo(Channel, { foreignKey: 'channelId' });

import { Account } from './accounts';
export const account = Video.belongsTo(Account, { foreignKey: 'accountId' });

import { List } from './lists';
export const lists = Video.belongsToMany(List, {
  foreignKey: 'videoId' ,
  through: 'videos_list',
  timestamps: false
});

List:

export class List extends Model {
  public static associations: {
    account: BelongsTo;
    videos: BelongsToMany;
  };

  public id: string;
  public name: string;
  public desc: string;
  public createdAt: Date;
  public updatedAt: Date;

  public accountId: string;
  public account: Conta;
  public getAccount: BelongsToGetAssociationMixin<Account>;
  public setAccount: BelongsToSetAssociationMixin<Account, string>;

  public videos: Video[];
  public getVideos: BelongsToManyGetAssociationsMixin<Video>;
  public setVideos: BelongsToManySetAssociationsMixin<Video, string>;
  public addVideo: BelongsToManyAddAssociationMixin<Video, string>;
  public addVideos: BelongsToManyAddAssociationsMixin<Video, string>;
  public createVideo: BelongsToManyCreateAssociationMixin<string>;
  public countVideos: BelongsToManyCountAssociationsMixin;
  public hasVideo: BelongsToManyHasAssociationMixin<Video, string>;
  public removeVideo: BelongsToManyRemoveAssociationMixin<Video, string>;
  public removeVideos: BelongsToManyRemoveAssociationsMixin<Video, string>;
}

Lista.init(
  {
    name: STRING(100),
    desc: STRING(500),
  },
  {
    sequelize,
    modelName: 'lists',
    hooks: {
      beforeCreate: (model, options) => {
        model.id = uuidv4();
      }
    }
  }
);

import { Account } from './accounts';   
export const account = Lista.belongsTo(Account, {
  foreignKey: 'accountId',
  as: 'account'
});

import { Video } from './videos';
export const videos = List.belongsToMany(Video, {
  foreignKey: 'listId',
  through: 'videos_list',
  timestamps: false
});

and I'm doing the query like this:

  List.findAll({
    attributes: [
      'name', 'desc',
      [ Sequelize.fn("COUNT", Sequelize.col("videos.id")), "countVideos" ]
    ],
    include: [{ model: Video, as: 'videos', attributes: [] }],
    group: ['videos.listId'],
    order: [['name', 'DESC']]
  })

But I'm getting the following error:

DatabaseError [SequelizeDatabaseError]: column videos.listId does not exist

What am I doing wrong here ?

I listed only List and Video models that I think were relevant. If you need any other please let me know and I'll include it.

Answer 1

In List.belongsToMany(Video... you have videoId as FK instead of listId.