CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Wandering Digital
Wandering Digital

Reputation: 1868

Ordering results of eager-loaded nested models in Node Sequelize

I have a complex set of associated models. The models are associated using join tables, each with an attribute called 'order'. I need to be able to query the parent model 'Page' and include the associated models, and sort those associations by the field 'order'.

The following is having no effect on the results' sort order:

db.Page.findAll({
  include: [{
    model: db.Gallery,
    order: ['order', 'DESC'],
    include: [{
      model: db.Artwork,
      order: ['order', 'DESC']
    }]
  }],
})

Upvotes: 74

Views: 47958

Answers (7)

Rolly
Rolly

Reputation: 3375

after some tries, it worked for me

module.exports = (sequelize, DataTypes) => {
  class Category extends Model {
    static associate(models) {
      Category.belongsTo(models.Restaurant, {
        foreignKey: { name: 'restaurant_id', type: DataTypes.UUID },
        as: 'restaurant',
      })
      Category.hasMany(models.Dish, {
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        foreignKey: { name: 'category_id', type: DataTypes.UUID },
        as: 'dishes',
      })
    }
  }
....
module.exports = (sequelize, DataTypes) => {
  class Dish extends Model {
    static associate(models) {
      Dish.belongsTo(models.Category, {
        foreignKey: { name: 'category_id', type: DataTypes.UUID },
        as: 'category',
      })
      Dish.hasMany(models.Picture, {
        onDelete: 'CASCADE',
        foreignKey: { name: 'dish_id', type: DataTypes.UUID },
        as: 'pictures',
      })
    }
  }
....

    categories = await Category.findAll({
      where,
      attributes: ['id', 'name', 'description', 'active', 'display_order'],
      include: [
        {
          model: Dish,
          as: 'dishes',
          attributes: [
            'id',
            'name',
            'description',
            'active',
            'display_order',
            'price',
            'previous_price',
          ],
          include: [
            {
              model: Picture,
              as: 'pictures',
              attributes: ['id', 'source'],
            },
          ],
        },
      ],
      order: [
        ['display_order', 'ASC'],
        [{ model: Dish, as: 'dishes' }, 'display_order', 'ASC'],
      ],
    })

Upvotes: 0

Lucio Mollinedo
Lucio Mollinedo

Reputation: 2424

I didn't need to populate the order property of the root model (in this case, db.Page).

Instead, I added order to the nested model. In this example, db.Artwork.

BUT, the Sequelize's order property cannot be an array of strings, where every string is a column name and the last string is the direction. Eg:

// Failed:
order: ['column1', 'column2', 'column3', 'DESC'],

Instead, it must be composed of an array where each element is another array composed of two elements:

  • the column we wish to order, and
  • the direction

Eg:

// Worked:
order: [['column1', 'ASC'], ['column2', 'DESC'], ['colum3', 'DESC']],

So the approach that worked for me was to sort the contents of db.Artwork by the column order in descendant direction, was:

include: [{
  model: db.Artwork,
  order: [['order', 'DESC']]
}]

I got that answer from another answer I found here on SO.

Upvotes: 0

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 382822

Just for completeness, another thing that also works is using sequelize.col as in:

db.Page.findAll({
  include: [{
    model: db.Gallery
    include: [{
      model: db.Artwork
    }]
  }],
  order: [
    [ sequelize.col('Gallery.order', 'DESC' ], 
    [ sequelize.col('Gallery.Artwork.order', 'DESC' ]
  ]
})

In this particular case it is slightly worse than the Arrays from https://stackoverflow.com/a/30017078/895245 (which more explicitly use existing Js classes rather than magic strings) but it seems that in most cases besides order:, .col is accepted but arrays aren't so it is good to have it in mind too.

There are some additional difficulties to overcome when you also need to limit: in nested includes:

When limiting on toplevel only, it seems that subQuery: false is required e.g. as in:

  u0WithComments = await User.findOne({
    where: { id: u0.id },
    order: [[
      'Comments', 'body', 'DESC'
    ]],
    limit: 1,
    subQuery: false,
    include: [{
      model: Comment,
    }],
  })

Upvotes: 1

Aman Kumar
Aman Kumar

Reputation: 111

This works for me:

let getdata = await categories_recipes.findAll({   
                    order:[ 
                        [{model: recipes, as: 'recipesdetails'},'id', 'DESC'] // change your column name like (id and created_at)
                    ],
                    include:[{
                        model:recipes, as : "recipesdetails",
                        include:[{
                            model:recipe_images, as: "recipesimages",
                        }],
                        where:{
                            user_id:data.id
                        },
                        required: true,
                    }]
                })

Upvotes: 1

Calvintwr
Calvintwr

Reputation: 8798

I believe you can do:

db.Page.findAll({
  include: [{
    model: db.Gallery
    include: [{
      model: db.Artwork
    }]
  }],
  order: [
    // sort by the 'order' column in Gallery model, in descending order.

    [ db.Gallery, 'order', 'DESC' ], 


    // then sort by the 'order' column in the nested Artwork model in a descending order.
    // you need to specify the nested model's parent first.
    // in this case, the parent model is Gallery, and the nested model is Artwork

    [ db.Gallery, db.ArtWork, 'order', 'DESC' ]
  ]
})

There are also a bunch of different ways, or things you can do when ordering. Read more here: https://sequelize.org/master/manual/model-querying-basics.html#ordering-and-grouping

Upvotes: 152

Shrikant
Shrikant

Reputation: 538

order: [
[ db.Sequelize.col('order'), 'DESC'],    /*If you want to order by page module as well you can add this line*/
[ db.Gallery, db.ArtWork, 'order', 'DESC' ]
]

Upvotes: 5

Relu Mesaros
Relu Mesaros

Reputation: 5038

If you also use 'as' and let's say you want to order by 'createdDate' , the query looks like this: 

DbCategoryModel.findAll({
    include: [
        {
            model: DBSubcategory,
            as: 'subcategory',
            include: [
                {
                    model: DBProduct,
                    as: 'product',
                }
            ],
        }
    ],
    order: [
        [
            {model: DBSubcategory, as: 'subcategory'},
            {model: DBProduct, as: 'product'},
            'createdDate',
            'DESC'
        ]
    ]
})

Upvotes: 36

Related Questions