Laravel API: Load nested relationship in resource

Question

I've got 3 models: Order, OrderItem and Product (for simplicity just showing the relationships):

class Order extends BaseModel
{
    use Uuid;

    protected $casts = [
        'status' => OrderStatuses::class,
    ];

    /**
     * An Order has multiple OrderItems associated to it.
     * @return HasMany
     */
    public function orderItems(): HasMany
    {
        return $this->hasMany(OrderItem::class);
    }

class OrderItem extends BaseModel
{
    /**
     * Get the Order the OrderItem belongs to.
     * @return BelongsTo
     */
    public function order(): BelongsTo
    {
        return $this->belongsTo(Order::class)
                ->withDefault();
    }

    /**
     * Get the product associated to this OrderItem.
     * @return HasOne
     */
    public function product(): HasOne
    {
        return $this->hasOne(Product::class, 'id');
    }
}

class Product extends BaseModel
{
    use Uuid;

    /**
     * Get the category the product belongs to.
     * @return BelongsTo
     */
    public function category(): BelongsTo
    {
        return $this->belongsTo(Category::class, 'category_id');
    }

with their respective DB tables:

orders: id, status, subtotal, total 
order_items: id, order_id, product_id, qty, price, total
products: id, name, slug, sku, description, price

I've got only a controller for Order and Product but I do have resources for all 3:

class OrdersResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => (string)$this->id,
            'type' => 'orders',
            'attributes' => [
                'status' => ($this->status)->value(),
                'payment_type' => $this->payment_type,
                'payment_transaction_no' => $this->payment_transaction_no,
                'subtotal' => $this->subtotal,
                'taxes'  => $this->taxes,
                'total' => $this->total,
                'items' => OrderItemsResource::collection($this->whenLoaded('orderItems')),
                'created_at' => $this->created_at,
                'updated_at' => $this->updated_at,
            ]
        ];
    }
}

class ProductsResource extends JsonResource
{
    public function toArray($request) : array
    {
        return [
            'id' => $this->id,
            'type' => 'products',
            'attributes' => [
                'barcode' => $this->barcode,
                'name' => $this->name,
                'slug' => $this->slug,
                'sku' => $this->sku,
                'description' => $this->description,
                'type' => $this->type,
                // todo return  category object?
                'category' => new CategoriesResource($this->whenLoaded('category')),
                'wholesale_price' => $this->wholesale_price,
                'retail_price' => $this->retail_price,
                'base_picture' => ($this->base_picture ? asset('images/products/' . $this->base_picture) : null),
                'current_stock_level' => $this->current_stock_level,
                'active' => $this->active,
            ]
        ];
    }
}

class OrderItemsResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'order_id' => $this->order_id,
            'product_id' =>  $this->product_id,
            'qty' => $this->qty,
            'price' => $this->price,
            'total' => $this->total,
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
        ];
    }
}

When hitting my orders controller I return the data (in this case for displaying an order) like this:

public function show(Order $order): JsonResponse
    {
        return (new OrdersResource($order->loadMissing('orderItems')))
            ->response()
            ->setStatusCode(Response::HTTP_OK);
    }

So far so go, the order is returned like this:

{
    "data": {
        "id": "20d9b0f3-2b32-45a7-8814-12c77210ad50",
        "type": "orders",
        "attributes": {
            "status": "new",
            "payment_type": "",
            "payment_transaction_no": "",
            "subtotal": 71000,
            "taxes": 0,
            "total": 71000,
            "items": [
                {
                    "id": 9,
                    "product_id": "444b0f3-2b12-45ab-3434-4453121231ad51",
                    "order_id": "20d9b0f3-2b32-45a7-8814-12c77210ad50",
                    "qty": 10,
                    "price": 200,
                    "total": 2000,
                    "created_at": "2022-11-05T16:26:07.000000Z",
                    "updated_at": "2022-11-05T16:28:02.000000Z"
                },
                {
                    "id": 10,
                    "product_id": "324b0f3-2b12-45ab-3434-12312330ad50",
                    "order_id": "20d9b0f3-2b32-45a7-8814-12c77210ad50",
                    "qty": 3,
                    "price": 23000,
                    "total": 69000,
                    "created_at": "2022-11-05T16:26:29.000000Z",
                    "updated_at": "2022-11-05T16:26:29.000000Z"
                }
            ],
            "created_at": "2022-11-05T16:26:07.000000Z",
            "updated_at": "2022-11-05T16:28:02.000000Z"
        }
    }
}

but now I'm in need of returning the actual product as part of OrderItem instead of just the product ID, so I updated my resource to include: 'product' => new ProductsResource($this->whenLoaded('product')),

my resource ended up looking like this:

public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'order_id' => $this->order_id,
            'product' => new ProductsResource($this->whenLoaded('product')),
            'qty' => $this->qty,
            'price' => $this->price,
            'total' => $this->total,
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at,
        ];
    }

yet the product is not visible in my response, what am I missing? Isn't that the right way to load it? do i need to load it from my controller?

Thanks

