Hierarchical SQL Query

Question

I'm working on a simple CMS system for which I have a database with the following tables:

Items
Contents
Langs

The Items table has the following structure:

itemId
name (for semantic reasons)
type
parent (foreign key to itemId)

An item can be either a document or a section type. A section is a piece of content on a document which is linked to it via the parent collumn. But also a document can have a parent which makes it a subpage.

Now I get stuck on making a query to fetch all the items from the database hierarchically. So something like this:

documentId => name
              metaDescription => language => meta
              sections => sectionId => language => title
                                                   content
                                                   uri
              subPages => documentId => name
                                        metaDescription
                                        sections => etc...

Just to clarify, a website can have multiple languages which are in the Langs table and every language is linked to a piece of content in the Contents table which is also linked to an item in the Items table. The metaDescription is the linked content collumn linked to a item of type document.

Is there a way to do this with one query? This was my first attempt, but it doesnt work for subPages:

    SELECT
        documents.itemId        AS id,
        documents.name          AS documentName,
        documents.lastModified  AS lastModified,
        meta.content            AS metaDescription,
        meta.uri                AS documentUri,
        sections.itemId         AS sectionId,
        sections.name           AS sectionName,
        sections.lastModified   AS sectionLastModified,
        contents.name           AS sectionTitle,
        contents.content        AS sectionContent,
        contents.uri            AS contentUri,
        contents.lastModified   AS contentLastModified,
        langs.name              AS contentLang
    FROM 
        SITENAME_kw_items AS documents
            INNER JOIN
        SITENAME_kw_contents AS meta
        ON documents.itemId = meta.itemId
            INNER JOIN
        SITENAME_kw_items AS sections
        ON sections.parent = documents.itemId
            INNER JOIN
        SITENAME_kw_contents AS contents
        ON sections.itemId = contents.itemId
            INNER JOIN
        SITENAME_kw_langs AS langs
        ON langs.langId = contents.langId

Sorry for the long question. Hope you guys can help!

OldProgrammer was asking for the DBMS (Postgres, Oracle, MySQL, ...). As you think the database is the same as the "engine", I guess you are using MySQL - which is unfortunate as that is one of the (very) few DBMS to not support recursive queries. — , Jun 20 '13 at 12:35
See this answer to a similar question for ways to traverse a hierarchical tree in MySQL: http://stackoverflow.com/a/11035966/359040 — , Jun 20 '13 at 12:43
See my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462) — Bill Karwin, Jun 20 '13 at 12:58

score 0 · Answer 1 · answered Jun 20 '13 at 12:03

0

The short answer is that you can't really do this with RDBMS. The long answer is you can sort of do it either programmatically (N+1 select) or you can use common table expressions (CTE).

The other option is to cheat and use a depth column as a hint for an order by.

answered Jun 20 '13 at 12:03

Adam Gent

47,843
23
153
203

Thanks for the fast answer! As I am a bit of an SQL noob non of the above options ring any bells for me. But of course there is always google. I am curious though which option you would recommend in this case? – Raapwerk Jun 20 '13 at 12:15
Of course one can do this with a recursive CTE. And that's not "sort of" doing it. But Raapwerk is using MySQL which doesn't support this standard feature. – Jun 20 '13 at 12:37
I said "sort of" as the result sets will still be column based and not tree like (ie JSON or XML doc). When he first asked the question he didn't say what DB. – Adam Gent Jun 23 '13 at 01:14

Stefan Steiger · Accepted Answer · 2013-06-20T12:32:34.853

Below is how I do it in "our" DMS (recursive CTE), which is Adam Gent's suggestion expanded.
Note that I just see one could use COALESCE instead of nesting ISNULL.

The order by you would do according to the breadcrumbs (here Bez_Path or UID_Path).

A far better way would be to use a closure-table architecture.
See here:
http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html
and here:
http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/

The closure table also has the advantage that it works on MySQL, where CTE & recursion are not supported.

Also note that closure tables are much better (and simpler and faster to query) than recursion.
Also think about symlinks in such a structure.
The something_UID, something_parent_UID pattern (as shown below) is almost always an antipattern.

