php - How to mass update many to many related tables in Laravel?
Get the solution ↓↓↓
I am fetching data from an external API and I want toinsert or update those data to two tables with many to many relationship using Laravel and MySQL.
Table Structure
Orders Table
|id (Auto Increment /Not related to API) | order_id (from API) | some other columns
Products Table
||id (Auto Increment /Not related to API) | product_id (from API) | some other columns
Orders_Products Table
||order_id (FK) | product_id (FK) | quantity (int) | some other columns
Here's the data structure of the response from the API.
|orders : [
order_id : 1234568586,
some_fields : abcd,
products : [
{
product_id : 14578546,
quantity : 10,
some_fields
},
{
product_id : 24578546,
quantity : 5,
some_fields
}
]
]
What I require to do
I want toinsert orupdateIfExist all these orders (into Orders Table) with their products (into Products table) and map the relationships (into Orders_Product Table).
My Approach
foreach (orders as order) {
//insert/update Order and keep its id
foreach(order->products as product){
//fetch Product Model if it product_id already exists
//else make a new Product Model.
//insert/update Product
//add relationship
}
}
My Question
My approach works fine. I can insert/update all the necessary data to the database. But I am pretty sure this is very inefficient because the database is queried many times. So if there are like 1000 orders, this code will take a long time to execute. I would appreciate it enormously if you could mention an efficient way to do this.
Answer
Solution:
Depending on how many product you have in your table, you could retrieve them all in an array, indexed with there ID.
This will allow you to avoid multiple database request each time.
// $products = [
// 1 => $product1,
// 2 => $product2,
// ...
// ];
$products = getAllMyProductsIndexedById();
foreach ($orders as $order) {
//insert/update Order and keep its id
foreach($order->products as $product){
//fetch Product Model if it product_id already exists
if(array_key_exists(product->id, $products){
$productEntity = $products[product->id];
} else {
//else make a new Product Model.
// and add it to the array $products
}
//insert/update Product
//add relationship
}
}
Answer
Solution:
Create temporary tables of the data structures from the API, and the do the laravel version ofinsert into orders select ... from orders_tmp ... on duplicate key update ... (and the same for products).
ref: MariaDB insert / MySQL insert
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: warning: undefined array key
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.