Morph ALL The things! — Linking Multiple Table Layers With Double-Polymorphic Eloquent Relations
A significant part of what we do at Smartbnb is automating messaging between hosts and guests on Airbnb and HomeAway/VRBO. Messages that our system captures from the two platforms are stored in separate tables. This includes automated messages that we send that are of course real messages that show up on each respective platform shortly thereafter. The actual fact of sending the automated messages is logged in three other tables distinct from the two already mentioned (immediate responses, scheduled responses based on check-in/check-out times, and answers to questions).
As part of our current web app redesign, we are also spending significant time and effort on optimising key parts of the system. The inbox is one of those parts. One of the recent challenges was how to very efficiently label any given conversation message as having been sent by us, with a key caveat: the legacy code hadn’t been storing the resulting platform-specific message identifier in the three “fact” tables after sending an automated message.
Essentially the legacy code looked a little like this (simplified to get the point across):
The Problem
Although working so far, this made building a clean API codebase for the Angular frontend a lot less satisfying and so many loops and text comparisons are just ugly and inefficient.
From an architectural perspective, this also meant that we couldn’t know if any given message in isolation (separately from the parent thread) was automated without first explicitly loading data from three other tables and vice versa: we couldn’t know which real message an automated “fact” refers to without loading all messages in a thread.
It wasn’t going to be a simple matter of writing a migration to add an additional real-message identifier column to the necessary tables. The tables in question have millions of rows and hold gigabytes of data. Such a migration would have serious operational implications due to database replication lag and overall downtime impact on 24/7 automated messaging. Plus you simply reach a point where you agree not to touch the structure of such heavy and heavily-used tables.
I had a feeling that Laravel’s Polymorphic Eloquent Relations may have what we needed, but they tend to be used in a very straightforward manner, along the lines of A Taggable Morph can be used to attach Tags to both Post and Image models. Not quite what we had going on here.
What we needed was a way to link five tables across two table layers in a single concept of “this real message is linked with this automated message,” while also supporting simple eager loading and id-based, indexed lookups.
Challenge accepted
This took staring at the screen, mind blank, for longer than I’d like to admit, but really all that was required in the end was a logical leap over the barrier of “one table, one morph.”
The initial stumbling block was thinking in terms of trying to immediately accomplish something like
echo $message->automatic->rule->name;… where automatic is the “fact” of the automated message that we sent and the rule being the rule template applied to it according to the customer’s setup.
But we’re ahead of ourselves. Let’s backtrack.
We have two tables for two platforms containing real messages and we want to link those — let’s just make it simple for now — to a single “scheduled” table. This is perfect for a Polymorphic Eloquent Relation. We can define the morph as something like real_id and real_type, the former being the message identifier in one of those real message tables and the latter being the model class representing that table. So far so good.
But! Remember that altering any of these big tables is a big No-No! So we can’t add these morph columns to existing schemas. What to do?
What this required was thinking of the relation that we were trying to get to less as an identifier sitting in some column but more like the kind of pivot table used in Many-to-Many Eloquent Relations. What this was really about was creating a middleman layer between the two other table layers that I was trying to link.
That was the first breakthrough. This is a morphOne() relation but there’s nothing stopping us from dropping it into its own, dedicated table and having an Eloquent model powering it.
This allows us to do $message->automated->scheduled->rule to get the automatic scheduled message entry, and eager load them!
But we don’t want just that…
We want 3 tables rather than just 1.
The first approach might be to just add three different columns for immediate, scheduled, and answer identifiers to the AutomatedMessage model, but that would be 2 out of 3 columns always being null and the need to check three different relations to know that a message was automatic. Too much work, and quite frankly it feels wrong.
Then came the second breakthrough.
Isn’t this also just a polymorphic relation, but in the other direction? Here we’re linking not the real tables to the other three, but the AutomatedMessage “pivot” table to the other three. We can add an “automatic” morph to the AutomatedMessage model:
And here’s the underlying table schema migration used to enable this:
Which means we’re almost there! All that’s left to accomplish the $message->automatic->rule->name shorthand without having ->automated-> in the middle there is to add a dynamic accessor method in the Message model:
So now we are ready to start using this single magical line …
… to make David our API engineer happy, and by proxy Ben our frontend engineer happy! Smiles all around!
I love using Laravel and finding new ways to squeeze more oomph out of the framework with golden nuggets such as these.
I have, of course, glossed over the fact that none of the existing data is capable of doing this. The legacy code at the top of this post still came in handy to create and back-fill the relevant AutomatedMessage entries for existing data in batches.
In a future post I’ll detail how we used Traits, trait inspections, and Eloquent Relation subclasses to automatically inject additional column constraints into relational queries behind the scenes to make Smartbnb developers’ lives easier.
We’re hiring!
Want to join an amazing, fully-remote team that’s on a mission to solve global short-term rental automation? We’re growing rapidly. Check out our open positions on Angelist: https://angel.co/smartbnb/jobs
