by mgordon
12. November 2007 04:48
Some time ago, I was working on a project that needed to work with users as a hierarchy. The hierarchy was similar to what you would see in typical organizational chart and the users were organized by who they worked for and who worked for them. As part of one of the requirements for the system, I needed to be able to retrieve from the database any particular user and all users who were placed beneath them in the chart, from their position in it all the way to the bottom.
To store the relationship between all the users, I created a self referencing table that contained user_id and managing_user_id columns. The idea was that the record would contain the id of a user and also the id of the user over them. If the user was at the top of the hierarchy, the managing_user_id column would be null.
Obviously, getting the data out of the table in the way I needed it could best be done with recursion. I needed to get the user, all records that pointed to that user as the managing user...all the records that pointed to each of those records and so on. I was wondering how to do this without making multiple calls to the database, however, because I could see the number of calls being astronomical for users near the top of the hierarchy.
Then I ran across a technique whereby I could use a CTE in a recursive way. The code looked something like this.
WITH userHierarchyTiers (user_id, managing_user_id, user_hierarchy_id) AS
(
SELECT uh.user_id, managing_user_id, user_hierarchy_id
FROM user_hierarchy uh, users u
WHERE uh.user_id = @UserId
AND u.user_id = uh.user_id
UNION ALL
SELECT uh.user_id, uh.managing_user_id, uh.user_hierarchy_id
FROM user_hierarchy uh
INNER JOIN userHierarchyTiers uht ON uh.managing_user_id = uht.user_id
)
SELECT user_id, managing_user_id from userHierarchyTiers
The key to this technique is in the fact that the second query in the CTE references the entire CTE in its FROM clause. The net effect is that the CTE is executed and for each row in the result, the CTE is executed again and so on until there are no rows in the result set.
Subscribe