how can I get order '2' < '10' with filter in loopback?

Question

My data like ['2', '13', '13A', '14-1'], How can i get the correct order with filter? Thanks everyone.

Answer 1

IIUC, you are storing numbers (2, 10, etc.) as strings ('2', '10', etc.) in your database.

LoopBack relies on the database to perform ordering (sorting).

Here are few things to try:

1. Modify your model definition to store the property as number. LoopBack is smart and will coerce string values provided by the user (REST API clients) to numbers before they are stored in the database. This would be my preferred solution, because it does not require any complex code in your application and preserves performance.

2. Depending on the database you are using, it may be possible to configure it to treat string values as numbers for sorting. This is not LoopBack specific, I can't really help you with that.

3. As a last resort, you can sort the records in-memory, LoopBack is already doing that for location-based queries when the database does not support them. The idea is to tell the database to return all records matching the filter criteria and then apply order, limit, skip and other options inside your Node.js process. Please note this comes with a severe performance hit and will work only for reasonably-sized data.

As for the 3rd option: implementation wise, you need to override find method in your model class.

// common/models/my-model.js
module.exports = function(MyModel) {
  MyModel.on('modelRemoted', () => {
    MyModel._findRaw = MyModel.find;
    MyModel.find = findWithCustomSort;
  });
}

function findWithCustomSort(filter, options, cb) {
  if (!cb) { 
    if (typeof options === 'function') {
      cb = options;
      options = undefined;
    } else if (!options && typeof filter === 'function') {
      cb = filter;
      filter = undefined;
    }
  }

  const dbFilter = {
    where: filter.where, 
    include: filter.include,
    fields: filter.fields,
  };


  if (cb) {
    this._findRaw(dbFilter, options, (err, found) => {
      if (err) return cb(err);
      else cb(null, sortResults(filter, found))
    });
  } else {
    return this._findRaw(dbFilter, options)
      .then(found => sortResults(filter, found));
  }
}

function sortResults(filter, data) {
  // implement your sorting rules, don't forget about "limit", "skip", etc.
}

UPDATE

Is there a way to use sql for query in custom method?

Yes, you can execute any SQL by using MyModel.dataSource.connector.execute function, see Executing native SQL. There is one catch though - this method is callback based, you cannot use Promise API or async/await.

const idValue = 1;
MyModel.dataSource.connector.execute(
  'SELECT * FROM MyModel WHERE id=?',
  [idValue]
  (err, results) => {
    if (err) console.error('query failed', err);
    else console.log('found data', results);
  });