LoginSignup
1
1

More than 1 year has passed since last update.

XTechグループ 2Advent Calendar 2020Day 12
@masatomur(masato mur)

スプレッドシートアプリを利用したデータベース編集を簡単実現

Last updated at Posted at 2021-02-24

はじめに

エキサイト株式会社（XTechグループ）エンジニアmasatomur🍣です。
先日、企画側からこんな要求がありました。

データベースって要はエクセルの表がいっぱい集まったようなものですよね？会員名簿の表なんかはエクセルの画面みたいに自由に編集したいんだけどできます？

時間が許す限りデータベースとエクセルとの違いをとくとくと説教したいところでしたが、

  • 万が一他のテーブルとのリレーショナルに問題が生じてもさほどクリティカルではないテーブルだったらまあいいかな
  • データ管理も運用側に任せてしまえばこちらも楽かな

ということで、つくってみることにしました。（Oracleクライアントなんて使わせられませんから）
このような裏方のツールにはあまり手間をかけたくないし、webでデータ編集画面てすごくつくりたくないので、

  1. 会員名簿テーブルのレコードをcsvファイルとしてダウンロード
  2. Numbersで開いて編集〜csvファイルとして書出し
  3. csvファイルをアップロードし会員名簿テーブルへインポート

裏方、このくらい手間かかってもいいよね？との合意を得ていざ開発。
すると、
Laravel+EloquentのupdateOrCreate
がなんとも秀逸でいとも簡単にできてしまったので、今回これをご紹介したいと思います。

テーブルのコントローラ

まずはテーブルのコントローラ、これだけです。

MembersController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use App\Http\Traits\Csv;
class MembersController extends Controller
{
    use Csv;

    public function __construct()
    {
        $this->middleware('auth');

        // MEMBERテーブル構造
        $this->columns = ['member_id', 'name', 'email', 'address'];
        $this->keys = ['member_id'];
    }

    public function index(Request $request)
    {
        // MEMBERテーブル読込
        $query = DB::table('member');
        foreach ($this->keys as $key) {
            $query->orderBy($key, 'asc');
        }
        $members = $query->get();

        // ダウンロード用csvファイル生成
        $id = $request->user()->id;
        $filename = "member.$id.csv";
        Csv::create($filename, $members, $this->columns);

        $data = [
            'members' => $members,
        ];
        return view('members.index', $data);
    }

    public function dl(Request $request)
    {
        // ダウンロード用csvファイル送信
        $id = $request->user()->id;
        $filename = "member.$id.csv";
        $filepath = $filename;
        $mimeType = Storage::mimeType($filepath);
        $headers = [['Content-Type' => $mimeType]];
        return Storage::download($filepath, $filename, $headers);
    }

    public function ul(Request $request)
    {
        $result = ['processed' => 0, 'updatedOrInserted' => 0, 'skipped' => 0];

        // アップロードされたcsvファイルを処理
        if ($request->file('ulfile')) {
            setlocale(LC_ALL, 'ja_JP');
            $ulfile = $request->file('ulfile')->store('');
            $fp = fopen(storage_path('app/') . $ulfile, 'r');
            while ($values = fgetcsv($fp)) {
                // ヘッダ行読み飛ばし
                if (!isset($header_skipped)) {
                    $header_skipped = true;
                    continue;
                }

                // （カラム名=>値）の配列を作成（空文字はnullへ）
                $values = array_combine($this->columns, $values);
                foreach ($values as $key=>$val) {
                    if ($val === '') $values[$key] = null;
                }

                // upsert実行
                foreach ($values as $key=>$val) {
                    if (in_array($key, $this->keys)) {
                        $upsert['keys'][$key] = $val;
                    } else {
                        $upsert['vals'][$key] = $val;
                    }
                }
                $success = DB::table('member')->updateOrInsert($upsert['keys'], $upsert['vals']);

                $result['processed']++;
                if ($success) {
                    $result['updatedOrInserted']++;
                } else {
                    $result['skipped']++;
                }
            }
        }

        $data = [
            'result' => $result,
        ];
        return view('members.ul', $data);
    }
}

