sequelize findAll sort order in nodejs

Question

I m trying to output all object list from database with sequelize as follow and want to get data are sorted out as I added id in where clause   exports getStaticCompanies   function          return Company findAll           where                id   46128  2865  49569   1488    45600    61991   1418   61919    53326    61680                     attributes    id    logo version    logo content type    name    updated at                But the problem is after rendering  all data are sorted out as follow   46128  53326  2865  1488  45600  61680  49569  1418         As I found  it s neither sorted by id nor name  Please help me how to solve it

User · Answer

You can accomplish this in a very back-handed way with the following code:

exports.getStaticCompanies = function () {
    var ids = [46128, 2865, 49569, 1488, 45600, 61991, 1418, 61919, 53326, 61680]
    return Company.findAll({
        where: {
            id: ids
        },
        attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at'],
        order: sequelize.literal('(' + ids.map(function(id) {
            return '"Company"."id" = \'' + id + '\'');
        }).join(', ') + ') DESC')
    });
};

This is somewhat limited because it's got very bad performance characteristics past a few dozen records, but it's acceptable at the scale you're using.

This will produce a SQL query that looks something like this:

[...] ORDER BY ("Company"."id"='46128', "Company"."id"='2865', "Company"."id"='49569', [...])

User · Answer

I don t think this is possible in Sequelize s order clause  because as far as I can tell  those clauses are meant to be binary operations applicable to every element in your list   This makes sense  too  as it s generally how sorting a list works    So  an order clause can do something like order a list by recursing over it asking  which of these 2 elements is older   Whereas your ordering is not reducible to a binary operation  compare bigger 1 2    gt  2  but is just an arbitrary sequence  2 4 11 2 9 0    When I hit this issue with findAll  here was my solution  sub in your returned results for numbers    var numbers    2  20  23  9  53   var orderIWant    2  23  20  53  9   orderIWant map x   gt    return numbers find y   gt    return y     x         Which returns  2  23  20  53  9   I don t think there s a better tradeoff we can make  You could iterate in place over your ordered ids with findOne  but then you re doing n queries when 1 will do

User · Answer

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

User · Answer

If you want to sort data either in Ascending or Descending order based on particular column  using sequlize js  use the order method of sequlize as follows     Will order the specified column by descending order order  sequelize literal  column name order   e g  order  sequelize literal  timestamp DESC

User · Answer

If you are using MySQL  you can use order by FIELD id       approach   Company findAll       where   id     in   companyIds        order  sequelize literal  FIELD company id   companyIds join                Keep in mind  it might be slow  But should be faster  than manual sorting with JS

[node.js] sequelize findAll sort order in nodejs

Examples related to node.js

Examples related to express

Examples related to sequelize.js