Sequlize Order by Only from Main Query

sequlize order by only from main query

May be this is not the best answer.

I just add the sub query in the order by

SELECT
  "model".*,
  "model2"."id"   AS "model2.id",
  "model2"."name" AS "model2.name"
FROM (SELECT
        "model"."id",
        CAST((SELECT count(*)
              FROM "table1"
              WHERE "table1"."id" = "model"."id" AS INTEGER) AS "table1Count"
      FROM "model" AS "model"
      WHERE "model"."status" = 'Pending'
      LIMIT '50'
      OFFSET '0') AS "model" LEFT OUTER JOIN
  "model2" AS "model2" ON "model"."id" = "model2"."modelId"
ORDER BY CASE WHEN CAST((SELECT count(*)
              FROM "table1"
              WHERE "table1"."id" = "model"."id" AS INTEGER) >= 1
  THEN CAST((SELECT count(*)
              FROM "table1"
              WHERE "table1"."id" = "model"."id" AS INTEGER) END DESC NULLS LAST, "model"."id" ASC

Sequelize Order within Query and Sub-Query

You can use the sorder subqueries the same way, add order attribute

models.Blog.findAll({
            order: 'blog_date DESC',
            limit: 10,
            include: [
            {
                model: models.Topic,
                attributes: ['topicName'],
                required: false
            },
            {
                model: models.BlogComment,
                include: [{
                    model: models.User,
                    attributes: ['userId','firstName', 'lastName'],
                    order : 'firstName DESC'
                }],
                required: false
            }]
        })

Update:

You will need to try

order: [["blog_date","DESC"]]

rather than

order: 'blog_date DESC'

Kindly refer to the following link

Sequelize js - limit and Sorting bug

sequelize findAll sort order in nodejs

In sequelize you can easily add order by clauses.

exports.getStaticCompanies = function () {
    return Company.findAll({
        where: {
            id: [46128, 2865, 49569,  1488,   45600,   61991,  1418,  61919,   53326,   61680]
        }, 
        // Add order conditions here....
        order: [
            ['id', 'DESC'],
            ['name', 'ASC'],
        ],
        attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at']
    });
};

See how I've added the order array of objects?

order: [
      ['COLUMN_NAME_EXAMPLE', 'ASC'], // Sorts by COLUMN_NAME_EXAMPLE in ascending order
],

Edit:

You might have to order the objects once they've been recieved inside the .then() promise. Checkout this question about ordering an array of objects based on a custom order:

How do I sort an array of objects based on the ordering of another array?

Ordering results of eager-loaded nested models in Node Sequelize

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

---

Related Topics