Blume Posts - Answer Overflow

Blume

Explore posts from servers

DIAdiscord.js - Imagine an app TtRPC DTDrizzle Team

•Created by Blume on 10/15/2023 in #help

Delete from join

Is it possible to do something like this:

DELETE P
  FROM Product P
  LEFT JOIN OrderItem I ON P.Id = I.ProductId
 WHERE I.Id IS NULL

DELETE P
  FROM Product P
  LEFT JOIN OrderItem I ON P.Id = I.ProductId
 WHERE I.Id IS NULL

With the ORM? e.g.

DB.delete(Product).leftJoin(OrderItem, eq(Product.id, OrderItem.productId).where(...);

DB.delete(Product).leftJoin(OrderItem, eq(Product.id, OrderItem.productId).where(...);

Or do I need to write the full SQL statement in raw sql?

4 replies

DTDrizzle Team

•Created by Blume on 10/15/2023 in #help

Use column name in like operator

I'm trying to create a query with a join where I get all entries whose value starts with the value of the root table. e.g. Tags = { ..., tag: "some", ... } tagsWithNoteIds = [{ ..., tag: "some", ... }, { ..., tag: "some/string", ... }]

DB.with(tagsWithNoteIds)
  .select({
    tagId: Tags.id,
    tag: Tags.tag,
    noteIds: sql`json_agg(${tagsWithNoteIds.noteIds})`,
  })
  .from(Tags)
  .leftJoin(tagsWithNoteIds, or(eq(tagsWithNoteIds.tag, Tags.tag), like(tagsWithNoteIds.tag, `${Tags.tag}/%`)))
  .where(eq(Tags.authorId, query.authorId))
  .groupBy(Tags.id);

DB.with(tagsWithNoteIds)
  .select({
    tagId: Tags.id,
    tag: Tags.tag,
    noteIds: sql`json_agg(${tagsWithNoteIds.noteIds})`,
  })
  .from(Tags)
  .leftJoin(tagsWithNoteIds, or(eq(tagsWithNoteIds.tag, Tags.tag), like(tagsWithNoteIds.tag, `${Tags.tag}/%`)))
  .where(eq(Tags.authorId, query.authorId))
  .groupBy(Tags.id);

I'm not sure how the syntax should be to do this in the like operator. Is this even possible? When I debug the SQL that is created by this it uses params: [ "[object Object]/%", "d792d767-6662-4a56-8b93-bb6dac1a350d" ] for the params.. In the end it would still need a way to know, that the column name shouldn't be used as a raw string so maybe this is just not possible?

2 replies

DTDrizzle Team

•Created by Blume on 10/14/2023 in #help

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?

2 replies

Gaming

Programming