lastInteraction relation

Readme.md

First, I'm french, sleep deprived, the last thing I want is to appear smug or passive aggressive because my english isn't good enough.

In yesterday's Advanced Querying with Eloquent talk (which was very good), you defined a lastInteraction relation like this :

public function lastInteraction()
{
    return $this->hasOne(Interaction::class, 'id', 'last_interaction_id');
}

public function scopeWithLastInteraction($query)
{
    $query->addSubSelect('last_interaction_id', Interaction::select('id')
        ->whereRaw('customer_id = customers.id')
        ->latest()
    )->with('lastInteraction');
}

Which would be used like this :

$customers = Customer::with('company')
    ->withLastInteraction()
    ->orderByName()
    ->paginate();

What prevents you from doing the following thing instead ?

public function lastInteraction()
{
    return $this->hasOne(Interaction::class)->latest();
}

Then you can use it like any relation :

$customers = Customer::with('company', 'lastInteraction')
    ->orderByName()
    ->paginate();

Am I missing something which will bite me in the ass later ?

I started using this approach a few days ago in prod. So far I didn't see any problems.

If you look into the getResults method of the HasOne relation class (here), first is called, so you won't get multiple results. And by using the latest method on the relation, I can see the two being functionnally equivalent, but with a bland relation instead of a special last_interaction_id attribute.

So, when put latest() on the relationship, all that does is SORT the interaction records, it does not LIMIT them. This is the key problem. You literally end up with the exact same amount of records (in the case of my demo yesterday, 7500 interactions for 15 users).

You need a limit(1) to only get the latest only. However, as mentioned on Twitter, that won't work with eager loading, since you'll only get one record back, not one for each customer (15).

With latest

    public function lastInteraction()
    {
        return $this->hasOne(Interaction::class)->latest();
    }

Here's the query that we get:

select * from "interactions" where "interactions"."customer_id" in ('177', '211', '249', '282', '390', '401', '460', '485', '617', '661', '763', '856', '878', '893', '959') order by "created_at" desc

Which is obviously bad, because we're not limiting the data at all, only sorting. So that still pulls 7500 records from the database, even though we only end up with one.

We can see that reflected in our memory usage:

I can see how this LOOKS like it might be right, because the hasOne() grabs the first one automatically, but be very aware of the fact that you are literally still get all the interaction records from the database

With latest and limit

    public function lastInteraction()
    {
        return $this->hasOne(Interaction::class)->latest()->limit(1);
    }

This would be the ideal way of doing it, but it's obviously not possible, because we only get 1 record back, not 15.