Drizzle TeamDT
Drizzle Team•2y ago
Tobias

Filter on many to many relationship

I want to find all sites where a user is a member in.

I have 3 tables that I have setup relations between

export const users = pgTable("users", {
    id: text("id").primaryKey(),
    name: text("name"),
    email: text("email").unique(),
    avatarUrl: text('avatar_url')
});

export const sites = pgTable("sites", {
    id: serial("id").primaryKey(),
    createdAt: timestamp('created_at', {
        withTimezone: true
    }).defaultNow(),
    uuid: uuid('uuid').defaultRandom(),
    name: text("name").notNull(),
    slug: text("slug").unique().notNull()
});

export const usersRelations = relations(users, ({ many }) => ({
    sites: many(siteMembers),
}));

export const sitesRelations = relations(sites, ({ many }) => ({
    members: many(siteMembers),
}));

export const siteMembers = pgTable('site_members', {
    siteId: integer('site_id').references(() => sites.id, { onDelete: 'cascade' }),
    userId: text("user_id").notNull().references(() => users.id, { onDelete: 'cascade' })
}, (table) => {
    return {
        pk: primaryKey(table.siteId, table.userId),
    }

});

export const userSiteMembersRelations = relations(siteMembers, ({ one }) => ({
    sites: one(sites, {
        fields: [siteMembers.siteId],
        references: [sites.id],
    }),
    user: one(users, {
        fields: [siteMembers.userId],
        references: [users.id],
    }),
}));


Now I am trying to figure out how to query all sites where user with a specific ID is a member in.

But when I do the following query, I just get all sites but with an empty array for the sites where the user is not a member in. My goal is to only get the sites where the user is a member in.
How can I query this?

await db.query.sites.findMany({
            with: {
                members: {
                    where: (members, { eq }) => eq(members.userId, user.id)
                },
            }
        })
Was this page helpful?