CREATE VIEW [dbo].[V_DMS_Navigation_Structure]
AS 
SELECT 
     NAV_UID 
    ,NAV_Typ 
    ,NAV_Parent_UID 
    ,NAV_Stufe 
    ,NAV_ApertureKey 
    ,NAV_Nr 
    --,NAV_Bemerkung 
    ,NAV_Status 
    ,NAV_Referenz 

    ,ISNULL(PJ_Bezeichnung, ISNULL(FO_Bezeichnung, DOC_Bezeichnung + '.' + DOC_Dateiendung)  ) AS NAV_Bezeichnung 
    ,NAV_PJ_UID 
    ,NAV_FO_UID 
    ,NAV_DOC_UID 
    ,ISNULL(NAV_PJ_UID, ISNULL(NAV_FO_UID,NAV_DOC_UID)) AS NAV_OBJ_UID 
FROM T_DMS_Navigation 

LEFT JOIN T_DMS_Projekt 
    ON T_DMS_Projekt.PJ_UID = T_DMS_Navigation.NAV_PJ_UID 

LEFT JOIN T_DMS_Folder 
    ON T_DMS_Folder.FO_UID = T_DMS_Navigation.NAV_FO_UID 

LEFT JOIN T_DMS_Dokument 
    ON T_DMS_Dokument.DOC_UID = T_DMS_Navigation.NAV_DOC_UID 








CREATE VIEW [dbo].[V_DMS_Navigation_Structure_Path]
AS 
WITH Tree 
(
     NAV_UID
    ,NAV_Bezeichnung
    ,NAV_Parent_UID
    ,Depth
    ,Sort
    ,Bez_Path
    ,UID_Path
    ,PJ_UID
    ,FO_UID
    ,DOC_UID
    ,OBJ_UID
) 
AS
(
    SELECT 
         NAV_UID 
        ,NAV_Bezeichnung 
        ,NAV_Parent_UID 
        ,0 AS Depth 
        ,CAST('0' AS varchar(10)) AS Sort 
        ,CAST(NAV_Bezeichnung AS varchar(4000)) AS Bez_Path 
        ,CAST(NAV_OBJ_UID AS varchar(4000)) AS UID_Path 
        ,NAV_PJ_UID AS PJ_UID 
        ,NAV_FO_UID AS FO_UID 
        ,NAV_DOC_UID AS DOC_UID 
        ,NAV_OBJ_UID AS OBJ_UID 
    FROM V_DMS_Navigation_Structure 

    WHERE NAV_Parent_UID IS NULL 

    UNION ALL 

    SELECT 
         CT.NAV_UID 
        ,CT.NAV_Bezeichnung 
        ,CT.NAV_Parent_UID 
        ,Parent.Depth + 1 AS Depth 
        ,CONVERT(varchar(10), Parent.Sort + '.' + CAST(Parent.Depth + 1 AS varchar(10))) AS Sort 
        ,CONVERT(varchar(4000), Parent.Bez_Path + '\' + CAST(CT.NAV_Bezeichnung AS varchar(1000))) AS Bez_Path 
        ,CONVERT(varchar(4000), Parent.UID_Path + '\' + CAST(CT.NAV_OBJ_UID AS varchar(1000))) AS UID_Path 
        ,NAV_PJ_UID AS PJ_UID 
        ,NAV_FO_UID AS FO_UID 
        ,NAV_DOC_UID AS DOC_UID 
        ,NAV_OBJ_UID AS OBJ_UID 
    FROM V_DMS_Navigation_Structure CT 

    INNER JOIN Tree AS Parent 
        ON Parent.NAV_UID = CT.NAV_Parent_UID
)

SELECT TOP 999999999999999 * FROM Tree
ORDER BY Depth

The closure table is *much* harder to maintain and to update than the adjacency list. — , Jun 20 '13 at 12:38
@a_horse_with_no_name: I agree that it's harder, but not "much harder". All you need to do is to insert the nodes self, self at depth 0 with a trigger, the rest is as is, with a slightly different sql query. You can find and copy pretty much all the code required from the web. Some minor rewrites are required for usage in MS SQL. You will realize that an adjacent list is actually more difficult, once the requirement is added that you need to be able to "link" to folders in a directory structure. — Stefan Steiger, Jun 21 '13 at 06:38

Hierarchical SQL Query

2 Answers2