Retrieving 1 pivot row per relation based on pivot values in Laravel belongsToMany relation

Question

Background - I'm creating a system where administrators can create arbitrary fields, which are then combined into a form. Users then complete this form, and the values input against each field are stored in a table. However, rather than overwrite the previous value, I plan on keeping each past value as individual rows in the table. I then want to be able to display the contents submitted in each form, but only the most recently submitted value.

Problem

I have a model, Service, that features a belongsToMany relationship with another model, Field. This relationship is defined as:

public function fields()
{
    return $this->belongsToMany('App\Field')->withPivot('id', 'value', 'date')->withTimestamps();
}

The intermediary table has 3 values I wish to retrieve, id, value and date.

A Service may have 1 or more Fields, and for each field it may also have more than 1 pivot row. That is, a single Service/Field pairing may have multiple entries in the pivot table with different pivot values. For example:

Table field_service

id      | service_id | field_id | value   | created_at
------------------------------------------------------
1       | 1          | 1        | lorem   | 2018-02-01
2       | 1          | 1        | ipsum   | 2018-01-01
3       | 1          | 1        | dolor   | 2017-12-01
4       | 1          | 2        | est     | 2018-03-10
5       | 1          | 2        | sicum   | 2018-03-09
6       | 1          | 2        | hoci    | 2018-03-08

What I want is to get either:

• A specific row from the pivot table for each Field associated with the Service, or
• A specific value from the pivot table for each Field associated with the Service.

For example - in the table above, I would like the Service with ID 1 to have 2 Fields in the relationship, with each Field containing an attribute for the corresponding pivot value. The Fields attached would be specified by the corresponding pivot table entry having the most recent date. Something akin to:

$service->fields()[0]->value = "lorem"
$service->fields()[1]->value = "est"

I feel there's an obvious, 'Laravel'ly solution out there, but it eludes me...

Update

Somewhat unbelievably this is another case of me not understanding windowing functions. I asked a question 7 years ago that is basically this exactly problem, but with raw MySQL. The following raw MySQL basically gives me what I want, I just don't know how to Laravelise it:

SELECT services.name, fields.name, field_service.value, field_service.created_at, field_service.field_id
FROM field_service
INNER JOIN 
    (SELECT field_id, max(created_at) as ts
    FROM field_service
    WHERE service_id = X
    GROUP BY field_id) maxt
ON (field_service.field_id = maxt.field_id and field_service.created_at = maxt.ts)
JOIN fields ON fields.id = field_service.field_id
JOIN services ON services.id = field_service.service_id

Answer 1

Try this:

public function fields()
{
    $join = DB::table('field_service')
        ->select('field_id')->selectRaw('max(`created_at`) as `ts`')
        ->where('service_id', DB::raw($this->id))->groupBy('field_id');
    $sql = '(' . $join->toSql() . ') `maxt`';
    return $this->belongsToMany(Field::class)->withPivot('id', 'value', 'created_at')
        ->join(DB::raw($sql), function($join) {
            $join->on('field_service.field_id', '=', 'maxt.field_id')
                ->on('field_service.created_at', '=', 'maxt.ts');
        });
}

Then use it like this:

$service->fields[0]->pivot->value // "lorem"
$service->fields[1]->pivot->value // "est"