kintone でSQLを使う

ブラウザ上の JavaScript 内 SQL がそれなりに動いたので、kintone でも試してみた。
結果、用途によれば、kintone でも SQL が使えそうだ！

※alasql データベース覚書 参照

こんな SQL で集計できました。

alasql(
"SELECT cust.[会社名], cust.[顧客ランク], SUM(orders.[受注金額]) as [受注金額] \
FROM ? AS cust JOIN ? AS orders ON cust.[会社名] = orders.[顧客名] \
GROUP BY cust.[会社名], cust.[顧客ランク] \
ORDER BY cust.[会社名]", [obj.rs1, obj.rs2]);

集計元アプリ

とりあえず、顧客と受注金額を集計します。

集計結果

集計は、グリッド表示にしました。
ぱっと見、集計されていそうです。

必要ライブラリ

• alasql.min.js : JavaScript で SQL が使えます。
• handsontable : グリッド表示用

集計コード

処理概要

• 顧客情報アプリと案件アプリのレコードを取得
• json 構造を簡単化
• SQL で結合(Join)と集計（Group By）

SQL で、普通にJoin, Group By が使えるのがすばらしい！

////////////////////////////////////////////////////////////////////////////
// kintone JavaScript: Summary of kintone records by alasql.
//  2017.02.14 by rex0220
////////////////////////////////////////////////////////////////////////////
(function() {
    "use strict";

    // 一覧表示
    kintone.events.on("app.record.index.show", function(event) {
        if (event.viewId !== 5332577) return event;

        var obj = {};
        var custApp = 433;

        // 顧客情報取得
        fetchRecords(custApp, '', ['会社名', '顧客ランク']).then(function(records) {
            obj.rs1 = convertToRows(records);
            console.log('rs1', JSON.stringify(obj.rs1));

            // 注文情報取得
            return fetchRecords(event.appId, '', ['顧客名', '受注金額']);

        }).then(function(records) {
            obj.rs2 = convertToRows(records);
            console.log('rs2', JSON.stringify(obj.rs2));

            // 顧客情報と注文情報を集計
            var result1 = 
                alasql(
                "SELECT cust.[会社名], cust.[顧客ランク], SUM(orders.[受注金額]) as [受注金額] \
                FROM ? AS cust JOIN ? AS orders ON cust.[会社名] = orders.[顧客名] \
                GROUP BY cust.[会社名], cust.[顧客ランク] \
                ORDER BY cust.[会社名]", [obj.rs1, obj.rs2]);

            console.log('result3', JSON.stringify(result1));

            // grid 表示
            var grid = document.getElementById('xp-grid');
            new Handsontable(grid, { 
                data: result1, 
                colWidths: [150, 100, 200],
                colHeaders: ['会社名', '顧客ランク', '受注金額'], 
                columns: [
                    { data: '会社名' },
                    { data: '顧客ランク' },
                    { data: '受注金額', type: 'numeric', format: '0,0' },
                ],
                readOnly: true 
            });

        });

        return event;
    });


    // get records
    function fetchRecords(appId, opt_query, opt_fields, opt_offset, opt_limit, opt_records) {
        var query = opt_query || '';
        var offset = opt_offset || 0;
        var limit = opt_limit || 500;
        var allRecords = opt_records || [];
        var params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset };
        if (opt_fields) params.fields = opt_fields;
        return kintone.api(kintone.api.url('/k/v1/records', true), 'GET', params).then(function(resp) {
            allRecords = allRecords.concat(resp.records);
            if (resp.records.length === limit) {
                return fetchRecords(appId, query, opt_fields, offset + limit, limit, allRecords);
            }
            return allRecords;
        });
    }

    // records convert to table rows 
    function convertToRows(records) {
        var rows = records.map(function(record){
            var keys = Object.keys(record);
            var row = {};
            keys.map(function(key){
                row[key] = record[key].type === 'NUMBER' ? Number(record[key].value) : record[key].value;
            });
            return row;
        });
        return rows;
    }

})();

参考

Alasql JavaScript SQL Database Library: User Manual

あとがき

対象レコード数が少なければ、問題なく使えそうです。
あとは、レコード件数が多い場合にどの程度のパフォーマンスになるか検証要です。

kintone でSQLを使う（その２）パフォーマンス検証 に続く
kintone でSQLを使う（その３）サブクエリー