How to return the data of a pivot table in the same resource LARAVEL

Question

I have a small question on my LARAVEL API. How can I return and concatenate the data of a pivot table in the same resource? I have 3 tables, inventories, products and inventories_products. This last table has stock and price data (of the product, because they vary depending on the inventory) and I want to list the products and show that price and stock (which comes from the pivot table)

I upload the product controller, the inventory and product models and the product resource. By the way, as I am doing it right now price and stock returns null.

So far, in my ProductController:

public function index()
{
   return ProductResource::collection(Product::with('inventories')->paginate(25));
}

In my product model:

class Product extends Model
{    
    public function inventories()
    {       
        return $this->belongsToMany('App\Inventory','inventory_product')->withPivot('price','stock')->withTimestamps();     
    }
}

In my inventory model:

class Inventory extends Model
{   
    public function products()
    {  
        return $this->belongsToMany('App\Product','inventory_product')->withPivot('price','stock')->withTimestamps();        
    }
}

In my product Resource:

public function toArray($request)
{
    return [
        'id'=>$this->id,
        'name'=>$this->name,
        'description'=>$this->description,
        'short_description'=>$this->short_description,
        'category'=>$this->category,//category_id
        'url'=>$this->url,
        'image'=>$this->image,
        'relevant'=>$this->relevant,
        'month'=>$this->month,
        'price'=>$this->price,
        'stock'=>$this->stock
    ];
}

my migration inventory table:

Schema::create('inventories', function (Blueprint $table) 
{
    $table->increments('id');
    $table->string('name');
    $table->unsignedInteger('city_id');
    $table->timestamps();
    $table-> foreign('city_id')->references('id')->on('cities')->onDelete('cascade');
});

my migration product table:

Schema::create('products', function (Blueprint $table) 
{
    $table->increments('id');
    $table ->string('name');
    //$table ->integer('stock');
    $table ->string('description');
    $table ->string('short_description');
    $table ->unsignedInteger('category');//category_id
    //$table ->integer('price');
    $table ->string('url');
    $table ->string('image');
    $table ->boolean('relevant');
    $table ->boolean('month');
    $table->timestamps();
    $table-> foreign('category')->references('id')->on('categories')->onDelete('cascade');
});

And my inventory_product migration table:

$table->increments('id');
    $table->integer('inventory_id')->unsigned();
    $table->integer('product_id')->unsigned();
    $table ->integer('price');
    $table ->integer('stock');
    $table->timestamps();
    $table-> foreign('inventory_id')->references('id')->on('inventories')->onDelete('cascade');
    $table-> foreign('product_id')->references('id')->on('products')->onDelete('cascade');

with that, I'm getting:

{
    "id": 1,
    //staff on product,
    "price": null,
    "stock": null
}

and I should be getting:

{
    "id": 1,
    //staff on product,
    "price": 123,//data on the pivot table
    "stock": 123//data on the pivot table
}

EDIT: Actually I should get something like:

{
    "id": 1,
    //staff on product,
[
    "inventory_id": 1,//data on the pivot table
    "price": 123,//data on the pivot table
    "stock": 123//data on the pivot table
]
[
    "inventory_id": 2,//data on the pivot table
    "price": 333,//data on the pivot table
    "stock": 333//data on the pivot table
]

}

In the case that the product would be on more than one inventory, right?

Thank you in advance :)

Answer 1

I think the problem is your index() function is trying to return a collection of Product Models which will only have parameters of that Model. If you want just the whole array you could do a join on that collection:

https://laravel.com/docs/5.8/queries#joins

Answer 2

your product might be in more than 1 inventory , you are not identifying which inventory your are getting items from , you can access it using $this->inventories put you don't need that , the answer depends on your logic , if 1 product might be in more than inventory you should return the collection of the inventories or sum the stock or what ever you need to view , if 1 product exist in 1 inventory you should edit the function to belongsTo and your code should work

Answer 3

your relation is many to many if you need to access pivot table for this relation can be get one product and first related inventory or other related from related rows than can access pivot for example in your Resource

public function toArray($request)
{
    return [
        'id'=>$this->id,
        'name'=>$this->name,
        'description'=>$this->description,
        'short_description'=>$this->short_description,
        'category'=>$this->category,//category_id
        'url'=>$this->url,
        'image'=>$this->image,
        'relevant'=>$this->relevant,
        'month'=>$this->month,
        'price'=>$this->inventories->first->price,
        'stock'=>$this->inventories->first->stock
    ];
}