Drizzle Team•13mo ago

how to disambiguate query relations

Hi everyone, I'm currently facing an issue with the Query API where I don't understand how to disambiguate a foreign key. I have the following schema:

const baseFields = {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
};

export const users = pgTable('users', {
  ...baseFields,

  email: varchar('email', { length: 256 }).unique().notNull(),
  firstName: varchar('first_name', { length: 256 }).notNull(),
  lastName: varchar('last_name', { length: 256 }).notNull(),
  active: boolean('active').notNull(),
  birthdate: date('birthdate').notNull(),
  sendsDaily: boolean('sends_daily').notNull(),
  joinedAt: date('joined_at').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  sentCoinsTransactions: many(coinsTransactions),
  receivedCoinsTransactions: many(coinsTransactions),
}));

export const coinsTransactions = pgTable('coins_transactions', {
  ...baseFields,

  fromUserId: uuid('from_user_id')
    .notNull()
    .references(() => users.id),
  toUserId: uuid('to_user_id')
    .notNull()
    .references(() => users.id),
  message: text('message').notNull(),
  amount: integer('amount').notNull(),
});

const baseFields = {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
};

export const users = pgTable('users', {
  ...baseFields,

  email: varchar('email', { length: 256 }).unique().notNull(),
  firstName: varchar('first_name', { length: 256 }).notNull(),
  lastName: varchar('last_name', { length: 256 }).notNull(),
  active: boolean('active').notNull(),
  birthdate: date('birthdate').notNull(),
  sendsDaily: boolean('sends_daily').notNull(),
  joinedAt: date('joined_at').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  sentCoinsTransactions: many(coinsTransactions),
  receivedCoinsTransactions: many(coinsTransactions),
}));

export const coinsTransactions = pgTable('coins_transactions', {
  ...baseFields,

  fromUserId: uuid('from_user_id')
    .notNull()
    .references(() => users.id),
  toUserId: uuid('to_user_id')
    .notNull()
    .references(() => users.id),
  message: text('message').notNull(),
  amount: integer('amount').notNull(),
});

as you can see users can have many sent and received transactions, but many offers no way to reference a field like one does, so I'm not sure how to build the users relations for this case. Ideally I would love to be able to query a list of users with their sent or received transactions. For example, in Prisma I would be able to set the reference name in the CoinsTransaction model and then use the same name for the relations in the User model, like they do here: https://www.prisma.io/docs/concepts/components/prisma-schema/relations#disambiguating-relations. Thanks!

Prisma

Relations (Reference)

A relation is a connection between two models in the Prisma schema. This page explains how you can define one-to-one, one-to-many and many-to-many relations in Prisma.

1 Reply

piedra•13mo ago

I think I figured it out

const baseFields = {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
};

export const users = pgTable('users', {
  ...baseFields,

  email: varchar('email', { length: 256 }).unique().notNull(),
  firstName: varchar('first_name', { length: 256 }).notNull(),
  lastName: varchar('last_name', { length: 256 }).notNull(),
  active: boolean('active').notNull(),
  birthdate: date('birthdate').notNull(),
  sendsDaily: boolean('sends_daily').notNull(),
  joinedAt: date('joined_at').notNull(),
  coins: integer('coins').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  sentCoinsTransactions: many(coinsTransactions, {
    relationName: 'sentCoinsTransactions',
  }),
  receivedCoinsTransactions: many(coinsTransactions, {
    relationName: 'receivedCoinsTransactions',
  }),
}));

export const coinsTransactions = pgTable('coins_transactions', {
  ...baseFields,

  fromUserId: uuid('from_user_id')
    .notNull()
    .references(() => users.id),
  toUserId: uuid('to_user_id')
    .notNull()
    .references(() => users.id),
  message: text('message').notNull(),
  amount: integer('amount').notNull(),
});

export const coinsTransactionsRelations = relations(
  coinsTransactions,
  ({ one }) => ({
    fromUser: one(users, {
      fields: [coinsTransactions.fromUserId],
      references: [users.id],
      relationName: 'sentCoinsTransactions',
    }),
    toUser: one(users, {
      fields: [coinsTransactions.toUserId],
      references: [users.id],
      relationName: 'receivedCoinsTransactions',
    }),
  })
);

const baseFields = {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
};

export const users = pgTable('users', {
  ...baseFields,

  email: varchar('email', { length: 256 }).unique().notNull(),
  firstName: varchar('first_name', { length: 256 }).notNull(),
  lastName: varchar('last_name', { length: 256 }).notNull(),
  active: boolean('active').notNull(),
  birthdate: date('birthdate').notNull(),
  sendsDaily: boolean('sends_daily').notNull(),
  joinedAt: date('joined_at').notNull(),
  coins: integer('coins').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  sentCoinsTransactions: many(coinsTransactions, {
    relationName: 'sentCoinsTransactions',
  }),
  receivedCoinsTransactions: many(coinsTransactions, {
    relationName: 'receivedCoinsTransactions',
  }),
}));

export const coinsTransactions = pgTable('coins_transactions', {
  ...baseFields,

  fromUserId: uuid('from_user_id')
    .notNull()
    .references(() => users.id),
  toUserId: uuid('to_user_id')
    .notNull()
    .references(() => users.id),
  message: text('message').notNull(),
  amount: integer('amount').notNull(),
});

export const coinsTransactionsRelations = relations(
  coinsTransactions,
  ({ one }) => ({
    fromUser: one(users, {
      fields: [coinsTransactions.fromUserId],
      references: [users.id],
      relationName: 'sentCoinsTransactions',
    }),
    toUser: one(users, {
      fields: [coinsTransactions.toUserId],
      references: [users.id],
      relationName: 'receivedCoinsTransactions',
    }),
  })
);

It would be good to document this on the site, is it ok to create a PR for the docs?

Gaming

Programming

how to disambiguate query relations