Eager load three way pivot table Laravel

Question

I am struggling to get eager loading working in Laravel when working with a three way pivot table. The database is set up as follows:

+-------+--------+-------+-------------+
| deals | venues | users |   redeems   |
+-------+--------+-------+-------------+
| id    | id     | id    | deal_id     |
| title | name   | name  | user_id     |
|       |        |       | venue_id    |
|       |        |       | redeemed_at |
+-------+--------+-------+-------------+

Where redeems is obviously the pivot table. The user model look like this:

<?php

class User extends Authenticatable
{
    /**
     * The deals redeemed by the user.
     */
    public function deals()
    {
        return $this->belongsToMany(Deal::class, 'redeems')
            ->withPivot(['venue_id', 'redeemed_at']);
    }
}

This allows me to do this:

$users = Users::with('deals')->all();

And then I get a nice overview of the user and all the deals that they have redeemed in a structure like this:

{
  "id": 1,
  "name": "Diederik",
  "deals": [
    {
      "id": 33,
      "title": "Deal Title",
      "pivot": {
        "user_id": 1,
        "deal_id": 33,
        "venue_id": 50
      }
    }
  ]
}

Now I would like to add the venue information by eager loading it instead of querying the $user->pivot->venue_id afterwards.

I tried setting up a belongsTo relationship in the deal model and then doing a nested with like so: $users = Users::with('deals.venue')->all(); but that does not work because the deal table does not have a foreign key to a venue. This is because a deal can be applied to multiple venues.

I am aware of the fact that this is very easily achievable using normal database queries, but for the way our API works it is essential to use the eloquent with relation function as our URL setup looks for includes that way.

TLDR: How to eager load nested three way pivot table relations.

Answer 1

I would sugest you to treat the pivot table as another model (Redeem). Then make one-to-many relationships with the others tables/models (Deal/Venue/User) so you could just make your query using Eloquent like this to lazy load the objects you need:

$redeems = User::find($id)->redeems;
$redeems->load(['venue','deal']);

return $redeems;

To format the data/response in an exact way you can use API Resources if your using 5.5. Or Fractal for 5.4 or below.