CODEWITHSUNDEEP
node.jsexpresssequelize.js
Pyae Phyoe Shein
Pyae Phyoe Shein

Reputation: 13787

sequelize findAll sort order in nodejs

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.

Upvotes: 146

Views: 288293

Answers (10)

Manishankar Rai
Manishankar Rai

Reputation: 1

Model.findAll({ order: [[ 'createdAt' , 'DESC']]});

Upvotes: 0

nhic0tine
nhic0tine

Reputation: 71

Incase anyone is using uuid type, using example by @Agniveer from above but modified

const ids = [
'f01a057e-5646-4527-a219-336804317246', 
'ee900087-4910-42b4-a559-06aea7b4e250', 
'b363f116-1fc5-473a-aed7-0ceea9beb14d'
];

const idsFormat = `'${ids.join("','")}'`;

const order = [sequelize.literal(`ARRAY_POSITION(ARRAY[${idsFormat}]::uuid[], "<insert_table_name>"."id")`)];

Upvotes: 0

iharsh08
iharsh08

Reputation: 41

Worked for me by using "" quotes surrounding the property name.
For Debugging You can see what is the query that is getting generated by sequelize and then try to run it on the particular DB console.

In My Case I was not able to sort the data by last updatedAt column
Code Snippet :

exports.readAll = (req, res) => {
  console.log("Inside ReadAll Data method");
  let data;
  if (!req.body) {
    data = CompanyModel.findAll({  order: [[sequelize.literal('"updatedAt"'), 'DESC']]});
  } else {
    data = CompanyModel.findAll({  order: [[sequelize.literal('"updatedAt"'), 'DESC']]});
  }
  data
    .then((data) => {
      res.send(
        data
      );
    })
    .catch((err) => {
      res.status(500).send({
        message: err.message || "Some error occurred while retrieving data.",
      });
    });
};

SQL Query getting formed in my case :

Inside ReadAll Data method

Executing (default): SELECT "company_name", "company_id", "on_record", "createdAt", "updatedAt" FROM "companies" AS "company" ORDER BY "updatedAt" DESC;

Upvotes: 0

alex
alex

Reputation: 955

if required, databases order their output by the generic order of values in the order by fields.

if your order is not like this, you may add to the select an order_field, and give it a value based upon the value in id:

case 
when id=46128 then 0
when id=2865 then 1
when id=49569 then 2
end as order_field

and order by order_field.

if there are lots of values, you may stuff them in their original order in a temporary table with an identity primary key order_field, and inner join your select to that temporary table by your value field, ordering by order_field.

i don't know how to do this in sequelize, but found here answers on how it does things that i needed.

Upvotes: 0

Agniveer
Agniveer

Reputation: 371

May be a little late but want to mention an approach.
Sorting based on the [46128, 2865, 49569, 1488, 45600, 61991, 1418, 61919, 53326, 61680] can be done using ARRAY_POSITION function of postgreSQL.

const arr = [46128, 2865, 49569,  1488,   45600,   61991,  1418,  61919,   53326,   61680];
const ord = [sequelize.literal(`ARRAY_POSITION(ARRAY[${arr}]::integer[], "id")`)];

return Company.findAll({
    where: {
        id: arr
    },
    attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at'],
    order: ord,
});

Upvotes: 5

meenal
meenal

Reputation: 324

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

Upvotes: 14

Pavlo Razumovskyi
Pavlo Razumovskyi

Reputation: 824

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.

Upvotes: 8

drs
drs

Reputation: 341

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', [...])

Upvotes: 5

Alex Moore-Niemi
Alex Moore-Niemi

Reputation: 3342

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) => 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 => { return numbers.find(y => { 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.

Upvotes: 0

James111
James111

Reputation: 15903

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?

Upvotes: 328

Related Questions