CODEWITHSUNDEEP
mysqlnode.jssequelize.js
user5113508
user5113508

Reputation:

How to fix Sequelize nested includes not working with limit/order/attributes?

I've got some models that have associations with each other, and I need to grab them all in a certain request. I need to use limit, order, and attributes on basically all parts of it, but this is causing the nested includes to freak out and I'm not entirely sure what's wrong with it.

It doesn't really print any errors or anything, the models either just don't get included in the response (i.e they are empty.), or they get included but stuff like order/limit is ignored.

I've already tried using subQuery, separate, etc... None of those worked.

The query in question;

const categories = await models.Category.findAll({
  attributes: ['id', 'title', 'description'],
  order: [['title', 'ASC']],
  include: [
    {
      model: models.Product,
      attributes: ['id', 'title'],
      through: { attributes: [] },
      include: [
        {
          model: models.Price,
          attributes: ['id', 'amount', 'createdAt'],
          order: [['createdAt', 'DESC']],
          limit: 1,
        },
      ],
    },
  ],
});

The associations;

models.Category.belongsToMany(models.Product);
models.Product.belongsToMany(models.Category);

models.Product.hasMany(models.Price);
models.Price.belongsTo(models.Product);

I ideally want the query provided above to return;

Upvotes: 7

Views: 13029

Answers (1)

Avraham
Avraham

Reputation: 968

In order to get the query to sort by Product.Price.createdAt, add [models.Product, models.Price, 'createdAt', 'DESC'] to order. As far as limiting: in order to limit the included model, you need to run it as a separate query, so add separate: true to the include.

Code:

const categories = await models.Category.findAll({
  attributes: ['id', 'title', 'description'],
  order: [['title', 'ASC'], [models.Product, models.Price, 'createdAt', 'DESC']],
  include: [
    {
      model: models.Product,
      attributes: ['id', 'title'],
      through: { attributes: [] },
      include: [
        {
          model: models.Price,
          attributes: ['id', 'amount', 'createdAt'],
          separate: true,
          limit: 1,
        },
      ],
    },
  ],
});

Upvotes: 7

Related Questions