Many -> Many relation with additional where clause to make it One > Many

I'm trying to work out if this is possible. I have the following DB schema:

export const paymentPlans = pgTable(
  'payment_plans',
  {
    id: uuid('id')
      .default(sql`gen_random_uuid()`)
      .notNull()
      .primaryKey(),
    revision: integer('revision').notNull().default(1),
    initialRevisionId: uuid('initial_revision_id').notNull().references(() => paymentPlans.id),
  }
);

export const paymentPlanAssetsRelation = relations(paymentPlans, ({ many }) => ({
  assets: many(paymentPlanAssets)
}));

export const paymentPlanAssets = pgTable(
  'payment_plan_assets',
  {
    assetId: uuid('assetId')
      .notNull()
      .references(() => assets.id),
    paymentPlanInitialRevisionId: uuid('payment_plan_initial_revision_id')
      .notNull()
      .references(() => paymentPlans.id),
  },
  (table) => {
    return {
      pk_payment_plan_assets: primaryKey(
        table.assetId,
        table.paymentPlanInitialRevisionId
      ),
    };
  }
);

export const paymentPlanAssetsPaymentPlanRelation = relations(paymentPlanAssets, ({ one }) => ({
  paymentPlan: one(paymentPlans, {
    fields: [paymentPlanAssets.paymentPlanInitialRevisionId],
    references: [paymentPlans.initialRevisionId],
    // SOME SORT OF ADDITIONAL FILTER HERE TO GET JUST THE
    // PAYMENT PLAN WITH HIGHEST REVISION
  }),
}));

export const paymentPlans = pgTable(
  'payment_plans',
  {
    id: uuid('id')
      .default(sql`gen_random_uuid()`)
      .notNull()
      .primaryKey(),
    revision: integer('revision').notNull().default(1),
    initialRevisionId: uuid('initial_revision_id').notNull().references(() => paymentPlans.id),
  }
);

export const paymentPlanAssetsRelation = relations(paymentPlans, ({ many }) => ({
  assets: many(paymentPlanAssets)
}));

export const paymentPlanAssets = pgTable(
  'payment_plan_assets',
  {
    assetId: uuid('assetId')
      .notNull()
      .references(() => assets.id),
    paymentPlanInitialRevisionId: uuid('payment_plan_initial_revision_id')
      .notNull()
      .references(() => paymentPlans.id),
  },
  (table) => {
    return {
      pk_payment_plan_assets: primaryKey(
        table.assetId,
        table.paymentPlanInitialRevisionId
      ),
    };
  }
);

export const paymentPlanAssetsPaymentPlanRelation = relations(paymentPlanAssets, ({ one }) => ({
  paymentPlan: one(paymentPlans, {
    fields: [paymentPlanAssets.paymentPlanInitialRevisionId],
    references: [paymentPlans.initialRevisionId],
    // SOME SORT OF ADDITIONAL FILTER HERE TO GET JUST THE
    // PAYMENT PLAN WITH HIGHEST REVISION
  }),
}));

This is a simplified version of the schema but essentially from the payment_plan_assets table I want a relation where I can get just the current (the record with highest revision value) where the initialRevisionId matches. I think I can get it to work using a where clause in the with property when I query it but then I need to add the where clause into everywhere I use the relationship. Is it possible to have it just defined in the schema?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Many -> Many relation with additional where clause to make it One > Many