How we compiled a lead management timeline with UNION

We’ve recently added a lead management system to Simpleweb investee startup Coherent, an all-in-one coworking space management platform that takes care of workspace admin and connects communities. The lead management system was most recently added to help workspace managers track and organise communication with potential new workspace users.

An important part of this feature was a timeline for each lead of all the interaction they have had with the workspace. Timelines are a fairly common bit of functionality, but they aren’t straightforward to implement, so I thought it would be interesting to share how we tackled it.

The challenge

The lead activity timeline needed to display a number of different types of record, all in chronological order:

• ‘Alerts’, which are the reminders workspace managers set for contacting a lead on a specific date
• ‘Alert completions’, which happen when a workspace manager confirms they have carried out a contact
• ‘Transitions’, which happen when a lead is either successfully converted to a signed up workspace user or is otherwise closed
• ‘Notes’, which can be attached to a lead by a workspace manager at any point

The challenge was pulling these records in from their separate database tables in the correct order in an efficient way.

It would be simple to pull them all in, unsorted, and then order them, but it would be a lot more efficient if the database returned them in the correct order to begin with, and that’s even more important when you only want to display a small number of results at a time. Suppose you only want the first 20 results to display. Unless your database query itself can order the results, you’ll need to request all the results, then order them, then throw away all but the first 20. That defeats the point of only showing a small number of results at a time.

Common solution: Join Tables

A common way to tackle this is through a ‘join table’ in the database. Each record in this join table would relate both to a lead and to a specific record – from some other database table – that needs to go in the activity timeline. The join-table records for a given lead effectively combine all the different timeline records from their respective tables. If the join table also has a column for the date the records need to be ordered by, an individual lead’s ordered timeline activity can be returned by querying that single join table, and the query could be tweaked to return just the first 20 records.

However, the problem here is the database query wouldn’t actually be returning all of the information we need about each item in the timeline. It would be returning a list of join table records, each of which refers to a corresponding record in some other table. We would then need a separate database query to retrieve each of those corresponding records, which isn’t very efficient.

Our solution: Combining queries with UNION

To avoid this problem, I turned to a type of database query called UNION. This query lets you combine queries to separate database tables in a single set of results. From the Alerts table, for example, I can select the action column and call it body, and I can select the due_on column and call it date. I can restrict the results to include only Alerts related to a given lead. Then I can combine these results with the results from a query to the Notes table, say, which selects the body column (and calls it body) and the created_at column (and calls it date). Combining these two queries with UNION returns a single set of results with body and date as the headers, and with the values taken from the two separate tables. This set of results can be ordered by date, and can be written to include all the necessary detail for displaying items in the timeline.

Ultimately, the database query ended up looking like this (where we’re generating a timeline for the lead whose ID is 1). I’ve highlighted each of the separate queries being combined:

One of the main challenges was getting that query to be readable and useable within our Rails application, because Rails doesn’t have an inbuilt helper method for generating UNION queries.

The first step was to extract each of the separate subqueries into the object responsible for interacting with the relevant database table. Each of these subqueries used the ActiveRecord query syntax we are familiar with. So for example, the Alert subquery looks like this:

And we have similar methods for each other subquery that get called and combined like this:

That produced everything we needed in the database query apart from the final two (pretty important) clauses, the first of which limits the records to only those in the past, and the second of which then orders them by date. I wanted to apply these two clauses by chaining familiar where and order methods onto the rest of the query, partly so we could later chain other methods such as limit and offset to easily return only a single page of results.

This meant applying the union_query in a way that returned an ActiveRecord Relation object, which ActiveRecord query methods can be chained onto. Here’s how I did it:

Here, the union_query is used in the from method to define a set of records we can then scope with where and sort with order.

The remaining problem was that I was using these ActiveRecord query methods in a class that didn’t have a corresponding table in the database. Instead it was using the results from the union_query as a sort of virtual table, but ActiveRecord raised errors because it was expecting an actual database table. The simplest solution was to just create a corresponding table, which I called virtual. This table exists in the database but has no rows and cannot be written to. If there’s a better way to bypass the errors, it would be great to know!

In the end, we had a query that does the job, is efficient, can be extended by chaining further ActiveRecord queries, and doesn’t feel too strange to developers familiar with ActiveRecord.

And it’s very simple to use:

TimelineItem.for_lead(lead)