JavaScript
kintone
alasql

kintone でSQLを使う(その3)サブクエリー

More than 1 year has passed since last update.

kintone でSQLを使う の続き。

サブクエリーを試してみました。


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


部品表の集計結果

2017-02-27_17h27_56.png


原材料の調達先

2017-02-27_17h28_20.png


必要ライブラリ


集計コード

処理概要


  • 原材料アプリのレコードを取得

  • 調達先の会社名を取得

  • 調達先の会社名で査察情報のレコードを取得

  • 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;
}

})();


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

処理概要

3本のアプリを読み込んで、SQL で組み立ててあげるだけですので、基本は同じです。

無理に3つの配列を JOIN しなくても、2本の 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 で扱うのは向かないと思いますが、お試しということで。


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

  • 半製品の原材料を登録します。

2017-03-01_00h02_29.png

処理としては、半製品の原材料を求める処理を追加します。

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

////////////////////////////////////////////////////////////////////////////

// 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(日付) をすると出来ない。

文字列比較になるためか?