AndréLB Posts - Answer Overflow

AndréLB

•Created by AndréLB on 1/4/2025 in #help

Upsert on foreign key changes serial ID

      const employeeStoresRes = await tx
        .insert(employeeStore)
        .values(employeeIDStoreID)
        .onConflictDoNothing()
        .returning({ storeID: employeeStore.storeID })

export const employeeStore = pgTable(
  'employeeStore',
  {
    employeeStoreID: serial().$type<EmployeeStoreID>().primaryKey(),
    storeID: integer()
      .$type<StoreID>()
      .references(() => stores.storeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    employeeID: integer()
      .$type<EmployeeID>()
      .references(() => employees.employeeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    ...dbDates,
  },
  (employeeStore) => {
    return {
      unique: unique('unique_employeeStore').on(employeeStore.storeID, employeeStore.employeeID),
    }
  },
)

export const employeeStoreRelations = relations(employeeStore, ({ one, many }) => ({
  employees: one(employees),
  stores: one(stores),
  employeeCheckin: many(employeeCheckin),
}))

export const employeeCheckin = pgTable(
  'employeeCheckin',
  {
    employeeCheckinID: serial().$type<EmployeeCheckinID>().primaryKey(),
    employeeStoreID: integer()
      .$type<EmployeeStoreID>()
      .references(() => employeeStore.employeeStoreID, {
        onDelete: 'cascade',
      }),
    employeeCheckedIn: timestamp({ mode: 'date' }),
    employeeCheckedOut: timestamp({ mode: 'date' }),
  },
  (employeeCheckin) => {
    return {
      index: index('checkinIndex').on(employeeCheckin.employeeCheckedIn),
    }
  },
)

      const employeeStoresRes = await tx
        .insert(employeeStore)
        .values(employeeIDStoreID)
        .onConflictDoNothing()
        .returning({ storeID: employeeStore.storeID })

export const employeeStore = pgTable(
  'employeeStore',
  {
    employeeStoreID: serial().$type<EmployeeStoreID>().primaryKey(),
    storeID: integer()
      .$type<StoreID>()
      .references(() => stores.storeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    employeeID: integer()
      .$type<EmployeeID>()
      .references(() => employees.employeeID, {
        onDelete: 'cascade',
      })
      .notNull(),
    ...dbDates,
  },
  (employeeStore) => {
    return {
      unique: unique('unique_employeeStore').on(employeeStore.storeID, employeeStore.employeeID),
    }
  },
)

export const employeeStoreRelations = relations(employeeStore, ({ one, many }) => ({
  employees: one(employees),
  stores: one(stores),
  employeeCheckin: many(employeeCheckin),
}))

export const employeeCheckin = pgTable(
  'employeeCheckin',
  {
    employeeCheckinID: serial().$type<EmployeeCheckinID>().primaryKey(),
    employeeStoreID: integer()
      .$type<EmployeeStoreID>()
      .references(() => employeeStore.employeeStoreID, {
        onDelete: 'cascade',
      }),
    employeeCheckedIn: timestamp({ mode: 'date' }),
    employeeCheckedOut: timestamp({ mode: 'date' }),
  },
  (employeeCheckin) => {
    return {
      index: index('checkinIndex').on(employeeCheckin.employeeCheckedIn),
    }
  },
)

The problem is that that even when doing nothing the employeeStoreID is being updated on the employeeStore table. This means the employeeCheckin table has an outdated value for employeeStoreID. I use employeeStoreID as a foreign key in many places so it won't be an easy refactor to have a composite key.

4 replies

DTDrizzle Team

•Created by AndréLB on 9/9/2024 in #help

Getting total sales by service

I have the tables orderListing and orders:

  const orderListing = = pgTable(
  'orderListing',
  {
    orderID: integer('orderID')
      .$type<OrderID>()
      .references(() => orders.orderID, { onDelete: 'cascade' })
      .notNull(),
    serviceID: integer('serviceID')
      .$type<ServiceID>()
      .references(() => services.serviceID, { onDelete: 'cascade' })
      .notNull(),
    name: varchar('name', { length: 256 }).$type<ServiceName>().notNull(),
    amount: integer('amount').$type<Amount>().notNull(),
    cost: real('cost').$type<ServiceCostNumber>().notNull(),
  },
  (orderListing) => {
    return { pk: primaryKey({ columns: [orderListing.orderID, orderListing.serviceID] }) }
  },
  )

  export const orders = pgTable( 'orders', {
  orderID: serial('orderID').$type<OrderID>().primaryKey(), 
  submissionTime: timestamp('submissionTime', { mode: 'string' }).$type<SubmissionTimeOrder>().notNull(),
  orderStatus: orderStatuspgEnum('orderStatus').notNull(), 
})

  const orderListing = = pgTable(
  'orderListing',
  {
    orderID: integer('orderID')
      .$type<OrderID>()
      .references(() => orders.orderID, { onDelete: 'cascade' })
      .notNull(),
    serviceID: integer('serviceID')
      .$type<ServiceID>()
      .references(() => services.serviceID, { onDelete: 'cascade' })
      .notNull(),
    name: varchar('name', { length: 256 }).$type<ServiceName>().notNull(),
    amount: integer('amount').$type<Amount>().notNull(),
    cost: real('cost').$type<ServiceCostNumber>().notNull(),
  },
  (orderListing) => {
    return { pk: primaryKey({ columns: [orderListing.orderID, orderListing.serviceID] }) }
  },
  )

  export const orders = pgTable( 'orders', {
  orderID: serial('orderID').$type<OrderID>().primaryKey(), 
  submissionTime: timestamp('submissionTime', { mode: 'string' }).$type<SubmissionTimeOrder>().notNull(),
  orderStatus: orderStatuspgEnum('orderStatus').notNull(), 
})

I want a function that returns:

{
  serviceID: id,
  name: name,
  total: cost*amount //sum of all cost*amount for all orderListings with the serviceID 
}[]

{
  serviceID: id,
  name: name,
  total: cost*amount //sum of all cost*amount for all orderListings with the serviceID 
}[]

I have unit costs and amount of each service in order listing but I want total sold for each service. I am stuck getting the sum of cost*amount for each serviceID. As there can be many orderListings with the same serviceID and since there is a multiplication it is too tricky. I would also like to select by orderStatus and by submissionTime but that I should be able to figure out easily

1 replies

DTDrizzle Team

•Created by AndréLB on 8/17/2024 in #help

Joins with results in arrays

      const fetchedOrderServices = await tx
        .select()
        .from(orderServices)
        .where(eq(orderServices.orderID, order))

      const fetchedOrderLocalServices = await tx
        .select()
        .from(orderLocalServices)
        .where(eq(orderLocalServices.orderID, order))

      const [fetchedOrder] = await tx
        .select()
        .from(orders)
        .where(eq(orders.orderID, order))
        .leftJoin(rentCarBookings, eq(rentCarBookings.orderID, order))

      const fetchedOrderServices = await tx
        .select()
        .from(orderServices)
        .where(eq(orderServices.orderID, order))

      const fetchedOrderLocalServices = await tx
        .select()
        .from(orderLocalServices)
        .where(eq(orderLocalServices.orderID, order))

      const [fetchedOrder] = await tx
        .select()
        .from(orders)
        .where(eq(orders.orderID, order))
        .leftJoin(rentCarBookings, eq(rentCarBookings.orderID, order))

I would like a single select with joins that gives me more or less the same result. Something along the lines of:

{
fetchedOrder: {
  orderID: OrderID,
  customerID: CustomerID
},
localServices: {orderID: OrderID, localServiceID: LocalServiceID}[},
globalServices: {orderID: OrderID, globalServiceID: GlobalServiceID}[]
}

{
fetchedOrder: {
  orderID: OrderID,
  customerID: CustomerID
},
localServices: {orderID: OrderID, localServiceID: LocalServiceID}[},
globalServices: {orderID: OrderID, globalServiceID: GlobalServiceID}[]
}

139 replies

DTDrizzle Team

•Created by AndréLB on 7/17/2024 in #help

Update with array of objects

type StoreSpecialHours = {
    storeID: StoreID;
    day: Day;
    dayOpen: DayOpen;
    dayClose: DayClose;
}

  openingHours: StoreSpecialHours[],
    const storeHours = await db
      .update(storespecialhours)
      .set(openingHours)
      .where(
        and(
          eq(storespecialhours.storeID, openingHours.storeID),
          eq(storespecialhours.day, openingHours.day),
        ),
      )
      .returning({
        storeID: storespecialhours.storeID,
        day: storespecialhours.day,
        dayOpen: storespecialhours.dayOpen,
        dayClose: storespecialhours.dayClose,
      })

type StoreSpecialHours = {
    storeID: StoreID;
    day: Day;
    dayOpen: DayOpen;
    dayClose: DayClose;
}

  openingHours: StoreSpecialHours[],
    const storeHours = await db
      .update(storespecialhours)
      .set(openingHours)
      .where(
        and(
          eq(storespecialhours.storeID, openingHours.storeID),
          eq(storespecialhours.day, openingHours.day),
        ),
      )
      .returning({
        storeID: storespecialhours.storeID,
        day: storespecialhours.day,
        dayOpen: storespecialhours.dayOpen,
        dayClose: storespecialhours.dayClose,
      })

How do I do an update with an array of object where I update multiple fields in the object. The examples I find are just updating one field in the row.

4 replies

DTDrizzle Team

•Created by AndréLB on 4/29/2024 in #help

onConflictDoUpdate error, there is no unique or exclusion constraint matching the ON CONFLICT

export const storespecialhours = pgTable(
  'storespecialhours',
  {
    specialDateID: serial('specialDateID').primaryKey(),
    storeID: integer('storeID')
      .references(() => stores.storeID, { onDelete: 'cascade' })
      .notNull()
      .unique(),
    day: date('day', { mode: 'date' }).notNull().unique(),
    dayOpen: time('dayOpen').notNull().unique(),
    dayClose: time('dayClose').notNull().unique(),
  },
  (storespecialhours) => ({
      unq: unique('dayOpen').on(storespecialhours.storeID, storespecialhours.day),
      unq1: unique('dayClose').on(storespecialhours.storeID, storespecialhours.day),
  }),
)

export const storespecialhours = pgTable(
  'storespecialhours',
  {
    specialDateID: serial('specialDateID').primaryKey(),
    storeID: integer('storeID')
      .references(() => stores.storeID, { onDelete: 'cascade' })
      .notNull()
      .unique(),
    day: date('day', { mode: 'date' }).notNull().unique(),
    dayOpen: time('dayOpen').notNull().unique(),
    dayClose: time('dayClose').notNull().unique(),
  },
  (storespecialhours) => ({
      unq: unique('dayOpen').on(storespecialhours.storeID, storespecialhours.day),
      unq1: unique('dayClose').on(storespecialhours.storeID, storespecialhours.day),
  }),
)

  const [storeHours] = await db
    .insert(storespecialhours)
    .values(openingHours)
    .onConflictDoUpdate({
      target: [storespecialhours.specialDateID, storespecialhours.day],
      set: { dayOpen: openingHours.dayOpen, dayClose: openingHours.dayClose },
    })
    .returning({
      specialDateID: storespecialhours.specialDateID,
      storeID: storespecialhours.storeID,
      day: storespecialhours.day,
      dayOpen: storespecialhours.dayOpen,
      dayClose: storespecialhours.dayClose,
    })

  const [storeHours] = await db
    .insert(storespecialhours)
    .values(openingHours)
    .onConflictDoUpdate({
      target: [storespecialhours.specialDateID, storespecialhours.day],
      set: { dayOpen: openingHours.dayOpen, dayClose: openingHours.dayClose },
    })
    .returning({
      specialDateID: storespecialhours.specialDateID,
      storeID: storespecialhours.storeID,
      day: storespecialhours.day,
      dayOpen: storespecialhours.dayOpen,
      dayClose: storespecialhours.dayClose,
    })

My goal is to insert new data into the storespecialhours table. If there is a conflict with specialDateID or (storeID and Day) then do an update. Lots of rows can have conflicting dayOpen, dayClose or day attributes that conflict. How do I fix it? Currently I am getting: there is no unique or exclusion constraint matching the ON CONFLICT specification

3 replies

DTDrizzle Team

•Created by AndréLB on 4/7/2024 in #help

Best way to use select in a function that is returning a specific type

export type UserID = { userID: number }
type UserPassword = { userPassword: string }
type UserFirstName = { userFirstName: string }
type UserLastName = { userLastName: string }
type UserEmail = { userEmail: string }


type IsSuperAdmin = {
  isSuperAdmin: boolean | null
}

export type VerifyUser = UserID &
  UserFirstName &
  UserLastName &
  UserEmail &
  UserPassword &
  IsSuperAdmin & { role: RoleName & RoleID }


export async function verifyUser(email: UserEmail): Promise<VerifyUser | undefined> {
  const results: VerifyUser[] | undefined = await db
    .select({
      userID: users.userID,
      userFirstName: users.firstName,
      userLastName: users.lastName,
      userEmail: users.email,
      userPassword: users.password,
      isSuperAdmin: users.isSuperAdmin,
      role: {
        roleID: roles.roleID,
        roleName: roles.roleName,
      },
    })
    .from(users)
    .innerJoin(roles, eq(users.roleID, roles.roleID))
    .where(and(eq(users.email, email.userEmail)))
  return results[0] ? results[0] : undefined
}

export type UserID = { userID: number }
type UserPassword = { userPassword: string }
type UserFirstName = { userFirstName: string }
type UserLastName = { userLastName: string }
type UserEmail = { userEmail: string }


type IsSuperAdmin = {
  isSuperAdmin: boolean | null
}

export type VerifyUser = UserID &
  UserFirstName &
  UserLastName &
  UserEmail &
  UserPassword &
  IsSuperAdmin & { role: RoleName & RoleID }


export async function verifyUser(email: UserEmail): Promise<VerifyUser | undefined> {
  const results: VerifyUser[] | undefined = await db
    .select({
      userID: users.userID,
      userFirstName: users.firstName,
      userLastName: users.lastName,
      userEmail: users.email,
      userPassword: users.password,
      isSuperAdmin: users.isSuperAdmin,
      role: {
        roleID: roles.roleID,
        roleName: roles.roleName,
      },
    })
    .from(users)
    .innerJoin(roles, eq(users.roleID, roles.roleID))
    .where(and(eq(users.email, email.userEmail)))
  return results[0] ? results[0] : undefined
}

I don't understand why this code works. It doesn't return something of type VerifyUser yet the type checker doesn't complain. I am getting:

 {
   userID: 1,
 }

 {
   userID: 1,
 }

Instead of:

 {
   userID: {userID: 1},
 }

 {
   userID: {userID: 1},
 }

2 replies

Gaming

Programming