DT
Drizzle Team•12mo ago
jakeleventhal

Many to many relation not enough information

export const orderRelations = relations(orders, ({ many, one }) => ({
  stripeTransactions: many(stripeTransactions),
}));

export const stripeTransactionRelations = relations(stripeTransactions, ({ many }) => ({
  orders: many(orders)
}));

export const orderToStripeTransactionRelations = relations(orderToStripeTransactions, ({ one }) => ({
  order: one(orders, {
    fields: [orderToStripeTransactions.a],
    references: [orders.id]
  }),
  stripeTransaction: one(stripeTransactions, {
    fields: [orderToStripeTransactions.b],
    references: [stripeTransactions.id]
  })
}));
export const orderRelations = relations(orders, ({ many, one }) => ({
  stripeTransactions: many(stripeTransactions),
}));

export const stripeTransactionRelations = relations(stripeTransactions, ({ many }) => ({
  orders: many(orders)
}));

export const orderToStripeTransactionRelations = relations(orderToStripeTransactions, ({ one }) => ({
  order: one(orders, {
    fields: [orderToStripeTransactions.a],
    references: [orders.id]
  }),
  stripeTransaction: one(stripeTransactions, {
    fields: [orderToStripeTransactions.b],
    references: [stripeTransactions.id]
  })
}));
when i query 
db.query.orders.findMany({
  with: { stripeTransactions: true }
})
db.query.orders.findMany({
  with: { stripeTransactions: true }
})
it tells me
Error: There is not enough information to infer relation "orders.stripeTransactions"
what other info could be needed
19 Replies
George
George•12mo ago
@jakeleventhal Hey, would you mind including the tables?
jakeleventhal
jakeleventhalOP•12mo ago
export const orders = pgTable(
  'Order',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
  }
);
 
export const stripeTransactions = pgTable('StripeTransaction', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => sql`gen_random_uuid()::text`)
    .notNull()
});


export const orderToStripeTransactions = pgTable(
  '_OrderToStripeTransaction',
  {
    a: text('A')
      .notNull()
      .references(() => orders.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    b: text('B')
      .notNull()
      .references(() => stripeTransactions.id, { onDelete: 'cascade', onUpdate: 'cascade' })
  },
  (table) => ({
    abUnique: uniqueIndex('_OrderToStripeTransaction_AB_unique').on(table.a, table.b),
    bIdx: index().on(table.b)
  })
);
export const orders = pgTable(
  'Order',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
  }
);
 
export const stripeTransactions = pgTable('StripeTransaction', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => sql`gen_random_uuid()::text`)
    .notNull()
});


