DT
Drizzle Team•6mo ago
Melvin

Querying relations

I have this schema: 
 
const users = pgTable()...
const groups = pgTable()...
const usersToGroups = pgTable()....references the respective columns

const usersRelations = relations()...users have many groups
const groupsRelations = relations()...groups have many users

const usersToGroupsRelations = relations()...one userId to one groupId in join table
 
const users = pgTable()...
const groups = pgTable()...
const usersToGroups = pgTable()....references the respective columns

const usersRelations = relations()...users have many groups
const groupsRelations = relations()...groups have many users

const usersToGroupsRelations = relations()...one userId to one groupId in join table
Now I want to search for all the users in a group, so I write a query: 
const filters: SQL[] = [];

query?.groupId ? filters.push(eq(groups.groupId, query.groupId)) : undefined;
query?.userId ? filters.push(eq(users.userId, query.userId)) : undefined;

const users = await db.query.users.findMany({
  columns: { 
    id: true,
    name: true,
  },
  with: {
    groups: {
      groupId: true,
      userId: true,
    }
    }
  },
  where: and(...filters),
)
const filters: SQL[] = [];

query?.groupId ? filters.push(eq(groups.groupId, query.groupId)) : undefined;
query?.userId ? filters.push(eq(users.userId, query.userId)) : undefined;

const users = await db.query.users.findMany({
  columns: { 
    id: true,
    name: true,
  },
  with: {
    groups: {
      groupId: true,
      userId: true,
    }
    }
  },
  where: and(...filters),
)
When I run this, I get the following error 
ERROR [ExceptionsHandler] column users.group_id does not exist
error: column users.group_id does not exist
ERROR [ExceptionsHandler] column users.group_id does not exist
error: column users.group_id does not exist
What am I doing wrong? I'm explicitly selecting the columns I want and the generated sql looked right to me and I believe I've defined the relations correctly. Any help is greatly appreciated.
10 Replies
Melvin
MelvinOP•6mo ago
Generated sql for more context: 
Query: select "users"."id", "users"."name", "users"."entra_user_id", "users"."email", "users_organizations"."data" as "organizations", "users_groups"."data" as "groups", "users_roles"."data" as "roles" from "users" left join lateral (select coalesce(json_agg(json_build_array("users_organizations"."user_id", "users_organizations"."organization_id")), '[]'::json) as "data" from "users_organizations" where "users_organizations"."user_id" = "users"."id") "users_organizations" on true left join lateral (select coalesce(json_agg(json_build_array("users_groups"."group_id", "users_groups"."user_id")), '[]'::json) as "data" from "users_groups" where "users_groups"."user_id" = "users"."id") "users_groups" on true left join lateral (select coalesce(json_agg(json_build_array("users_roles"."user_id", "users_roles"."role_id")), '[]'::json) as "data" from "users_roles" where "users_roles"."user_id" = "users"."id") "users_roles" on true where "users"."group_id" = $1 -- params: [""]
Query: select "users"."id", "users"."name", "users"."entra_user_id", "users"."email", "users_organizations"."data" as "organizations", "users_groups"."data" as "groups", "users_roles"."data" as "roles" from "users" left join lateral (select coalesce(json_agg(json_build_array("users_organizations"."user_id", "users_organizations"."organization_id")), '[]'::json) as "data" from "users_organizations" where "users_organizations"."user_id" = "users"."id") "users_organizations" on true left join lateral (select coalesce(json_agg(json_build_array("users_groups"."group_id", "users_groups"."user_id")), '[]'::json) as "data" from "users_groups" where "users_groups"."user_id" = "users"."id") "users_groups" on true left join lateral (select coalesce(json_agg(json_build_array("users_roles"."user_id", "users_roles"."role_id")), '[]'::json) as "data" from "users_roles" where "users_roles"."user_id" = "users"."id") "users_roles" on true where "users"."group_id" = $1 -- params: [""]
rphlmr âš¡
rphlmr ⚡•6mo ago
Hey 👋 I guess it is the example from the doc? This is a demo with multiple ways https://drizzle.run/vmxbde9k7qh5g5gbtny7fl12
Drizzle Run
Multiple ways to query relations - Drizzle Run
rphlmr âš¡
rphlmr ⚡•6mo ago
console.log(
  "All users of group 1 - group with users",
  await db.query.groups.findFirst({
    where: eq(groups.id, 1),
    with: {
      usersToGroups: {
        with: {
          user: true,
        },
      },
    },
  }),
);

