How to order by an aggregate function in sequelize

Question

I feel as if I've hit a brick wall while converting my old PHP app functionality over to Node and Sequelize. Maybe I'm approaching the limits of what an ORM can do, but just want to ask before I give up.

Let's imagine three tables: Video, Task, PublishDate.

The application requires that a Video may have multiple PublishDates and multiple Tasks. The Tasks will have due dates calculated by looking at the minimum of the Video's PublishDates.

So if a Video has two PublishDates of 2015-12-01 and 2015-08-15, we'd use 2015-08-15 and perform additional calculations on that to get the Task due date.

The problem occurs when I'm trying to get a list of Tasks ordered by their calculated due dates.

I've attempted many different approaches. This latest try attempts to use the sequelize.fn function

models.Task.findAll({
  where: {isActive: false, isCompleted: false},
    include: [
      {
        model: models.Video, 
        attributes: [[sequelize.fn('min', 'video.publishDates.date'), 'dueDate']],
        include: [models.PublishDate]
      }
    ],
  order: [['video', 'dueDate', 'ASC']],
  limit: 50
})

I'm not encouraged that I'm headed in the right direction because even without the order clause, this attempt only returns one record. Still, this feels like the closest I've been all day and I'm missing some bit of information or syntax which would make this possible.

This is my first foray into the world of ORMs, so I apologize if there is something simple that I'm missing. I tried my best looking into the documentation and other questions on Google but no luck so far.

Answer 1

you need to order using the function. you should try:

order : [['video','ASC'],[sequelize.fn('min', 'video.publishDates.date'), 'ASC']]