UPDATE

I've updated the relationship in OrderItem, now it looks like the following:

public function product(): HasOne
    {
        return $this->hasOne(Product::class, 'id', 'product_id');
    }

Where id points to the id column in products table, and product_id is the FK in order_item.

At this point if I do the following:

// just get any orderItem
$orderItem = OrderItem::first();
dd($orderItem->product);

I do see that the relationship is working because the product is being printed, but the object is still not part of the API response, meaning that in my resource this line is not working as expected:

'product' => new ProductsResource($this->whenLoaded('product')),

UPDATE 2

I updated the way I was trying to load product in the resource to be either of these two:

'product' => $this->load('product'),

'product' => $this->loadMissing('product'),

but that's giving me a nested object over the already nested one like this:

{
    "data": {
        "id": "20d9b0f3-2b32-45a7-8814-12c77210ad50",
        "type": "orders",
        "attributes": {
            "status": "new",
            "payment_type": "",
            "payment_transaction_no": "",
            "subtotal": 71000,
            "taxes": 0,
            "total": 71000,
            "items": [
                {
                    "id": 9,
                    "order_id": "20d9b0f3-2b32-45a7-8814-12c77210ad50",
                    "product": {
                        "id": 9,
                        "order_id": "20d9b0f3-2b32-45a7-8814-12c77210ad50",
                        "product_id": "f6bd3290-7748-49fa-8995-e0de47291fc9",
                        "qty": 10,
                        "price": 200,
                        "total": 2000,
                        "created_at": "2022-11-05T16:26:07.000000Z",
                        "updated_at": "2022-11-05T16:28:02.000000Z",
                        "product": {
                            "id": "f6bd3290-7748-49fa-8995-e0de47291fc9",
                            "barcode": "010101010101010101",
                            "name": "Test 5",
                            "slug": "test-5",
                            "sku": "t55te345c",
                            "description": "asd asd asd asd asd",
                            "type": "goods",
                            "category_id": 4,
                            "wholesale_price": 34,
                            "retail_price": 200,
                            "base_picture": null,
                            "current_stock_level": 0,
                            "active": 1,
                            "created_at": "2022-09-23T16:29:18.000000Z",
                            "updated_at": "2022-09-23T22:00:40.000000Z"
                        }
                    },
                    "qty": 10,
                    "price": 200,
                    "total": 2000,
                    "created_at": "2022-11-05T16:26:07.000000Z",
                    "updated_at": "2022-11-05T16:28:02.000000Z"
                }
    ]
   }
  }
}

Notice how product now has all over again the data from items

Answer 1

In your controller:

$order->loadMissing('orderItems.product')

Answer 2

There are a few problems.

1. Your Product model is missing the relationship to OrderItem. You defined:

   // In OrderItem model
   public function product(): HasOne
   {
       return $this->hasOne(Product::class, 'id');
   }
   

   And you are missing the required inverse relationship:

   // In Product model
   public function orderItem(): BelongsTo
   {
       return $this->belongsTo(OrderItem::class);
   }
   

2. It looks like your schema is incorrect. Consider the hasOne() example in the docs. If a User hasOne() Phone, then:

   In this case, the Phone model is automatically assumed to have a user_id foreign key

   In terms of the schema, it means the phones table has a user_id column. Compare that to your OrderItem which hasOne() Product. The convention described in the example above means that the products table is expected to have an order_item_id column. But it does not - you need to add that.

   Maybe you started this relationship around the other way (Product hasOne OrderItem), because you do have an order_items.product_id column - but that is not required with the relationships you have shown. You should remove that field.

3. In the OrderItem model you have overruled the default foreign key and specified it as id:

   public function product(): HasOne
   {
       return $this->hasOne(Product::class, 'id');
   }
   

   This means the id column in the products table represents the OrderItem ID. That doesn't make any sense, and I am guessing that is incorrect. Just remove that id in the relationship, and the default conventions will mean Laravel will look for an order_item_id field in the products table, exactly as described in 2) above.

   public function product(): HasOne
   {
       return $this->hasOne(Product::class);
   }
   

   In my experience you almost never need to override Laravel's default foreign key conventions, unless you're doing something particularly strange. Even then, it complicates things enough that it is maybe worth rethinking your schema just to get it to match the expected conventions!

Answer 3

Ok, I finally figured it out. Thanks to both @Bob and @Don'tPanic whose answers pointed me in the right direction.

First I needed to correct my relationship definition in OrderItem model specifying both keys used in the current table and in the table where the related field is being used:

public function product(): HasOne
    {
        return $this->hasOne(Product::class, 'id', 'product_id');
    }

but that itself was not being enough for my resource to return the expected data via this:

'product' => new ProductsResource($this->whenLoaded('product')),

I also needed to load the relationship in my controller:

public function show(Order $order): JsonResponse
    {
        return (new OrdersResource($order->loadMissing('orderItems', 'orderItems.product')))
            ->response()
            ->setStatusCode(Response::HTTP_OK);
    }

That was the last step needed. Once again, thanks to both of you.