How to use an array as a foreign key in a model and build a hasMany relationship in laravel-CodePudding

This is my schema as follows:

Medicines

id
price
description
etc..

Ecommerce Orders(has many Medicines)

id
user_id
products (array that holds the id's of the medicine's and quantity)

And in my EcommerceOrders Model i have

protected $casts = [
   'products' => 'array',
];

public function items()
{
    return $this->hasMany(Medicines::class,'id','products')->withTrashed();
}

What I'm trying to do is to create a relationship between ecommerceOrders and medicines so when i get the data of an order that a client has made i also get the data of the products he has orederd How can i do this with the foreign key in this instance being an array

CodePudding user response：

You need another table called a pivot table, "ecommerce_order_medicines" with the fields medicine_id, ecommerce_order_id, qty

You would have a record for each different medicine that belongs to the ecommerce order.

You should not store it the way you are doing right now, you won't be able to make any relationship with it stored as an array like that. You also are making it much more difficult to do any kind of reports in the future, because you won't be able to properly query your database when it's stored as an array with ID's and QTY in the same column with no relationship. You will likely have to process large sets of data through PHP instead which won't scale very well.