How do we know this page is belonged which space ? Because we can not find it's spacdid field in the content table.
And also how do we this comment is belonged which page? Because we can not find it's pageid field in the content table.
Whenever you encounter a page with no
spaceid, it's most likely a older version of your current page. Only the latest page have a
spaceid. The older page will have
prever as a references to the newer page. Same goes to comment, older version of comment doesn't have a
pageid, but a
prevver referring to the updated comment.
That being said, make sure you include
prevver is null in your SQL statement to exclude all the older version of record.
Hope this helps.
For sure, the current page version is the only page that doesn't have a value in it's
prevver column. That's how Confluence determine if that's the current page version, the rest would be available in
Page History page. For example, you wish to query all the current page in your database :
SELECT * FROM CONTENT WHERE PREVVER IS NULL AND CONTENTTYPE='PAGE';
We use confluence 5.8.X version, and now we can use query as shown as below:
select * from likes order by creationdate desc
and then use the sql query as shown as below :select * from content where contentid in (the contentid from likes table ) It can collect likes as listed as below:
But some comment and page just only has prevver and pageid information, how can we use one query sentence to list like or comment by space? (It mean list like or comment list by space just only use one query sentence)
Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time! We're looking for people to participate in a remote 1-hr workshop...
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs