How to get the product id saved as json array on orders table in laravel?-CodePudding

There is an Order model with product_id as json, with saved data in the database as [18, 17]. the question is how I can get each product_id properties. I mean the product_id = 18's name, size, price, and so on.

in order model relation:

    public function product()
    {
        return $this->belongsTo(Product::class);

    }

    protected $casts = [
        "product_id" => 'json',
    ];

controller:

Order::with('user', 'product')->get()

or I must make another table?

CodePudding user response：

the belongsToMany() may be a better chose instead of a json column.

php artisan make:migration create_order_products_table

20xx_xx_xxxxxx_create_order_products_table.php

public function up()
{
    Schema::create('order_products', function (Blueprint $table) {
        $table->unsignedBigInteger('product_id');
        $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
        $table->unsignedBigInteger('order_id');
        $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade');
    });
}

Order.php

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

now, you can sync a product/products to an order

$order->products()->sync([1, 2, 3]); // [1, 2, 3] is product id

get products in an order

$order->products;

CodePudding user response：

As the other user commented, you should use a Many-to-Many relationship. This due to:

An order can contain many products
A product can be included in many orders

Now, to your question. What you desired is define a relationship using elements stored in a json column. For this, I'd suggest you to use this package: https://github.com/staudenmeir/eloquent-json-relations

With it you will be able to define your relationship very easily.