Get data by array of ids

Question

I'm trying to make a filter songs for a my project, i have an array of genres ids that i retrieve from the client, i do this for get all the audios from one id:

Audio.findAll({
  include: [{
    model: db.Genres,
    as: "genres",
    where: {
      id: {
        [Op.and]: [1]
      }
    },
  }]
})

But i need to get all audios from an array of genres/mods ids, also want filter audios by genre ids and mods ids, but i don'y know how to make it, any idea?

Song Model

const Audio =  sequelize.define('Audio', {
  id: {
    autoIncrement: true,
    type: DataTypes.INTEGER(30),
    allowNull: false,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
})
Audio.associate = function(models) {
  Audio.belongsToMany(models.Genres, {through: 'AudioGenres', foreignKey: 'id_audio', as: 'genres'})
  Audio.belongsToMany(models.Mods, {through: 'AudioMods', foreignKey: 'id_audio', as: 'mods'})
}

AudioGenreModel

const AudioGenres =  sequelize.define('AudioGenres', {
  id_audio: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    primaryKey: true,
    references: {
      model: 'Audio',
      key: 'id'
    }
  },
  id_genre: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    primaryKey: true,
    references: {
      model: 'Genres',
      key: 'id'
    }
})
AudioGenres.associate = function(models) {
  AudioGenres.belongsTo(models.Audio, {foreignKey: 'id_audio'})
  AudioGenres.belongsTo(models.Genres, {foreignKey: 'id_genre'})
};

AudioModModel

const AudioMods =  sequelize.define('AudioMods', {
  id_audio: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    primaryKey: true,
    references: {
      model: 'Audio',
      key: 'id'
    }
  },
  id_mod: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    primaryKey: true,
    references: {
      model: 'Mods',
      key: 'id'
    }
})
AudioMods.associate = function(models) {
  AudioMods.belongsTo(models.Audio, {foreignKey: 'id_audio'})
  AudioMods.belongsTo(models.Mods, {foreignKey: 'id_mod'})
};

Mods and Genres Model

const Mods =  sequelize.define('Mods', {
  id: {
    autoIncrement: true,
    type: DataTypes.INTEGER(30),
    allowNull: false,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
})
Mods.associate = function(models) {
  Mods.belongsToMany(models.Audio, {through: 'AudioMods', foreignKey: 'id_mod', as: 'audios'})
}

const Genres =  sequelize.define('Genres', {
  id: {
    autoIncrement: true,
    type: DataTypes.INTEGER(30),
    allowNull: false,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
})
Genres.associate = function(models) {
  Genres.belongsToMany(models.Audio, {through: 'AudioGenres', foreignKey: 'id_genre', as: 'audios'})
}

Answer 1

You should use the Op.in query operator when finding a column that is in an array of values. Many of the fields in your definition will be automatically created with the relationships, for AudioGenres and AudioMods you can initially create them with no fields.

// only define name, others will be generated
const Audio =  sequelize.define('Audio', {
  name: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
});
Audio.associate = function(models) {
  Audio.belongsToMany(models.Genres, { as: 'genres', through: 'AudioGenres', foreignKey: 'id_audio' })
  Audio.belongsToMany(models.Mods, { as: 'mods', through: 'AudioMods', foreignKey: 'id_audio' })
}

const Genres =  sequelize.define('Genres', {
  name: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
});
Genres.associate = function(models) {
  Genres.belongsToMany(models.Audio, { as: 'audios', through: 'AudioGenres', foreignKey: 'id_genre' })
}

const Mods =  sequelize.define('mods', {
  name: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
});
Mods.associate = function(models) {
  Mods.belongsToMany(models.Audio, { as: 'audios', through: 'AudioMods', foreignKey: 'id_mod' })
}

// create empty table, relationships added automatically
const AudioGenres = sequelize.define('AudioGenres', {}, { timestamps: false });
const AudioMods = sequelize.define('AudioMods', {}, { timestamps: false }); 

Use the Op.in query operator, arrays for table joins.

// use the Sequelize.Op query operators, specifically Op.in
const { Op } = require('sequelize');

// these are the values to use for the joins.
const myArrayOfGenreIds = [1, 2, ...];
const myArrayOfModIds = [1, 2, ...];

Query for values in an array joining both tables on the IDs in the arrays and returning results where one of them is not null.

// now query for Audios joined to Genres and Mods through relationship tables
const audios = await Audio.findAll({
  include: [
    {
      model: Genres,
      as: 'genres',
      attributes: [],
      where: {
        id: {
          [Op.in]: myArrayOfGenreIds,
        },
      },
    },
    {
      model: Mods,
      as: 'mods',
      attributes: [],
      where: {
        id: {
          [Op.in]: myArrayOfModIds,
        },
      },
    },
  ],
  // only return results from Audios
  group: [sequelize.col('audios.id')],
  having: {
    // check to see if the count of IDs we got back matches the count we expect
    [Op.and]: [
      sequelize.where(
        sequelize.fn('COUNT', sequelize.col('genres.id')), 
        myArrayOfGenreIds.length)
      ),
      sequelize.where(
        sequelize.fn('COUNT', sequelize.col('mods.id')), 
        myArrayOfModIds.length)
      ),
    ],
  },
});