Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequelize - SQL Server - order by for association tables

I have 3 tables such as user, userProfile and userProfileImages. User is mapping with userPrfoile as has many and userProfile is mapping with userProfileImages as has many.

I need to write the order by query in userProfileImages, I tried as below, but no luck.

User.findById(uID, { 
include: [
   model: sequelize.models.userProfile
   as: userProfile,
   include: [
    {
      model: sequelize.models.userProfileImages,
      as: 'profileImages',

    }
   ],
    order: [[sequelize.models.userProfileImages.id, "desc"]]
  // order: [["id", "desc"]] --> Tried this way also no luck
] }

I am getting the result, but userProfilePicture table's result is not as desc.

Kindly give the solutions

like image 415
Muthukumar Marichamy Avatar asked Sep 03 '17 19:09

Muthukumar Marichamy


People also ask

How do I use order in Sequelize?

Order by included modelfindAll({ order: [[Survey_Questions, "orderIndex", "ASC"]], include: { model: Survey_Questions, }, }); We added model to include and the same model is used in order params, where we declare that we want to order results by orderIndex from joined table.

How do I get data from two tables in Sequelize?

There are two ways you can create JOIN queries and fetch data from multiple tables with Sequelize: Create raw SQL query using sequelize. query() method. Associate related Sequelize models and add the include option in your Sequelize query method.

What is findByPk?

findByPk ​ The findByPk method obtains only a single entry from the table, using the provided primary key. const project = await Project. findByPk(123); if (project === null) {

How do you include two models in Sequelize?

How do you include two models in Sequelize? Your solution, along with using include: {all:true} in my findAll query, did the trick. instead of using include: {all:true} in findAll you can use include: {model: models. User, as: 'createdByUser'} , etc.


2 Answers

From Sequelize offical docs:

    // Will order by an associated model's created_at using an association object. (preferred method)
    [Subtask.associations.Task, 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using association objects. (preferred method)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],

By referring above syntax, Update your order option like below

User.findById(uID, { 
    include: [{
        model: sequelize.models.userProfile
        as: userProfile,
        include: [{
           model: sequelize.models.userProfileImages,
           as: 'profileImages',
        }],
        order: [['profileImages','id', 'desc']]
    }]
});

Official Documentations: http://docs.sequelizejs.com/manual/tutorial/querying.html#ordering

Refer this thread for more solutions: https://github.com/sequelize/sequelize/issues/4553

like image 193
Raj Adroit Avatar answered Sep 27 '22 17:09

Raj Adroit


Update your order in association like below

User.findById(uID, { 
include: [
    model: sequelize.models.userProfile,
    as: userProfile,        
    include: [{
       model: sequelize.models.userProfileImages,
       as: 'profileImages',
       separate:true
       order: [['id', 'desc']]
    }],
]});
like image 22
Yash Shah Avatar answered Sep 27 '22 19:09

Yash Shah