Melvin Posts - Answer Overflow

Melvin

•Created by Melvin on 7/18/2024 in #help

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.

20 replies

Gaming

Programming