SQL help needed: List all spaces with description and categories(labels?).

Andrey Rezvushkin August 6, 2018

Hi!

I want to get all spaces with description and categories. I see some categories in AO_54C900_SPACE_BLUEPRINT_AO, but they are standart. How can i see custom categories? And how i can see description about spaces? 

 

I look they on Space Directory.

1 answer

1 accepted

2 votes
Answer accepted
Pete Singleton
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 6, 2018

Try this:

SELECT S.SpaceKey, L.Name "Type", BC.Body "Description"
FROM SPACES S, CONTENT C, CONTENT_LABEL CL, LABEL L, BODYCONTENT BC
WHERE S.SpaceID=C.SpaceID
AND C.ContentID=CL.ContentID
AND BC.ContentID=C.ContentID
AND CL.LabelID=L.LabelID
AND L.Namespace ='team'
Andrey Rezvushkin August 6, 2018

Amaaaaazing! thx you sooo much!

ITS Support November 8, 2021

This query is almost what I'm after.  It only lists sites that have a category and description.  How can the query be modified to list all sites and include the category and description if available?

Babs Adewole February 25, 2022

@ITS Support . This should work

select s.spaceid, s.spacekey, s.spacename ,bc.bodydescription,l.name category
from spaces s
left join content c on c.spaceid = s.spaceid and c.contenttype = 'SPACEDESCRIPTION'
left join bodycontent bc on bc.contentid = c.contentid
left join content_label cl on cl.contentid = c.contentid
left join label l on l.labelid = cl.labelid

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events