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.

Answer 1

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

Answer 2

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 **