shay Posts - Answer Overflow

shay

Explore posts from servers

DIAdiscord.js - Imagine an app CDCloudflare Developers DTDrizzle Team

•Created by shay on 7/7/2024 in #help

One-to-many relation always returning no records for the `many` relation

Hello, I need some help debugging a relation that I have set up for a postgres database. The schema is as follows:

// `snowflakePk()` is a shortcut for generating a snowflake as the primary key with the name `id`
export const flows = pgTable("Flow", {
  id: snowflakePk(),
  name: text("name"),
});

export const flowsRelations = relations(flows, ({ many }) => ({
  actions: many(flowActions, { relationName: "Flow_Action" }),
}));

export const flowActions = pgTable("Action", {
  id: snowflakePk(),
  type: integer("type").notNull().$type<FlowActionType>(),
  data: json("data").notNull().$type<FlowAction>(),
  flowId: snowflake("flowId")
    .notNull()
    .references(() => flows.id, { onDelete: "cascade" }),
});

export const flowActionsRelations = relations(flowActions, ({ one }) => ({
  flow: one(flows, {
    fields: [flowActions.id],
    references: [flows.id],
    relationName: "Flow_Action",
  }),
}));

// `snowflakePk()` is a shortcut for generating a snowflake as the primary key with the name `id`
export const flows = pgTable("Flow", {
  id: snowflakePk(),
  name: text("name"),
});

export const flowsRelations = relations(flows, ({ many }) => ({
  actions: many(flowActions, { relationName: "Flow_Action" }),
}));

export const flowActions = pgTable("Action", {
  id: snowflakePk(),
  type: integer("type").notNull().$type<FlowActionType>(),
  data: json("data").notNull().$type<FlowAction>(),
  flowId: snowflake("flowId")
    .notNull()
    .references(() => flows.id, { onDelete: "cascade" }),
});

export const flowActionsRelations = relations(flowActions, ({ one }) => ({
  flow: one(flows, {
    fields: [flowActions.id],
    references: [flows.id],
    relationName: "Flow_Action",
  }),
}));

The expected behavior here is to be able to query flows { with: { actions: true }} and get all Action records that have the same flowId as the "parent" Flow. However, this is not the case:

const flow = await db.query.flows.findFirst({
  where: (flows, { eq }) => eq(flows.id, 57101725823143939n),
  columns: { id: true },
  with: { actions: true },
});
console.log(flow); // { id: 57101725823143939n, actions: [] }

const flow = await db.query.flows.findFirst({
  where: (flows, { eq }) => eq(flows.id, 57101725823143939n),
  columns: { id: true },
  with: { actions: true },
});
console.log(flow); // { id: 57101725823143939n, actions: [] }

psql query proving that the expected records do exist:

shay=# select * from "Action" where "flowId" = 57101725823143939;
        id         | type |             data              |      flowId       
-------------------+------+-------------------------------+-------------------
 57101725831532548 |    0 | "{\"type\":0}"                | 57101725823143939
(1 row)

shay=# select * from "Action" where "flowId" = 57101725823143939;
        id         | type |             data              |      flowId       
-------------------+------+-------------------------------+-------------------
 57101725831532548 |    0 | "{\"type\":0}"                | 57101725823143939
(1 row)

So what is going on here? Thanks.

drizzle-kit: v0.22.7
drizzle-orm: v0.31.2

drizzle-kit: v0.22.7
drizzle-orm: v0.31.2

7 replies

Gaming

Programming