はじめに

プリザンターでサイトの一覧を取得したいケースが出てきたので、データベースに格納されているサイト情報からサイトの一覧を取得するクエリを組み立ててみました。

クエリを見てみる

WITH CTE AS ( 
    SELECT
        [SiteId],
        [ReferenceType], 
        [Title], 
        [ParentId], 
        TRY_CAST([Title] AS NVARCHAR)                         AS [FullName] 
    FROM
        [Sites] 
    WHERE
        [ParentId] = 0 
    UNION ALL 
    SELECT
        C.[SiteId], 
        C.[ReferenceType],
        C.[Title], 
        C.[ParentId], 
        TRY_CAST((P.[Title] + ' > ' + C.[Title]) AS NVARCHAR) AS [FullName] 
    FROM
        [Sites] C 
        INNER JOIN CTE P 
            ON P.[SiteId] = C.[ParentId]
) 
SELECT
    [SiteId], 
    [ReferenceType],
    [FullName], 
    [ParentId] 
FROM
    CTE 
ORDER BY
    [ParentId], 
    [SiteId]

共通テーブルを使用してタイトルを親>子で連結しながら取り出しています。
実際にこれを実行してみた結果はこのようになります。

SiteId	ReferenceType	FullName	ParentId
3457307	Sites	[Sandbox]社内用	0
3526345	Results	[Sandbox]社内用 > 記録テーブル(子テーブル)	3457307
3526346	Issues	[Sandbox]社内用 > 期限付きテーブル(親テーブル)	3457307
3578784	Results	[Sandbox]社内用 > ユーザ複数選択テスト	3457307

親子関係を維持したままキレイに値が取れています。

ちょっと応用

この情報、APIで取れると便利なので拡張SQLを使用してAPI化してみます。

GetSiteList.json

{
    "Name": "GetSiteList",
    "Api": true,
    "CommandText": "-- Write an arbitrary SQL statement."
}

GetSiteList.json.sql

WITH CTE AS ( 
    SELECT
        [SiteId],
        [ReferenceType], 
        [Title], 
        [ParentId], 
        TRY_CAST([Title] AS NVARCHAR)                         AS [FullName] 
    FROM
        [Sites] 
    WHERE
        [ParentId] = 0 
    UNION ALL 
    SELECT
        C.[SiteId], 
        C.[ReferenceType],
        C.[Title], 
        C.[ParentId], 
        TRY_CAST((P.[Title] + ' > ' + C.[Title]) AS NVARCHAR) AS [FullName] 
    FROM
        [Sites] C 
        INNER JOIN CTE P 
            ON P.[SiteId] = C.[ParentId]
) 
SELECT
    [SiteId], 
    [ReferenceType],
    [FullName], 
    [ParentId] 
FROM
    CTE 
ORDER BY
    [ParentId], 
    [SiteId]

まとめ

今回はサイトの一覧を階層情報付きで取得する方法を紹介しました。パンくずリストやサイトツリーなどの階層を持ったメニューの作成に便利な情報取得の方法なので、是非活用してみてください。