Query in drizzle doesn't work
Error:
DrizzleError: No fields selected for table "usersToGroups" ("users_groups")DrizzleError: No fields selected for table "usersToGroups" ("users_groups")Drizzle query builder
await db
.select({
id: users.id,
name: users.name,
groups: sql`json_agg(jsonb_build_object('id', ${groups.id}, 'name', ${groups.name}))`,
})
.from(users)
.leftJoin(usersToGroups, eq(users.id, usersToGroups.userId))
.leftJoin(groups, eq(groups.id, usersToGroups.groupId))
.groupBy(users.id, users.name, usersToGroups.userId)
.where(eq(users.id, newUser.id)); await db
.select({
id: users.id,
name: users.name,
groups: sql`json_agg(jsonb_build_object('id', ${groups.id}, 'name', ${groups.name}))`,
})
.from(users)
.leftJoin(usersToGroups, eq(users.id, usersToGroups.userId))
.leftJoin(groups, eq(groups.id, usersToGroups.groupId))
.groupBy(users.id, users.name, usersToGroups.userId)
.where(eq(users.id, newUser.id));SQL query
SELECT
u.id,
u.name,
json_agg(jsonb_build_object('id', g.id, 'name', g.name)) AS groups
FROM users u
LEFT JOIN users_to_groups utg ON u.id = utg.user_id
LEFT JOIN groups g ON utg.group_id = g.id
GROUP BY u.id, u.name, utg.user_id;SELECT
u.id,
u.name,
json_agg(jsonb_build_object('id', g.id, 'name', g.name)) AS groups
FROM users u
LEFT JOIN users_to_groups utg ON u.id = utg.user_id
LEFT JOIN groups g ON utg.group_id = g.id
GROUP BY u.id, u.name, utg.user_id;pgsql cli output 
