Mongo Distinct Query

Hi,

I hope you can help me on this. I have a collection with 10k+ rows that I want to display and filter efficiently. I present the collection using a data table and a filter sidebar. When the user filters the table, the filter drop downs should update to reflect the new filtered data set. For example, if the user filters on country = ‘USA’, I don’t want the city drop down to have London in it.

Each filter drop down is then backed by a query. However, I don’t want to iterate through 10k rows to find unique column values. I’d like to execute a distinct query in Mongo.

I can’t create separate collections with unique country, country-city, etc. One of the filter values is a date. So if the user chooses > 2015-01-01 then all countries, cities, etc. drop downs should only contain values in the updated data set.

Is a distinct query possible through Meteor?

3 Likes

In meteor 1.0.4 you have access to the rawDatabase and rawCollection, so in theory you should be able to call the native distinct operation; alas I recommend checking out the package mrt:mongodb-aggregation since it nicely implements this on client and server.

1 Like

Thanks for the quick reply! I’ll check out mrt:mongodb-aggregation.

Has anyone been able to get a distinct query or get aggregation to work yet?

Use the MongoDB Distinct Command! From the shell you can use db.collection.distinct()

I did already try that but got an error

My HTML:

<div class="form-group">
  <label for="descField">Project:</label>  {{> getProjects}}
</div>

My JS Helper:

Template.getProjects.helpers({
  sites: function() {
  return Sites.distinct('project')
  }
});

If I call db.sites.distinct(‘project’) at the command line, it works. But if I try that in my helper, it doesn’t seem to work. I’ve tried various flavors such as:

  return db.sites.distinct('project')

and

 return Sites.sites.distinct('project')

and

  return Sites.distinct('project')

Each time the console shows “Exception in template helper” and no data is displayed/replaced in the HTML.

My finds however do work (but obviously I’m after distinct values:

Template.getProjects.helpers({
    sites: function() {
      return Sites.find({}, {
        sort: {
          project: 1
        }
      }); //.distinct('project', true)
    }
  });

I also tried this for the helper and it didn’t work either:

  Template.getProjects.helpers({
    sites: function() {
    return db.sites.distinct('project')
    }
  });

Your Mongo lives on the server side only. On the client there is MiniMongo - it is an emulation of some mongo functionality.

Ah ha! Ok, I told you I was a noob, right?

Thank you!!!

You could use the meteorhacks:aggregate package to run an aggregation. However, as @mrzafod correctly says, this will only work on the server, and the meteorhacks:aggregate package does not provide any client functionality for you to transparently work with the results (for example in a template helper).

There are a number of ways of making this work.

For a small number of documents, you could do this without any server aggregation, by using _.uniq in your helper:

Template.getProjects.helpers({
  sites: function() {
    return _.uniq(Sites.find({},{sort: {
      project: 1}
    }).fetch(), true, doc => {
      return doc.project;
    });
  }
});

<template name="getProjects">
  {{#each sites}}
    <div>Project: {{project}}</div>
  {{/each}}
</template>

However, for a large base document count, the optimum solution will depend on how much reactivity you want and how that reactivity is being driven. The suggestion below is probably one of the simplest and is minimally reactive, in that it requires you to kick the aggregation off and wait for the results. I haven’t used meteorhacks:aggregate, because it’s easy enough to run the distinct method on a rawCollection (which exposes the underlying node library) and doing it this way is useful insight.

Client

Template.getProjects.onCreated(function() {
  this.distinct = new ReactiveVar();
  Meteor.call('getDistinct', (error, result) => {
    if (error) {
      // do something
    } else {
      this.distinct.set(result); // save result when we get it
    }
  });
});

Template.getProjects.helpers({
  sites: function() {
    const projects = Template.instance().distinct.get();
    // turn our array of project values into an array of {project: project}
    return _.map(projects, project => {
      return {project}
    });
  }
});

<template name="getProjects">
  {{#each sites}}
    <div>Project: {{project}}</div>
  {{/each}}
</template>

Server

Meteor.methods({
  getDistinct: function() {
    return Meteor.wrapAsync(callback => {
      Sites.rawCollection().distinct('project', callback);
    })();
  }
});

You’ll need to meteor add reactive-var to your app if it’s not already in.

EDIT: fixed “new” typo.

5 Likes

Thank you so much @robfallows for your help on this one! I think I’ve added the code (the code for large base document count at the bottom of your post) correctly in the appropriate places, but I get an error:

DPro_Dashboard.js:222:24: DPro_Dashboard.js: Unexpected token (222:24)

Line 222 is this one in my code from you:

    this.distinct = New ReactiveVar();

I did install it and restart the application using:

meteor add reactive-var

Any thoughts on what would be causing that error?

Hmm. Can’t see an error there, but maybe it’s from an earlier line. Have you also done meteor add ecmascript? My example uses ES2015.

Change from

this.distinct = New ReactiveVar();

to

this.distinct = new ReactiveVar();
1 Like

Bingo, thanks @serkandurusoy, the capital letter in New was indeed the issue! Changing it as you suggested worked perfectly!

1 Like

I must stop trying to answer things using my phone. Squinting at a tiny screen doesn’t work :wink:

I’ve corrected the error!

3 Likes

thx for this, I am maintaining collection of youtube Tags and distinct help a lot to be run by synced cron on hourly basis to clean it. Sounds much better than checking or manipulating occurence count every time I remove video.

1 Like