Confluence Categories and Spaces link in Database

Question

Hi All,

Can anybody tell me how Spaces and Categories and Sub-categories are linked into Database.

I know about the Space_key and Space _ID under table.

I am using BreadCrumb plugin which shows the flow of my current page.

I need to know how this call is made, which tables are queried to bring the data in front.

z · Answer

Spaces can have more than one categories. In case you want them in a single row per space with MYSQL you can use the following query:

select s.spacekey, GROUP_CONCAT(l.name) from SPACES s, CONTENT c, CONTENT_LABEL cl, LABEL l 
    where s.spacetype = 'global' and s.spaceid=c.spaceid 
    and c.contentid=cl.contentid and cl.labelid=l.labelid 
    and l.namespace='team' group by s.spacekey

I also added a criteria to get only the site spaces.

Gregory Kneller · Answer

Space categories are actually labels. Here is the relation:

select s.spacekey, l.name from spaces s, content c, content_label cl, label l 
    where s.spaceid=c.spaceid 
    and c.contentid=cl.contentid
    and cl.labelid=l.labelid
    and l.namespace ='team'