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.

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? — Tom Jardine-McNamara, Oct 23 '16 at 18:35
@TomJardine-McNamara here look i solved it, thanks for your time. — pirate, Oct 26 '16 at 08:16

score 28 · Answer 1 · edited Mar 16 '18 at 19:24

28

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.

edited Mar 16 '18 at 19:24

todgru

139
2
12

answered Aug 29 '17 at 10:37

ozgeneral

6,079
2
30
45

score 4 · Answer 2 · answered May 21 '19 at 18:21

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'] 
]

score 3 · Accepted Answer · answered Oct 26 '16 at 08:14

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.

score 3 · Answer 4 · answered Nov 01 '20 at 13:30

3

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'],
]

answered Nov 01 '20 at 13:30

ppenelon

43
5

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? – F_Bass Feb 07 '23 at 21:19
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. – ppenelon Feb 12 '23 at 18:43

Order by in nested eager loading in sequelize not working

4 Answers4

Linked