Laravel - one-to-one relation through pivot table with eager load

Question

I have this relationship

• A Movement can have multiples steps
• A Step can belongs to multiples Movements

So a had to create a pivot table and a belongsToMany relationship, but my pivot table have some extras columns, like finished and order

I want to have two relationships, one to get all steps from a movement and another one to get the current step from the movement (the last finished step)

I know how to get all steps

public function steps()
{
    return $this->belongsToMany(MovementStep::class, 'movement_movement_steps')
        ->withPivot('order', 'finished')
        ->orderBy('pivot_order');
}

But how about the current step? I need this kind of relationship, but returning only one record and be able to eager load it cause I'm passing it to vue.js

public function current_step()
{
    return $this->belongsToMany(MovementStep::class, 'movement_movement_steps')
        ->withPivot('order', 'finished')
        ->where('finished', true)
        ->orderBy('pivot_order', 'desc');
}

Notice, I'd like to do that without extras packages

alternative solution, but with extra package: Laravel hasOne through a pivot table (not the answer marked as correct, the answer from @cbaconnier)

Answer 1

A different approach from the answer provided by @mrhn is to create a custom relationship. Brent from Spatie did an excellent article about it

Although my answer will do the exact same queries than the one provided by staudenmeir's package it makes me realized that either you use the package, this answer or @mrhn answer, you may avoid the n+1 queries but you may still ends up will a large amount of hydrated models.

In this scenario, I don't think it's possible to avoid one or the other approach. The cache could be an answer though.

Since I'm not entirely sure about your schema, I will provide my solution using the users-photos example from my previous answer.

User.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{

    public function photos()
    {
        return $this->belongsToMany(Photo::class);
    }

    public function latestPhoto()
    {
        return new \App\Relations\LatestPhotoRelation($this);
    }
}

LastestPhotoRelation.php

<?php


namespace App\Relations;

use App\Models\User;
use App\Models\Photo;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\Relation;

class LatestPhotoRelation extends Relation
{

    /** @var Photo|Builder */
    protected $query;

    /** @var User */
    protected $user;

    public function __construct(User $user)
    {
        parent::__construct(Photo::query(), $user);
    }

    /**
     * @inheritDoc
     */
    public function addConstraints()
    {
        $this->query
            ->join(
                'user_photo',
                'user_photo.photo_id',
                '=',
                'photos.id'
            )->latest();

            // if you have an ambiguous column name error you can use
            // `->latest('movement_movement_steps.created_at');`
    }

    /**
     * @inheritDoc
     */
    public function addEagerConstraints(array $users)
    {
        $this->query
            ->whereIn(
                'user_photo.user_id',
                collect($users)->pluck('id')
            );
    }

    /**
     * @inheritDoc
     */
    public function initRelation(array $users, $relation)
    {
        foreach ($users as $user) {
            $user->setRelation(
                $relation,
                null
            );
        }
        return $users;
    }

    /**
     * @inheritDoc
     */
    public function match(array $users, Collection $photos, $relation)
    {
        if ($photos->isEmpty()) {
            return $users;
        }

        foreach ($users as $user) {
            $user->setRelation(
                $relation,
                $photos->filter(function (Photo $photo) use ($user) {
                    return $photo->user_id === $user->id;  // `user_id` came with the `join` on `user_photo`
                })->first() // Photos are already DESC ordered from the query
            );
        }

        return $users;
    }

    /**
     * @inheritDoc
     */
    public function getResults()
    {
        return $this->query->get();
    }
}

Usage

$users = \App\Models\User::with('latestPhoto')->limit(5)->get();

The main difference from Brent's article, is that instead of using a Collection we are returning the latest Photo Model.

Answer 2

Laravel has a way to create getters and setters that act similar to columns in the database. These can perfectly solve your problem and you can append them to your serialization.

So instead your current_step is gonna be an accessor (getter). The syntax is getCurrentStepAttribute() for the function which will make it accessible on the current_step property. To avoid N + 1, eager load the steps when you retrieve the model(s) with the with('steps') method. Which is better than running it as a query, as it will execute N times always.

public function getCurrentStepAttribute() {
    return $this->steps
        ->where('finished', true)
        ->sortByDesc('pivot_order')
        ->first();
}

Now you can use the append property on the Movement.php class, to include your Eloquent accessor.

protected $appends = ['current_step'];