如何在 knex.js 中将联接表中的数据嵌套在一(或多)关系中

Question

假设我有用户、技能和user_skill表。

如何查询，以便如果我有 3 个用户，

我会得到 3 个对象(显然在一个数组中(，并且对于每个用户的对象，应该有一个具有所有技能的嵌套数组？

像这样：

{
    "users": [
        {
            "name": "dusan",
            "id": 1,
            "facebook": "dusan's facebook",
            "skills": [
                {"name": "skill1", "id": 1}
                {"name": "skill2", "id": 2}
            ]
        },
        {
            "name": "jenny",
            "id": 2,
            "facebook": "jenny's facebook",
            "skills": [
                {"name": "skill1", "id": 1}
                {"name": "skill2", "id": 2}
            ]
        },
        {
            "name": "michael",
            "id": 3,
            "facebook": "michael's facebook",
            "skills": [
                {"name": "skill1", "id": 1}
                {"name": "skill2", "id": 2}
            ]
        },
    ]
}

我的代码：

await db("users")
  .join("user_skill",  "users.id", "skills.user_id")
  .join("skills", "skills.id", "user_skill.id")
  .select(
    "users.name as name",
    "users.id as id",
    "skills.name as skill",
    "skills.id as skill_id"
);

上面的代码返回一个完全扁平的结构化 JSON，我希望将技能嵌套在用户对象中。

{
    "users": [
        {
            "name": "dusan",
            "id": 1,
            "skill": "node.js",
            "skill_id": 1
        },
        {
            "name": "dusan",
            "id": 1,
            "skill": "php",
            "skill_id": 2
        },
        {
            "name": "dusan",
            "id": 1,
            "skill": "mongodb",
            "skill_id": 3
        },
        {
            "name": "jaca",
            "id": 2,
            "skill": "angular",
            "skill_id": 4
        },
        {
            "name": "jaca",
            "id": 2,
            "skill": "reactjs",
            "skill_id": 5
        },
    ]
}

我知道这就是SQL的设计方式，以返回扁平结构，但是有没有办法完成相关数据的嵌套？

Answer 1

一位开发人员提出了这个解决方案，所以我决定分享它，希望有人会发现它有帮助：

returnedUsers = {
  "users": [{
      "name": "dusan",
      "id": 1,
      "skill": "node.js",
      "skill_id": 1
    },
    {
      "name": "dusan",
      "id": 1,
      "skill": "php",
      "skill_id": 2
    },
    {
      "name": "dusan",
      "id": 1,
      "skill": "mongodb",
      "skill_id": 3
    },
    {
      "name": "jaca",
      "id": 2,
      "skill": "angular",
      "skill_id": 4
    },
    {
      "name": "jaca",
      "id": 2,
      "skill": "reactjs",
      "skill_id": 5
    },
  ]
};
const groupBy = (array, key) =>
  array.reduce((a, c) => ({
    ...a,
    [c[key]]: [...a[c[key]] || [], c]
  }), {});
const uniques = (...values) =>
  Array.from(new Set([].concat(...values).filter(Boolean)));
const singularize = array =>
  array.length == 1 ? array[0] : array;
const singularizedUniques = (...values) =>
  singularize(uniques(...values));
const mergeCollect = array =>
  array.reduce((mergedObject, curentObject) =>
    Object.entries(curentObject).reduce((newObject, [k, v]) => ({
      ...newObject,
      [k]: singularizedUniques(newObject[k], v)
    }), mergedObject), {});
const groupByKey = (array, key) =>
  Object.fromEntries(Object.entries(groupBy(array, key)).map(([k, v]) => [k, mergeCollect(v)]));
console.log(groupByKey(returnedUsers.users, 'id'));