On Queries and Orphans

First of all, this article is about Ruby on Rails. It’s about an issue I faced when building an expense tracker application that you can find here if you fancy checking that out. Feel free to sign up and try it out!

The Problem

Let’s say you have a Group class like the one below:

Obviously, there is a User class and an Expense class as well. The group belongs to a user, and the group can have many expenses. The group, in this case, is an expense category such as Entertainment or Food. The expense might be anything that the users want to add to the group/category. What happens if the user deletes the category? What do we do with the groups’ expenses? Should we delete them as well? Can’t we just let them be and do nothing regarding the non-existent reference to a dead group?

We could destroy them, certainly, but deleting a category does not mean the expenses were not made and we cannot simply do nothing because of a little thing called referential integrity. Having that in mind we should not delete expenses when we delete the group they belonged to.

What we must do is “uncategorize” them, even when that’s not an actual word. The Expense has a field called group_id, which is the ID of the group it belongs to. In order to orphan the expense we need to set that value to NULL or nil, in Ruby’s terms. But what if the category we are deleting has 10,000 expenses attached to it? That can quickly get cumbersome and prone to performance issues.

Luckily, Rails has a lovely feature that allows us to do this seamlessly and in an efficient manner.

The Solution

We can nullify group_id‘s value in a Railsy way by adding the following to our Group class:

The dependent: :nullify addition does the trick here. Whenever a Group/Category gets deleted, it goes and sets the group_id value of all the group’s expenses to NULL in the database.

Once done that, you can treat the expenses as uncategorized expenses and do whatever you need to do with them. This is pretty useful, as it greatly simplifies the process of deleting an object without having to delete its dependencies or children.

This does not solve the performance problem though. It will still go and update the database as normal if there were 10,000 expenses for that category. For situations where there are a ton of records to nullify and in order to not hurt user experience, there’s a gem called Miss Hanningan, that uses Action Cable to execute the queries under the hood without blocking the application. The update queries still need to get executed though, there’s no way around that.