Get one to many relationship in the pivot table of another many to many relationship in Laravel 11

Question

I have a Place, User models with a many to many relationship as the pivot table / model named Reservation, I needed to make a new relationship between the pivot table and another table called reservation_statuses to link each reservation with its status and a new relationship between reservation_statuses and reservation_status_translations to store the reservation status in multiple languages.

Now I need to get all the logged in user reservations with the reservation status and its translation, here's my models / tables structure :

class Place extends Model
{
    use HasFactory, SoftDeletes;

    public function users()
    {
        return $this->belongsToMany(User::class, 'reservations')
            ->withPivot(['id', 'reservation_number', 'reservation_status_id'])
            ->as('reservations')
            ->withTimestamps();
    }
}

class User extends Authenticatable
{
    use HasFactory, HasApiTokens, Notifiable, SoftDeletes, HasRoles;

    public function places()
    {
        return $this->belongsToMany(Place::class, 'reservations')
            ->withPivot(['id', 'reservation_number', 'reservation_status_id'])
            ->as('reservations')
            ->withTimestamps();
    }
}

class Reservation extends Pivot
{
    use HasFactory, SoftDeletes;

    protected $table = 'reservations';

    protected $fillable = [
        'user_id',
        'place_id',
        'reservation_status_id'
    ];

    public function reservationStatus()
    {
        return $this->belongsTo(ReservationStatus::class);
    }
}

class ReservationStatus extends Model
{
    public function reservations()
    {
        return $this->hasMany(Reservation::class);
    }

    public function reservationStatusTranslations()
    {
        return $this->hasMany(ReservationStatusTranslation::class);
    }
}

class ReservationStatusTranslation extends Model
{
    use HasFactory;

    protected $fillable = [
        'name',
        'reservation_status_id',
        'language_id',
    ];
    
    public function reservationStatus()
    {
        return $this->belongsTo(ReservationStatus::class);
    }

    public function language()
    {
        return $this->belongsTo(Language::class);
    }
}

Now here's when I'm trying to get the authenticated user reservations :

public function index()
{
    $user = request()->user();
    $reservations = $user->places()->get(['places.id', 'name']);
    return $this->sendResponse(__('general.list_all_reservations'), $reservations);
}

and here is what I get :

{
    "success": true,
    "message": "List all reservations",
    "data": [
        {
            "id": 1,
            "name": "farm",
            "reservations": {
                "user_id": 151,
                "place_id": 1,
                "id": 122,
                "reservation_number": "772-826-152",
                "reservation_status_id": 4, // here I need the reservation status relationship and then access the reservation status translation relationship
                "created_at": "2024-06-25T19:23:16.000000Z",
                "updated_at": "2024-06-25T19:23:54.000000Z"
            }
        }
    ]
}

Any help is appreciated...

Answer 1

After many tries, I found a solution :

Basically I need to start the query build from my pivot table model and set the where condition to get only the the data from the authenticated user ID, in this case I'm able to call the with function to access the reservation status then reservation status translation relationships and then add the condition for the authenticated user only, here an example :

/**
 * Display a listing of the resource.
 */
public function index()
{
    $user = request()->user('sanctum');

    $reservations = Reservation::with(['reservationStatus.reservationStatusTranslations' => function ($query) {
        $query->select(['name', 'reservation_status_id']);
        $query->where('language_id', $this->language_id);
    }])->where('user_id', $user->id)->get(); // here i'm making sure I get only the reservations that belongs to the current user 

    return $this->sendResponse(__('general.list_all_reservations'), $reservations);
}

The response now :

{
    "success": true,
    "message": "View reservation",
    "data": {
        "id": 1,
        "reservation_number": "283-922-177",
        "amount": "236137.00",
        "discount_amount": "90777.00",
        "people_count": 1,
        "reservation_time": "2024-07-20 17:28:26",
        "deleted_at": null,
        "created_at": "2024-07-04T01:47:26.000000Z",
        "updated_at": "2024-07-04T01:47:26.000000Z",
        "reservation_status": {
            "id": 1,
            "name_code": "created",
            "created_at": "2024-07-04T01:47:24.000000Z",
            "updated_at": "2024-07-04T01:47:24.000000Z",
            "reservation_status_translations": [
                {
                    "name": "Created"
                }
            ]
        }
    }
}

Hope this helps someone.