Thinky — How to Join on Foreign Key of Already Joined Table

In an earlier published tutorial, we’ve touched the topic of filtering documents by fields of nested objects using Thinky. With the native support of joins in RethinkDB, you’re be able to structure your documents with relations between individual Thinky models.

That means, splitting up your nested objects into separate Thinky models and create a relation between them. This guide will show you how to query documents with joins that involve a hierarchical model structure.

Please note: this guide doesn’t explain relations in RethinkDB and what kind of relations (1-1, 1-n, n-m) are used in real world.

There are other guides on Thinky available that are worth to check out:

Thinky Series Overview

Scenario

At Future Studio, we’re getting close to launch the Future Studio University which is basically a membership that allows you to access exclusive premium content (tutorials, videos, etc.).

Enrolling for a semester in the University gets you the Future Student title which is represented as a user model in Thinky. Internally, the semester is the representation of a subscription. Subscriptions base on a plan that are called guest semester and semester in the University.

All in all, we’ve the following hierarchy for the models:

User -> Subscription -> Plan  

A user buys a subscription. The subscription is based on a plan that defines a time range and price.

Preparation

Based on the descriptions in the section above (Scenario), you need to create three models: User, Subscription, and Plan. We’ll define their relations at the end of this section.

Create Models

Subsequent, you’ll get some more details about the individual Thinky models and how they relate.

The user models contains of just three fields. You don’t need to define a subscription property, because Thinky will do the job automatically when adding the relation.

user.js

var User = thinky.createModel("User", {  
  id: type.string().default(r.uuid()),
  username: type.string(),
  password: type.number()
})

Each individual subscription belongs to a user, that means you need to save the related user ID. Further, a subscription is based on a given plan which indicates that you also need to save the plan ID reference.

subscription.js

var Subscription = Subscription = thinky.createModel('Subscription', {  
  id: type.string().default(r.uuid()),
  name: type.string(),
  user_id: type.string(),
  plan_id: type.string()
})

The plan describes the foundations of each (guest) semester by defining the price and end date.

plan.js

var Plan = thinky.createModel('Plan', {  
  id: type.string().default(r.uuid()),
  name: type.string(),
  price: type.number(),
  ends_at: type.date(),
})

Having the three models ready, you can create the relations between them so Thinky knows how to fetch data when using joins.

Relations

To avoid circular references and model definitions, please make sure to follow the architectual advice on Thinky’s homepage. The following snippet imports the individual models and defines relations separately from the underlying model creation.

index.js

'use strict'

const user = require('./user')  
const subscription = require('./subscription')  
const plan = require('./plan')

/**
 * Relate the user to the subscription
 *
 * @param  {Model}  User - user model
 * @param  {string}  user - field name
 * @param  {string}  id - user primary key
 * @param  {string}  user_id - foreign key, user id in subscription model
 */
user.hasOne(subscription, 'subscription', 'id', 'user_id')

/**
 * Relation between subscription and plan
 */
subscription.hasOne(plan, 'plan', 'id', 'plan_id')

module.exports = {  
  User: user,
  Subscription: subscription,
  Plan: plan
}

The code looks quite complex even though it isn’t. At first, you just import the actual models for user, subscription, and plan. Afterwards, you define the relations between user and subscription as well as subscription and plan.

To avoid blowing up the code, you can skip other directions of the relations for user-subscripion and subscription-plan at this point.

Join on Already Joined Table

Now you get to the core of this tutorial. In the second code block below, you can see a function called findUserByUsername that queries a specific user document based on the given username. What you want is the user’s data including its subscription and the fundamental plan that is applied for the subscription.

The most important part of the query is the join:

User.getJoin({  
  subscription: {
    plan: true
  }
})

The “nested” join will make it straight forward to fetch everything you want. Of course, there are many ways to grab the desired data if you think of a better way to do the job, please let us know in the comments below :)

The following example contains the mentioned function that fetches the complete set of user data.

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

var findUserByUsername = function (username) {  
  return User.getJoin({
    subscription: {
      plan: true
    }
  }).filter({ username: username }).then(function (users) {
    if (_.isEmpty(users)) {
      // nothing found
      return when.reject('No user found with given username: ' + username)
    }

    // actually, there should only be a single user object in the result list
    // (for unique usernames)
    var user = _.first(users)

    // return or perform desired operations on document(s)
    // we just return in a resolving Promise :)
    return when.resolve(user)
  })
}

See how simple it is to get the user object including the subscription and plan? Use a join to reference a possibly available subscription and further down to the underlying plan. Awesome!

Outlook

Hopefully you like the functionality of “nested” joins for model relations to easily fetch a more complete set of data with just a single query.

If you feel there’s something missing or needs further explanation, please let us know within the comments below or shoot us a message on Twitter: @futurestud_io.

Enjoy coding & make it rock!

Explore the Library

Find interesting tutorials and solutions for your problems.