Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

smatrouh's avatar

Pivot table with three columns

I have 3 models

Locations Services Documents

Each location can have many services .

Each Service can also belong to many locations

Each service can have many documents.

Each Document can belong to many services in different locations.

A Single service can have different documents depending on the locations.

For example, service1 may need document1 and document2 in location1, but need document1 and document3 in location2.

I have created a pivot table with three columns. location_id, document_id and service_id.

In a perfect world, I'd like to get a nested response from the 3 column pivot table. For example, something like:

Locations::with('services.documents')->get();

In my Location model, I have defined a belongsToMany with Service.

public function services() {
        return $this->belongsToMany(Service::class, 'pivot_table_name')->withPivot('service_id');
    }

In my Service model, I have defined a belongsToMany with 'Document`.

public function documents() {
        return $this->belongsToMany(Document::class, 'pivot_table_name')->withPivot('location_id');
    }

While I do get a nested response, the documents listed under a service are not unique to that location. If you have a look at location1->Service1->Document1, you will see two occurrences. One of those documents belongs to the service in location 1 and the second one belongs to the same service in location 2. Yet they're both showing up under location1.

Here's a sample response.

[
   {
      "id":1,
      "name":"location1",
      "created_at":null,
      "updated_at":null,
      "services":[
         {
            "id":1,
            "name":"Service1",
            "created_at":null,
            "updated_at":null,
            "pivot":{
               "location_id":1,
               "service_id":1
            },
            "documents":[
               {
                  "id":2,
                  "name":"Document1",
                  "created_at":null,
                  "updated_at":null,
                  "pivot":{
                     "service_id":1,
                     "document_id":2,
                     "location_id":1
                  }
               },
               {
                  "id":2,
                  "name":"Document1",
                  "created_at":null,
                  "updated_at":null,
                  "pivot":{
                     "service_id":1,
                     "document_id":2,
                     "location_id":2
                  }
               }
            ]
         },
         {
            "id":2,
            "name":"Service2",
            "created_at":null,
            "updated_at":null,
            "pivot":{
               "location_id":1,
               "service_id":2
            },
            "documents":[
               {
                  "id":3,
                  "name":"Document3",
                  "created_at":null,
                  "updated_at":null,
                  "pivot":{
                     "service_id":2,
                     "document_id":3,
                     "location_id":1
                  }
               }
            ]
         }
      ]
   },
   {
      "id":2,
      "name":"Location2",
      "created_at":null,
      "updated_at":null,
      "services":[
         {
            "id":1,
            "name":"Service1",
            "created_at":null,
            "updated_at":null,
            "pivot":{
               "location_id":2,
               "service_id":1
            },
            "documents":[
               {
                  "id":2,
                  "name":"Document1",
                  "created_at":null,
                  "updated_at":null,
                  "pivot":{
                     "service_id":1,
                     "document_id":2,
                     "location_id":1
                  }
               },
               {
                  "id":2,
                  "name":"Document1",
                  "created_at":null,
                  "updated_at":null,
                  "pivot":{
                     "service_id":1,
                     "document_id":2,
                     "location_id":2
                  }
               }
            ]
         }
      ]
   },
   {
      "id":3,
      "name":"Location3",
      "created_at":null,
      "updated_at":null,
      "services":[
         
      ]
   }
]

What am I missing? Is this possible within eloquent without multiple queries and manual processing or is there a magic eloquent one liner that will give me what I'm looking for.

0 likes
3 replies
rodrigo.pedra's avatar

What am I missing?

Nothing, you are missing nothing.

Unfortunately there are some situations an ORM does not cover the use-case we want. This is true for Eloquent not supporting three-way pivot tables. As I already told you on the other thread:

https://laracasts.com/discuss/channels/eloquent/laravel-9-3-column-pivot-table-nested-json

Which includes a proposed solution to your problem, that you seem not to believe is a good one.

Is this possible within eloquent without multiple queries and manual processing

Unfortunately no.

One thing I already suggested you on the previous thread was to write a custom relation class, extending from the BelongsToMany one, that would add the needed constraint under the hood.

Or to search for a package that already provides such relation.

or is there a magic eloquent one liner that will give me what I'm looking for.

No, there isn't.

If you believe this should be part of the Eloquent ORM you can try sending a PR to Laravel's GitHub repository.

But currently there isn't. Unfortunately an ORM cannot cover all use-cases, and more important, it would be very hard to maintain every use-case.

It seems you want to find something ready-made, by someone else's work, instead of collaborating with the solution to a use-case you are amused Eloquent does not ship with a built-in solution.

If you want a one-liner (actually a one-command) you can use the Model::fromRaw() and use a custom SQL statement:

Locations::with([
    'services.documents' => function ($relation) {
        // custom SQL that filters documents based
        // on the selected services 
        $relation->fromRaw('SELECT * FROM ...', [/* bindings */]);
    },
])->get();

Of course I believe you are not expecting someone to write the inner SQL for you, right?

Epilogue

On the other thread I showed you two solutions, what is wrong with them?

If you don't like the manual processing, or you think it is somehow ugly to have it around, you can move it to either a local scope or a custom collection.

Some good reading for you:

At the time of these post were written, Tim McDonald wasn't a core Laravel team member. As of today, he is.

Or you can try a third thread to see if someone else will come up with an easier, or a more beautiful/pleasing solution.

smatrouh's avatar

@rodrigo.pedra Hi Rodrigo. Your solution is great and is what we're using right now. However in the first thread my question was around getting locations.documents.

What I was hoping for is a command that we can use across multiple models. For example:

Locations::with('companies.services.documents')->get();

Or

Company::with('locations.services.documents')->get();

I'm grateful for your earlier answer as it has allowed me to move forward with the project at hand. But as always, I'm looking for ways to improve and refactor as I learn more.

Laravel has been an absolute joy to work with and I'm definitely looking at contributing to the community as opposed to taking/asking.

To reiterate, there is absolutely nothing wrong with your previous responses. I will however take your advise and move the processing to a local scope or custom collection. Of course I'll have to learn how to do these first.

Thank you for the reading material. I will read through them tonight.

1 like

Please or to participate in this conversation.