Issue with pivot table Laravel

Question

I have problem with pivot table in Laravel, I made customers and products tables and customer_product table to connect this two but it isn't working. Below I add this pivot table

    Schema::create('customer_product', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('customer_id');
        $table->foreign('customer_id')->references('id')->on('customers');
        $table->unsignedInteger('product_id');
        $table->foreign('product_id')->references('id')->on('products');
        $table->decimal('selling_customer_price');
        $table->decimal('purchase_customer_price'); 
        $table->decimal('consumed_customer_price');
        $table->timestamps();
    });
}

Part of my ProductController where I list products and I want show customers products

public function list()
{

    return view('products.list', [
        'customers' => Customer::all(),
        'products' => Product::orderby('name')->get(),

    ]);
}

and part of simple blade code

<div>
     @foreach ($customers as $customer)
        <li> {{ $customer->name }} {{ $customer->products }} </li>
     @endforeach
</div>

part of Product class

public function customers()
{
    return $this->belongsToMany(Customer::class);
}

and part of Customers class

public function products()
{
    return $this->hasMany(Product::class);
}

when I type list site I have error about lack of customer_id column in product table but I want use my pivot table because I have to use different prices to different customers.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'products.customer_id' in 'where clause' (SQL: select * from products where products.customer_id = 1 and products.customer_id is not null) (View: /home/vagrant/code/resources/views/products/list.blade.php)

The SQL error suggest you forget to update the products schema with a customer_id column... Is the table customer_product correctly created? Because $table->foreign('customer_id')->references('id')->on('customers'); should fail.. Assuming the mysql's default engine is InnoDB which only supports foreign keys. — Raymond Nijland
Does the customer_id field is present in your products table. — Sushank Pokharel

rkj rkj · Accepted Answer · 2018-07-08T12:09:35

You said you have many to many relation then you should have relation like below and from your comments, you have selling_customer_price field in pivot table for that you have to use withPivot. For details check https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

part of Product class

public function customers()
{
    return $this->belongsToMany(Customer::class)->withPivot('selling_customer_price');
}

and part of Customers class

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

Then fetch it like this

public function list()
{

    return view('products.list', [
        'customers' => Customer::with('products')->all(),
        'products' => Product::orderby('name')->get(),

    ]);
}

In view

<div>
      <ul>
        @foreach ($customers as $customer)
          <li> {{ $customer->name }} </li>
          <ul>
            @foreach ($customer->products as $product)
                <li> {{ $product->name }} </li>
                <li> {{ $product->pivot->selling_customer_price }} </li>
            @endforeach
          </ul>
        @endforeach
     </ul>
</div>

Issue with pivot table Laravel

1 Answers