marcbejar Posts - Answer Overflow

marcbejar

Explore posts from servers

•Created by marcbejar on 8/19/2024 in #help

Help with Join query

Hi all, I have this three tables: users (id, name, email) account (id, username, public) userAccounts (user_id, account_id, role) I would like to get a query that for a given account it gives me the account data and all the managers that this account have. I have archived this via two queries, but I would like to do it in just one DB call. The expected result: [ { username public managers: { name, email role, } } ]

3 replies

DTDrizzle Team

•Created by marcbejar on 7/22/2024 in #help

Compostie primary key of a composite primary key

Hello! 👋👋 I have a table Accounts with id as primary key


export const accounts = pgTable('accounts', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
});

Then I have the table sections where the primary key is a composite key of the table id and the account.id referenced key


export const sections = pgTable('sections', {
  id: text('id').notNull().$defaultFn(() => generateIdFromEntropySize(5)),
  account: uuid('account').notNull().references(() => accounts.id, {onUpdate: 'cascade', onDelete:'cascade'}),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.id, table.account] })
  }
})

The problem is that now i need a third table Events where the primary key is a composite between the table primary key (id) and the sections composite primary key:


export const events = pgTable('events', {
  id: text('id').notNull().$defaultFn(() => generateIdFromEntropySize(5)),
  section_id: text('section_id').notNull(),
  section_account: uuid('section_account').notNull(),
}, (table) => {
  return {
    sectionReference: foreignKey({
      columns: [table.section_id, table.section_account],
      foreignColumns: [sections.id, sections.account]
    }),
    pk: primaryKey({ columns: [table.id, table.section_id, table.section_account] })
  }
})

Everything is working fine but I don't know if it's the correct solution as I'm seeing duplicated named fields on neon after applying the migration.

3 replies

Gaming

Programming