Join database table based on Json colum having multiple key

Question

I am trying to create an ecommerce application using laravel, So when someone orders something, I store data in orders table, in which a column "products" exists. I store json object in this column. this is products json:

[{"pid":65,"min_price":"349.0000"},{"pid":71,"min_price":"349.0000"}]

Now, I want to show these order details to the user profile. So, i need to fetch rows from an another table (products), with the primary key same as "pid" in given json object.

please, suggest me how to resolve it.

Refilon · Accepted Answer · 2022-06-22 11:57:27Z

1

You can't do this without retrieving it first and looping over it or something.

What you might want to consider is the following:

Remove the column products from the orders table and create a order_product table. This is called a pivot table.
You can create this table by running the command php artisan create:migration CreateOrderProductTable

Now in this table you define 2 foreign keys. order_id and product_id.

If you have done this, you have to create the relations in the Product model and the Order model.

You can now use this relation to query all products that an order has like this: Order::find(1)->products()->get()

answered Jun 22, 2022 at 11:57

Refilon

3,4971 gold badge33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Join database table based on Json colum having multiple key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related