kintone でSQLを使う（その３）サブクエリー

kintone でSQLを使う の続き。
サブクエリーを試してみました。

部品と最新の会社査定を JOIN して、表示

部品表の集計結果

原材料の調達先

必要ライブラリ

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

集計コード

処理概要

• 原材料アプリのレコードを取得
• 調達先の会社名を取得
• 調達先の会社名で査察情報のレコードを取得
• json 構造を簡単化
• SQL で集計（サブクエリー）

クエリーの処理

• サブクエリー：会社の査察年月日を降順に並べて、その先頭（最新日付）の日付・評価を取得
• 最新の査察年月日・評価と原材料を会社名で JOIN して、原材料の名前順に表示

製品の工程情報があれば、工程順に原材料を並べたいところ。

join の後にサブクエリーを書けなかったので、少し配列の順番に注意

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

    // 一覧表示
    kintone.events.on("app.record.detail.show", function(event) {

        var record = event.record;

        var obj = {};
        var appId1 = 749;
        var q1 = '製品名 = "' + record['製品名'].value + '" and 削除フラグ not in ("削除")';

        // 原材料取得
        fetchRecords(appId1, q1, ['製品名', '原材料名', '会社名']).then(function(records) {
            obj.rs1 = convertToRows(records);
            console.log('rs1', JSON.stringify(obj.rs1));

            var companyNames = {};
            obj.rs1.map(function(row) {
                companyNames['"' + row['会社名'] + '"'] = 1;
            });

            var strNames = Object.keys(companyNames).join(',');

            console.log('会社名', strNames);

            if (!strNames)
                return event;

            // 査察情報取得
            var appId2 = 748;
            var q2 = '会社名 in (' + strNames + ') and 削除フラグ not in ("削除")';
            // var q1 = '削除フラグ not in ("削除")';
            return fetchRecords(appId2, q2, ['会社名', '査察年月日', '査察の評価']);

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

            // var result0 = alasql(
            //     "SELECT [会社名], FIRST([査察年月日]) as [査察年月日], FIRST([査察の評価]) as [査察の評価]\
            //     FROM (SELECT * FROM ? ORDER BY [会社名], [査察年月日] DESC) \
            //     GROUP BY [会社名]", [obj.rs2]);
            // console.log('r0', result0);

            var result1 = alasql(
                "SELECT  a.[製品名], a.[原材料名], a.[会社名], b.[査察年月日], b.[査察の評価]\
                FROM ( \
                    SELECT [会社名], FIRST([査察年月日]) as [査察年月日], FIRST([査察の評価]) as [査察の評価]\
                    FROM (SELECT * FROM ? ORDER BY [会社名], [査察年月日] DESC) \
                    GROUP BY [会社名] ) as b \
                    join ? as a on a.[会社名] = b.[会社名]\
                ORDER BY a.[製品名], a.[原材料名], a.[会社名]", [obj.rs2, obj.rs1]);
            console.log('r0a', result1);

            // grid 表示
            var grid = kintone.app.record.getSpaceElement('xp-grid');
            new Handsontable(grid, { 
                data: result1, 
                colWidths: [150, 150, 150, 100, 100],
                colHeaders: ['製品名', '原材料名', '会社名', '査察年月日', '査察の評価'], 
                columns: [
                    { data: '製品名' },
                    { data: '原材料名' },
                    { data: '会社名' },
                    { data: '査察年月日' },
                    { data: '査察の評価' },
                ],
                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;
    }

})();

部品表、部品調達、査察情報アプリに分かれている場合

処理概要

３本のアプリを読み込んで、SQL で組み立ててあげるだけですので、基本は同じです。
無理に３つの配列を JOIN しなくても、２本の JOIN 結果が配列に作って、さらに JOIN してもkintone で扱うデータ件数であれば処理時間はそんなに変わりません。

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

    // 一覧表示
    kintone.events.on("app.record.detail.show", function(event) {

        var record = event.record;

        var obj = {};
        var appId1 = 749;
        var q1 = '製品名 = "' + record['製品名'].value + '" and 削除フラグ not in ("削除")';

        // 原材料取得
        fetchRecords(appId1, q1, ['製品名', '原材料名']).then(function(records) {
            obj.rs1 = convertToRows(records);
            console.log('rs1', JSON.stringify(obj.rs1));

            var partNames = {};
            obj.rs1.map(function(row) {
                partNames['"' + row['原材料名'] + '"'] = 1;
            });
            var strNames = Object.keys(partNames).join(',');
            console.log('原材料名', strNames);

            // 部品調達情報取得
            var appId2 = 751;
            var q2 = '原材料名 in (' + strNames + ') and 削除フラグ not in ("削除")';
            // var q1 = '削除フラグ not in ("削除")';
            return fetchRecords(appId2, q2, ['原材料名', '会社名']);

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

            var companyNames = {};
            obj.rs2.map(function(row) {
                companyNames['"' + row['会社名'] + '"'] = 1;
            });

            var strNames = Object.keys(companyNames).join(',');

            console.log('会社名', strNames);

            // 査察情報取得
            var appId3 = 748;
            var q3 = '会社名 in (' + strNames + ') and 削除フラグ not in ("削除")';
            // var q1 = '削除フラグ not in ("削除")';
            return fetchRecords(appId3, q3, ['会社名', '査察年月日', '査察の評価']);

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

            var result1 = alasql(
                "SELECT  a.[製品名], a.[原材料名], c.[会社名], c.[査察年月日], c.[査察の評価]\
                FROM ( \
                    SELECT [会社名], FIRST([査察年月日]) as [査察年月日], FIRST([査察の評価]) as [査察の評価]\
                    FROM (SELECT * FROM ? ORDER BY [会社名], [査察年月日] DESC) \
                    GROUP BY [会社名] ) as c \
                JOIN ? as b ON b.[会社名] = c.[会社名]\
                JOIN ? as a ON a.[原材料名] = b.[原材料名]\
                ORDER BY a.[製品名], a.[原材料名], c.[会社名]", [obj.rs3, obj.rs2, obj.rs1]);
            console.log('r0a', result1);

            // grid 表示
            var grid = kintone.app.record.getSpaceElement('xp-grid');
            new Handsontable(grid, { 
                data: result1, 
                colWidths: [150, 150, 150, 100, 100],
                colHeaders: ['製品名', '原材料名', '会社名', '査察年月日', '査察の評価'], 
                columns: [
                    { data: '製品名' },
                    { data: '原材料名' },
                    { data: '会社名' },
                    { data: '査察年月日' },
                    { data: '査察の評価' },
                ],
                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;
    }

})();

半製品の登録と所要量展開

半製品と所要量展開は、kintone で扱うのは向かないと思いますが、お試しということで。

• 原材料に、半製品を追加します。
• 半製品の原材料を登録します。

処理としては、半製品の原材料を求める処理を追加します。
半製品の階層が多い場合は、半製品の原材料を求める処理を繰り返します。

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

    // 一覧表示
    kintone.events.on("app.record.detail.show", function(event) {

        var record = event.record;

        var obj = {};
        var appId1 = 749;
        var q1 = '製品名 = "' + record['製品名'].value + '" and 削除フラグ not in ("削除")';

        // 原材料取得
        fetchRecords(appId1, q1, ['製品名', '原材料名']).then(function(records) {
            obj.rs1 = convertToRows(records);
            console.log('rs1', JSON.stringify(obj.rs1));

            var partNames = {};
            obj.rs1.map(function(row) {
                partNames['"' + row['原材料名'] + '"'] = 1;
            });
            var strNames = Object.keys(partNames).join(',');
            console.log('原材料名', strNames);

            // 半製品原材料取得
            var appId1 = 749;
            var q1a = '製品名 in (' + strNames + ') and 削除フラグ not in ("削除")';
            // var q1 = '削除フラグ not in ("削除")';
            return fetchRecords(appId1, q1a, ['製品名', '原材料名']);
        }).then(function(records) {
            obj.rs1a = convertToRows(records);
            console.log('rs1a', JSON.stringify(obj.rs1a));

            obj.rs1 = obj.rs1.concat(obj.rs1a);
            console.log('rs1', JSON.stringify(obj.rs1));

            var partNames = {};
            obj.rs1.map(function(row) {
                partNames['"' + row['原材料名'] + '"'] = 1;
            });
            var strNames = Object.keys(partNames).join(',');
            console.log('原材料名', strNames);


            // 部品調達情報取得
            var appId2 = 751;
            var q2 = '原材料名 in (' + strNames + ') and 削除フラグ not in ("削除")';
            // var q1 = '削除フラグ not in ("削除")';
            return fetchRecords(appId2, q2, ['原材料名', '会社名']);

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

            var companyNames = {};
            obj.rs2.map(function(row) {
                companyNames['"' + row['会社名'] + '"'] = 1;
            });

            var strNames = Object.keys(companyNames).join(',');

            console.log('会社名', strNames);

            // 査察情報取得
            var appId3 = 748;
            var q3 = '会社名 in (' + strNames + ') and 削除フラグ not in ("削除")';
            // var q1 = '削除フラグ not in ("削除")';
            return fetchRecords(appId3, q3, ['会社名', '査察年月日', '査察の評価']);

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

            var result1 = alasql(
                "SELECT  a.[製品名], a.[原材料名], c.[会社名], c.[査察年月日], c.[査察の評価]\
                FROM ( \
                    SELECT [会社名], FIRST([査察年月日]) as [査察年月日], FIRST([査察の評価]) as [査察の評価]\
                    FROM (SELECT * FROM ? ORDER BY [会社名], [査察年月日] DESC) \
                    GROUP BY [会社名] ) as c \
                JOIN ? as b ON b.[会社名] = c.[会社名]\
                JOIN ? as a ON a.[原材料名] = b.[原材料名]\
                ORDER BY a.[製品名], a.[原材料名], c.[会社名]", [obj.rs3, obj.rs2, obj.rs1]);
            console.log('r0a', result1);

            // grid 表示
            var grid = kintone.app.record.getSpaceElement('xp-grid');
            new Handsontable(grid, { 
                data: result1, 
                colWidths: [150, 150, 150, 100, 100],
                colHeaders: ['製品名', '原材料名', '会社名', '査察年月日', '査察の評価'], 
                columns: [
                    { data: '製品名' },
                    { data: '原材料名' },
                    { data: '会社名' },
                    { data: '査察年月日' },
                    { data: '査察の評価' },
                ],
                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;
    }

})();

あとがき

GROUP BY で MAX(日付) をすると出来ない。
文字列比較になるためか？