How to orderby belongsToMany association using the field in the through table?

[itrethan]

I have following model:
Course {
name;
...
}
Lessson {
name;
...
}
CourseLesson {
order;
...
}
Course.belongsToMany(Lesson, {through: CourseLesson , as: 'lesson'})
Lessson .belongsToMany(Course, {through: CourseLesson, as: 'course'});

In the query's order field, how could I order the lesson if I include the lesson in the query?
include: [{model: Lessson , as: 'lesson'}]
order: [{model: ???, as: ???} , order , ASC]

Thanks!

[sushantdhiman]

http://docs.sequelizejs.com/manual/tutorial/models-usage.html#ordering-eager-loaded-associations

[itrethan]

Thanks @sushantdhiman ,

But the doc only described about the nested use case, for my example,

order: [ [CourseLesson , order , ASC] ]

It doesn't seems to work, it's giving out error: 'CourseLesson ' in order / group clause is not valid association

[itrethan]

anyone know what's going on?

[sushantdhiman]

@itrethan Can you present a SSCCE which I can execute, a small, self contained (Sequelize based) code which I can quick run to reproduce problem you are facing ? For example like this #7318 (comment)

[aurbman]

To order on a field in the through table:
order: [[Sequelize.literal('CourseLesson.order'), 'ASC']]

or in my case, I had to put CourseLesson.order in back-ticks

[nawlbergs]

Could not get order: [[Sequelize.literal('CourseLesson.order'), 'ASC']] with my models

i had to look at the actual sql getting generated by sequelize to get this to work... and needed to do:
[ Sequelize.literal("`Lessson->CourseLesson`.`order`"), 'asc'],

This seems like a hack... and breakable. (im on v5)

[ephys]

@nawlbergs using a literal is always going to be easily breakable yes

Have you tried ordering using the association as a parameter?
Something like [Lesson.associations.CourseLesson, 'order', 'ASC'], ? (just guessing your field names)

(source)

[rhwinter]

@nawlbergs if [ Sequelize.literal("`Lessson->CourseLesson`.`order`"), 'asc'] works, then I think that what you want to do is something like:
order: [ [{ through: {model: CourseLesson, as: 'lesson'} }, 'rank', 'ASC'] ]

[yogithesymbian]

http://docs.sequelizejs.com/manual/tutorial/models-usage.html#ordering-eager-loaded-associations

page not found

i found here https://sequelize.org/docs/v6/other-topics/sub-queries/#using-sub-queries-for-complex-ordering
but its shown as raw query...

how about

const relationDatabase = [
  {
    model: user,
    paranoid: false
  },
  {
    model: verification_hm,
    separate: true
  },
  {
    model: form_tug_boat,
    paranoid: false,
    include: [
      {
        model: tug_boat,
        paranoid: false
      }
    ]
  },
...
...
....

order: [
          [Sequelize.literal('verification_hm.created_at'), type] // not work
        ]
order: [
          [Sequelize.literal('$verification_hm.created_at$'), type] // not work
        ]

[WikiRik]

The v6 docs for that page is https://sequelize.org/docs/v6/advanced-association-concepts/eager-loading/#ordering-eager-loaded-associations