How to nest data from a join table in a one (or many) to many relationship in knex.js?

user_7132099 0
Viewed 1216

Let's say I have users, skills and user_skill tables.

How can I query so that if I have 3 users, I get 3 objects (in an array, obviously) and for each user's object there should be a nested array with all the skills?

Something like this: 

{
    "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}
            ]
        },

    ]
}

My code:

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"
);

The above code returns a completely flat structured JSON, and I want to have skills nested inside the user object. 

{
    "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
        },
    ]
}

I understand this is how SQL is designed, to return flat structure, but is there a way I could accomplish the nesting of related data?

knex.js node.js postgresql orm sql
1 Answers

A fellow developer has come up with this solution, so I've decided to share it in hope that someone would find it helpful:

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'));

user_71320990
Related