開発スタッフがアプリ関連の最新技術やニュースをご紹介します。

When we are supposed to work with large csv file with 500K or 1mil records, there is always a thing to be careful about the memory usage. If our program consumes lot of memory , its not good for the physical server we are using. Beside from that our program should also performed well. Today I would like to share some tips working with laravel csv file.

Configuration

Firstly , we have to make sure our PHP setting is configured. Please check the below settings and configure as you need. But keep in mind, don’t enlarge the memory_limit value if it’s not required.

- memory_limit
- post_max_size
- upload_max_filesize
- max_execution_time

Code

Once our PHP configuration is done, you might to restart the server or PHP itself. The next step is our code. We have to write a proper way not to run out of memory.

Normally we used to read the csv files like this.

$handle = fopen($filepath, "r"); //getting our csv file
while ($csv = fgetcsv($handle, 1000, ",")) { //looping through each records
//making csv rows validation
// inserting to database
// etc.
}

The above code might be ok for a few records like 1000 to 5000 and so on. But if you are working with 100K 500K records , the while loop will consume lot of memory. So we have to chunk and separate the loop to get some rest time for our program.

$handle = fopen($filepath, "r"); //getting our csv file
$data = []; 
while ($csv = fgetcsv($handle, 1000, ",")) { //looping through each records
   $data[] = $csv;// you can customize the array as you want
   //we will only collect each 1000 records and do the operations
   if(count($data) >= 1000){
　 // do the operations here
   // inserting to database (If you already prepared the array in above, can directly add to db, no need loops)
   // etc.
   
   //resetting the data array
   $data = [];
   }

   //if there is any rows less than 1000, keep going for it
   if(count($data) > 0){
      // do the operations here
   }
}

Above one is a simple protype to run the program not to run out of the memory, our program will get rest time for each 1000records.

Here is an another way using array_chuck and file function

$csvArray = file($filepath); //this will output array of our csv file
//chunking array by 1000 records
$chunks = array_chunk($csvArray,1000);

// Then lets store the chunked data files in somewhere
foreach ($chunks as $key => $chunk) {
   file_put_contents($path,$chunk);
}

//get the files we have stored and can loop through it
files = glob('path/path'."/*.csv");

foreach ($files as $key => $file) {
  $filer = fopen($file, "r");
  while ($csv = fgetcsv($filer, 1000, ",")) {
     // do the operations here
  }

  //delete the file back
  unlink($file);
}

Please don’t forget to close the files back fclose if you have done the file operations.

Looping content

One more thing to keep in mind is we have to take care of the codes we put inside loops. If there is any

• Database calls or
• Third party API calls,

it will surely slow down the performance and consume the memory more. So if you can put these calls outside of the loops, our program will be much more efficient.

I am sure there might also be some other work arounds or some packages to handle about this issue behind the scence.

Yuuma

Tweet

Laravel v9はLaravelの次のLTSバージョンで、2022年2月頃に登場する予定です。この記事では、これまでに発表された新機能や変更点を概説したいと思います。

テストカバレッジオプションを追加

新しいartisan test –coverage オプションは、テストカバレッジをターミナルに直接表示します。また、–min オプションを使用すると、テストカバレッジの最小閾値を指定することができます。

Enumを使った暗黙のルートバインディング

PHP 8.1ではEnumのサポートが導入されました。Laravel 9では、ルート定義にEnumをタイプヒントする機能が導入され、LaravelはそのルートセグメントがURIの有効なEnum値である場合にのみルートを呼び出します。そうでない場合は、HTTP 404レスポンスが自動的に返されます。例えば、次のようなEnumがあるとします。

ルートセグメント {category} が fruits または people のときだけ呼び出されるルートを定義することができます。そうでない場合は、HTTP 404 レスポンスが返されます。

全文インデックス／Where句

fullText メソッドをカラム定義に追加して、フルテキストインデックスを生成することができるようになりました。

$table->text('bio')->fullText();

whereFullTextまたはWhereFullTextメソッドを使用すると、フルテキストを取得することができます。

$users = DB::table('users')
           ->whereFullText('bio', 'web developer')
           ->get();

* laravelのリリースから引用しています。

また、公式リリースページもご覧いただけます。

今週はここで終了となります。

最後までご高覧頂きまして有難うございました。

By Ami

Tweet

Today I would like to share about useful laravel packages. The following packages are most useful 7 packages of the best laravel packages. Let’s take a look.

Laravel Debugbar

Laravel Debugbar is a package that help users add a developer toolbar to their applications. This package is mainly used for debugging purposes. There are a lot of options available in Debugbar. It allows you to monitor and debug all the requests directly on the Laravel view. You can also monitor SQL queries, Mail, and queue.  

https://github.com/barryvdh/laravel-debugbar

Laravel User Verification

This package allows you to handle user verification and validates emails. It generates and stores a verification token for the registered user, sends or queue an email with the verification token link, handles the token verification, sets the user as verified. This package also provides functionality, i.e verified route middleware.

https://github.com/jrean/laravel-user-verification

Socialite

Socialite offers a simple and easy way to handle OAuth authentication. It allows the users to login via some of the most popular social networks and services including Facebook, Twitter, Google, GitHub, and BitBucket.

https://github.com/laravel/socialite

Laravel Mix

Laravel Mix provides a clean and rich Application Programming Interface (API) for defining webpack-build steps for your project. It is the most powerful asset compilation tool available for Laravel today.

https://www.npmjs.com/package/laravel-mix

Migration Generator

Migration generator is a Laravel package that you can use to generate migrations from an existing database, including indexes and foreign keys.

https://github.com/Xethron/migrations-generator

Laravel Backup

This Laravel package creates a backup of all your files within an application. It creates a zip file that contains all files in the directories you specify along with a dump of your database. You can store a backup on any file system.

https://github.com/spatie/laravel-backup

No Captcha

No Captcha is a package for implementing Google reCaptcha validation and protecting forms from spamming. First, you need to obtain a free API key from reCaptcha.

https://github.com/anhskohbo/no-captcha

This is all for now.

Hope you enjoy that.

By Asahi

Tweet