-
-
Notifications
You must be signed in to change notification settings - Fork 569
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support WITH RECURSIVE #209
Comments
I can look into this! |
@neilZon Hey, have you had any luck with this so far? Interested in seeing this come to fruition. |
I started getting some progress but then I got really busy in the past couple of weeks. I should be able to start working on it again this weekend |
@neilZon and @dankochetov any updates ? |
I believe @Angelelz is working with the team on a |
@ngregrichardson Thank You, I was creating a table for Hierarchical data (a tree structure) with adjacency list but now I started implementing it using closure table so I think I can get back to this in the future. |
Just an update, gonna start working on this now. This wil be blocked by #1218 I think but I'll try to working alongside it as this draft pr progresses |
With the changes from @Angelelz I was able to create a recursive query by the following. The issue is within the recursive step, the only way to refer to the outer name of the CTE is by the Current Draftconst employeeHierarchy = db.$withRecursive('employeeHierarchy').as(
// base
db.select({
id: employees.id,
name: employees.name,
position: employees.position,
manager_id: employees.manager_id,
level: sql<string>`0`.as("level")
})
.from(employees)
.where(isNull(employees.manager_id))
.union(
// recursive
db.select({
id: employees.id,
name: employees.name,
position: employees.position,
manager_id: employees.manager_id,
level: sql<string>`employeeHierarchy.level + 1`.as('level')
})
.from(employees)
.innerJoin(sql<string>`employeeHierarchy`, eq(sql<string>`employeeHierarchy.id`, employees.manager_id))
)
)
const query = db.with(employeeHierarchy).select().from(employeeHierarchy) Revisionconst employeeHierarchy = recursiveAlias('employeeHierarchy', employees)
const recursiveCte = db.$withRecursive(employeeHierarchy).as(
db.select({
id: employees.id,
name: employees.name,
position: employees.position,
manager_id: employees.manager_id,
level: sql<string>`0`.as("level")
})
.from(employees)
.where(isNull(employees.manager_id))
.union(
db.select({
id: employees.id,
name: employees.name,
position: employees.position,
manager_id: employees.manager_id,
level: sql<string>`${employeeHierarchy}.level + 1`.as('level')
})
.from(employees)
.innerJoin(employeeHierarchy, eq(employeeHierarchy.id, employees.manager_id))
)
)
const query = db.with(recursiveCte).select().from(recursiveCte) |
Hi, any updates? |
No description provided.
The text was updated successfully, but these errors were encountered: