public
Last active

Recursive T-SQL CTE to find root of arbitrarily deep hierarchy

  • Download Gist
default_language.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
-- schema
--DROP TABLE dbo.common_Text;
--DROP TABLE dbo.common_LanguageType;
--DROP TABLE dbo.common_Comment;
 
CREATE TABLE dbo.common_Comment (
CommentId BIGINT NOT NULL PRIMARY KEY
);
 
CREATE TABLE dbo.common_LanguageType (
LanguageId INT NOT NULL PRIMARY KEY
, Name NVARCHAR(100) NOT NULL
, Code NVARCHAR(2) NOT NULL
, DefaultId INT NOT NULL
, FOREIGN KEY (DefaultId) REFERENCES dbo.common_LanguageType(LanguageId)
);
 
CREATE TABLE dbo.common_Text (
TextId BIGINT NOT NULL PRIMARY KEY
, CommentId BIGINT NOT NULL
, Text NVARCHAR(500) NOT NULL
, LanguageId INT NOT NULL
, CommentTypeId INT NOT NULL
, FOREIGN KEY (LanguageId) REFERENCES dbo.common_LanguageType(LanguageId)
, FOREIGN KEY (CommentId) REFERENCES dbo.common_Comment(CommentId)
);
 
-- data
INSERT [dbo].[common_Comment] ([CommentId]) VALUES (1)
 
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (1, N'english', N'en', 1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (2, N'russian', N'ru', 1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (3, N'ukrainian', N'ua', 2)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (4, N'arabic', N'ar', 1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (5, N'farsi', N'fr', 4)
 
INSERT [dbo].[common_Text] ([TextId], [CommentId], [Text], [LanguageId], [CommentTypeId]) VALUES (1, 1, N'english text', 1, 1)
INSERT [dbo].[common_Text] ([TextId], [CommentId], [Text], [LanguageId], [CommentTypeId]) VALUES (2, 1, N'wlik ehhh', 4, 1)
 
-- query
DECLARE @CommentId BIGINT = 1;
DECLARE @LanguageCode NVARCHAR(2) = 'ar';
 
WITH languages AS (
-- base case: language has required comment
SELECT
lt.LanguageId AS RootLanguageId
, lt.LanguageId
, lt.Code
, lt.DefaultId
, 0 AS Level
FROM
dbo.common_LanguageType lt
--WHERE
-- lt.LanguageId = lt.DefaultId
WHERE
EXISTS (
SELECT *
FROM dbo.common_Text t
WHERE
t.CommentId = @CommentId
AND t.LanguageId = lt.LanguageId
)
UNION ALL
-- recursive case: language is not its own default and
-- does not have the required comment
SELECT
l_default.RootLanguageId
, l.LanguageId
, l.Code
, l.DefaultId
, l_default.Level + 1 AS Level
FROM
dbo.common_LanguageType l
INNER JOIN languages l_default
ON l.DefaultId = l_default.LanguageId
WHERE
l.LanguageId <> l.DefaultId
AND NOT EXISTS (
SELECT *
FROM dbo.common_Text t
WHERE
t.CommentId = @CommentId
AND t.LanguageId = l.LanguageId
)
)
--SELECT *
--FROM languages;
SELECT t.Text
FROM
languages l
INNER JOIN dbo.common_Text t
ON l.RootLanguageId = t.LanguageId
WHERE
l.Code = @LanguageCode
;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.