MySql Recursive Common Table Expressions (CTE)

Working with a thorny denormalized table that contains id references to itself in a Parent column that indicates a tree hierarchical structure and had to bring out the recursvie Common Table Expressions (CTE)

Simplified table example

create table TaskLookup
(
    Id                      int unsigned auto_increment primary key,
    ParentId                int unsigned,
    Body                    mediumtext   not null,
    Depth					int,
)

Every task above will have a unique id, but not every task can have a parent. The tasks without a parent are considered to be children of the overall root node of the tree. To be brief circular graph representations of the data are not considered. It is checked in another part of the code that cyclical routes are not permitted as in Id -> ParentId -> Id or a task can not have any level of parent task ids that loop back to refencing itself. Now on to the simpler problem need to find the parent chain of any particular task.

C# Lookup

Here recursively joining the TaskLookup table with itself so that we can get all possible combinations and then use a group by clause to keep the results in the path lookups, and finally use a @filterId to do this per task id lookup and this is lookup the parent paths for a task.

public List<TaskLookup> SelectTaskLookup(int? filterId = null)
{
	const string sql = @"WITH RECURSIVE cte as (
			SELECT Id, Id NextId, ParentId
			FROM TaskLookup
			UNION ALL
			SELECT cte.Id, s.Id, s.ParentId
			FROM TaskLookup s
					JOIN cte ON cte.ParentId = s.Id)
		SELECT Id, NextId TaskLookupId
		FROM cte
		where Id != NextId
		  AND Id = COALESCE(@filterId, Id)
		GROUP BY Id";

	var results = DapperForList<TaskLookup>(sql, new { filterId });
	return results == null ? new List<TaskLookup>() : results.ToList();
}

Bonus recursive CTE

This is a single statement to update all possible paths depth (parents from any task). As it is done in a recursive CTE it will update all valid paths in a single statement lightning fast.

const string sql = @"update TaskLookup
	inner join
	(
		WITH RECURSIVE
		cte as
		(
			SELECT Id, Id NextId, ParentId
				FROM TaskLookup
			UNION ALL
			SELECT cte.Id, s.Id, s.ParentId
				FROM TaskLookup s
				JOIN cte ON cte.ParentId = s.Id
		)
		SELECT Id, COUNT(*) Depth
		FROM cte
		where Id != NextId
		group by Id
	) cc
	on cc.Id = TaskLookup.Id
	set TaskLookup.Depth = cc.Depth;";