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

Here is my answer, and I hope I can bring some light to your problem. I already published a GitHub repository with an example of all the code I write here. I added more information about replicating my scenario there in the README.md file.

The Question:

How to load Pivot data from a Belongs To Many relations?

My Answer and Recommendations:

Here is my interpretation of your database and its relations. You can review all the migrations into the repository. My intent here is not to define your structure as is but to help you understand how Laravel works. I hope after this, you can change, adapt or update your project values as needed.

I expect to provide you with information about how to interact with your Pivot tables and how to manipulate them to hide and transform data by casting the pivot table properties. Laravel uses the relational Pivot table as an add-on. If you need this information, you need to attach the details of the required pivot data in the model.

First, you can define the relationships on any model, but I choose to present the pivot data on the Product model. Here's the model definition (All the dots represent hidden data to simplify the class definition, but you can review the whole model here):

<?php
...
class Product extends Model
{
...

    protected $with = ['properties']; // 6

    public function properties(): BelongsToMany // 1
    {
        return $this->belongsToMany(Property::class) // 2
            ->as('attributes') // 3
            ->using(ProductProperty::class) // 4
            ->withPivot('position'); // 5
    }
}

Let's explain what's happened here:

1. The properties methods represent the relation between the Product and their Attributes.
2. As Product is the parent object, you could represent this relationship with a hasMany relation, but it disallows you from relating the Pivot table. To connect the Pivot table, you must define this relation as a BelongsToMany relation. This unlocks all the other methods specified in the example.
3. The as method allows you to rename the relation. By default, Laravel calls any pivot-loaded data by the pivot keyword. In this case, we rename this with the attributes value.
4. The using method allows you to use a defined Pivot model to cast and modify the behavior of the data retrieved from the Pivot table.
5. The withPivot method defines all the extra required fields from the Pivot relation. By default, Laravel loads the primary key columns from the related base models. In this case, I'm adding the position column from the Pivot table.
6. The $with protected variable defines which relations should be loaded when you call the Product model. In this case, I'm adding all the Properties of a Product when you load any product.

Next, let's explain what happens on the ProductProperty Model (You can review the whole model here):

<?php
...
class ProductProperty extends Pivot // 1
{
    protected $casts = [ // 2
        'position' => 'integer',
    ];

    protected $hidden = [ // 3
        'product_id',
        'property_id',
    ];
}

Let's explain as before:

1. As you can see, the ProductProperty class extends the Pivot class. This is REALLY important. This is how Laravel identifies this as a Pivot relation and not a base model. (And that's why you can use this in the Product model with the using method).
2. The $cast protected property allows you to define column types to take advantage of data transformation. More info here: Eloquent: Mutators & Casting
3. The $hidden protected property allows you to define the list of columns you don't want to present when you see a relationship that includes this Pivot table Model definition.

Ok. Now you know how to define the Pivot relation. But, How can I use it? Let's check the ProductController definition (You can review the whole controller here):

<?php
...
class ProductController extends Controller
{
    public function index(Request $request)
    {
        $products = Product::all(); // 1
        return new ProductCollection($products); // 2
    }
}

// Returned data:
/**
{
   "data":[
      {
         "id":1,
         "name":"Costa Rica",
         "properties":[
            {
               "id":1,
               "name":"DeepSkyBlue",
               "attributes":{ // By default Laravel use 'pivot' here, but we replace this with the 'attributes' name.
                  "position":8
               }
            },
            {
               "id":2,
               "name":"Azure",
               "attributes":{
                  "position":8
               }
            },
            {
               "id":3,
               "name":"LavenderBlush",
               "attributes":{
                  "position":8
               }
            }
         ]
      },
    ... // Sample of 1 Product with 3 Properties
   ]
}
 */

What's happening here:

1. I'm calling all the existing products from the Database. As we have the $with property defined on the Product model, all Products are returned with their Properties.
2. I'm returning the Products wrapped on API Resources to allow the transformation of the Endpoint structure if needed in the future. Here is more information: Eloquent: API Resources

Well, that's it! Here I covered the basics as simply as I can. I hope this information could help.

---

For this example, I used Laravel Blueprint to accelerate the project scaffolding. You can read more about it here: Laravel Blueprint

Answer 2

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.