Subquery within same table not working as expected
I have this table that list employees:
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:
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));