Drizzle TeamDT
Drizzle Team•8mo ago
coder2000

Issues with query

I am having an interesting issue with a query of mine. To start my schema looks like this:

// Caregiver-specific information
export const caregivers = pgTable('caregivers', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  addressId: text('address_id')
    .notNull()
    .references(() => addresses.id, { onDelete: 'cascade' }),
  bio: text('bio'),
  experience: text('experience'),
  hourlyRate: integer('hourly_rate'),
  isAvailable: boolean('is_available').notNull().default(true),
  availability: text('availability').notNull(), // JSON string of availability schedule
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Certifications table - simplified
export const certifications = pgTable('certifications', {
  id: text('id')
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: text('name').notNull().unique(),
  description: text('description'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Caregiver-Certification many-to-many relationship - with issuing organization
export const caregiverCertifications = pgTable(
  'caregiver_certifications',
  {
    caregiverId: text('caregiver_id')
      .notNull()
      .references(() => caregivers.id, { onDelete: 'cascade' }),
    certificationId: text('certification_id')
      .notNull()
      .references(() => certifications.id, { onDelete: 'cascade' }),
    issuingOrganization: text('issuing_organization'),
    dateObtained: date('date_obtained', { mode: 'string' }),
    expirationDate: date('expiration_date', { mode: 'string' }),
    notes: text('notes'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.caregiverId, table.certificationId] }),
  ]
);

// Define relations for certifications
export const certificationsRelations = relations(
  certifications,
  ({ many }) => ({
    caregivers: many(caregiverCertifications),
  })
);

export const caregiverCertificationsRelations = relations(
  caregiverCertifications,
  ({ one }) => ({
    caregiver: one(caregivers, {
      fields: [caregiverCertifications.caregiverId],
      references: [caregivers.id],
    }),
    certification: one(certifications, {
      fields: [caregiverCertifications.certificationId],
      references: [certifications.id],
    }),
  })
);

export const caregiversRelations = relations(caregivers, ({ one, many }) => ({
  user: one(users, {
    fields: [caregivers.userId],
    references: [users.id],
  }),
  address: one(addresses, {
    fields: [caregivers.addressId],
    references: [addresses.id],
  }),
  bookings: many(bookings),
  certifications: many(caregiverCertifications),
}));


When I try and run this query I get an error saying "RangeError: Offset is outside the bounds of the DataView":

 return await db.query.caregivers.findFirst({
   where: eq(caregivers.id, input.id),
   with: {
     address: true,
     user: true,
     certifications: {
       with: {
         certification: true,
       },
     },
   },
 });


I had to break it up into separate queries just to get it to work. I've tried with a select query as well but can't seem to get the joins right. Any tips?
Was this page helpful?