loopback: distinct query

Question

I am using Loopback to create Rest API. Need to fetch distinct data based on a particular column from a collection. I tried below, but it's not working, instead the below snippet is fetching duplicate data also:

this.app.models.location.find(
                {
                    distinct: ("regionName",
                    {state: st})
                }
                ,
                function(err, location){........}

'RegionName' is the property of 'location' collection and I need data only for selected states (state is another property of location collection) which is represented by 'st'. Thanks.

TWright · Answer 1 · 2016-10-19T20:49:41.537

I have the answer in the following two examples:

1.) No query, just distinct on the "name" field of the "Role" collection:

var roleCollection = user.app.models.Role.getDataSource().connector.collection(user.app.models.Role.modelName);
roleCollection.distinct( "name",
  function(err, records) {
    if(err) {
      return cb(err);
    } else {
      return cb(null, records);
    }
  });

2.) With a query, distinct on the "name" field of the "Role" collection leaving out any role with the name of "admin":

var roleCollection = user.app.models.Role.getDataSource().connector.collection(user.app.models.Role.modelName);
roleCollection.distinct( "name", { name: { $ne: 'admin' } },
  function(err, records) {
    if(err) {
      return cb(err);
    } else {
      return cb(null, records);
    }
  });

Loopback.io v2.29.1

score 2 · Answer 2 · answered Feb 28 '16 at 08:28

I have this working now in my project.

Here is some example code (to help explain your question) that should do what you need...

// Distinct regions

Locations.regions = function (cb) {
  console.log('Locations.build');
  var ds = Locations.app.datasources.myDS;
  var sql = "SELECT DISTINCT region FROM Locations ORDER BY region"; // here you write your sql query.

  ds.connector.execute(sql, [], function (err, regions) {

    if (err) {
      cb(err, null);
    } else {
      cb(null, regions);
    }

  });

};

Locations.remoteMethod(
  'regions', {
    http: {
      path: '/regions',
      verb: 'get'
    },
    returns: {
      root: true,
      type: 'object'
    }
  }
);

** Note: this is for MySQL, but you should be able to modify the query for other connectors **

You can access datasource using Locations.dataSource if it is mapped in model-config — Black, Jun 14 '16 at 11:06

score 0 · Answer 3 · edited Jan 12 '16 at 04:59

0

Loopback framework doesn't yet provide distinct function.
Mongo db query to use distinct is as below:

db.runCommand ( { distinct: "<collection>", key: "<field>", query: { <query>} })

Refer below link:
https://docs.mongodb.org/manual/reference/command/distinct/

edited Jan 12 '16 at 04:59

GitaarLAB

14,536
11
60
80

answered Oct 21 '15 at 11:13

Heena Ghai

63
1
3

you should raise a feature request with your use case here: https://github.com/strongloop/loopback-connector-mongodb/issues – xangy Oct 27 '15 at 10:55

score 0 · Answer 4 · answered Nov 28 '18 at 10:21

For further reference, here is a complete example for the MongoDB Connector using a Foreign Key filter and sorting:

var mongodb = require('mongodb');

module.exports = function (User) {

    User.getDistinctCities = function (countryId, cb) {
        var mongoUserCollection = User.getDataSource().connector.collection(User.modelName);
        var options = {bool_soft_delete: {$ne: true}};
        if (countryId) {
            options['countryId'] = new mongodb.ObjectID(countryId);
        }
        mongoUserCollection.distinct("city", options, function (err, records) {
            records.sort();
            return cb(err, records);
        });
    };

    User.remoteMethod('getDistinctCities', {
        accepts: {arg: 'countryId', type: 'string'},
        returns: {arg: 'cities', type: 'array'},
        http: {verb: 'get'}
    });
};

loopback: distinct query

4 Answers4