CODEWITHSUNDEEP
javascriptsqlknex.js
Raj
Raj

Reputation: 1655

Using Knex, how can the relevant rows of a joined table be given as a nested object?

Using knex, how can I get a user's relevant row from the users table along with an Array of all the groups for a user with the id=1?

This is my users table: https://i.imgur.com/t2psBd4.png

This is my groups table: enter image description here

And this is my users_groups association table: enter image description here

…I am running this query but it's returning 3 separate rows for the same user:

db("users").join("users_groups", "users.id", "=", "users_groups.user_id").join("groups", "groups.id", "=", "users_groups.group_id").where("users.id", "=", 1)

which I believe translates to:

select * from users inner join users_groups on users.id = users_groups.user_id inner join groups on groups.id = users_groups.group_id where users.id=1

The SQL returns: enter image description here

It's currently returning:

Array(3) [Object, Object, Object]
length:3
__proto__:Array(0) [, …]
0:Object {email:"[email protected]" group_id:1, id:1, name:"step 1", name:"r", role:"superadmin", user_id:1, username:"raj"}
1:Object {email:"[email protected]" group_id:2, id:1, name:"step 2", name:"r", role:"superadmin", user_id:1, username:"raj"}
2:Object {email:"[email protected]" group_id:3, id:1, name:"step 3", name:"r", role:"superadmin", user_id:1, username:"raj"}

Stringified, it looks like this

"[{"id":1,"name":"step 1","email":"[email protected]","username":"raj","password":"$2b$10$GbbLTP2sEPS7OKmR4l8RSeX/PUmoIFyNBJb1RIIIrbZa1NNwolHFK","role":"superadmin","created_at":"2020-04-14T12:45:38.138Z","user_id":1,"group_id":1},{"id":2,"name":"step 2","email":"[email protected]","username":"raj","password":"$2b$10$GbbLTP2sEPS7OKmR4l8RSeX/PUmoIFyNBJb1RIIIrbZa1NNwolHFK","role":"superadmin","created_at":"2020-04-14T12:45:38.138Z","user_id":1,"group_id":2},{"id":3,"name":"step 3","email":"[email protected]","username":"raj","password":"$2b$10$GbbLTP2sEPS7OKmR4l8RSeX/PUmoIFyNBJb1RIIIrbZa1NNwolHFK","role":"superadmin","created_at":"2020-04-14T12:45:38.138Z","user_id":1,"group_id":3}]"

I'd rather have it return an object representing the single user row, with a nested object for the 3 relevant rows from the groups table. For example: 

{id:1, name:"raj", groups:[{id:1, name:"step 1"}, {id:2,name:"step 2"}, {id:3,name:"step 3"}]}

Is this possible? Or would multiple queries be required and how wasteful is that?

Upvotes: 0

Views: 2131

Answers (1)

felixmosh
felixmosh

Reputation: 35503

Knex is not able to aggregate flat data as you needed. You should do it yourself.

(await db('users')
  .join('users_groups', 'users.id', '=', 'users_groups.user_id')
  .join('groups', 'groups.id', '=', 'users_groups.group_id')
  .where('users.id', '=', 1)
  )
  .reduce((result, row) => {
    result[row.id] = result[row.id] || {
      id: row.id,
      username: row.username,
      email: row.email,
      groups: [],
    };

    result[row.id].groups.push({ id: row.group_id, name: row.name });
    return result;
  }, {});

Upvotes: 2

Related Questions