Thinky — Case Insensitive Sorting with RethinkDB and Thinky ORM

Sorting in RethinkDB (and JavaScript in general) can cause a lot of pain. We previously posted a workaroud for MongoDB to fix this issue. Now we're going to fix it for RethinkDB. The problem: RethinkDB (like MongoDB) always sorts case sensitive in the following order

  1. Numerics: 0-9
  2. Uppercase letters: A-Z
  3. Lowercase letters: a-z

Thinky Series Overview


Let's face concrete examples and therefore we define a Tag schema:

Schema

var thinky = require('thinky');

Tag = thinky.createModel("Tag", {  
    name: String,
    approved: boolean
});

We'll skip the actual document create since you know how to create and save documents to the database. We proceed with the following example documents.

Documents in Collection

Let's assume we have the following three documents in our Tags collection:

{name: 'Cape Canaveral', approved: 0},
{name: 'beer', approved: 1},
{name: 'Tour de France', approved: 1}

Sort in RethinkDB

Actually, there is no difference sorting documents in RethinkDB whether using Thinky or any other RethinkDB driver. The previous mentioned Tags collection is based on the Tag schema with respective fields. The native sorting result using Thinky and the Tag schema will look like this:

Sort Query

Thinky integrates promises and callback support for queries. Use your preferred method.

Query with Promises

Tag.orderBy('name').run().then(function(tags) {  
    // tags collection: 1. Cape Canaveral, Tour de France, beer
}).error(function(error) {
    // error handling
});

Query with Callbacks

Tag.orderBy('name').run(function(err, tags) {  
    if (err) {
        // error handling
    }

    // tags collection: 1. Cape Canaveral, Tour de France, beer
});

Sorting Result

  1. Cape Canaveral
  2. Tour de France
  3. beer

You see, we actually expect beer to be the first result, but it isn't.

Add Real Sorting

The sorting result above doesn't make sense to humans and we expect the platform to return results sorted by ignoring lower- or uppercase. This insensitive sorting isn't integrated in RethinkDB. We need to add a workaround by adding a new field to the schema. In our case, we add a normalized field to the Tag schema. This field contains the name in lowercase.

var thinky = require('thinky');

Tag = thinky.createModel("Tag", {  
    name: String,
    normalized: String,
    approved: boolean
});

The normalized field contains the name in lowercase. When saving a new document to the collection, use the name.toLowerCase() method for the normalized value: normalized = name.toLowerCase().
The documents in the Tags collection change to

{name: 'Cape Canaveral', normalized: 'cape canaveral', approved: 0},
{name: 'beer', normalized: 'beer', approved: 1},
{name: 'Tour de France', normalized: 'tour de france', approved: 1}

Now we run sorting on the normalized field instead of name and get the expected result.

Sort with Promises

And like before both versions to retrieve tags from database. Now with correct sorting :)

Tag.orderBy('normalized').run().then(function(tags) {  
    // tags collection: 1. beer, Cape Canaveral, Tour de France
}).error(function(error) {
    // error handling
});

**Sort with Callbacks**

Tag.orderBy('normalized').run(function(err, tags) {  
    if (err) {
        // error handling
    }

    // tags collection: 1. beer, Cape Canaveral, Tour de France
});

Sorting Result

  1. beer
  2. Cape Canaveral
  3. Tour de France

Native RethinkDB Sorting Functionality

Actually, the guys from RethinkDB are looking for ways to implement case-insensitive sorting for any column name by default. They propose different implementation ideas in this RethinkDB Issue on GitHub. Since May 2013 this issue is still open. With the help of this workaround you'll get efficient case insensitive sorting for a given column.

Explore the Library

Find interesting tutorials and solutions for your problems.

Miscellaneous