Sequelize - JOIN query table with AND operator

0
votes

I'm using sequelize to handle SQLite database used by Electon app. Application let the user search for music based on selected topics, moods and so on.

I'm trying to build search mechanism that allow to select multiple moods and the function should return tracks that have all of the selected moods

Here is mentioned above database simplified model:

enter image description here

Also the sequelize relation between models are set


db.moods.belongsToMany(db.tracks, {
  through: db.moodsTracks,
  foreignKey: 'uuid',
});
db.tracks.belongsToMany(db.moods, {
  through: db.moodsTracks,
  foreignKey: 'trackId',
});
db.moods.hasMany(db.moodsTracks, {foreignKey: 'uuid'});
db.tracks.hasMany(db.moodsTracks, {foreignKey: 'trackId'});

Now I'm trying to find all tracks that contain has specific moods

let tracks = await db.tracks.findAll({
  include: [{
    model: db.moods,
    required: true,
    where: uuid: {
      [Op.and]: ['MOOD-UUID-1', 'MOOD-UUID-2']
    }
  }],
})

(first try fail)

I have tried to log generated by sequelize code and its returns:

INNER JOIN `moodsTracks` AS `moodsTracks` ON `tracks`.`id` = `moodsTracks`.`trackId` 
AND (
  `moodsTracks`.`uuid` = 'MOOD-UUID-1' 
  AND `moodsTracks`.`uuid` = 'MOOD-UUID-2'
)

Then I have try to build raw SQLite query 

SELECT 
  COUNT(trackid), 
  * 
FROM 
  `tracks` AS `tracks` 
  INNER JOIN `moodstracks` AS `moodsTracks` ON `tracks`.`id` = `moodstracks`.`trackid` 
WHERE 
  (
    `moodsTracks`.`uuid` = 'MOOD-UUID-1' 
    OR `moodsTracks`.`uuid` = 'MOOD-UUID-2'
  ) 
GROUP BY 
  (`moodsTracks`.`trackId`) 
HAVING 
  COUNT(trackid) = 2;

I'm aware that isn't great solution, but it works in SQL console.

Questions: Is there any other way to solve that kind problem? Maybe I use AND operator wrongly

If not, I will try to implement that SQL code above. Is there any documentation for HAVING keyword in sequelize, i didn't found any thing like this on official web page

2
node.jssequelize.jsjointable

2 Answers

0
votes

Why are you using [Op.and] when in your raw query you are using 'OR'?

0
votes

This solution works for me:

let tracks = await db.tracks.findAll({
  include: [db.moods],
  group: 'moodsTracks.trackId',
  include: [{
    model: db.moods,
    where: {
      uuid:['UUID1', 'UUID2']
    },
    having: ["COUNT(DISTINCT moodsTracks.uuid) = 2"]
  }]
})