Drizzle Team•13mo ago

ERROR: operator does not exist: uuid = character varying

export const hubPost = pgTable("HubPost", {
  id: uuid("Id").defaultRandom().primaryKey(),
  text: text("Text").notNull(),
  title: varchar("Title", { length: 256 }).notNull(),
  hub: varchar("Hub", { length: 128 }).notNull(),
  authorId: uuid("AuthorId").notNull(),
});

export const hub = pgTable("Hub", {
  id: uuid("Id").defaultRandom().primaryKey(),
  name: varchar("Name", { length: 128 }).notNull(),
  createdAt: timestamp("CreatedAt").defaultNow(),
  creatorId: varchar("CreatorId").notNull(),
});

export const hubRelationsToHubPost = relations(hub, ({ many }) => ({
  hubPosts: many(hubPost),
}));

export const hubPostRelationsToHub = relations(hubPost, ({ one }) => ({
  hub: one(hub, {
    fields: [hubPost.hub],
    references: [hub.id],
  }),
}));

export const hubPost = pgTable("HubPost", {
  id: uuid("Id").defaultRandom().primaryKey(),
  text: text("Text").notNull(),
  title: varchar("Title", { length: 256 }).notNull(),
  hub: varchar("Hub", { length: 128 }).notNull(),
  authorId: uuid("AuthorId").notNull(),
});

export const hub = pgTable("Hub", {
  id: uuid("Id").defaultRandom().primaryKey(),
  name: varchar("Name", { length: 128 }).notNull(),
  createdAt: timestamp("CreatedAt").defaultNow(),
  creatorId: varchar("CreatorId").notNull(),
});

export const hubRelationsToHubPost = relations(hub, ({ many }) => ({
  hubPosts: many(hubPost),
}));

export const hubPostRelationsToHub = relations(hubPost, ({ one }) => ({
  hub: one(hub, {
    fields: [hubPost.hub],
    references: [hub.id],
  }),
}));

Here is my relation, and when I tried to run a relational query, it shows this error

14 Replies

tomeverson•13mo ago

ohhh nvm :)

GeorgeCirevo•10mo ago

@tomeverson What was your fix?

tomeverson•10mo ago

i forgor :3

Logan•10mo ago

having the same error when using db.query

shreddish•5mo ago

anyone know what was going on with this? all the sudden starting to see this no clue what the error is referencing

Sillvva•5mo ago

In postgres, the types are strict. They either need to match or be type cast. In the above case, hubPost.hub is varchar and hub.id is uuid. You can cast the uuid to varchar in a join if you're not using the rqb syntax.

shreddish•5mo ago

thank you! - now gotta find where i have the mismatch any idea if theres a way to see which column has the mismatch in the error? i have some fairly complex relations so hard to track it down

Sillvva•5mo ago

Turn logger on. I believe it would be the last query logged before the error occurred

Sillvva•5mo ago

https://orm.drizzle.team/docs/goodies#logging

Drizzle ORM - Goodies

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

shreddish•5mo ago

could it possibly be that in my one to many relationship - the many side has a uuid field but that field can potentially be null - (i.e. if an outbound load has not been assigned to a job support equipment) (sending code in follow up message)

