Optimize Laravel db seeder

Question

This is my laravel seed for two table. I need an efficient and optimized way for this seeder. How can make it efficient and standard. Give me some tips.

$userId = User::first()->id;

        // information of email template variable
        $emailTemplateVariables = [
            'user-create' => ['module' => 1, 'module_event' => 1, 'value' => ['USER_NAME', 'USER_EMAIL']],
            'user-deactive' => ['module' => 1, 'module_event' => 7, 'value' => ['USER_NAME', 'USER_EMAIL']],
            'purchase-order-create' => ['module' => 5, 'module_event' => 1, 'value' => ['ORDER_NO', 'PRODUCT_NAME', 'PURCHASE_DATE', 'PRODUCT_QUANTITY', 'ORDER_PRICE', 'SUPPLIER', 'SUPPLIER_INVOICE_NO', 'WAREHOUSE', 'STORE_NAME', 'STORE_ADDRESS', 'STORE_PHONE', 'STORE_WEBSITE', 'STORE_EMAIL']]
        ];

        collect($emailTemplateVariables)->each(function ($templateVariableItem, $slug) use ($userId) {
            $templateVariableIds = collect($templateVariableItem['value'])
                ->map(function ($value) use ($userId) {
                    return EmailTemplateVariable::updateOrCreate(
                        ['variable_key' => $value],
                        ['created_by' => $userId]
                    )->id;
                })->toArray();

            EmailTemplateSetting::create([
                'slug' => $slug,
                'module_id' => $templateVariableItem['module'],
                'module_event_id' => $templateVariableItem['module_event'],
                'template_variable_ids' => json_encode($templateVariableIds),
                'created_by' => $userId,
            ]);
        });

Answer 1

It's all about bulk inserts and updates to reduce the DB network roundtrip calls.

As of now, you seem to be having at least 19-21 DB calls by individually creating each row. Make use of upsert and insert on Eloquent models to reduce the DB calls to just 3 calls.

upsert all the values instead of updateOrCreate.

insert as in bulk insert for EmailTemplateSetting.

<?php

$userId = User::first()->id;

$templateSettings = [];

// information of email template variable
$emailTemplateVariables = [
    'user-create' => ['module' => 1, 'module_event' => 1, 'value' => ['USER_NAME', 'USER_EMAIL']],
    'user-deactive' => ['module' => 1, 'module_event' => 7, 'value' => ['USER_NAME', 'USER_EMAIL']],
    'purchase-order-create' => ['module' => 5, 'module_event' => 1, 'value' => ['ORDER_NO', 'PRODUCT_NAME', 'PURCHASE_DATE', 'PRODUCT_QUANTITY', 'ORDER_PRICE', 'SUPPLIER', 'SUPPLIER_INVOICE_NO' , 'WAREHOUSE', 'STORE_NAME', 'STORE_ADDRESS', 'STORE_PHONE', 'STORE_WEBSITE', 'STORE_EMAIL']]
];

$rows = [];
$values = [];

foreach($emailTemplateVariables as $templ){
    foreach($templ['value'] as $v){
        $values[] = $v;
        $rows[] = ['variable_key' => $v, 'created_by' => $userId];
    }
}

EmailTemplateVariable::upsert($rows, ['variable_key', 'created_by']);

$variableMap = EmailTemplateVariable::select('id', 'variable_key')
                ->whereIn('variable_key', $values)
                ->where('created_by', $userId)
                ->get()->toArray();

$variableMap = array_column($variableMap, 'id', 'variable_key');

$rows = [];

foreach($emailTemplateVariables as $slug => $templ){
    $variableIds = [];
    foreach($templs['value'] as $v){
        $variableIds[] = $variableMap[ $v ];
    }
    $rows[] = [
        'slug' => $slug,
        'module_id' => $templ['module'],
        'module_event_id' => $templ['module_event'],
        'template_variable_ids' => json_encode($variableIds),// should rather be a one-to-many-relationship form to follow 1st NF
        'created_by' => $userId,
    ];
}

EmailTemplateSetting::insert($rows);

P.S: I haven't tested this on my system since I can't replicate your issue but you can fix any minor issues here and there but the overall idea remains the same.