Hello, We've got Pocket Query installed in our Confluence (5.4.3 download) and I want to use it to create a SQL query that does the following: * find pages in a specific space; that are sub-pages of page X; that were last edited before date X. Whilst I can read SQL to a degree, I don't write it, so do any of you wonderful people have such a thing already written? Thanks in advance.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL Query for finding pages not edited from before a spedific date

Mick Davidson_o2ws6w

Hello,

We've got Pocket Query installed in our Confluence (5.4.3 download) and I want to use it to create a SQL query that does the following:

find pages in a specific space; that are sub-pages of page X; that were last edited before date X.

Whilst I can read SQL to a degree, I don't write it, so do any of you wonderful people have such a thing already written?

Thanks in advance.

Accepted answers

All comments

salehparsa

Hi Mick,

I think it's what you are look after:

select title from content where (lastmoddate &gt;= 'YYYY-MM-DD' AND contenttype = 'ParentPageName') AND spaceid = (select spaceid from spaces where spacename ='YourSpaceName');

Let me know if I've misunderstood on what you're looking to achieve.

Cheers,

Saleh

Mick Davidson_o2ws6w

Saleh,

Thanks for your answer, I will see if I can make it work and get back to you.

Cheers.

FelipeA

Hi Mick,

I made some modifications to the query provided by my colleague, that I believe will work for you case:

SELECT title
FROM content
WHERE (lastmoddate &gt;= 'YYYY-MM-DD'
       AND contenttype = 'PAGE'
       AND content_status = 'current'
       AND prevver IS NULL
       AND parentid IN 
		(WITH RECURSIVE fcontent (contentid) AS
                (SELECT contentid
                FROM content
                WHERE title = 'ParentPageName'
                	AND prevver IS NULL
                     	AND content_status='current'
                UNION ALL SELECT c2.contentid
                FROM content c2
                INNER JOIN fcontent ON 
			fcontent.contentid = c2.parentid)
                SELECT *
                FROM fcontent))
  	AND spaceid =
		(SELECT spaceid
     		FROM spaces
     		WHERE spacename ='YourSpaceName');

Best regards,

Felipe Alencastro

Davin Studer

Just to throw it out there you could also do this using a user macro that implements the search macro. Here is what I created for my organization.

Macro Name:
stalepages

Macro Title:
Stale Pages

Macro Body Processing:
No Macro Body

Template:
## Developed by: Davin Studer
## Date created: 12/26/2013
## This will show pages that have not been modified within the last (x) number of days.

## @paramDaysBack:title=Not Modified Within Last (x) Days|type=int|required=true|desc=Pages that have not been modified within the given amount of days.
## @paramSpaceKey:title=Restrict to this Space Key|type=string|required=false|desc=This is case-sensitive. If not specified, all accessible spaces are searched.
## @paramParent:title=Parent Page|type=confluence-content|required=false|desc=This will restrict the results to only pages that are children of this parent page.

#if($paramDaysBack && $paramDaysBack != "")
##########################################################
## Convert the parameter to an integer. Even though the ##
## param type is int Confluence still treats as a ##
## string. ##
##########################################################
#set($paramDaysBack = $generalUtil.convertToInteger("-$paramDaysBack"))

##########################################################
## Take today's date and subtract the days back number. ##
##########################################################
#set($cal = $action.dateFormatter.getCalendar())
$cal.add(5, $paramDaysBack)
#set($end = $action.dateFormatter.formatGivenString("yyyyMMdd", $cal.getTime()))

##########################################################
## Get space key to use ##
##########################################################
#if($paramSpaceKey && $paramSpaceKey != "")
#set($key = $paramSpaceKey)
#else
#set($key = $content.spaceKey)
#end

##########################################################
## Get id of Parent page ##
##########################################################
#if($paramParent && $paramParent != "")
#set($containerManagerClass=$content.class.forName('com.atlassian.spring.container.ContainerManager'))
#set($getInstanceMethod=$containerManagerClass.getDeclaredMethod('getInstance',null))
#set($containerManager=$getInstanceMethod.invoke(null,null))
#set($containerContext=$containerManager.containerContext)
#set($pageManager=$containerContext.getComponent('pageManager'))

#if($pageManager)
#set($tempcontent = $pageManager.getPage($key, $paramParent))
#end
#if($tempcontent && $tempcontent != "")
#set($content = $tempcontent)
#set($id = "AND ancestorIds:$content.getIdAsString()")
#else
The page "$paramParent" was not found in the $key space.
#set($content = "")
#end
#end

#if($content != "")
##########################################################
## Render the search macro with 19000101 as the start ##
## date and our calculated back days as the end. ##
##########################################################
<ac:structured-macro ac:name="search">
<ac:parameter ac:name="query">modified:[19000101 TO
$end]$id</ac:parameter>
<ac:parameter ac:name="maxLimit">999999999</ac:parameter>
<ac:parameter ac:name="type">page</ac:parameter>
<ac:parameter ac:name="spacekey">
<ri:space ri:space-key="$key"/>
</ac:parameter>
</ac:structured-macro>
#end
#end

Davin Studer

One thing to note this would only get direct children ... not grandchildren and so forth.

FelipeA

Hi Davin,

You're right, sorry for that. Here's the new query that will also return grandchildren pages and so:

SELECT title
FROM content
WHERE (lastmoddate &gt;= 'YYYY-MM-DD'
       AND contenttype = 'PAGE'
       AND content_status = 'current'
       AND prevver IS NULL
       AND parentid IN (WITH RECURSIVE fcontent (contentid) AS
                          ( SELECT contentid
                           FROM content
                           WHERE title = 'ParentPageName'
                             AND prevver IS NULL
                             AND content_status='current'
                           UNION ALL SELECT c2.contentid
                           FROM content c2
                           INNER JOIN fcontent ON fcontent.contentid = c2.parentid)
                        SELECT *
                        FROM fcontent))
  AND spaceid =
    (SELECT spaceid
     FROM spaces
     WHERE spacename ='YourSpaceName');

Best regards,

Felipe Alencastro

FelipeA

Hi Davin,

Yes you're right. I'm sorry for that. I edited the query again to include grandchildren and so.

Best regards,

Felipe Alencastro

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

sql

confluence-administration

pocketquery