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
});
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));
1 Reply
However this returns the same 2 subordinates for all results. However this SQL query, which seems to me to be the equivalent does return the correct result.
Any clue what I am doing wrong?
SELECT
w.worker_id AS "workerId",
w.name,
w.people_leader_worker_id AS "peopleLeaderWorkerId",
(
SELECT ARRAY_AGG(sub.worker_id)
FROM workers sub
WHERE sub.people_leader_worker_id = w.worker_id
) AS "subordinateWorkerIds",
(
SELECT ARRAY_AGG(DISTINCT sub.company_id)
FROM workers sub
WHERE sub.people_leader_worker_id = w.worker_id
) AS "subordinateCompanyCodes"
FROM
workers w
WHERE
w.people_leader_worker_id = 51963;
SELECT
w.worker_id AS "workerId",
w.name,
w.people_leader_worker_id AS "peopleLeaderWorkerId",
(
SELECT ARRAY_AGG(sub.worker_id)
FROM workers sub
WHERE sub.people_leader_worker_id = w.worker_id
) AS "subordinateWorkerIds",
(
SELECT ARRAY_AGG(DISTINCT sub.company_id)
FROM workers sub
WHERE sub.people_leader_worker_id = w.worker_id
) AS "subordinateCompanyCodes"
FROM
workers w
WHERE
w.people_leader_worker_id = 51963;