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:
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.
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 >= '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
One thing to note this would only get direct children ... not grandchildren and so forth.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 >= '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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mick,
I think it's what you are look after:
select title from content where (lastmoddate >= '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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Saleh,
Thanks for your answer, I will see if I can make it work and get back to you. :)
Cheers.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.