Drizzle Team•14mo ago

many-to-many query with where filter on second level

Hi, I'm trying to create a many-to-many query where I can filter on the second "with" level.. My schema looks like this:

/**
 * NOTES
 */

export const Notes = pgTable("notes", {
  id: uuid("id").defaultRandom().primaryKey(),
  authorId: uuid("author_id")
    .references(() => Users.id)
    .notNull(),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
});

export const NotesRelations = relations(Notes, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * TAGS
 */

export const Tags = pgTable(
  "tags",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    authorId: uuid("author_id")
      .references(() => Users.id)
      .notNull(),
    tag: text("tag").notNull(),
    createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({ unq: unique().on(t.authorId, t.tag) }),
);

export const TagsRelations = relations(Tags, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * NOTES_TAGS
 */

export const NotesToTags = pgTable(
  "notes_to_tags",
  {
    noteId: uuid("note_id")
      .notNull()
      .references(() => Notes.id),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => Tags.id),
  },
  (t) => ({ pk: primaryKey(t.noteId, t.tagId) }),
);

export const NotesToTagsRelations = relations(NotesToTags, ({ one }) => ({
  note: one(Notes, { fields: [NotesToTags.noteId], references: [Notes.id] }),
  tag: one(Tags, { fields: [NotesToTags.tagId], references: [Tags.id] }),
}));

/**
 * NOTES
 */

export const Notes = pgTable("notes", {
  id: uuid("id").defaultRandom().primaryKey(),
  authorId: uuid("author_id")
    .references(() => Users.id)
    .notNull(),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
});

export const NotesRelations = relations(Notes, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * TAGS
 */

export const Tags = pgTable(
  "tags",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    authorId: uuid("author_id")
      .references(() => Users.id)
      .notNull(),
    tag: text("tag").notNull(),
    createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({ unq: unique().on(t.authorId, t.tag) }),
);

export const TagsRelations = relations(Tags, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * NOTES_TAGS
 */

export const NotesToTags = pgTable(
  "notes_to_tags",
  {
    noteId: uuid("note_id")
      .notNull()
      .references(() => Notes.id),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => Tags.id),
  },
  (t) => ({ pk: primaryKey(t.noteId, t.tagId) }),
);

export const NotesToTagsRelations = relations(NotesToTags, ({ one }) => ({
  note: one(Notes, { fields: [NotesToTags.noteId], references: [Notes.id] }),
  tag: one(Tags, { fields: [NotesToTags.tagId], references: [Tags.id] }),
}));

And I tried to query it with

return DB.query.Notes.findMany({
  where: (Notes, { eq }) => eq(Notes.authorId, query.authorId),
  with: {
    notesToTags: {
      with: {
        tag: {
          where: (Tags, { like }) => {
            if (query.tag) return like(Tags.tag, `${query.tag}%`);
          },
        },
      },
    },
  },
});

return DB.query.Notes.findMany({
  where: (Notes, { eq }) => eq(Notes.authorId, query.authorId),
  with: {
    notesToTags: {
      with: {
        tag: {
          where: (Tags, { like }) => {
            if (query.tag) return like(Tags.tag, `${query.tag}%`);
          },
        },
      },
    },
  },
});

But it won't allow the where inside the second with.. Am I doing something wrong? Is this simply something that is not possible with drizzle? What I'm aiming for is to get all Notes where the tag starts with query.tag. I'm not that used to SQL so I might be missing some fundamentals to querying many-to-many relations. In pure SQL I think it could be done with 2 left joins and some aggregation, right?

1 Reply

BlumeOP•14mo ago

I was able to solve it with

const selectTags = DB.$with("sq").as(
  DB.select({ noteId: NotesToTags.noteId, tags: sql<string[]>`array_agg(${Tags.tag})`.as("tags") })
    .from(NotesToTags)
    .leftJoin(Tags, eq(Tags.id, NotesToTags.tagId))
    .where(like(Tags.tag, `${query.tag ?? ""}%`))
    .groupBy(NotesToTags.noteId),
);

const result = await DB.with(selectTags)
  .select()
  .from(Notes)
  .innerJoin(selectTags, eq(selectTags.noteId, Notes.id))
  .where(and(eq(Notes.authorId, query.authorId)));

const selectTags = DB.$with("sq").as(
  DB.select({ noteId: NotesToTags.noteId, tags: sql<string[]>`array_agg(${Tags.tag})`.as("tags") })
    .from(NotesToTags)
    .leftJoin(Tags, eq(Tags.id, NotesToTags.tagId))
    .where(like(Tags.tag, `${query.tag ?? ""}%`))
    .groupBy(NotesToTags.noteId),
);

const result = await DB.with(selectTags)
  .select()
  .from(Notes)
  .innerJoin(selectTags, eq(selectTags.noteId, Notes.id))
  .where(and(eq(Notes.authorId, query.authorId)));

Gaming

Programming

many-to-many query with where filter on second level