export const orderToStripeTransactions = pgTable(
  '_OrderToStripeTransaction',
  {
    a: text('A')
      .notNull()
      .references(() => orders.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    b: text('B')
      .notNull()
      .references(() => stripeTransactions.id, { onDelete: 'cascade', onUpdate: 'cascade' })
  },
  (table) => ({
    abUnique: uniqueIndex('_OrderToStripeTransaction_AB_unique').on(table.a, table.b),
    bIdx: index().on(table.b)
  })
);
(obviously truncated irrelevant fields) the many to many table was generated via prisma
George
George•12mo ago
It'd probably be less confusing if you renamed a and b to orderId and stripeTransactionsId respectively, but that's besides the point This should solve the issue, since a many-to-many relationship is two one-to-many relationships with a table in between: 
export const orderRelations = relations(orders, ({ many, one }) => ({
  stripeTransactions: many(orderToStripeTransactionRelations),
}));

export const stripeTransactionRelations = relations(stripeTransactions, ({ many }) => ({
  orders: many(orderToStripeTransactionRelations)
}));

export const orderToStripeTransactionRelations = relations(orderToStripeTransactions, ({ one }) => ({
  order: one(orders, {
    fields: [orderToStripeTransactions.a],
    references: [orders.id]
  }),
  stripeTransaction: one(stripeTransactions, {
    fields: [orderToStripeTransactions.b],
    references: [stripeTransactions.id]
  })
}));
export const orderRelations = relations(orders, ({ many, one }) => ({
  stripeTransactions: many(orderToStripeTransactionRelations),
}));

export const stripeTransactionRelations = relations(stripeTransactions, ({ many }) => ({
  orders: many(orderToStripeTransactionRelations)
}));

export const orderToStripeTransactionRelations = relations(orderToStripeTransactions, ({ one }) => ({
  order: one(orders, {
    fields: [orderToStripeTransactions.a],
    references: [orders.id]
  }),
  stripeTransaction: one(stripeTransactions, {
    fields: [orderToStripeTransactions.b],
    references: [stripeTransactions.id]
  })
}));
which would mean querying using: 
db.query.orders.findMany({
    with: {
        stripeTransactions: {
            with: {
                stripeTransaction: true,
            },
        },
    },
});
db.query.orders.findMany({
    with: {
        stripeTransactions: {
            with: {
                stripeTransaction: true,
            },
        },
    },
});
jakeleventhal
jakeleventhalOP•12mo ago
yeah, i suppose that works its just a bit confusing having the double nested with also doesnt really allow me to do things like "only query stripe transactions where stripeTransaction.isRefund is false those came from auto-gend prisma fields
George
George•12mo ago
yeah, that's because prisma creates the pivot table which sits between the many-to-many relationship on two tables... so it's a hidden table from the schema, but it applied to the database You can definitely do that through the query builder. I'm relatively new to drizzle, so I'm not sure if you can do it using the query api, although I believe I've seen people here filter using a foreign table
George
George•12mo ago
https://orm.drizzle.team/docs/rqb#select-filters
Drizzle ORM - next gen TypeScript ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
George
George•12mo ago
here 
db.query.orders.findMany({
    with: {
        stripeTransactions: {
            with: {
                stripeTransaction: {
                  where: (stripeTransactions, { eq }) => eq(stripeTransactions.isRefund , false)
                },
            },
        },
    },
});
db.query.orders.findMany({
    with: {
        stripeTransactions: {
            with: {
                stripeTransaction: {
                  where: (stripeTransactions, { eq }) => eq(stripeTransactions.isRefund , false)
                },
            },
        },
    },
});
so i guess it would look like this?
jakeleventhal
jakeleventhalOP•12mo ago
that doesnt work because you cant use where conditions on one relations and the where really needs to be on the outer stripeTransactions query
George
George•12mo ago
ahh you're right, yeah
jakeleventhal
jakeleventhalOP•12mo ago
then you get a really ugly nested query thats why i thought surely im not doing this correctly 
stripeTransactions: {
  where: WHATGOESHERE,
  with: { stripeTransaction: true }
}
stripeTransactions: {
  where: WHATGOESHERE,
  with: { stripeTransaction: true }
}
George
George•12mo ago
i believe the table and relation definitions are correct, but i haven't experimented enough myself to suggest a solution for filtering
jakeleventhal
jakeleventhalOP•12mo ago
stripeTransactions: {
  where: inArray(
    orderToStripeTransactions.b,
    db
      .select({ id: stripeTransactions.id })
      .from(stripeTransactions)
      .where(and(eq(stripeTransactions.isRefund, false), eq(stripeTransactions.userId, req.user.id)))
  ),
  with: { stripeTransaction: true }
}
stripeTransactions: {
  where: inArray(
    orderToStripeTransactions.b,
    db
      .select({ id: stripeTransactions.id })
      .from(stripeTransactions)
      .where(and(eq(stripeTransactions.isRefund, false), eq(stripeTransactions.userId, req.user.id)))
  ),
  with: { stripeTransaction: true }
}
this looks really dumb and creates a lot of overhead @Angelelz is this the best wya to handle this type of query? seems like theres gotta be a better way than to query all of the users stripeTransactions
George
George•12mo ago
yeah, this would be useful for me to know too :D
Angelelz
Angelelz•12mo ago
I don't think that's dumb, or creates overhead. What gives you that impression? With the correct indexes, and if this is the structure of the data, this is it This is how SQL works The other option is to get the transactions first and then send another query, you might want to benchmark both approaches When you're using the RQB, you are more limited. The correct way of running this query is with joins. That will be the most performant way
jakeleventhal
jakeleventhalOP•12mo ago
you cant really do joins with the relational query builder though right the overhead was that it seems like a large subquery
Angelelz
Angelelz•12mo ago
Yeah, to do joins you need to run the queries with the crud api
jakeleventhal
jakeleventhalOP•12mo ago
huh? what do you mean you mean just like db.select... etc
Angelelz
Angelelz•12mo ago
Yeah
jakeleventhal
jakeleventhalOP•12mo ago
yeah it's just messy with jsonagg etc i guess a prisma-like many to many with would be nice basically bypass the pivot table by specifying it in the relations for many to many. then just use with: { stripeTransactions: true }
Want results from more Discord servers?
Add your server