Support WITH RECURSIVE

[dankochetov]

[neil-ptr]

I can look into this!

[ngregrichardson]

@neilZon Hey, have you had any luck with this so far? Interested in seeing this come to fruition.

[neil-ptr]

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

[fireayehu]

@neilZon and @dankochetov any updates ?

[ngregrichardson]

I believe @Angelelz is working with the team on a UNION ALL implementation that would be a step towards supporting this.

[fireayehu]

I believe @Angelelz is working with the team on a UNION ALL implementation that would be a step towards supporting this.

@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.

[neil-ptr]

Just an update, gonna start working on this now. This wil be blocked by https://github.com/drizzle-team/drizzle-orm/pull/1218 I think but I'll try to working alongside it as this draft pr progresses

[neil-ptr]

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 sql operator. I was thinking about revising it to have some function that creates an alias for recursive queries to allow the recursive step to have access to the columns of the table. However this alias will not have access to columns added within the recursive statement such as the level column. Thoughts @dankochetov?

Current Draft

const 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)

Revision

const 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)