Baron Von Enablestein Posts

Baron Von Enablestein

•Created by Baron Von Enablestein on 2/27/2025 in #help

Subquery within same table not working as expected

I have this table that list employees:

export const workersTable = pgTable('workers', {
  workerId: integer('worker_id').notNull().primaryKey(),
  name: text('name').notNull(),
  peopleLeaderWorkerId: integer('people_leader_worker_id').notNull(),
  username: text('username').notNull(),
  active: boolean('active').notNull().default(true),
  firstActive: timestamp('first_active').notNull().defaultNow(),
  lastModified: timestamp('last_modified').notNull().defaultNow(), // Last time any fields except the lastModified field was updated
});

export const workersTable = pgTable('workers', {
  workerId: integer('worker_id').notNull().primaryKey(),
  name: text('name').notNull(),
  peopleLeaderWorkerId: integer('people_leader_worker_id').notNull(),
  username: text('username').notNull(),
  active: boolean('active').notNull().default(true),
  firstActive: timestamp('first_active').notNull().defaultNow(),
  lastModified: timestamp('last_modified').notNull().defaultNow(), // Last time any fields except the lastModified field was updated
});

My query should result all personnel that has a particular employee as their leader and list for each of the found employees who they have as subordinates:

    const workerResults = await db
      .select({
        workerId: workersTable.workerId,
        name: workersTable.name,
        peopleLeaderWorkerId: workersTable.peopleLeaderWorkerId,
        username: workersTable.username,
        subordinateWorkerIds: sql<number[]>`(
          SELECT ARRAY_AGG(${workersTable.workerId})
          FROM ${workersTable} sub
          WHERE sub.people_leader_worker_id = ${workersTable.workerId}
        )`,
        subordinateCompanyCodes: sql<string[]>`(
          SELECT ARRAY_AGG(DISTINCT ${workersTable.companyId})
          FROM ${workersTable} sub
          WHERE sub.people_leader_worker_id = ${workersTable.workerId}
        )`,
      })
      .from(workersTable)
      .where(eq(workersTable.peopleLeaderWorkerId, peopleLeaderWorkerId));

    const workerResults = await db
      .select({
        workerId: workersTable.workerId,
        name: workersTable.name,
        peopleLeaderWorkerId: workersTable.peopleLeaderWorkerId,
        username: workersTable.username,
        subordinateWorkerIds: sql<number[]>`(
          SELECT ARRAY_AGG(${workersTable.workerId})
          FROM ${workersTable} sub
          WHERE sub.people_leader_worker_id = ${workersTable.workerId}
        )`,
        subordinateCompanyCodes: sql<string[]>`(
          SELECT ARRAY_AGG(DISTINCT ${workersTable.companyId})
          FROM ${workersTable} sub
          WHERE sub.people_leader_worker_id = ${workersTable.workerId}
        )`,
      })
      .from(workersTable)
      .where(eq(workersTable.peopleLeaderWorkerId, peopleLeaderWorkerId));

2 replies

DTDrizzle Team

•Created by Baron Von Enablestein on 8/23/2024 in #help

Database connections do not close using RDS Serverless v2 and Data API

1 replies

DTDrizzle Team

•Created by Baron Von Enablestein on 4/30/2024 in #help

pgEnum conversion not working as expected

I have the following pgEnum defined:

export const ApprovalRequestTypePgEnum = pgEnum('approval_request_type', ['CREDIT_NOTE']);

export const ApprovalRequestTypePgEnum = pgEnum('approval_request_type', ['CREDIT_NOTE']);

(Yes, there'll be more types later). My schema is:

export const approvalRequestsTable = pgTable('approval_requests', {
  id: uuid('id').default(sql`gen_random_uuid()`).primaryKey().notNull(),
  requestorId: integer('requestor_id').notNull().references(() => employeesTable.workerId),
  type: ApprovalRequestTypePgEnum('type').notNull(),
  approvalObjectId: uuid('approval_object_id').notNull(),
  status: ApprovalStatusPgEnum('status').notNull(),
  totalValue: integer('total_value').notNull(),
  currencyCode: text('currency_code').notNull(),
});

export const approvalRequestsTable = pgTable('approval_requests', {
  id: uuid('id').default(sql`gen_random_uuid()`).primaryKey().notNull(),
  requestorId: integer('requestor_id').notNull().references(() => employeesTable.workerId),
  type: ApprovalRequestTypePgEnum('type').notNull(),
  approvalObjectId: uuid('approval_object_id').notNull(),
  status: ApprovalStatusPgEnum('status').notNull(),
  totalValue: integer('total_value').notNull(),
  currencyCode: text('currency_code').notNull(),
});

However when I do:

export const insertApprovalRequest = async (approvalRequest: ApprovalRequestInput): Promise<ApprovalRequest> => {
  logger.info('Inserting approval request', { approvalRequest });
  const insertedApprovalRequest = await db
    .insert(approvalRequestsTable).values({
      ...approvalRequest,
      type: ApprovalRequestType.CREDIT_NOTE
    }).returning().then((res) => res[0]);
  logger.info('Inserted approval request', { insertedApprovalRequest });

  return getApprovalRequest(insertedApprovalRequest.id) as Promise<ApprovalRequest>;
};

export const insertApprovalRequest = async (approvalRequest: ApprovalRequestInput): Promise<ApprovalRequest> => {
  logger.info('Inserting approval request', { approvalRequest });
  const insertedApprovalRequest = await db
    .insert(approvalRequestsTable).values({
      ...approvalRequest,
      type: ApprovalRequestType.CREDIT_NOTE
    }).returning().then((res) => res[0]);
  logger.info('Inserted approval request', { insertedApprovalRequest });

  return getApprovalRequest(insertedApprovalRequest.id) as Promise<ApprovalRequest>;
};

I get the error

ERROR: column \"type\" is of type approval_request_type but expression is of type text; Hint: You will need to rewrite or cast the expression.

How can I solve this? My ApprovalRequestType enum is also mapped to 'CREDIT_NOTE'

2 replies

DTDrizzle Team

•Created by Baron Von Enablestein on 3/20/2024 in #help

Drizzle Studio + RDS Data API

Is there any estimate on when RDS Data API support for Drizzle Studio might land?

2 replies

Gaming

Programming