How to mass update many to many related tables in Laravel?

user_16122874 0
Viewed 456

I am fetching data from an external API and I want to insert 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 to insert or updateIfExist 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.

laravel many-to-many sql-update mysql php
2 Answers

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
   }
}
user_47795600

Create temporary tables of the data structures from the API, and the do the laravel version of insert into orders select ... from orders_tmp ... on duplicate key update ... (and the same for products).

ref: MariaDB insert / MySQL insert

user_101951530
Related