Within the previous posts, you’ve already learned how to query documents by fields of nested objects and how to add custom methods to a Thinky model. This guide still focuses on models, but shifts gears to relations. Precisely, you’ll learn how to apply sorting on joined documents.

Before heading into the details, have a look at previously published posts about Thinky:

Thinky Series Overview

RethinkDB/Thinky Relations

RethinkDB allows you to define relations between tables and combine them via the well-known JOIN command. Thinky as a RethinkDB ORM provides this functionality as well and enables you to define relations between models. Models are mapped to tables and with the help of joins it will automatically fetch related data as you wish.

While implementing functionality for the Future Studio University (which will launch soon), we defined separate models for User and Subscription. Each user can have multiple subscriptions and an individual subscription can have different states, like active, inactive, cancelled, expired, etc. With that in mind, we want each user to have only a single active subscription, but if the users cancels it and comes back later to join the University again, he should be able to create a new one.

Sort Joined Documents

The scenario described above shows you that the most recently created subscription is the important one and contains all required information about the current state (active, cancelled, etc). When fetching user data including the related subscriptions, we wanted to sort the subscriptions by creation date and have the most recent as the first item within the subscription list.

The following snippet illustrates the findById() function that fetches a user by id and the related subscriptions. Further, the subscriptions are ordered descending by creation date using the created_at field.

var _ = require('lodash')  
var Boom = require('boom')  
var when = require('when')  
var User = require('./models/user')

var findById = function (id) {  
  // reject if id is missing
  if (_.isEmpty(id)) {
    return when.reject(Boom.badRequest('User id missing.'))
  }

  // get user for given id and fetch related subscriptions
  return User.get(id).getJoin({
    subscriptions: {
      _apply: function (sequence) {
        return sequence.orderBy(thinky.r.desc('created_at'))
      }
    }
  }).run().then(function (user) {
    if (user) {
      // do whatever you want :)
      // e.g. add the most recent created subscription to user object
      user.subscription = _.first(user.subscriptions)
      return when.resolve(user)
    }

    return when.reject(Boom.notFound('No user registered with provided credentials.'))
  })
}

At first, we check if the id contains a proper value and return a reject Promise if that’s not the case. Afterwards, the User model is used to get the user by id and with the getJoin() method, you’re adding related data. In particular, the options object that is passed to the getJoin(options) method defines what will be applied to the related data. The _apply function returns a sequence of subscriptions that will be ordered descending by creation date. You need to use the r.desc() helper method provided by Thinky to define the correct order.

That’s the trick to fetch data sorted by a given field of the related object.

Outlook

This guide walked you through the details on how to fetch related data using Thinky and also sort the joined documents in a specific order. Of course, you can customize and manipulate the actual and joined documents as you wish once the result is available.

Please feel free to ask any question that comes to mind. Let us know in the comments below or reach out on Twitter @futurestud_io.

Make it rock & enjoy coding!