How can I retrieve a user's relevant row from the users table using knex, along with an array of all the groups associated with a user whose id is 1?
Here is the structure of my users table: https://i.sstatic.net/9lhPZ.png
This is the setup of my groups table: https://i.sstatic.net/A4aMf.png
And this is the schema for my users_groups association table: https://i.sstatic.net/3TrD8.png
Despite running a query, it currently returns three 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)
The SQL equivalent of this query is as follows:
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 current output is:
Array(3) [Object, Object, Object]
length:3
__proto__:Array(0) [, …]
0:Object {email:"<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="3d4f5c577d4f5c57134f5c57">[email protected]</a>" group_id:1, id:1, name:"step 1", name:"r", role:"superadmin", user_id:1, username:"raj"}
1:Object {email:"<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1664777c5664777c3864777c">[email protected]</a>" group_id:2, id:1, name:"step 2", name:"r", role:"superadmin", user_id:1, username:"raj"}
2:Object {email:"<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ee9c8f84ae9c8f84c09c8f84">[email protected]</a>" group_id:3, id:1, name:"step 3", name:"r", role:"superadmin", user_id:1, username:"raj"}
In stringified format, it appears like this:
"[{"id":1,"name":"step 1","email":"<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bdcfdcd7fdcfdcd793cfdcd7">[email protected]</a>","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":"<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="384a5952784a...
An ideal output would be an object representing the user along with nested objects for the relevant group rows from the groups table, such as:
{id:1, name:"raj", groups:[{id:1, name:"step 1"}, {id:2,name:"step 2"}, {id:3,name:"step 3"}]}
Is there a possibility to achieve this in a single query or would multiple queries be necessary, and how efficient would that be?