The best way is to just upload the file, register a job to process it in the background.
Suggestions to accelerate Laravel - excel upload
This is how I do that
public function store(Request $request)
{
$request->validate([
'file' => ['required','mimes:csv']
]);
$file = $request->file('file');
$file_name =$file->getClientOriginalName();
$filename_exist= Shipment::where('filename', $file_name)->first();
if ($filename_exist) {
return back()->withErrors([sprintf('file %s exists ', $file_name)]);
}
$sampleHeadings = [
"quantity","type","brand","model","product","generation","status","part_number","list_id","batch",
"serial", "mfg_serial","note","warehouse","location","cost","availability","sold_to","order_id","sale_price",
"order_date","shipment","visibility"
];
$headings = (new HeadingRowImport())->toArray($file);
$headings =$headings[0][0];
$missedCols =[];
foreach ($sampleHeadings as $sampleHeading) {
if (!in_array($sampleHeading, $headings)) {
array_push($missedCols, $sampleHeading);
}
}
if (count($missedCols) > 0) {
return back()->withErrors(['error', sprintf('columns %s required', implode(' - ', $missedCols))]);
}
\DB::transaction(function () use ($file, $file_name, &$ship) {
$ship = Shipment::create([
'filename' => $file_name
]);
$all_products = Product::all();
$imported = \Excel::toCollection(new ProductsImport($ship->id), $file);
$sku_start_number = 100001;
$imported->first()->map(function ($import) use ($ship, &$sku_start_number) {
$import = $import->toArray();
unset($import['id']);
trim($import['serial']);
$import['shipment_id'] = $ship->id;
$import['created_at'] = now();
$import['updated_at'] = now();
// Generate SKU
// if the name already exists - get the same sku
$same_name = Product::where('product', $import['product'])->first();
if ($same_name) {
$import['sku'] = $same_name->sku;
} else {
$type_2letters = $import['type'] ? substr(trim($import['type']), 0, 2) : 'PR';
$brand_2letters = $import['brand'] ? substr(trim($import['brand']), 0, 2) : 'BR';
$sku_start_number += 1;
$sku = $type_2letters . $brand_2letters . $sku_start_number;
$import['sku'] = strtoupper($sku);
}
$duplicate = Product::where('serial', $import['serial'])->orWhere('mfg_serial', $import['mfg_serial'])->first();
if ($duplicate) {
Duplicate::insert($import);
} else {
Product::insert($import);
}
});
});
return redirect('/shipments/'.$ship->id)->with('success', 'success ');
}
this is the import class
class ProductsImport implements ToModel, WithHeadingRow
{
protected $shipment_id;
public function __construct($shipment_id)
{
$this->shipment_id = $shipment_id;
}
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
// dd($row);
return new Product([
'quantity'=>$row['quantity'],
'type'=>$row['type'],
'brand'=>$row['brand'],
'model'=>$row['model'],
'product'=>$row['product'],
'generation'=>$row['generation'],
'status'=>$row['status'],
'sku'=>$row['sku'],
'part_number'=>$row['part_number'],
'list_id'=>$row['list_id'],
'batch'=>$row['batch'],
'serial'=>$row['serial'],
'mfg_serial'=>$row['mfg_serial'],
'note'=>$row['note'],
'warehouse'=>$row['warehouse'],
'location'=>$row['location'],
'cost'=>$row['cost'],
'availability' =>$row['availability'],
'sold_to' =>$row['sold_to'],
'order_id' =>$row['order_id'],
'sale_price'=>$row['sale_price'],
'order_date'=>$row['order_date'],
'shipment'=>$row['shipment'],
'visibility'=>$row['visibility'],
'shipment_id'=>$this->shipment_id
]);
}
public function collection(Collection $rows)
{
foreach ($rows as $row) {
//
}
}
}
The problem is large files give ( NGINX gateway timeout ) error .. for smaller files it is slow but OK ..
ANY suggestions are highly appreciated .. every req second matters
@lifesound There are time limits for a regular request, if I remember correctly it's 60 seconds. If a request takes longer than that it is timed out, it is also bad to make the user wait, they will likely press the back button or some other link in your app, causing the request to be canceled in an uncontrolled way.
The guideline is to give the system back to the users as fast as possible. We have two scenarios here, the first we process the file directly upon upload.
- The user uploads the file 5s.
- Your application starts processing the file 10-55s
- If step 2 takes longer the request will be canceled.
- The use will have to wait up to 60 seconds before the system is available to him.
in the second we queue the processing, and give the system back to the user much faster.
- The user uploads the file 5s
- Your application queues a job to process the file 0s
- The user gets the system back, and can continue to work.
- Your application starts to process the file, and it doesn't really matter if it takes 10s or 500s since we don't have the limit of the request.
- When the job is done, broadcast a notification to the user, that their data is available.
In scenario 1, you can't upload files that would cause the request to time out, this puts a limit on how big a file can be, while in scenario 2, you can basically upload an excel file that has a million records, and takes ten minutes to process.
Bottom line, never let the user wait, time consuming tasks should be done in the back end to not affect the users.
Please or to participate in this conversation.