Assistance writing a SQL query to count pages with specific labels

[Daniel Knapp]

Hello,

I have this query which counts the number of pages with an ancestorID:

SELECT COUNT(*) FROM CONFANCESTORS WHERE ancestorid = '123830295';

And a query which counts all pages with specific labels:

SELECT COUNT(*) FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT_LABEL WHERE LABELID IN (SELECT LABELID FROM LABEL WHERE NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae')));

How do I 'merge' the query so I get the total number of pages with the labels: 'os-ae','os-ae-r','os-dk','os-n-ae' with the parent page: 123830295

Many thanks

[NotTheRealStephenSifersNOPENOPENOPENOPE · Accepted solution]

Hey Daniel,

Give this a try:

SELECT COUNT(*)
FROM CONTENT C1
JOIN CONTENT_LABEL C2 ON C1.CONTENTID = C2.CONTENTID
JOIN LABEL L1 ON C2.LABELID = L1.LABELID
WHERE L1.NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae')
AND C1.PARENTID = '123830295'

[Daniel Knapp]

Hi Stephen,

Thank you for your reply. 

Running your query only returns a count of 19. We have around 300 pages that is a child page of parentID 123830295.

I know for a fact that at least 250 of these pages are labelled with one of os-ae, os-ae-r, os-dk or os-n-ae. Any ideas why it's returning such a low number? 

[NotTheRealStephenSifersNOPENOPENOPENOPE]

I understand a little bit more of how you're wanting to find these now. Thank you.

I am digging into this further to get you an answer. For some reason I have not discovered yet, the numbers in the SQL query even on my end are not reconciling with the numbers I get back from Confluence.

Prime example:

I search Confluence KB space for labels with: kb-how-to-article

I see the following: Page 1 of 35. Showing 349 results (0.009 seconds)

I query SQL for label: kb-how-to-article

I see the following: (274 rows affected)

Still looking into this.

[NotTheRealStephenSifersNOPENOPENOPENOPE]

So instead of parentID I used space ID and was able to return the same count as the Confluence search:

SELECT COUNT(*)
FROM CONTENT C1
JOIN CONTENT_LABEL C2 ON C1.CONTENTID = C2.CONTENTID
JOIN LABEL L1 ON C2.LABELID = L1.LABELID
WHERE (L1.NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae'))
AND (C1.SPACEID = '123830295')
AND C1.CONTENT_STATUS = 'current'

[Daniel Knapp]

Thanks again for your response.

I ran your new query and got 0. 

Using:

Select count(*) from CONFANCESTORS where ancestorID='123830295' 

I get the total number of pages (278). 

Does this not need to be integrated into the query somehow? 

[NotTheRealStephenSifersNOPENOPENOPENOPE]

Sorry, I should have clarified. Instead of using the ancestorID, use the spaceID of the space that page resides. This will help me to true up the counts you are reporting vs what SQL is coming back with. Once I have trued up counts I can better see how to write the query for the pageID (ancestorID) you are looking for.

For this step I just want to verify numbers to ensure we're not hitting a moving target.

In the query I sent, replace the SpaceID with the space you are wanting to search within.

[Daniel Knapp]

Brilliant. 

I used the spaceID and got the results I needed. 

Thank you so much. 

[NotTheRealStephenSifersNOPENOPENOPENOPE]

That's great news. If this solved your problem please accept my answer as the solution.

Thanks and glad you were able to get this working.