It's not the same without you
Join the community to find out what other Atlassian users are discussing, debating and creating.
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 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
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
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
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
This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.
Read moreHi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...
Connect with like-minded Atlassian users at free events near you!
Find a groupConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.
Start an AUGYou're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.