export const outboundLoads = pgTable(
  'outbound_loads',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    loadNumber: integer('load_number').notNull(),

    organizationId: uuid('organization_id')
      .references(() => organizations.id)
      .notNull(),

    jobId: uuid('job_id')
      .references(() => jobs.id)
      .notNull(),

    trailerClassId: uuid('trailer_class_id')
      .references(() => vehicleTypeClasses.id)
      .notNull(),
    trailerId: uuid('trailer_id').references(() => vehicles.id),

    loadStatus: outboundLoadStatusEnum('load_status').notNull().default('Unassigned'),

    name: varchar('name'),
    description: varchar('description'),

    createdOn: timestamp('created_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),

    updatedOn: timestamp('updated_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),
  },
  (t) => ({
    jobIdIdx: index().on(t.jobId),
    statusIdx: index().on(t.loadStatus),
  }),
);

export const outboundLoadRelations = relations(outboundLoads, ({ one, many }) => ({
  destinationAddress: one(addresses, {
    fields: [outboundLoads.id],
    references: [addresses.belongsTo],
  }),
  job: one(jobs, {
    fields: [outboundLoads.jobId],
    references: [jobs.id],
  }),

  trailerClass: one(vehicleTypeClasses, {
    fields: [outboundLoads.trailerClassId],
    references: [vehicleTypeClasses.id],
  }),

  trailer: one(vehicles, {
    fields: [outboundLoads.trailerId],
    references: [vehicles.id],
  }),

  supportEquipment: many(jobSupportEquipment),
  supportVehicles: many(jobSupportVehicles),

  lotPieces: many(lotPieces),
}));

export const outboundLoads = pgTable(
  'outbound_loads',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    loadNumber: integer('load_number').notNull(),

    organizationId: uuid('organization_id')
      .references(() => organizations.id)
      .notNull(),

    jobId: uuid('job_id')
      .references(() => jobs.id)
      .notNull(),

    trailerClassId: uuid('trailer_class_id')
      .references(() => vehicleTypeClasses.id)
      .notNull(),
    trailerId: uuid('trailer_id').references(() => vehicles.id),

    loadStatus: outboundLoadStatusEnum('load_status').notNull().default('Unassigned'),

    name: varchar('name'),
    description: varchar('description'),

    createdOn: timestamp('created_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),

    updatedOn: timestamp('updated_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),
  },
  (t) => ({
    jobIdIdx: index().on(t.jobId),
    statusIdx: index().on(t.loadStatus),
  }),
);

export const outboundLoadRelations = relations(outboundLoads, ({ one, many }) => ({
  destinationAddress: one(addresses, {
    fields: [outboundLoads.id],
    references: [addresses.belongsTo],
  }),
  job: one(jobs, {
    fields: [outboundLoads.jobId],
    references: [jobs.id],
  }),

  trailerClass: one(vehicleTypeClasses, {
    fields: [outboundLoads.trailerClassId],
    references: [vehicleTypeClasses.id],
  }),

  trailer: one(vehicles, {
    fields: [outboundLoads.trailerId],
    references: [vehicles.id],
  }),

  supportEquipment: many(jobSupportEquipment),
  supportVehicles: many(jobSupportVehicles),

  lotPieces: many(lotPieces),
}));

export const jobSupportEquipment = pgTable(
  'job_support_equipment',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    jobId: uuid('job_id')
      .references(() => jobs.id)
      .notNull(),
    equipmentId: uuid('equipment_id')
      .references(() => equipment.id)
      .notNull(),

    outboundLoadId: uuid('outbound_load_id').references(() => outboundLoads.id),
    loadStatus: outboundLoadStatusEnum('load_status').notNull().default('Unassigned'),

    createdOn: timestamp('created_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),

    updatedOn: timestamp('updated_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),
  },
  (t) => ({
    jobIdIdx: index().on(t.jobId),
    uniqueEquipmentId: uniqueIndex().on(t.jobId, t.equipmentId),
  }),
);

export const jobSupportEquipmentRelations = relations(jobSupportEquipment, ({ one, many }) => ({
  job: one(jobs, {
    fields: [jobSupportEquipment.jobId],
    references: [jobs.id],
  }),
  equipment: one(equipment, {
    fields: [jobSupportEquipment.equipmentId],
    references: [equipment.id],
  }),
  outboundLoad: one(outboundLoads, {
    fields: [jobSupportEquipment.outboundLoadId],
    references: [outboundLoads.id],
  }),
}));

export const jobSupportEquipment = pgTable(
  'job_support_equipment',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    jobId: uuid('job_id')
      .references(() => jobs.id)
      .notNull(),
    equipmentId: uuid('equipment_id')
      .references(() => equipment.id)
      .notNull(),

    outboundLoadId: uuid('outbound_load_id').references(() => outboundLoads.id),
    loadStatus: outboundLoadStatusEnum('load_status').notNull().default('Unassigned'),

    createdOn: timestamp('created_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),

    updatedOn: timestamp('updated_on', {
      mode: 'date',
      withTimezone: true,
    }).defaultNow(),
  },
  (t) => ({
    jobIdIdx: index().on(t.jobId),
    uniqueEquipmentId: uniqueIndex().on(t.jobId, t.equipmentId),
  }),
);

export const jobSupportEquipmentRelations = relations(jobSupportEquipment, ({ one, many }) => ({
  job: one(jobs, {
    fields: [jobSupportEquipment.jobId],
    references: [jobs.id],
  }),
  equipment: one(equipment, {
    fields: [jobSupportEquipment.equipmentId],
    references: [equipment.id],
  }),
  outboundLoad: one(outboundLoads, {
    fields: [jobSupportEquipment.outboundLoadId],
    references: [outboundLoads.id],
  }),
}));

Sillvva•5mo ago

I don't think the field being nullable would cause this specific error. This is about a type incompatibility and the query system being unable to successfully join the two tables.

shreddish•5mo ago

yeah its weird because i have an "identical" relation on the job model for supportEquipment and it doesn't throw the type error i've been playing around and now I'm getting this error (granted i think i've abused the QB model and have some extremely complex queries to build) so maybe i need to rethink how im utilizing it but not sure if theres an easier route i can take in the meantime

 detail: 'There is a column named "vehicle_class_id" in table "customers_projects_jobs_outboundLoads_supportVehicles_vehicle_c", but it cannot be referenced from this part of the query.',
  hint: 'To reference that column, you must mark this subquery with LATERAL.',

 detail: 'There is a column named "vehicle_class_id" in table "customers_projects_jobs_outboundLoads_supportVehicles_vehicle_c", but it cannot be referenced from this part of the query.',
  hint: 'To reference that column, you must mark this subquery with LATERAL.',

Sillvva•5mo ago

Hmm. I'm not sure. I know about lateral joins, but drizzle does not appear to have any documentation for using them.

shreddish•5mo ago

yeah additionally this is all done through query builder which AFAIK you aren't specifying the joins its doing it for you - most likely meaning my nested relations are causing issues for it

Gaming

Programming

ERROR: operator does not exist: uuid = character varying