Sequelize: find latest record per group of id

Question

I'm trying to accomplish the same query on the link below but got no luck here:

https://dzone.com/articles/get-last-record-in-each-mysql-group

Can you suggest the proper way of converting the raw query on the link above into sequelize ORM format?

Solution 1

The idea would be something like this:

Posts.findAll({
    attributes: [sequelize.fn("max", sequelize.col('id'))],
    group: ["category_id"]
}).then(function(maxIds){
    return Posts.findAll({
        where: {
            id: {
                [Op.in]: maxIds
            }
        }
    })
}).then(function(result){
    return Promise.resolve(result);
});

Solution 2

Here's what I did base on Nguyen's idea with some tweaks:

  let conversationIds = conversations.map(conversation => {
    return conversation.conversation_id;
  });

  models.conversationDetails.findAll({
    attributes: [
      [models.sequelize.fn("max", models.sequelize.col('id')), 'id']
    ],
    where: {
      conversation_id: conversationIds
    },
    group: ['conversation_id']
  })
  .then(function(results) {
    let ids = results.map(result => {
      return result.id;
    });

    models.conversationDetails.findAll({
      include: [
        {
          model: models.conversationMeta,
          as: 'conversationMeta'
        }
      ],
      where: {
        id: {
            [Op.in]: ids
        }
      }
    })
    .then(function(conversationList) {
      callback(false, conversationList);
    })
    .catch(function(error) {
      console.log(error);
      callback(true, 'Internal Server Error');
    });
  })
  .catch(function(error) {
    console.log(error);
    callback(true, 'Internal Server Error');
  });

Comments

[Nestor A]

Thanks for the idea, it looks like this solves my issue. I'll post what I did with some tweaks. Thanks again

[My Nguyen]

I'm glad it helps. :)

[TheHanna]

Is there any way to accomplish this without touching the database twice? This solution works, but it seems unoptimized

[My Nguyen]

Hi @TheHanna, if you really want to touch the db once. I would suggest you passing the raw query directly to sequelize.query. Please consider the performance vs maintainability before you do any micro optimization.