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

5 Replies

shayOP•6mo ago

I would provide a working drizzle.run for this but for some reason it's not creating the tables. I have to go but here's what I have https://drizzle.run/n2z8f3privcq6y53sv8bfawo

Drizzle Run

undefined - Drizzle Run

rphlmr ⚡•6mo ago

Hey 👋 In order to share your playground, you have to enable share (the link you shared is a local only playground 😬). Sorry if the process was not clear enough. your issue is here:

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

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

the field of flowActions is flowActions.flowId not flowActions.id

rphlmr ⚡•6mo ago

https://drizzle.run/tmae7pbreskk7dl8gxd9pjxg

Drizzle Run

shay's one-to-many relation - Drizzle Run

shayOP•6mo ago

oh! thanks so much, I don't know how I didn't see that. and sorry about the playground, oops

rphlmr ⚡•6mo ago

No problem, I updated Drizzle Run to add some labels (icons can sometimes not be obvious).

Gaming

Programming

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