Level 56
- How are you querying?
- How are the relationships defined on each model?
Share some code, so we can better help you
Sep 15, 2022
8
Level 8
Laravel 9 - 3 column pivot table nested JSON
I have the following models.
- Service
- Location
- Document
Each service can exist in multiple locations.
Each Document can exist in multiple services and location.
I'd like to query the Service model and get a nested response as below.
Service 1
- Location 1
- Document 1
- Document 2
- Location 2
- Document 1
- Document 3
Service 2
- Location 4
- Document 1
- Document 2
- Document 5
What's the best way to structure my databases and best practices for inserting and querying the data. I'm not expecting high volumes of data (1 new record created an hour at most), but I still like to follow best practices and guidelines.
I have tried creating a pivot table called document_location_service and I have three columns (document_id, location_id, and service_id). But when I query the table I get duplicate results in my JSON.
Level 56
Although your pivot is a 3-way pivot, you still need to define the relationships between model 2 by 2.
Add ->distinct() to the relationship definition.
For example the Service model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
class Service extends Model
{
public function documents(): BelongsToMany
{
return $this->belongsToMany(Document::class, 'document_location_service')
->distinct();
}
public function locations(): BelongsToMany
{
return $this->belongsToMany(Location::class, 'document_location_service')
->distinct();
}
}
the Location model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
class Location extends Model
{
public function documents(): BelongsToMany
{
return $this->belongsToMany(Document::class, 'document_location_service')
->distinct();
}
public function services(): BelongsToMany
{
return $this->belongsToMany(Service::class, 'document_location_service')
->distinct();
}
}
And the Document model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
class Document extends Model
{
public function locations(): BelongsToMany
{
return $this->belongsToMany(Location::class, 'document_location_service')
->distinct();
}
public function services(): BelongsToMany
{
return $this->belongsToMany(Service::class, 'document_location_service')
->distinct();
}
}
Then eager load the nested relations as such:
$services = App\Models\Service::query()
->with(['locations.documents'])
->get();
Level 8
@rodrigo.pedra Thank you Rodrigo. I have updated my classes but now get an error when I try to load the records. For loading I'm using this code:
return Service::query()->with(['locations.documents'])->get();
I get following error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'locations.document_location_service' in 'where clause' (SQL: select distinct * from locationswherelocations.document_location_servicein (1, 2) andlocations.deleted_at is null)
Here's how I have defined the document_location_service table migration.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateDocumentLocationServiceTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('document_location_service', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('document_id')->index();
$table->foreign('document_id')->references('id')->on('documents');
$table->unsignedBigInteger('location_id')->index();
$table->foreign('location_id')->references('id')->on('locations');
$table->unsignedBigInteger('service_id')->index();
$table->foreign('service_id')->references('id')->on('services');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('document_location_service');
}
}
My locations, services and documents tables all consist of an ID and Name (string) columns.
Any thoughts on what the issue could be?
Level 8
@rodrigo.pedra My apologies. I had an error in my relationship definition. I have left my previous comment for future visitors.
Instead of belongs to many on locations() I had defined it as hasMany.
The updated code returns a valid JSON, but the results are still not constrained to the parent object. Here's what it's currently returning.
[
{
"id":1,
"name":"Security",
"deleted_at":null,
"created_at":"2022-09-18T11:02:39.000000Z",
"updated_at":"2022-09-18T11:02:39.000000Z",
"locations":[
{
"id":2,
"name":"ACT",
"deleted_at":null,
"created_at":"2022-09-18T11:02:16.000000Z",
"updated_at":"2022-09-18T11:02:16.000000Z",
"pivot":{
"service_id":1,
"location_id":2
},
"documents":[
{
"id":1,
"name":"Security Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:22.000000Z",
"updated_at":"2022-09-18T11:02:22.000000Z",
"pivot":{
"location_id":2,
"document_id":1
}
},
{
"id":2,
"name":"Security Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:23.000000Z",
"updated_at":"2022-09-18T11:02:23.000000Z",
"pivot":{
"location_id":2,
"document_id":2
}
},
{
"id":3,
"name":"Cleaning Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:28.000000Z",
"updated_at":"2022-09-18T11:02:28.000000Z",
"pivot":{
"location_id":2,
"document_id":3
}
},
{
"id":4,
"name":"Cleaning Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:30.000000Z",
"updated_at":"2022-09-18T11:02:30.000000Z",
"pivot":{
"location_id":2,
"document_id":4
}
}
]
},
{
"id":1,
"name":"NSW",
"deleted_at":null,
"created_at":"2022-09-18T11:02:15.000000Z",
"updated_at":"2022-09-18T11:02:15.000000Z",
"pivot":{
"service_id":1,
"location_id":1
},
"documents":[
{
"id":1,
"name":"Security Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:22.000000Z",
"updated_at":"2022-09-18T11:02:22.000000Z",
"pivot":{
"location_id":1,
"document_id":1
}
},
{
"id":2,
"name":"Security Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:23.000000Z",
"updated_at":"2022-09-18T11:02:23.000000Z",
"pivot":{
"location_id":1,
"document_id":2
}
},
{
"id":3,
"name":"Cleaning Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:28.000000Z",
"updated_at":"2022-09-18T11:02:28.000000Z",
"pivot":{
"location_id":1,
"document_id":3
}
},
{
"id":4,
"name":"Cleaning Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:30.000000Z",
"updated_at":"2022-09-18T11:02:30.000000Z",
"pivot":{
"location_id":1,
"document_id":4
}
}
]
}
]
},
{
"id":2,
"name":"Cleaning",
"deleted_at":null,
"created_at":"2022-09-18T11:02:44.000000Z",
"updated_at":"2022-09-18T11:02:44.000000Z",
"locations":[
{
"id":2,
"name":"ACT",
"deleted_at":null,
"created_at":"2022-09-18T11:02:16.000000Z",
"updated_at":"2022-09-18T11:02:16.000000Z",
"pivot":{
"service_id":2,
"location_id":2
},
"documents":[
{
"id":1,
"name":"Security Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:22.000000Z",
"updated_at":"2022-09-18T11:02:22.000000Z",
"pivot":{
"location_id":2,
"document_id":1
}
},
{
"id":2,
"name":"Security Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:23.000000Z",
"updated_at":"2022-09-18T11:02:23.000000Z",
"pivot":{
"location_id":2,
"document_id":2
}
},
{
"id":3,
"name":"Cleaning Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:28.000000Z",
"updated_at":"2022-09-18T11:02:28.000000Z",
"pivot":{
"location_id":2,
"document_id":3
}
},
{
"id":4,
"name":"Cleaning Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:30.000000Z",
"updated_at":"2022-09-18T11:02:30.000000Z",
"pivot":{
"location_id":2,
"document_id":4
}
}
]
},
{
"id":1,
"name":"NSW",
"deleted_at":null,
"created_at":"2022-09-18T11:02:15.000000Z",
"updated_at":"2022-09-18T11:02:15.000000Z",
"pivot":{
"service_id":2,
"location_id":1
},
"documents":[
{
"id":1,
"name":"Security Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:22.000000Z",
"updated_at":"2022-09-18T11:02:22.000000Z",
"pivot":{
"location_id":1,
"document_id":1
}
},
{
"id":2,
"name":"Security Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:23.000000Z",
"updated_at":"2022-09-18T11:02:23.000000Z",
"pivot":{
"location_id":1,
"document_id":2
}
},
{
"id":3,
"name":"Cleaning Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:28.000000Z",
"updated_at":"2022-09-18T11:02:28.000000Z",
"pivot":{
"location_id":1,
"document_id":3
}
},
{
"id":4,
"name":"Cleaning Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:30.000000Z",
"updated_at":"2022-09-18T11:02:30.000000Z",
"pivot":{
"location_id":1,
"document_id":4
}
}
]
}
]
}
]
Here's what I'm expecting it to return.
[
{
"id":1,
"name":"Security",
"deleted_at":null,
"created_at":"2022-09-18T11:02:39.000000Z",
"updated_at":"2022-09-18T11:02:39.000000Z",
"locations":[
{
"id":2,
"name":"ACT",
"deleted_at":null,
"created_at":"2022-09-18T11:02:16.000000Z",
"updated_at":"2022-09-18T11:02:16.000000Z",
"pivot":{
"service_id":1,
"location_id":2
},
"documents":[
{
"id":1,
"name":"Security Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:22.000000Z",
"updated_at":"2022-09-18T11:02:22.000000Z",
"pivot":{
"location_id":2,
"document_id":1
}
},
{
"id":2,
"name":"Security Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:23.000000Z",
"updated_at":"2022-09-18T11:02:23.000000Z",
"pivot":{
"location_id":2,
"document_id":2
}
}
]
},
{
"id":1,
"name":"NSW",
"deleted_at":null,
"created_at":"2022-09-18T11:02:15.000000Z",
"updated_at":"2022-09-18T11:02:15.000000Z",
"pivot":{
"service_id":1,
"location_id":1
},
"documents":[
{
"id":1,
"name":"Security Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:22.000000Z",
"updated_at":"2022-09-18T11:02:22.000000Z",
"pivot":{
"location_id":1,
"document_id":1
}
},
{
"id":2,
"name":"Security Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:23.000000Z",
"updated_at":"2022-09-18T11:02:23.000000Z",
"pivot":{
"location_id":1,
"document_id":2
}
}
]
}
]
},
{
"id":2,
"name":"Cleaning",
"deleted_at":null,
"created_at":"2022-09-18T11:02:44.000000Z",
"updated_at":"2022-09-18T11:02:44.000000Z",
"locations":[
{
"id":2,
"name":"ACT",
"deleted_at":null,
"created_at":"2022-09-18T11:02:16.000000Z",
"updated_at":"2022-09-18T11:02:16.000000Z",
"pivot":{
"service_id":2,
"location_id":2
},
"documents":[
{
"id":3,
"name":"Cleaning Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:28.000000Z",
"updated_at":"2022-09-18T11:02:28.000000Z",
"pivot":{
"location_id":2,
"document_id":3
}
},
{
"id":4,
"name":"Cleaning Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:30.000000Z",
"updated_at":"2022-09-18T11:02:30.000000Z",
"pivot":{
"location_id":2,
"document_id":4
}
}
]
},
{
"id":1,
"name":"NSW",
"deleted_at":null,
"created_at":"2022-09-18T11:02:15.000000Z",
"updated_at":"2022-09-18T11:02:15.000000Z",
"pivot":{
"service_id":2,
"location_id":1
},
"documents":[
{
"id":3,
"name":"Cleaning Document 1",
"deleted_at":null,
"created_at":"2022-09-18T11:02:28.000000Z",
"updated_at":"2022-09-18T11:02:28.000000Z",
"pivot":{
"location_id":1,
"document_id":3
}
},
{
"id":4,
"name":"Cleaning Document 2",
"deleted_at":null,
"created_at":"2022-09-18T11:02:30.000000Z",
"updated_at":"2022-09-18T11:02:30.000000Z",
"pivot":{
"location_id":1,
"document_id":4
}
}
]
}
]
}
]
Level 56
Hmm, now I see. You need the documents to be also filtered by the location's parent service...
Well you will have to do some manual steps.
The easiest way is to eager load just the locations, and then iterate through the services and load the service's locations' documents constrained by the service .
Something like this:
$services = App\Models\Service::query()
->with(['locations'])
->get()
// iterate each service
->transform(function (Service $service) {
// eager load a Service's locations' document
$service->locations->load([
'documents' => fn($relation) => $relation
->wherePivot('service_id', $service->getKey()),
]);
return $service;
});
The drawback of this approach is that instead of 3 queries (one for all services, one for all locations, and one for all documents), you will end with 2 + number of services queries (one for all services, one for all locations, and one for each service to load its locations' documents).
Note that this is not as bad as lazy loading each location document, as we are going to execute an additional query for each service only, and not for each location belonging to a service.
If your services count is small (up to 20, or 30), or if you can paginate the results, use this and call it a day.
If your service count is big, then I recommend you writing a custom relation, or searching for a package that provide a custom relation where you can specify more than one constraint when eager loading, to improve the performance on doing this query.
Level 56
Another option if you won't have many documents per location is to eager load as before and at the end filter out the documents.
This would keep only 3 queries, but would require you to add a ->withPivot() clause to ever BelongsToMany definition to tell Laravel to load the "missing" id on that relation.
For example, in the Service model you would need to add this to its relations' definition:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
class Service extends Model
{
public function documents(): BelongsToMany
{
return $this->belongsToMany(Document::class, 'document_location_service')
->withPivot('location_id') // load location_id on the pivot
->distinct();
}
public function locations(): BelongsToMany
{
return $this->belongsToMany(Location::class, 'document_location_service')
->withPivot('document_id') // load document_id on the pivot
->distinct();
}
}
In the Service@locations relation, belongs to many would already load the service_id and location_id, as they both are part of the relation. So we tell Laravel to also load the document_id which is the "missing" id on this relation.
You'd need to do similar on the Location and Document models' relations definitions.
Then you can try this:
$services = Service::query()
->with(['locations.documents'])
->get()
->transform(function (Service $service) {
$service->locations->transform(function (Location $location) use ($service) {
$documents = $location->documents->filter(function (Document $document) use ($service) {
return $document->pivot->service_id == $service->getKey();
});
$location->setRelation('documents', $documents);
return $location;
});
return $service;
});
It requires a bit of more work, as you need to iterate over two nested collections, but you'd only execute 3 queries at the end.
It is a trade-off between performance and simplicity. See which fits better your use-case.
Level 8
@rodrigo.pedra you are a superstar. I tried your second method and it's working like a charm. 🙏
1 like
Level 56
@smatrouh I am glad to help. Have a nice day =)
Please or to participate in this conversation.