How to access a Pivot table from an API Resource in Laravel 9

Question

I am building an API using Resources. I have a Product (Chocolate Cake) that can be linked to a Property (Allergies) that has Properties_property (Glutten) that need to be shown in different orders for every product.

   product    <---[many-to-many] --->   properties
     ^                                      ^
     |                                      | 
[many-to-many]-->  properties_property  --[belongs-to] 
(pivot table)
  position

The tables look like this:

products:
id
name

product_property (first pivot table)
product_id
property_id

properties:
id
name 

properties_properties
id
name

product_properties_property (this is the pivot table witht the value) 
product_id
properties_property_id
position

The aspired JSON out put of https://localhost/product would be :

{
    "product": [{
        "product_id": 1,
        "name": "Choco Cake",
        "properties": [{
            "property_id": 1,
            "name": "Allergies",
            "properties_properties": [{
                "properties_property_id": 1,
                "name": "Gluten",
                "position": 1
            }]
        }]
    }]
}

The PropertiesProperty Model has a belongs to many relation in it like so:

public function products () {
  return $this->belongsToMany(Product::class)->withPivot('position');
}

I throw all the products in there from the routes/api.php

Route::get('/product', function () {
    return new ProductCollection(Product::all());
});

I have the following Resources: ProductResource, PropertyResource and PropertiesPropertyResource. The resources link to one another like so:

return [
 'product_id' => $this->product_id,
 'name' => $this->name,
 'properties' => ProductsPropertyResource::collection($this->properties)
];

In the Resource of Properties_property I would like to access the position field of the pivot table. How do I go about this? Idealy my App\Http\Resource\PropertiesPropertyResourse.php would look something like:

return [
  'properties_property_id' => $this->id,
  'name' => $this->name,
  'position' => $this->products->pivot->position
];

But this returns an "Property[pivot] does not exist on this collection"

I can write an sql in my PropertiesPropertyResource.php to get it like so:

return [
...
'position' => $this->products->where('id', $this->product_id)->first()->pivot->position
],

This does a lot of extra loading! The problem (I think) is that I want to access the data on the pivot table in the resource from the parent Resource (ProperiesProperty) not the child(Product) like you would usually do. Is there not a more Laravel-like method to do this?

REQUESTED INFO: There are about 230 connections in the pivot table currently, so this should not be a big deal.

UPDATE:

I found this question and I tried the solution:

'position' => $this->whenPivotLoaded ('product_properties_property', function () {
  return $this->pivot->position;
}),

but here the position key didn't even show up in the Json of the /product endpoint. I am starting to suspect that you need to fill these values with the SQL that you put in the controller or (in my case) the routes/api.php file.

Answer 1

You can't reach the pivot table product_properties_property in the PropertiesPropertyResource since it is not yet known there which relation is loaded. (as @Nicklas Kevin Frank pointed out)

Best solution in this case would be to rename your pivot to something like "attributes" in the model(thanks @Ricardo Vargas for that idea):

public function properties_properties()
{
   return $this->belongsToMany(Product::class) 
       ->as('attributes') 
       ->withPivot('position'); 
}

Then make a PropertiesPropertyAttributeResource that returns the sought after attribute(s):

class PropertiesPropertyAttributeResource extends JsonResource
{
    public function toArray($request)
    {
      return [
          'position' => $this->attributes->position,
        ];
    }
}

And load this in the PropertiesPropertyResources:

class ProductsPropertiesPropertyResource extends JsonResource
{
    public function toArray($request)
    {
      return [
        'properties_property_id' => $this->id,
        'name' => $this->name,
        'attributes' => 
  PropertiesPropertyAttributeResource::collection(
    $this->products
      ->where('id',
        //this is a hack i am using to get access to the current product_id
        $request->get('my_name')['product_id])
      ];
    }
}

This way the pivot table values will show up in your JSON like so:

{
    "product": [{
        "product_id": 1,
        "name": "Choco Cake",
        "properties": [{
            "property_id": 1,
            "name": "Allergies",
            "properties_properties": [{
                "properties_property_id": 1,
                "name": "Gluten",
                "attributes" [{
                    "position": 1
                }]
            }]
        }]
    }]
}

NOTE: In the case I asked about, where there are 2 many-to-many relationships between the source and eventual record, you need to create and Eloquent object that contains the right information for generating the output using the API Resources. There is no default way for _PropertiesProperty to know what Product originated the request. My hack displayed here only works on small data sets, as soon as the collection of $this->products becomes to big this will be a major memory eater.