I have the following table with many to many association between them.

content-modal.js

const Content = sequelize.define('Content', {
    title: DataTypes.STRING,
    name: DataTypes.TEXT,
    duration: DataTypes.INTEGER,
    ...
  }, {
    timestamps: true,
    paranoid: true,
  });

category-modal.js

  const Category = sequelize.define('Category', {
    name: DataTypes.STRING,
    type: DataTypes.STRING,
  }, {
    timestamps: true, 
    paranoid: true,
  });

content-category-modal.js

  const ContentCategory = sequelize.define('ContentCategory', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    categoryId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: { model: 'Category', key: 'id' },
    },
    contentId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: { model: 'Content', key: 'id' },
    },
    categoryType: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {});
  ContentCategory.associate = function(models) {
    models.Category.belongsToMany(models.Content, { through: ContentCategory });
    models.Content.belongsToMany(models.Category, { through: ContentCategory });
  };

Here each content has fixed no of category. So whenever I query through database using JOIN for one of the category I will be getting just the category I have passed on to where clause. For instance say there is the following field in the table:

Content Table

Category Table

Content Category Table

Here when I filter for all the videos where category is animation using the following sequelize query:

//styleId=7, topicId=null
const { topicId, styleId } = req.query; 
return db.Content.findAll({
   include: [{ 
       model: db.Category,
       attributes: ['id', 'name', 'type'],
       where: { id: 7 }
   }],
})

I get the content with only one of the two categories associated with a video which is as expected from the query:

data: [{
   "id": 2,
   "title": "big_buck_bunny.mp4",
   "name": "1669976024371.mp4",
   "Categories": [{
      "id": 7,
      "name": "Animation",
      "type": "style"
   }],
},
{
   "id": 3,
   "title": "newEra.mp4",
   "name": "1669976456758.mp4",
   "Categories": [{
      "id": 7,
      "name": "Animation",
      "type": "style"
   }],
}]

But I want to get all the category for each video if it matches the queried categoryId. i.e.

data: [{
   "id": 2,
   "title": "big_buck_bunny.mp4",
   "name": "1669976024371.mp4",
   "Categories": [{
      "id": 7,
      "name": "Animation",
      "type": "style"
   },{
      "id": 8,
      "name": "Awareness",
      "type": "topic"
   }],
},
{
   "id": 3,
   "title": "newEra.mp4",
   "name": "1669976456758.mp4",
   "Categories": [{
      "id": 7,
      "name": "Animation",
      "type": "style"
   },{
      "id": 6,
      "name": "Education",
      "type": "topic"
   }],
}]

If it is possible to do so, please share it in the answer or comment. If any further information is needed for clarification do let me know. I will add them in the question.

Note: If nothing is found the last option for me will be to query all the data and then filter it based on the category, but I don't think it is considered a good practice.

EDIT: @Anatoly's approach is the best possible solution for this case. But just to list out all the option another approach will be to nest multiple include so that it point back to itself and thereafter we can filter out the actual content. Again note that this is highly inefficient method since with increase in the content there will be large number of content linked to one Category and fetching & filtering it requires additional processing and also increase in the number of joins will make this query very slow.

const contents = await db.Content.findAll({
   include: [{ 
       model: db.Category,
       attributes: ['id', 'name', 'type'],
       where: { id: 7 },
       include: [{
          model: db.Content,
          attributes: ['id'],
          through: { attributes: [] },
          include: [{ model: db.Category }]
       }]
   }],
})

Which can be later extracted using:

const category = contents?.Category[0]?.Contents?.filter(r => r.id == contents.id)[0]?.Categories?.map(category => {....});

CodePudding user response：

I would advise to add one more m:n association to the same tables with a different alias BUT in this case it would be multiplication of too many records and you might end up with out of memory error. So it would be good to get only ids of Content records that satisfy the condition and afterwards to execute one more similar query with the condition against only Content records using given ids.

const foundItems = await db.Content.findAll({
   attributes: ['id'],
   include: [{ 
       model: db.Category,
       attributes: ['id'],
       where: { id: 7 }
   }],
})
const itemWithAllCategories = db.Content.findAll({
   where: {
     id: foundItems.map(x => x.id)
   },
   include: [{ 
       model: db.Category,
       attributes: ['id', 'name', 'type'],
   }],
})