How to perform case insensitive sorting

[riebeekn]

Hello, have been playing around with doing some pagination / sorting and one thing I’ve stumbled upon is that when doing a sort within a find clause, sorting is case sensitive. For example:

Animals.find({}, {sort:{"name": 1});

Results in:

Cat
Dog
bobcat

From what I’ve read this is a limitation of MongoDB, what’s the recommended approach for doing case insensitive sorting in Meteor?

One approach seems to be to add an additional column that contains down-cased data. This seems a little hacky; especially if one wanted to enable sorting on a large number of columns, you’d be duplicating potentially a lot of data just in order to perform sorting.

Any thoughts?

[arunoda]

There is no way to do it with Mongodb. What you have suggested is the correct way to go.

In mongodb demoralization and duplication is common. Don’t worry about that, it’s not an anti pattern.

[camiel]

Sort by slugified title? Often going to need slugs anyway.

[yauh]

You can use aggregation to avoid storing the duplicate content in your database:

Mongo DB sorting With case insensitive

In order to use aggregate you need a package or use MongoInternals like here https://github.com/meteorinaction/ch07-pubsub/blob/master/workoutTracker/server/publications.js#L21-L50

For better transparency, here is the place to watch if MongoDB ever supports case-insensitive sorting: https://jira.mongodb.org/browse/SERVER-90

[RevMen]

demoralization

Why so dark?

[riebeekn]

Thanks for the suggestions, coming from a traditional DB background there is lots to learn when it comes to MongoDB!

I decided to go with duplicate columns, that seems pretty common, and with the Collection2 package it is easy to accomplish:

Animals.attachSchema(new SimpleSchema({
    name: {
        type: String,
        label: "name"
    },

    name_sort: {
        type: String,
        optional: true,
        autoValue: function() {
            var name = this.field("name");
            if (name.isSet) {
                return name.value.toLowerCase();
            } else {
                this.unset(); 
            }
        }
    }

Gotta’ love all the great Meteor packages out there!

[arunoda]

Oh btw: auto completion rocks

[charlespourreau]

Hi there. Old question, but i’m sharing a solution preventing data duplication using transform

\imports\api\items\items.js 
[...]
export const Items = new Mongo.Collection('items', {
  transform: function(item) {
    item.lowerCasedName = item.name.toLowerCase();
    return item;
  }
});

\imports\ui\itemspage\items.js
[...]
Template.show_items.helpers({
  items() {
    let items = Items.find({}, {sort: {lowerCasedName: 1}});
    return items;
  },
  [...]
}),

[MickaelFM]

This does not work

transform Function

An optional transformation function.
Documents will be passed through this function before being
returned from fetch or findOne , and before being
passed to callbacks of observe , map , forEach
, allow , and deny . Transforms are not applied
for the callbacks of observeChanges or to cursors
returned from publish functions.