Pivot Tables vs Pivot Models

Lately, I have been working on multiple projects that have to deal with Polymorphic relationships, Many-To-Many, relationships, and the occasional Polymorphic Many-To-Many relationship. In working with these, a few patterns begun to arise that really irked me when thinking about good architecture and traditional ORM relationships. So, starting this week, we'll look at new patterns when creation your relationship maps. Today, we will look at standard Many-To-Many tables and their related pivot table.

Consider a run mapper as our domain. A user can log a day which will have multiple laps of various tracks or routes. It's relatively simple you think so you create the following schema

days:
    date
    temperature
    #more info
routes:
    gis_points
    public
    distance
    title
    #more stuff
day_route: #this will be our many-many pivot table
    day_id
    routes_id
    laps

This works ok, but things start to get a bit strange when you consider how would you grab the total distance for a day. Well, you might make something like this (pseudo-PHP implementation use Eloquent)

public function getTotalDistanceAttribute()
{
    $routes = $this->routes()->list('laps', 'distance'); //Makes an associative array where with each $lap => $distance
    $total = 0;
    foreach($routes as $laps => $distance) {
        $total += ($laps * $distance);
    }
    return $total;
}

That works ok, but it does seem like the day is doing a bit much. For instance, what would happen if I wanted to see the total distance that you spent on a single route for that day. You could do some custom accessors, but where would you put that logic? In the route model? In a custom service? That seems a bit mad to me.

We already have a Pivot table and we want to start doing logic on this table so shouldn't that be more of a model? Consider this schema instead of the original

days:
    date
    temperature
    #more info
routes:
    gis_points
    public
    distance
    title
    #more stuff
runs: #this was out day_route table
    day_id
    routes_id
    lap_distance
    laps

If you look at this it is just renaming our pivot to be a model Run with the exact same information! Now our Run will know it's own total distance that is really easily accessible! To make things even better, our totalDistance accessor benefits from this too.

public function getTotalDistanceAttribute()
{
    $sum = 0;
    $this->runs->each(function($run) use(&$sum) {
        $sum += $run->distance;
    });
    return $sum;
}

This is much cleaner in my mind. The Day shouldn't need to think about how to calculate the distance for each route within that day, it just knows how to sum up the distances. Then Run can be ready to worry about all of its logic while also storing the distance of the route when it it tackled (what if later in time the user modifies the route, then all of the past days' distances would be messed up).

We'll look at tackling similar solutions as the week goes on and see how this helps us all over the place, not just by saving our getTotalDistanceAttribute function one line.