javascript - 将分组查询转换为对象树

Question

我想在 js 中将查询结果转换为树对象，查询的每个字段都被添加到数组中，如 js 中的 ['field2', 'field3']文件，所以后端用它创建动态查询，树必须从左到右创建最外部到最内部字段(查询也按字段的 id 分组)，这就是查询(记住，字段是动态的，因此添加了更多字段)，树的最后一个分支应该是数量(也是动态的)

SELECT 
    T2.field2 as field2, T3.field3 as field3, SUM(quantity) AS quantity
FROM 
    table1 T1
    INNER JOIN
    table2 T2 ON T1.table2_id = T2.id
    INNER JOIN
    table3 T3 ON T1.table3_id = T3.id
WHERE
    AND T1.date = 20190611
GROUP BY T1.table2_id, T1.table3_id

这会给我一组这样的数据

'Group 1', 'Data 0', '9172'
'Group 1', 'Data 1', '789'
'Group 1', 'Data 22', '289'
'Group 1', 'Data 3', '254'
'Group 1', 'Data 4', '197'
'Group 1', 'Data 55', '173'
'Group 2', 'Data 0', '38'
'Group 2', 'Data 11', '3'
'Group 2', 'Data 4', '4'
'Group 3', 'Data 0', '807'
'Group 3', 'Data 1', '40'
'Group 3', 'Data 18', '4'
'Group 3', 'Data 2', '35'

我需要做的就是把它改造成这样的东西。

[
  {
    field2: 'Group 1',
    data: [
      {
        field3: 'Data 0',
        data: [
          { quatity: 9172 },
        ],
      },
      {
        field3: 'Data 1',
        data: [
          { quatity: 789 },
        ],
      },
      {
        field3: 'Data 3',
        data: [
          { quatity: 289 },
        ],
      },
      {
        field3: 'Data 4',
        data: [
          { quatity: 197 },
        ],
      },
      {
        field3: 'Data 55',
        data: [
          { quatity: 173 },
        ],
      },
    ],
  },
  {
    field2: 'Group 2',
    data: [
      {
        field3: 'Data 0',
        data: [
          { quatity: 38 },
        ],
      },
      {
        field3: 'Data 11',
        data: [
          { quatity: 3 },
        ],
      },
      {
        field3: 'Data 4',
        data: [
          { quatity: 4 },
        ],
      },
    ],
  },
  {
    field2: 'Group 3',
    data: [
      {
        field3: 'Data 0',
        data: [
          { quatity: 807 },
        ],
      },
      {
        field3: 'Data 1',
        data: [
          { quatity: 40 },
        ],
      },
      {
        field3: 'Data 18',
        data: [
          { quatity: 4 },
        ],
      },
      {
        field3: 'Data 2',
        data: [
          { quatity: 35 },
        ],
      },
    ],
  },
]

这里的技巧是查询中的字段是动态的，所以我需要一直查找直到它到达列表中的最后一个字段，所以如果字段数组有像 ['field2', 'field3 ', 'field4'] 值数组有一个类似 ['quantity', 'balance'] 的数组结果应该是这样的

[
  {
    field2: 'Group 1',
    data: [
      {
        field3: 'Data 0',
        data: [
          {
            field4: 'Last field,
            data: [
              { quantity: 3435, balance: 43.53 },
            ],
          },
          {
            field4: 'Last field,
            data: [
              { quantity: 234, balance: 241.53 },
            ],
          },
        ],
      },
    ],
  },
]

如何实现这一点？

Answer 1

您可以采用组合方法，使用键和值数组并查找相同的对象。

var data = [['Group 1', 'Data 0', '9172'], ['Group 1', 'Data 1', '789'], ['Group 1', 'Data 22', '289'], ['Group 1', 'Data 3', '254'], ['Group 1', 'Data 4', '197'], ['Group 1', 'Data 55', '173'], ['Group 2', 'Data 0', '38'], ['Group 2', 'Data 11', '3'], ['Group 2', 'Data 4', '4'], ['Group 3', 'Data 0', '807'], ['Group 3', 'Data 1', '40'], ['Group 3', 'Data 18', '4'], ['Group 3', 'Data 2', '35']],
    keys = ['field2', 'field3'],
    values = ['quantity'],
    result = data.reduce((r, a) => {
        keys
            .reduce((q, k, i) => {
                var temp = q.find(o => o[k] === a[i]);
                if (!temp) q.push(temp = { [k]: a[i], data: [] });
                return temp.data;
            }, r)
            .push(Object.assign({}, ...values.map((k, i) => ({ [k]: a[keys.length + i] }))));
        return r;
    }, []);

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }