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],
    }),
}));

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)
                },
            }
        })

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

3 Replies

Mykhailo•4mo ago

Hi, @Tobias! In the query, a LEFT JOIN LATERAL with a subquery is used to aggregate site member data into a JSON array. However, the condition linking the user to the sites is applied only within the subquery, not to the main selection of sites. This results in the query returning all sites but aggregates member data only for those sites where the specified user is a member. For sites where the user is not a member, an empty JSON array is returned. I think you can do something like this in your case:

await db.query.sites.findMany({
  where: exists(
    db
      .select()
      .from(schema.siteMembers)
      .where(and(eq(schema.siteMembers.siteId, schema.sites.id), eq(schema.siteMembers.userId, 'userId-1'))),
  ),
  with: {
    members: {
      where: eq(schema.siteMembers.userId, 'userId-1'),
    },
  },
});

await db.query.sites.findMany({
  where: exists(
    db
      .select()
      .from(schema.siteMembers)
      .where(and(eq(schema.siteMembers.siteId, schema.sites.id), eq(schema.siteMembers.userId, 'userId-1'))),
  ),
  with: {
    members: {
      where: eq(schema.siteMembers.userId, 'userId-1'),
    },
  },
});

This query retrieves all sites where user with specific id is a member. In members you always have only 1 user record due to specific id.

Tobias•4mo ago

Hi @solo , thank you for your reply. I figured it was a bit more complex than non working solution.

Mykhailo•4mo ago

btw, you can use query builder for this case and it will be a bit easier

Gaming

Programming

Filter on many to many relationship