CODEWITHSUNDEEP
node.jssequelize.jseager-loading
pirate
pirate

Reputation: 303

Order by in nested eager loading in sequelize not working

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.

Upvotes: 11

Views: 21991

Answers (4)

ppenelon
ppenelon

Reputation: 73

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

Upvotes: 4

James
James

Reputation: 449

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

Upvotes: 5

ozgeneral
ozgeneral

Reputation: 6779

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.

Upvotes: 29

pirate
pirate

Reputation: 303

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.

Upvotes: 3

Related Questions