How to cascade deletions in many-to-many schema which have only one relation?

[hilja]

I’m using the beta RGB-v2, and I have a many-to-many relations from audits to reports.

Schema and relations

// relations.ts
export const relations = defineRelations(schema, r => ({
  reports: {
    audits: r.many.audits(),
  },
  audits: {
    reports: r.many.reports({
      from: r.audits.id.through(r.auditsToReports.auditId),
      to: r.reports.id.through(r.auditsToReports.reportId),
    }),
  },
}))

The junction table:

// audits.ts
export const auditsToReports = pgTable(
  'audits_to_reports',
  {
    auditId: text('audit_id')
      .notNull()
      .references(() => audits.id, { onDelete: 'cascade' }),
    reportId: text('report_id')
      .notNull()
      .references(() => reports.id, { onDelete: 'cascade' }),
  },
  t => [
    primaryKey({ columns: [t.auditId, t.reportId] }),
    index('audits_to_reports_audit_id_idx').on(t.auditId),
    index('audits_to_reports_report_id_idx').on(t.reportId),
    index('audits_to_reports_composite_idx').on(t.auditId, t.reportId),
  ]
)

When I delete the "Report A", the deletion cascades to the junction tables just fine, but not to the the audits.

And if I query the related audit ids to delete them, I will get all of them ofc, but some of them are related to other reports and should not be deleted.

Is there a smart way not to delete the shared audits? All I can think of is to peruse the database periodically looking for orphaned audits.

Similar question:

• How do delete conditionally in a many-to-many relation #2587

[hilja]

I think I can find the orphaned audits by checking if the auditId is present in audits and in the junction table:

const orphanedAudits = await db
  .select({ id: audits.id })
  .from(audits)
  .leftJoin(auditsToReports, eq(audits.id, auditsToReports.auditId))
  .where(isNull(auditsToReports.auditId))