Order by in nested eager loading in sequelize not working

Question

i have four model Tehsil, Ilr, Patwar, and Villages. and their association is

Tehsil -> 1:m -> Ilr -> 1:m -> Patwar -> 1:m -> Villages

i want to to apply order by on all four of my models.

Query:

var tehsilQuery = {
    include: [{
        model: Ilr,
        as: 'GirdawariKanoongo',
        include: [{
            model: Patwar,
            as: 'GirdawariPatwar',
            include: [{
                model: Villages,
                as: 'GirdawariVillages',
            }]
        }]
    }],
    order: [
        ['tehsil_name', 'ASC'],
        [ {model: Ilr, as: 'GirdawariKanoongo'}, 'kanoongo_name', 'ASC'],
        [ {model: Patwar, as: 'GirdawariPatwar'}, 'patwar_area', 'ASC'],
        [ {model: Villages, as: 'GirdawariVillages'}, 'village_name', 'ASC'],
    ]
};
return Tehsils.findAll(tehsilQuery);

[Error: 'girdawari_patwar' in order / group clause is not valid association]

order by is working if i remove Patwar and Villages(lat two model) from order.

Subtenant

The underscore in the association name in the error suggests Sequelize is trying to auto-generate the association name, though I'm not sure why. Can you post the code where you set up your associations? – Casillas

@TomJardine-McNamara here look i solved it, thanks for your time. – Subtenant

Answer 1

i solved it. here's how order looks:

order: [
        'tehsil_name',
        'GirdawariKanoongo.kanoongo_name',
        'GirdawariKanoongo.GirdawariPatwar.patwar_area',
        'GirdawariKanoongo.GirdawariPatwar.GirdawariVillages.village_name' 
       ]

all i have to do is: using the as in association of table and chaining them followed by column_name on which order supposed to apply.

Subtenant

Answer 2

Another working example with nested ordering:

order: [  
  [ { model: chapterModel, as: 'Chapters' }, 'createdAt', 'ASC'], 
  [ { model: chapterModel, as: 'Chapters' }, 
    { model: partModel, as: 'Parts' }, 'createdAt', 'ASC'] 
],

where part and chapter have M:1 relation.

Mika

Answer 3

Our scenario was with two nested include statements where the inner most nesting was not ordering correctly. By applying the ordering at the highest level of the findAll we were able to successfully return the ordered object.

model relationship as follows for our surveySet.findAll:

• suveySet hasMany surveys

• surveys belongsToMany questions

  order: [  
      [ { model: survey, as: 'survey' }, 'subjectId', 'ASC'], 
      [ { model: survey, as: 'survey' }, 
        { model: question, as: 'question' }, 'id', 'ASC'] 
  ]
  

Holocaust

Answer 4

For anyone wondering how it could be achieved using object style definition, this is how it would be solved. You could find more information about ordering in Sequelize documentation.

order: [
    ['tehsil_name', 'ASC'],
    [ {model: Ilr, as: 'GirdawariKanoongo'}, 'kanoongo_name', 'ASC'],
    [ {model: Ilr, as: 'GirdawariKanoongo'}, {model: Patwar, as: 'GirdawariPatwar'}, 'patwar_area', 'ASC'],
    [ {model: Ilr, as: 'GirdawariKanoongo'}, {model: Patwar, as: 'GirdawariPatwar'}, {model: Villages, as: 'GirdawariVillages'}, 'village_name', 'ASC'],
]

Amoroso

Thank you for this. I think the documentation is not at all clear about this. With the same example you posted, how would you order the models by two or more columns? – Epanaphora

Hi @F_Bass, this is a bit old in my memory but this example sorts by 4 columns already, if you need more you can just append new orders in the order array and fit the order statement as you need. – Amoroso