0

I have a number of tables that are related. It can be condensed down to: Product, Offer

Each Product has a lot of Offers from many stores. Each Offer is an offer at the given time, so only the most recent offer is always the current one.

I want to list all Products with their most recent (newest) Offer from each Store.

This is my base query that is working to give me all Products with all their respective Offers.

const products = await Product.findAll({
    include: [{
        model: Offer,
        as: 'offers',
        order: [['createdAt', 'DESC']],
    }]
});

What I want to get from this, however, is all Products and only the newest / most recent Offer of each store as identified by Offer.store_id.

Research:

So far I learned that distinct is definitely the wrong approach, as that matches whole lines and that I need to use GROUP BY (sequelize group). Sadly all of my attempts proved fruitless.

  • this Person wants pretty much what I want to have - I just need it on my included (/joined) Model and working in sequelize.

  • I think this person is looking for the same solution, but the proposed one is terrible and inefficient and does not utilize sequelize to achieve the result.

  • I have tried various proposed solutions [1], [2], [3] (and many more) using group, none of which helped me get a result and all resulted in MYSQL exceptions.

I can't imagine that this can be so hard to achieve.

fraxton
  • 13
  • 3

1 Answers1

0

So after a brain-melting 6 hour session with ChatGPT and trying endless approaches I managed to find a working Solution. Actually I found both a working SQL Query and a completely different, but working, Sequelize model query using a literal subquery.

This is the Sequelize query:

const products = await Product.findAll({
  include: [{
    model: Offer,
    where: {
      createdAt: {
        [Op.eq]: Sequelize.literal(
          '(SELECT MAX(createdAt) FROM offers o2 WHERE offers.product_id = o2.product_id AND offers.store_id = o2.store_id)'
        ),
      }
    }
  }]
});

store_id is the column on Offer that identifies the Store of which only the latest entry should be returned.

Its surprisingly short of a query, which I kind of expected but was surprised to see almost no coverage on the use-case.

fraxton
  • 13
  • 3