console.log(
  "All users of group 1",
  await db.query.usersToGroups.findMany({
    where: eq(usersToGroups.groupId, 1),
    with: {
      user: true,
      group: true,
    },
  }),
);

console.log(
  "Alternative",
  await db
    .select({ ...getTableColumns(users) })
    .from(usersToGroups)
    .where(eq(usersToGroups.groupId, 1))
    .innerJoin(users, eq(users.id, usersToGroups.userId)),
);
console.log(
  "All users of group 1 - group with users",
  await db.query.groups.findFirst({
    where: eq(groups.id, 1),
    with: {
      usersToGroups: {
        with: {
          user: true,
        },
      },
    },
  }),
);

console.log(
  "All users of group 1",
  await db.query.usersToGroups.findMany({
    where: eq(usersToGroups.groupId, 1),
    with: {
      user: true,
      group: true,
    },
  }),
);

console.log(
  "Alternative",
  await db
    .select({ ...getTableColumns(users) })
    .from(usersToGroups)
    .where(eq(usersToGroups.groupId, 1))
    .innerJoin(users, eq(users.id, usersToGroups.userId)),
);
Melvin
MelvinOP•6mo ago
This is great! And if my users have roles which have permissions, can I include the permissions of the roles within my selection of the roles relationship? Also for my understanding why was it trying to select(?) that column from my users table?
rphlmr âš¡
rphlmr ⚡•6mo ago
I have pushed a change on the demo but I only know how to do that the traditionnal way :/ 
// With role
console.log(
  "With role",
  await db
    .select({ ...getTableColumns(users) })
    .from(usersToGroups)
    .where(and(eq(usersToGroups.groupId, 1), eq(roles.name, "admin")))
    .innerJoin(users, eq(users.id, usersToGroups.userId))
    .innerJoin(usersToRoles, eq(usersToRoles.userId, usersToGroups.userId))
    .innerJoin(roles, eq(roles.id, usersToRoles.userId)),
);
// With role
console.log(
  "With role",
  await db
    .select({ ...getTableColumns(users) })
    .from(usersToGroups)
    .where(and(eq(usersToGroups.groupId, 1), eq(roles.name, "admin")))
    .innerJoin(users, eq(users.id, usersToGroups.userId))
    .innerJoin(usersToRoles, eq(usersToRoles.userId, usersToGroups.userId))
    .innerJoin(roles, eq(roles.id, usersToRoles.userId)),
);
Melvin
MelvinOP•6mo ago
hmm still running into this issue
rphlmr âš¡
rphlmr ⚡•6mo ago
Relation API is limited for complex queries. It will be far easier with joins you can't join/reference other tables in where. It's good for one direct relation but not for checking against multiple conditions
Melvin
MelvinOP•6mo ago
hmm, still getting the same error with my initial query where does the query get generated, maybe I can step through dn see what I'm doing wrong hmmm, okay that confirms one of the tests I just did. With the join approach, is it possible to select columns from the joined tables? Looking at the roles example, lets say I want to get back 
{ userId: 1, groupId: 1, roles: ["Admin", "Manager"] }
{ userId: 1, groupId: 1, roles: ["Admin", "Manager"] }
is that possible? I was able to with the complex query. Only downside is I now need to manipulate the data into the shape I need.
rphlmr âš¡
rphlmr ⚡•6mo ago
If it is too complex with sql, do this mapping in JS/TS after you get the results of the query.
Melvin
MelvinOP•6mo ago
Yeah that's what I ended up doing.
Want results from more Discord servers?
Add your server