page and comment information

Dear partners, 

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.

Thank you.

3 answers

0 vote

Hi Hsu,

 

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.

Best Regards,
Jonathan 

Dear Jonathan, 

Thank you for your reply. If the command or page has many prever version, would it be possible to find out it's original pageid or spaceid ? Thank you.

Hey Hsu,

 

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';

Cheers,
Jonathan 

Dear Jonathan, 

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:

tab2.jpg

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)

Thank you.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Apr 13, 2018 in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi 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...

372 views 20 10
Join discussion

Atlassian User Groups

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!

Find my local user group

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

Groups near you