DT
Drizzle Team•16mo ago
shay

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
shay
shayOP•16mo 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 âš¡
rphlmr ⚡•16mo 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 âš¡
rphlmr ⚡•16mo ago
shay
shayOP•16mo ago
oh! thanks so much, I don't know how I didn't see that. and sorry about the playground, oops
rphlmr âš¡
rphlmr ⚡•16mo ago
No problem, I updated Drizzle Run to add some labels (icons can sometimes not be obvious).

Did you find this page helpful?