Thomsr Posts - Answer Overflow

Thomsr

•Created by Thomsr on 11/17/2024 in #help

On delete cascade not working

I have the following tables:

export const exercises = sqliteTable("exercises", {
  id: integer("id").primaryKey().notNull(),
  created_at: integer("created_at").default(sql`(strftime('%Y-%m-%d', 'now'))`),
  exercise_name: text("exercise_name").notNull(),
});

export const labels = sqliteTable("labels", {
  id: integer("id").primaryKey().notNull(),
  name: text("name").notNull(),
  parent_id: integer("parent_id"),
});

export const exercise_label = sqliteTable("exercise_label", {
  exercise_id: integer("exercise_id")
    .references(() => exercises.id)
    .notNull(),
  label_id: integer("label_id")
    .references(() => labels.id)
    .notNull(),
});

export const sets = sqliteTable("sets", {
  id: integer("id").primaryKey().notNull(),
  exercise_id: integer("exercise_id")
    .references(() => exercises.id, { onDelete: "cascade" })
    .notNull(),
  date: integer("date")
    .default(sql`(strftime('%Y-%m-%d', 'now'))`)
    .notNull(),
  kg: integer("kg").notNull(),
  reps: integer("reps").notNull(),
});

export const exercises = sqliteTable("exercises", {
  id: integer("id").primaryKey().notNull(),
  created_at: integer("created_at").default(sql`(strftime('%Y-%m-%d', 'now'))`),
  exercise_name: text("exercise_name").notNull(),
});

export const labels = sqliteTable("labels", {
  id: integer("id").primaryKey().notNull(),
  name: text("name").notNull(),
  parent_id: integer("parent_id"),
});

export const exercise_label = sqliteTable("exercise_label", {
  exercise_id: integer("exercise_id")
    .references(() => exercises.id)
    .notNull(),
  label_id: integer("label_id")
    .references(() => labels.id)
    .notNull(),
});

export const sets = sqliteTable("sets", {
  id: integer("id").primaryKey().notNull(),
  exercise_id: integer("exercise_id")
    .references(() => exercises.id, { onDelete: "cascade" })
    .notNull(),
  date: integer("date")
    .default(sql`(strftime('%Y-%m-%d', 'now'))`)
    .notNull(),
  kg: integer("kg").notNull(),
  reps: integer("reps").notNull(),
});

Note that int the sets table, there is a statement that on deletion of an exercise, the sets should also be deleted. however, when i test this, the rows in sets with the exercise id of the deleted exercise are not deleted. Am I doing something wrong?

3 replies

DTDrizzle Team

•Created by Thomsr on 10/17/2024 in #help

Is there a way to create this query in drizzle?

Hi, I have this query for my mobile app with expo and react native. I need to have a recursive query, but cant seem to find how to create one in drizzle.

    WITH RECURSIVE LabelHierarchy AS (
      SELECT id, name, parent_id
      FROM labels
      WHERE id = ${labelId}
      UNION ALL
      SELECT l.id, l.name, l.parent_id
      FROM labels l
      INNER JOIN LabelHierarchy lh ON lh.id = l.parent_id
    )
    SELECT e.id, e.exercise_name
    FROM exercises e
    INNER JOIN exercise_label el ON e.id = el.exercise_id
    INNER JOIN LabelHierarchy lh ON el.label_id = lh.id;

    WITH RECURSIVE LabelHierarchy AS (
      SELECT id, name, parent_id
      FROM labels
      WHERE id = ${labelId}
      UNION ALL
      SELECT l.id, l.name, l.parent_id
      FROM labels l
      INNER JOIN LabelHierarchy lh ON lh.id = l.parent_id
    )
    SELECT e.id, e.exercise_name
    FROM exercises e
    INNER JOIN exercise_label el ON e.id = el.exercise_id
    INNER JOIN LabelHierarchy lh ON el.label_id = lh.id;

Please let me know if it is possible to rewrite this with drizzle :) Thanks!

1 replies

Gaming

Programming