会員名簿テーブルはMEMBERという名前のテーブルにしています。

ポイント
1. MEMBERテーブルからレコードを抽出した際は都度csvファイルにも出力しておきます(function index)。他の使用者に上書きされないように、ファイル名にLaravel認証のuser()->idを入れます。
2. csvファイルのダウンロードが要求されたら(function dl)を実行します。
3. csvファイルがアップロードされたら(function ul)を実行します。csvファイル１行ずつupdateOrInsertを実行します。

updateOrInsert

特筆すべきなのはこのupdateOrInsertだけで様々な処理をこなしている点です。ReaDoubleには以下のように書かれています。

updateOrInsert
// OaklandからSan Diego行きの飛行機があれば、料金へ９９ドルを設定する。
// 一致するモデルがなければ、作成する。
$flight = App\Flight::updateOrCreate(
    ['departure' => 'Oakland', 'destination' => 'San Diego'],
    ['price' => 99]
);

つまり

  • 第１引数の配列(列名=値)に一致するレコードが存在すれば、第２引数の配列(列名=値)にupdateする
  • 存在しなければ、第１引数の配列(列名=値)+第２引数の配列(列名=値)のレコードをinsertする

なので、csvファイルから読み込んだすべての値を第１、第２引数のどちらかに振り分けてやれば良いのです。

さらにすごいのが、実行しても既存のレコードと比べて変更箇所がない場合updateが行われない点です（戻り値：affectedRowsが0）。このおかげで全行のcsvファイルをインポートしても、変更のあった行だけフィードバックしてやるのも簡単です。

その他のコード

web.php
    Route::get('/members', 'MembersController@index');
    Route::get('/members/dl', 'MembersController@dl');
    Route::get('/members/ul', 'MembersController@ul');
    Route::post('/members/ul', 'MembersController@ul');
Csv.php
<?php
namespace App\Http\Traits;

trait Csv {

    public static function create($filename, $lists, $header)
    {
        $fp = fopen(storage_path('app/'.$filename), 'w');

        // ヘッダー行書出し
        fputcsv($fp, $header);

        // 値書出し
        foreach ($lists as $list) {
            fputcsv($fp, (array)$list);
        }

        fclose($fp);
    }

    public static function purge($filename) {
        return unlink(storage_path('app/'.$filename));
    }
}

あとはview

  • members/index.blade.php
  • members/ul.blade.php

をつくれば完成です。

ul.blade.phpの例
<div class="card">
    <div class="card-header">
        <i class="fa fa-align-justify"></i>MEMBERテーブル更新
    </div>
    @if($result['processed'])
        <div class="card-body">
            インポート結果（{{ $result['processed'] }}行）<br />
            - updatedOrInserted：{{ $result['updatedOrInserted'] }}行<br />
            - skipped：{{ $result['skipped'] }}行<br />
        </div>
    @endif
    <div class="card-body">
        <form class="form-inline" method="post" action="/members/ul" enctype="multipart/form-data">
            <input type="file" value="ファイルを選択" name="ulfile">
            @csrf
            <button class="btn btn-primary" type="submit">インポート</button>
        </form>
    </div>
    <div class="card-body">
        <div class="alert alert-secondary" role="alert">
            MEMBERテーブルのキー項目は｜member_id｜です。<br />
            キー項目が同じレコードが既に存在する場合、レコードはインポートデータで上書き更新されます。（更新箇所がある場合のみ）<br />
            存在しない場合はインポートデータが新たにレコードとして追加されます。
        </div>
    </div>
</div>

おわりに

自分で使ってみた感じですが、短いコードのわりにはなかなか使えるものができたと思います。わざわざ開発しなくても、スプレッドシートアプリの豊富な編集機能を利用することができます。
実際に使ってみたいよ！という方はコメントいただければサンプルのURLをお教えします。
またご意見ご感想もいただけたら嬉しいです。
それではまたお会いしましょう。

著者

note.com/masatomur

P.S.

エキサイト株式会社では仲間を募集しています！｜wantedly

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
Sign upLogin
1
1