Get data that contains all the ids of the array

Question

I'm trying to make a filter songs, 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 that contains all the ids from array of genres/moods, also want filter audios by genres ids and moods ids, but i don't know how to make it, any idea? (One song can have many genres/moods)

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.Moods, {through: 'AudioMoods', foreignKey: 'id_audio', as: 'moods'})
}

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'})
};

AudioMoodModel

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

Moods and Genres Model

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

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

score 0 · Accepted Answer · answered Nov 04 '20 at 15:12

0

I suppose you should add all conditions in AND operator in both include options like this:

Audio.findAll({
  include: [{
    model: db.Genres,
    as: "genres",
    where: {
      [Op.and]: [{ id: 1}, { id: 3},{ id: 2}]
    },
  }, {
    model: db.Moods,
    as: "moods",
    where: {
      [Op.and]: [{ id: 4}, { id: 5},{ id: 6}]
    },
  }]
})

answered Nov 04 '20 at 15:12

Anatoly

20,799
3
28
42

I try this but this query returns me a void array – Ricardo Alvarado Nov 04 '20 at 16:08
It means that you have no audios that satisfy all conditions. My my example they should have genres with ids = 1,2,3 and at the same time have moods with ids = 4,5,6. Was it what you want to achieve? – Anatoly Nov 04 '20 at 16:29
I'm trying this https://imgur.com/a/qfBol4o but returns void array(the variables name can change a little) – Ricardo Alvarado Nov 04 '20 at 18:35
Can you add an SQL-query that corresponds to this Sequelize query to your post? – Anatoly Nov 04 '20 at 18:39
Here are the query generated by sequelize https://justpaste.it/62hmu , temporaly i'm dping the correct query(https://justpaste.it/5kh8k) manually and using "sequelize.query" – Ricardo Alvarado Nov 05 '20 at 14:14
In correct SQL you used OR which means "find any of" but you wrote in the question "all the ids from array of genres/moods" that means you should use AND as I wrote in my answer – Anatoly Nov 05 '20 at 15:20

Get data that contains all the ids of the array

1 Answers1